Bug in flushing the Previous/Next Cache

[ken]

Here is the 3.4.7 code for cleanupCache() in CRM/Core/BAO/PrevNextCache.php ...

Code: [Select]

    static function cleanupCache( ) {
        // clean up all prev next caches older than $cacheTimeIntervalDays days
        $cacheTimeIntervalDays  = 2;

        // first find all the cacheKeys that match this
        $sql = "
DELETE     pn, c
FROM       civicrm_cache c
INNER JOIN civicrm_prevnext_cache pn ON c.path = pn.cacheKey
WHERE      c.group_name = %1
AND        c.created_date < date_sub( NOW( ), INTERVAL $cacheTimeIntervalDays day )
";
        $params = array( 1 => array( 'CiviCRM Search PrevNextCache', 'String' ),
                         2 => array( $cacheTimeIntervalDays, 'Integer' ) );
        CRM_Core_DAO::executeQuery( $sql );
    }
}

The SQL string has a %1 placemarker for $params[1] but no %2 placemarker for $params[2]. What this appears to do is cause the parameter substitution to fail so I get errors like ...

Dec 13 13:02:36  [info] $Fatal Error Details = Array
(
   [callback] => Array
       (
           [ 0 ] => CRM_Core_Error
           [ 1 ] => handle
       )

   [ code ] => -2
   [message] => DB Error: syntax error
   [mode] => 16
   [debug_info] =>
DELETE     pn, c
FROM       civicrm_cache c
INNER JOIN civicrm_prevnext_cache pn ON c.path = pn.cacheKey
WHERE      c.group_name = %1
AND        c.created_date < date_sub( NOW( ), INTERVAL 2 day )
 [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 '%1
AND        c.created_date < date_sub( NOW( ), INTERVAL 2 day )' at line 4]
   [type] => DB_Error
   [user_info] =>
DELETE     pn, c
FROM       civicrm_cache c
INNER JOIN civicrm_prevnext_cache pn ON c.path = pn.cacheKey
WHERE      c.group_name = %1
AND        c.created_date < date_sub( NOW( ), INTERVAL 2 day )
 [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 '%1
AND        c.created_date < date_sub( NOW( ), INTERVAL 2 day )' at line 4]
   [to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::handle prefix="" info="
DELETE     pn, c
FROM       civicrm_cache c
INNER JOIN civicrm_prevnext_cache pn ON c.path = pn.cacheKey
WHERE      c.group_name = %1
AND        c.created_date < date_sub( NOW( ), INTERVAL 2 day )
 [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 '%1
AND        c.created_date < date_sub( NOW( ), INTERVAL 2 day )' at line 4]"]
)


Dec 13 13:02:36  [info] $backTrace = /sites/all/modules/civicrm/CRM/Core/Error.php, backtrace, 205
, 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, 2424
/sites/all/modules/civicrm/packages/DB/DataObject.php, _query, 1610
/sites/all/modules/civicrm/CRM/Core/DAO.php, query, 152
/sites/all/modules/civicrm/CRM/Core/DAO.php, query, 879
/sites/all/modules/civicrm/CRM/Core/BAO/PrevNextCache.php, executeQuery, 178
/sites/all/modules/civicrm/CRM/Core/BAO/Cache.php, cleanupCache, 233
/sites/all/modules/civicrm/CRM/Core/BAO/Cache.php, cleanupCache, 183
/sites/all/modules/civicrm/CRM/Core/Session.php, storeSessionToCache, 449
/sites/all/modules/civicrm/CRM/Utils/System.php, storeSessionObjects, 1101
/sites/all/modules/civicrm/CRM/Utils/System.php, civiExit, 331
/sites/all/modules/civicrm/CRM/Core/QuickForm/Action.php, redirect, 83
/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Process.php, popUserContext, 65
/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/State.php, handle, 135
/sites/all/modules/civicrm/CRM/Core/StateMachine.php, handleNextState, 171
/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/Contact/Page/View/GroupContact.php, run, 87
/sites/all/modules/civicrm/CRM/Contact/Page/View/GroupContact.php, edit, 130
/sites/all/modules/civicrm/CRM/Core/Invoke.php, run, 223
/sites/all/modules/civicrm/drupal/civicrm.module, invoke, 347
, civicrm_invoke,
/includes/menu.inc, call_user_func_array, 349
/index.php, menu_execute_active_handler, 17

[Donald Lobo]

this has been fixed in 4.1

BUT, i cannot see where the bug / why it is happening on your install. Can you try to track it down and see why its failing to replace %1

thanx

lobo