Connection::executeCacheQuery: unexpected behaviour if using an in-memory object cache

Question

Connection::executeCacheQuery: unexpected behaviour if using an in-memory object cache

Closed this issue a month ago · 2 comments

Bug Report

Q	A
Version	4.2.1

Summary

This issue is not necessarily a DBAL problem, but could be easily solved in DBAL, and leads to unexpected behaviour if using an in-memory cache which stores the cache objects as object references in memory.

If the Caching engine just stores the items to cache in memory, as object references, we get the SAME instance of the internal ArrayResult when using Connection::executeCacheQuery on the same query, with the internal iterator pointer still increased. So we cannot iterate over a Result to a cached query when fetching the 2nd time.

Current behavior

Doctrine\DBAL\Connection::executeCacheQuery stores the result as an Doctrine\DBAL\Cache\ArrayResult in an externally configured Caching engine.
This happens in vendor/doctrine/dbal/src/Connection.php, in the executeCacheQuery method, on about line 832-843:

	$rows = $result->fetchAllNumeric();
	$value[$realKey] = new ArrayResult($columnNames, $rows);
	$item->set($value);
	$lifetime = $qcp->getLifetime();
	if ($lifetime > 0) {
		$item->expiresAfter($lifetime);
	}
	$resultCache->save($item);
// ....

This is correct behaviour, the external Cache engine gets the result, now wrapped in an ArrayResult Object, and stores it in the cache.

Now there are Caching engines that do not serialize the cached objects, but keep them in memory as object references:
Examples of such caching engines are:

PHPFastCache with the useStaticItemCaching option enabled (default),
Symfony's Array Cache Adapter when configured accordingly: $cache = new \Symfony\Component\Cache\Adapter\ArrayAdapter(defaultLifetime: 60, storeSerialized: false);

Now if we iterate over the Result of such a cached query, the internal ArrayResult object is iterated, and its internal iterator pointer is increased (ArrayResult::$num):

$qcp = new QueryCacheProfile(60);
$res = $conn->executeCacheQuery('SELECT * FROM testdata', [], [], $qcp)->fetchAllAssociative();
print_r(count($res)); // some number > 0

Now if we execute the SAME query a 2nd time, and its result is already cached in the in-memory object cache,
we get another result:

$qcp = new QueryCacheProfile(60);
$res = $conn->executeCacheQuery('SELECT * FROM testdata', [], [], $qcp)->fetchAllAssociative();
print_r(count($res)); // some number > 0

$res = $conn->executeCacheQuery('SELECT * FROM testdata', [], [], $qcp)->fetchAllAssociative();
print_r(count($res)); // 0!

The cause

This happens because of the storing mechanism of the mentioned external Caching library: If the Caching enging just stores the items to cache in memory, as object references,
we get the SAME instance of the ArrayResult object, with the internal iterator pointer still increased.

This happens when fetching the items from the Cache, in vendor/doctrine/dbal/src/Connection.php, in the executeCacheQuery method, on about line 810-823:

        $item = $resultCache->getItem($cacheKey);
        if ($item->isHit()) {
            $value = $item->get();
            if (! is_array($value)) {
                $value = [];
            }
			// --> Here, ArrayResult MIGHT still be the same instance as from the previous query:
            if (isset($value[$realKey]) && $value[$realKey] instanceof ArrayResult) {
                return new Result($value[$realKey], $this);
            }
        } else {
            $value = [];
        }

We therefore cannot (re-)iterate over the result of the 2nd call to executeCacheQuery: The internal ArrayResult is still the same object instance, with its iterator pointer not resetted.

Expected behavior

As said initially, this is not necessarily a DBAL problem: It is a problem that can be solved by configuring the Caching engine to (de-)serialize the cached objects, instead of keep them in memory as object references. But it would be a small detail / change to be fixed in the DBAL code:

We just need a way to rewind the ArrayResult iterator: This could e.g. be done in the executeCacheQuery method, on about line 818-820:

Actual behaviour

// Instead of just returning the untouched ArrayResult:
if (isset($value[$realKey]) && $value[$realKey] instanceof ArrayResult) {
	return new Result($value[$realKey], $this);
}

Possible solution:

// Reset the internal iterator pointer of the ArrayResult
if (isset($value[$realKey]) && $value[$realKey] instanceof ArrayResult) {
	$arrResult = $value[$realKey];
	$arrResult->rewind();
	return new Result($arrRes, $this);
}

... while we have to implement the rewind() method in ArrayResult, too:

<?php

namespace Doctrine\DBAL\Cache;
// ......
final class ArrayResult implements Result
{
	// ......
    public function rewind()
    {
        $this->num = 0;
    }
}

How to reproduce

The following script reproduces the problem: It uses a Symfony in-memory-cache, with an in-memory sqlite DB:

<?php

// you need symfony/cache: `composer require symfony/cache`
// and of course dbal: `composer require doctrine/dbal`
require_once(__DIR__ . '/vendor/autoload.php');

use Doctrine\DBAL\Cache\QueryCacheProfile;
use Doctrine\DBAL\DriverManager;

// Configure an in-memory-cache that keeps the Objects as references in memory:
// install symfony cache with: `composer require symfony/cache`
$cache = new \Symfony\Component\Cache\Adapter\ArrayAdapter(defaultLifetime: 60, storeSerialized: false);

// Connection setup: in-memory sqlite, including a Query cache:
$connectionParams = [
    'dbname' => ':memory',
    'driver' => 'pdo_sqlite',
];
$conn = DriverManager::getConnection($connectionParams);
$conn->getConfiguration()->setResultCache($cache);

// insert some test data:
$conn->executeStatement("CREATE TABLE testdata (id INTEGER PRIMARY KEY, name VARCHAR(100))");
for ($i = 0; $i < 10; $i++) {
    $conn->executeStatement("INSERT INTO testdata (id, name) VALUES ({$i}, 'name{$i}')");
}

// First query: Populates the cache and returns the correc result:
$qcp = new QueryCacheProfile(60);
$res = $conn->executeCacheQuery('SELECT count(*) FROM testdata', [], [], $qcp)->fetchOne();
var_dump($res); // -> 10

// Second query: Returns the result from the cache, but cannot read it, because the ArrayResult is aready iterated:
$res = $conn->executeCacheQuery('SELECT count(*) FROM testdata', [], [], $qcp)->fetchOne();
var_dump($res); // -> false

Discussion

The mentioned problem above is a real-life issue found while using phpfastcache in its default configuration. It would be a small but useful enhancement of DBAL's cached query behaviour if this could be fixed, even if it is not a DBAL issue per se.

If you want, I can also implement the solution and issue a Pull Request, but before I do the work, I want to know if this is wanted and approved first.

Thanks for your consideration!

Answer 1 · 2024-11-11T09:48:39.000Z

I just saw that this is already solved here:

#6552

so this issue is therefore obsolete and solved.

Answer 2 · 2024-12-13T00:28:42.000Z

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.