Description
Bug Report
| Q | A |
|---|---|
| BC Break | no |
| Version | 2.17.1 |
Summary
I am experiencing an issue with Doctrine ORM's query caching mechanism, leading to Redis memory overflow. This is primarily due to the way 'limit' and 'offset' values are handled in cached queries.
Current Behavior
In my production environment, Doctrine ORM caches each query including 'limit' and 'offset' as literal values in the SQL statement, rather than as parameters. As a result, every paginated request results in a new cache entry, with the only difference being the varying 'limit' and 'offset' values. This leads to an excessive number of cache entries in Redis.
How to Reproduce
- Configure Doctrine ORM with query caching, using Redis as the cache storage.
- Run a query that includes 'limit' and 'offset' clauses.
- Increment the 'offset' value (simulate pagination) and re-execute the query.
- Observe the creation of multiple cache entries in Redis for each query variation.
See test code here
Expected Behavior
The expected behavior is for Doctrine ORM to treat 'limit' and 'offset' as parameters rather than literals in the query. This approach would allow for a single cache entry to be applicable to multiple paginated requests of the same query, thereby reducing the number of cache entries and preventing cache memory overflow.
Additional Information
Redis version: 7
PHP version: 8.2
DB: MySQL 8
Environment: Production
Addressing this issue is critical for optimizing query caching efficiency in Doctrine ORM, especially for applications that heavily rely on pagination.