KohanaPHP » Kohana v2.x

In the below test case, queries are being cached across databases and invalid results are returned. The sleep calls are to identify when caching is and is not taking place.

// Server and credentials
$connection = array(
  'type' => 'mysql',
  'host' => 'localhost',
  'socket' => TRUE,
  'user' => 'root',
  'pass' => 'password',
);

$db1 = Database::instance('first', array(
  'cache' => TRUE,
  'connection' => array_merge($connection, array('database' => 'testfirst')),
));

$db2 = Database::instance('second', array(
  'cache' => TRUE,
  'connection' => array_merge($connection, array('database' => 'testsecond')),
));

$db1->query('CREATE TABLE testtable ( firstfield integer )');
$db1->query('INSERT INTO testtable VALUES ( 1 )');
$db1->query('INSERT INTO testtable VALUES ( 2 )');

// CREATE statement is cached
$db1->clear_cache();

$db2->query('CREATE TABLE testtable ( firstfield integer )');
$db2->query('INSERT INTO testtable VALUES ( 5 )');

// Clear all caches
$db1->clear_cache();
$db2->clear_cache();

// These queries should return different results
$result1 = $db1->query('SELECT *, SLEEP(0.5) FROM testtable')->as_array(); // 1
$result2 = $db2->query('SELECT *, SLEEP(0.5) FROM testtable')->as_array(); // 2

$db2->clear_cache();

// Correct results are obtained after clearing the cache
$result3 = $db2->query('SELECT *, SLEEP(0.5) FROM testtable')->as_array(); // 3

// These queries should still be cached
$db1->query('SELECT *, SLEEP(0.5) FROM testtable')->as_array(); // 4
$db2->query('SELECT *, SLEEP(0.5) FROM testtable')->as_array(); // 5

$db1->query('DROP TABLE testtable');

// DROP statement is cached
$db1->clear_cache();

$db2->query('DROP TABLE testtable');

• Queries 1 and 3 are not cached.
• Queries 2, 4, and 5 are cached.
• Results 1 and 2 match.
• Results 2 and 3 differ.

• Queries 1, 2, and 3 should not be cached.
• Queries 4 and 5 should be cached.
• Results 1 and 2 should differ.
• Results 2 and 3 should match.

Solution:

Query caching should happen per DB config group.