CiviCRM Community Forums (archive)

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
        )

   

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


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
        )

   

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


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).