Author Topic: DB error on Contribution Submit (Read 5146 times)

Shai · May 15, 2012, 01:49:47 pm

I'm getting a

Sorry. A non-recoverable error has occurred.
DB Error: syntax error

It's happening when I'm logged out but not while I'm logged in. I have perms set up properly for anonymous users to make donations.

Where can I actually view the error? I checked on my server and I can't find it.

What should I do next to trouble shoot this.

This page was working yesterday.

Thanks for your help.

Shai Gluskin

Shai · May 15, 2012, 02:16:42 pm

I get the same error when trying to save a Civi contact when it was accessed from the Drupal user page. No problem editing/saving records when accessing Civi records from a Civi Search.

Shai · May 15, 2012, 02:49:57 pm

The URL just before the error:

https://<HOST>/civicrm/contribute/transact

and after the URL is the same:

https://<HOST>/civicrm/contribute/transact

Shai

Shai · May 15, 2012, 04:05:21 pm

Okay, so I found where to turn the debugging on. I'm running MySQL 5.1.62-cll. Here is the error:

Code: [Select]

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
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -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]"]
)

Shai · May 15, 2012, 04:12:29 pm

Here is the backtrace:

backTrace

/sites/all/modules/civicrm/CRM/Core/Error.php, backtrace, 159
, handle,
/sites/all/modules/civicrm/packages/PEAR.php, call_user_func, 931
/sites/all/modules/civicrm/packages/DB.php, PEAR_Error, 968
/sites/all/modules/civicrm/packages/PEAR.php, DB_Error, 564
/sites/all/modules/civicrm/packages/DB/common.php, raiseError, 1903
/sites/all/modules/civicrm/packages/DB/mysql.php, raiseError, 898
/sites/all/modules/civicrm/packages/DB/mysql.php, mysqlRaiseError, 327
/sites/all/modules/civicrm/packages/DB/common.php, simpleQuery, 1216
/sites/all/modules/civicrm/packages/DB/DataObject.php, query, 2428
/sites/all/modules/civicrm/packages/DB/DataObject.php, _query, 1614
/sites/all/modules/civicrm/CRM/Core/DAO.php, query, 166
/sites/all/modules/civicrm/CRM/Dedupe/BAO/RuleGroup.php, query, 246
/sites/all/modules/civicrm/CRM/Dedupe/Finder.php, fillTable, 125
/sites/all/modules/civicrm/CRM/Contribute/Form/Contribution/Confirm.php, dupesByParams, 627
/sites/all/modules/civicrm/CRM/Core/Form.php, postProcess, 254
/sites/all/modules/civicrm/CRM/Core/StateMachine.php, mainProcess, 167
/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Next.php, perform, 64
/sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php, perform, 203
/sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php, handle, 103
/sites/all/modules/civicrm/CRM/Core/Controller.php, handle, 284
/sites/all/modules/civicrm/CRM/Core/Invoke.php, run, 227
/sites/all/modules/civicrm/drupal/civicrm.module, invoke, 455
, civicrm_invoke,
/includes/menu.inc, call_user_func_array, 516
/index.php, menu_execute_active_handler, 21

Shai · May 15, 2012, 04:22:45 pm

I found the problem.

:)The default Individual de-dupe rule, which I had created as a custom rule, was the cause of the problem. I re-set the default back to the "in-built" one and now anonymous people can make donations. Error gone.

We might want to consider making it impossible to set a custom de-dupe rule as the default, or at least put big warning signs around the UI for creating a custom dedupe rule that this setting can cause errors in other settings.

Shai

Hershel · May 16, 2012, 04:53:46 am

Can you reproduce this on the demo? Looks like a bug to me.

Shai · May 17, 2012, 07:31:08 am

@Hershel,

With the current configuration of drupal.demo.civicrm.org one cannot test whether this bug can be reproduced on the demo server.

The issue is that to test the problem, a donation must be attempted while not logged in. However, the demo site does not allow Annonymous users to make donations. Drupal permissions are not accessible to the demo account.

Shai

Hershel · May 17, 2012, 07:33:46 am

You could wait a bit and see if anyone else responds here and then if not, go ahead and post an Issue.

polestar · July 13, 2012, 06:38:15 am

Dealing with a similar issue when event registration is supposed to redirect to PayPal it gives a DB syntax error.

jarune · September 10, 2012, 04:22:23 am

I get the same error when importing individual contacts.

kreynen · May 19, 2013, 05:26:33 am

I know this thread is old, but I just ran into it in 4.3.3 on a site I inherited. Since the thread doesn't end in a solution, I'm adding what worked for us. Users on this site were having this error when some users tried to register for an event. This was confusing to staff because it didn't happen for all users on an event or event all events for a specific user.

The first thing I tried was upgrading to 4.3.3. Same error for some users when trying to register for an event. The issue on this site were duplicate dedupe rules. I'm not sure how this happened, but I'm guessing there was an issue in a previous upgrade. This site had 2 Name and Address (reserved) dedupe rules and no Email (reserved) rule when looking at civicrm/contact/deduperules. To solve this, I simply dropped the corrupted civicrm_dedupe_rule and civicrm_dedupe_rule_group tables and imported clean copies from a fresh 4.3.3 install. Because this site hadn't intentionally modified any of the dedupe rules, this resolved the problem.

The reason it didn't happen to every event for users how reported the problem is that they were registering for some events while logged in and others as anonymous. It was only when they weren't logged in that the dedupe rules were trying to process during the event registrations process.

Hope this helps someone else resolve this quickly.

nzcodarnoc · May 29, 2013, 04:27:19 pm

Hey there,

Reporting the same problem with a fresh install of 4.3.2, running alongside Drupal 7

And it also showed up when I tried to create a new user under the "People" screen in Drupal.

I had set up a couple of rules to de-dupe on custom fields these rules were used to help while importing legacy data.

I removed the offending rules from the database tables and everything came back to life.

Obviously I did a select query first to find out what the ID's of the offending rows were.

arevamp · July 27, 2013, 09:20:34 am

Thanks for this thread, which helped us to solve this problem:
We had the same error here after an upgrade from joomla 1.5 and civicrm 3 to joomla 2.5 and civicrm 4.3.
Everytime when users in frontend tried to change their profile or create a new one, they got the error message:

Quote

Array ( [callback] => Array([0] => CRM_Core_Error [1] => handle)
[code] => -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]

After deleting all not standard entries in database tables dedupe_rule and dedupe_rule_group the error has disappeared!
=> maybe it would be good, to include in civicrm code at the appropriate place an error message like:

Quote

An error has occurred, please go to 'Contacts > Find and Merge Duplicate Contacts' and check your dedupe settings

CiviCRM Community Forums (archive)

News:

Author Topic: DB error on Contribution Submit (Read 5146 times)

Shai

DB error on Contribution Submit

Shai

Re: DB error on Contribution Submit

Shai

Re: DB error on Contribution Submit

Shai

Re: DB error on Contribution Submit

Shai

Re: DB error on Contribution Submit

Shai

Re: DB error on Contribution Submit

Hershel

Re: DB error on Contribution Submit

Shai

Re: DB error on Contribution Submit

Hershel

Re: DB error on Contribution Submit

polestar

Re: DB error on Contribution Submit

jarune

Re: DB error on Contribution Submit

kreynen

Re: DB error on Contribution Submit

nzcodarnoc

Re: DB error on Contribution Submit

arevamp

Re: DB error on Contribution Submit