DB Error: Syntax Error

[prickeke]

Today I upgraded from 1.7 to 2.2.0.  Wow.  How fun!

I am wondering if someone can tell me if I did something wrong based on this dump I am getting or if there is some other bug.  I filed a Jira Bug report (http://issues.civicrm.org/jira/browse/CRM-4253?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=26037#action_26037).  Basically, when I was attempting to do a fuzzy merge of an individual contact I got this, and now when attempting to use the paypal through "test-drive"-ing my contribution page I get this:

Here is the dump:

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

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 ')' at line 8, 1064

Return to home page.

Error Details:

Array
(
    [callback] => Array
        (
           

• => CRM_Core_Error
[1] => handle
        )

   

Code: [Select]

=> -2
    [message] => DB Error: syntax error
    [mode] => 16
    [debug_info] =>
DELETE     g
FROM       civicrm_group_contact_cache g
INNER JOIN civicrm_contact c ON c.id = g.contact_id
WHERE      g.group_id IN (
    SELECT id
    FROM   civicrm_group
    WHERE  TIMESTAMPDIFF(MINUTE, cache_date, NOW()) >   
)
 [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 ')' at line 8]
    [type] => DB_Error
    [user_info] =>
DELETE     g
FROM       civicrm_group_contact_cache g
INNER JOIN civicrm_contact c ON c.id = g.contact_id
WHERE      g.group_id IN (
    SELECT id
    FROM   civicrm_group
    WHERE  TIMESTAMPDIFF(MINUTE, cache_date, NOW()) >   
)
 [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 ')' at line 8]
    [to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::handle prefix="" info="
DELETE     g
FROM       civicrm_group_contact_cache g
INNER JOIN civicrm_contact c ON c.id = g.contact_id
WHERE      g.group_id IN (
    SELECT id
    FROM   civicrm_group
    WHERE  TIMESTAMPDIFF(MINUTE, cache_date, NOW()) >   
)
 [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 ')' at line 8]"]
)


[prickeke]

I found a short-term fix as posted on the jira site:

I did a grep for some of the terms in the query.  Poking around the code I see that the query is in the file: sites/all/modules/civicrm/CRM/Contact/BAO/GroupContactCache.php near line 140.

I found the issue was caused due to my smart group cache timeout not being set.  I set it to 15 minutes and this problem went away.

The value can be set using the "Global Settings" >> "Miscellaneous Settings."

I am wondering about the code around this query, if my cache timeout setting wasn't set then should the "isset()" function take care of this issue?  How does the php "isset()" function work?  Does something else need to be used here?  For whatever reason it wasn't appending "0" to the end when my value was not set.

[timbreese]

I have the same problem but it was not fixed by the method that you suggested: I changed my Server cache settings to ON and at 15 minutes. Is that what you meant?

I was trying to upgrade to CiviCRM 2.2 and it said that it installed successfully.

Here is the error page:

Sorry. A non-recoverable error has occurred.

DB Error: syntax error

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 '(MINUTE, cache_date, NOW()) > 0 )' at line 7, 1064

Error Details:

Array
(
    [callback] => Array
        (
           

• => CRM_Core_Error
[1] => handle
        )

   

Code: [Select]

=> -2
    [message] => DB Error: syntax error
    [mode] => 16
    [debug_info] =>
DELETE     g
FROM       civicrm_group_contact_cache g
INNER JOIN civicrm_contact c ON c.id = g.contact_id
WHERE      g.group_id IN (
    SELECT id
    FROM   civicrm_group
    WHERE  TIMESTAMPDIFF(MINUTE, cache_date, NOW()) > 0   
)
 [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 '(MINUTE, cache_date, NOW()) > 0   
)' at line 7]
    [type] => DB_Error
    [user_info] =>
DELETE     g
FROM       civicrm_group_contact_cache g
INNER JOIN civicrm_contact c ON c.id = g.contact_id
WHERE      g.group_id IN (
    SELECT id
    FROM   civicrm_group
    WHERE  TIMESTAMPDIFF(MINUTE, cache_date, NOW()) > 0   
)
 [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 '(MINUTE, cache_date, NOW()) > 0   
)' at line 7]
    [to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::handle prefix="" info="
DELETE     g
FROM       civicrm_group_contact_cache g
INNER JOIN civicrm_contact c ON c.id = g.contact_id
WHERE      g.group_id IN (
    SELECT id
    FROM   civicrm_group
    WHERE  TIMESTAMPDIFF(MINUTE, cache_date, NOW()) > 0   
)
 [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 '(MINUTE, cache_date, NOW()) > 0   
)' at line 7]"]
)


[Donald Lobo]

please fill your forum profile and enter your php/mysql versions

if i had to guess, you are using an older (4.x) version of mysql. civicrm requires a recent version of 5.0.x

lobo

[prickeke]

I'm not sure what your issue is, the syntax seems correct, but obviously it is not.

A temporary fix for the issue I was seeing is as follows (This is done on 2.2.0):

+ Click on "Adminster CiviCRM"
+ Click on "Global Settings"
+ Click on "Miscellaneous Settings"
+ Change the "Smart group cache timeout" from a blank value to something, such as 1 or 15.  The units are in minutes.

The permanent fix is checked in for 2.2.1 or 2.2.2 and handles the case where the value is blank.  In your case you have a "0" after the ">" sign on the last line there.  I my case I didn't have it.  In my case I see the syntax error (the "0" is missing).  Perhaps someone else can see something else wrong with your SQL syntax there (looks like Donald Lobo beat me to this post, but I'll post the temporary fix anyway).

[1green1]

FYI, with the same error, this fix worked for me. Took a while to find the answer in the forums, but it's nice to be able to! Thanks!