syntax error

[carolineb]

Hi
I have a file of 66 contacts to import. The file has abotu 6 columns including first name, last name and email which are all matched (I woudl prefer just to match on email but I don't seem to have that option)
I have done this a lot of times!

However - this time when I tried to import the data it - using the update function - I got all the way through only to get a syntax error. Repeated it abotu 4 times but with the same result.

Also a bit odd, I have always imported data and either skipped or updated without specifying a dedupe rule, but on this system I seemed to have to create one (email, use without supervision) to get the import to work. (I managed to get the import to work when it was only the email and one other field)

Can anyone give me some tips on how to start troubleshooting. I have no idea where or what the logs are so if that is the starting point any tips on where i find them would be appreciated.

Thank you in advance!

[Eileen]

What version of CiviCRM? You might be able to turn on debugging & get a backtrace

[JonGold]

Hi Caroline,

First things first:  You CAN match on just the e-mail address.  You will need to create an appropriate duplicate rule (Contacts menu > Find and Merge Duplicate Contacts) and specify that rule on the first import screen.  I wouldn't use the built-in rule, which is misleadingly named.

With that out of the way:
* "Syntax Error" isn't, by itself, very informative.  You can get a much more detailed error by turning on debugging and backtraces.  Those are both available on the Administer menu > System Settings > Debugging and Error Handling page.  Turn them both on, then when you get the syntax error you'll have a much more detailed error.  Posting THAT error will help us troubleshoot better.
* Also, could you please report your version of CiviCRM, as well as which CMS you're using and the CMS version?  It's helpful if you fill out your forum profile with that info (you can see how it's listed beneath my user icon on the forum).
* Finally - I'm afraid your attachment didn't make it through to this ticket!

[carolineb]

Thank you both - I have now turned on the debugging and the backtrace and I have got the extra information (pasted  below) - any thoughts very welcome!
What is strange is that I can now see that the upload was actually successful and does not appear to have created duplicates either BUT the error still occurs at the end.

The version of civicrm is Powered by CiviCRM 4.4.6

And one further question (Jon Gold)
You CAN match on just the e-mail address.  You will need to create an appropriate duplicate rule (Contacts menu > Find and Merge Duplicate Contacts) and specify that rule on the first import screen.  I wouldn't use the built-in rule, which is misleadingly named.

I did create a rule which I called email, under the rules for individual contacts. However ont he next screen first name, surname and email all were red with the little star saying "match to contact". I have included a screen grab of my rule. I instinctive;ly feel this is the problem.

Many many thanks

backTrace


#0 /home/teamgs/www/www/sites/all/modules/civicrm/CRM/Core/Error.php(149): CRM_Core_Error::backtrace()
#1 [internal function](): CRM_Core_Error::handle(Object(DB_Error))
#2 /home/teamgs/www/www/sites/all/modules/civicrm/packages/PEAR.php(931): call_user_func((Array:2), Object(DB_Error))
#3 /home/teamgs/www/www/sites/all/modules/civicrm/packages/DB.php(969): PEAR_Error->PEAR_Error("DB Error: syntax error", -2, 16, (Array:2), "INSERT INTO dedupe (id1, id2, weight) SELECT 0 id1, 0 id2, 0 weight LIMIT 0 G...")
#4 /home/teamgs/www/www/sites/all/modules/civicrm/packages/PEAR.php(564): DB_Error->DB_Error(-2, 16, (Array:2), "INSERT INTO dedupe (id1, id2, weight) SELECT 0 id1, 0 id2, 0 weight LIMIT 0 G...")
#5 /home/teamgs/www/www/sites/all/modules/civicrm/packages/DB/common.php(1905): PEAR->raiseError(NULL, -2, NULL, NULL, "INSERT INTO dedupe (id1, id2, weight) SELECT 0 id1, 0 id2, 0 weight LIMIT 0 G...", "DB_Error", TRUE)
#6 /home/teamgs/www/www/sites/all/modules/civicrm/packages/DB/mysql.php(898): DB_common->raiseError(-2, NULL, NULL, NULL, "1064 ** You have an error in your SQL syntax; check the manual that correspon...")
#7 /home/teamgs/www/www/sites/all/modules/civicrm/packages/DB/mysql.php(327): DB_mysql->mysqlRaiseError()
#8 /home/teamgs/www/www/sites/all/modules/civicrm/packages/DB/common.php(1216): DB_mysql->simpleQuery("INSERT INTO dedupe (id1, id2, weight) SELECT 0 id1, 0 id2, 0 weight LIMIT 0 G...")
#9 /home/teamgs/www/www/sites/all/modules/civicrm/packages/DB/DataObject.php(2421): DB_common->query("INSERT INTO dedupe (id1, id2, weight) SELECT 0 id1, 0 id2, 0 weight LIMIT 0 G...")
#10 /home/teamgs/www/www/sites/all/modules/civicrm/packages/DB/DataObject.php(1613): DB_DataObject->_query("INSERT INTO dedupe (id1, id2, weight) SELECT 0 id1, 0 id2, 0 weight LIMIT 0 G...")
#11 /home/teamgs/www/www/sites/all/modules/civicrm/CRM/Core/DAO.php(160): DB_DataObject->query("INSERT INTO dedupe (id1, id2, weight) SELECT 0 id1, 0 id2, 0 weight LIMIT 0 G...")
#12 /home/teamgs/www/www/sites/all/modules/civicrm/CRM/Dedupe/BAO/RuleGroup.php(254): CRM_Core_DAO->query("INSERT INTO dedupe (id1, id2, weight) SELECT 0 id1, 0 id2, 0 weight LIMIT 0 G...")
#13 /home/teamgs/www/www/sites/all/modules/civicrm/CRM/Dedupe/Finder.php(122): CRM_Dedupe_BAO_RuleGroup->fillTable()
#14 /home/teamgs/www/www/sites/all/modules/civicrm/CRM/Utils/DeprecatedUtils.php(1401): CRM_Dedupe_Finder::dupesByParams((Array:4), "Individual", "Unsupervised", (Array:0), "12")
#15 /home/teamgs/www/www/sites/all/modules/civicrm/CRM/Contact/Import/Parser/Contact.php(1656): _civicrm_api3_deprecated_contact_check_params((Array:6), TRUE, TRUE, FALSE, "12")
#16 /home/teamgs/www/www/sites/all/modules/civicrm/CRM/Contact/Import/Parser/Contact.php(680): CRM_Contact_Import_Parser_Contact->createContact((Array:6), (Array:34), "4", NULL, TRUE, "12")
#17 /home/teamgs/www/www/sites/all/modules/civicrm/CRM/Contact/Import/Parser.php(208): CRM_Contact_Import_Parser_Contact->import("4", (Array:13), NULL)
#18 /home/teamgs/www/www/sites/all/modules/civicrm/CRM/Contact/Import/ImportJob.php(276): CRM_Contact_Import_Parser->run("civicrm_import_job_548a0b1d390decd0ab6ddf6759bd1786", (Array:10), 8, 1, "_id", "_status", "4", "704a4cc66fce88fa91440da2465d8771", 61, NULL, 30, NULL, "12")
#19 /home/teamgs/www/www/sites/all/modules/civicrm/CRM/Contact/Import/Form/Preview.php(282): CRM_Contact_Import_ImportJob->runImport(Object(CRM_Contact_Import_Form_Preview))
#20 /home/teamgs/www/www/sites/all/modules/civicrm/CRM/Core/Form.php(261): CRM_Contact_Import_Form_Preview->postProcess()
#21 /home/teamgs/www/www/sites/all/modules/civicrm/CRM/Core/StateMachine.php(160): CRM_Core_Form->mainProcess()
#22 /home/teamgs/www/www/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Next.php(60): CRM_Core_StateMachine->perform(Object(CRM_Contact_Import_Form_Preview), "next", "Next")
#23 /home/teamgs/www/www/sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Next->perform(Object(CRM_Contact_Import_Form_Preview), "next")
#24 /home/teamgs/www/www/sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Contact_Import_Form_Preview), "next")
#25 /home/teamgs/www/www/sites/all/modules/civicrm/CRM/Core/Controller.php(339): HTML_QuickForm_Page->handle("next")
#26 /home/teamgs/www/www/sites/all/modules/civicrm/CRM/Core/Invoke.php(323): CRM_Core_Controller->run((Array:3), NULL)
#27 /home/teamgs/www/www/sites/all/modules/civicrm/CRM/Core/Invoke.php(72): CRM_Core_Invoke::runItem((Array:13))
#28 /home/teamgs/www/www/sites/all/modules/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:3))
#29 /home/teamgs/www/www/sites/all/modules/civicrm/drupal/civicrm.module(456): CRM_Core_Invoke::invoke((Array:3))
#30 [internal function](): civicrm_invoke("import", "contact")
#31 /home/teamgs/www/www/includes/menu.inc(517): call_user_func_array("civicrm_invoke", (Array:2))
#32 /home/teamgs/www/www/index.php(21): menu_execute_active_handler()
#33 {main}



Sorry but we are not able to provide this at the moment.

--------------------------------------------------------------------------------

DB Error: syntax error
Error Details
Database Error Code: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY id1, id2 ON DUPLICATE KEY UPDATE weight = weight + VALUES(weight)' at line 1, 1064
Additional Details:

Array
(
    [callback] => Array
        (
           

• => CRM_Core_Error
[1] => handle
        )

   

Code: [Select]

=> -2
    [message] => DB Error: syntax error
    [mode] => 16
    [debug_info] => INSERT INTO dedupe (id1, id2, weight) SELECT 0 id1, 0 id2, 0 weight LIMIT 0 GROUP BY id1, id2 ON DUPLICATE KEY UPDATE weight = weight + VALUES(weight) [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY id1, id2 ON DUPLICATE KEY UPDATE weight = weight + VALUES(weight)' at line 1]
    [type] => DB_Error
    [user_info] => INSERT INTO dedupe (id1, id2, weight) SELECT 0 id1, 0 id2, 0 weight LIMIT 0 GROUP BY id1, id2 ON DUPLICATE KEY UPDATE weight = weight + VALUES(weight) [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY id1, id2 ON DUPLICATE KEY UPDATE weight = weight + VALUES(weight)' at line 1]
    [to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT INTO dedupe (id1, id2, weight) SELECT 0 id1, 0 id2, 0 weight LIMIT 0 GROUP BY id1, id2 ON DUPLICATE KEY UPDATE weight = weight + VALUES(weight) [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY id1, id2 ON DUPLICATE KEY UPDATE weight = weight + VALUES(weight)' at line 1]"]
)



Return to home page.


[JonGold]

Hi Caroline,

I think you're right; I think your e-mail weight and rule threshold have to be a number greater than zero.  Try making them both a "1" and try again?

Also, the red fields that indicate match fields don't change when you use an alternate dedupe rule, you can safely ignore which fields are red when using a custom dedupe rule.

Jon