Bug #106006
openFlush pages cache can run in an MySQL error on large pages
0%
Description
We have a page with a product plugin on it. This page is cachable so TYPO3 generates for every product an cache entry. In this example it is the page with the UID: 5. Now TYPO3 generates a cache_page_tag "pageId_5" and in cache_pages the real cache for every product. In my case we have 18 languages with 4000 products each language. In some situations TYPO3 have to clean the cache from multiple tags so that we have more than ~80k cache entries.
If TYPO3 decides to clean the cache for that page, it uses one of these methods in the Typo3DatabaseBackend.php.
/**
* Removes all entries tagged by any of the specified tags. Performs the SQL
* operation as a bulk query for better performance.
*
* @param string[] $tags
*/
public function flushByTags(array $tags)
{
$this->throwExceptionIfFrontendDoesNotExist();
if (empty($tags)) {
return;
}
$connection = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable($this->cacheTable);
// A large set of tags was detected. Process it in chunks to guard against exceeding
// maximum SQL query limits.
if (count($tags) > 100) {
$chunks = array_chunk($tags, 100);
array_walk($chunks, [$this, 'flushByTags']);
return;
}
// VERY simple quoting of tags is sufficient here for performance. Tags are already
// validated to not contain any bad characters, e.g. they are automatically generated
// inside this class and suffixed with a pure integer enforced by DB.
$quotedTagList = array_map(static function ($value) {
return '\'' . $value . '\'';
}, $tags);
$queryBuilder = $connection->createQueryBuilder();
$result = $queryBuilder->select('identifier')
->from($this->tagsTable)
->where('tag IN (' . implode(',', $quotedTagList) . ')')
// group by is like DISTINCT and used here to suppress possible duplicate identifiers
->groupBy('identifier')
->executeQuery();
$cacheEntryIdentifiers = $result->fetchFirstColumn();
$quotedIdentifiers = $queryBuilder->createNamedParameter($cacheEntryIdentifiers, Connection::PARAM_STR_ARRAY);
$queryBuilder->delete($this->cacheTable)
->where($queryBuilder->expr()->in('identifier', $quotedIdentifiers))
->executeStatement();
$queryBuilder->delete($this->tagsTable)
->where($queryBuilder->expr()->in('identifier', $quotedIdentifiers))
->executeStatement();
}
/**
* Removes all cache entries of this cache which are tagged by the specified tag.
*
* @param string $tag The tag the entries must have
*/
public function flushByTag($tag)
{
$this->throwExceptionIfFrontendDoesNotExist();
if (empty($tag)) {
return;
}
$connection = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable($this->cacheTable);
$quotedTag = '\'' . $tag . '\'';
$queryBuilder = $connection->createQueryBuilder();
$result = $queryBuilder->select('identifier')
->from($this->tagsTable)
->where('tag = ' . $quotedTag)
// group by is like DISTINCT and used here to suppress possible duplicate identifiers
->groupBy('identifier')
->executeQuery();
$cacheEntryIdentifiers = $result->fetchFirstColumn();
$quotedIdentifiers = $queryBuilder->createNamedParameter($cacheEntryIdentifiers, Connection::PARAM_STR_ARRAY);
$queryBuilder->delete($this->cacheTable)
->where($queryBuilder->expr()->in('identifier', $quotedIdentifiers))
->executeStatement();
$queryBuilder->delete($this->tagsTable)
->where($queryBuilder->expr()->in('identifier', $quotedIdentifiers))
->executeStatement();
}
The problem is that there is no limit at the delete process. In my case the MySQL couldn't complete the task it was taking too long
Updated by Christian Kuhn 10 days ago
What's your suggestion? Implement chunking for the 4 delete queries if $cacheEntryIdentifiers returns a huge list?
Updated by Pascal Geldmacher 8 days ago
I don't know if this a solution in general but I solved the problem to rewrite that part with this code:
/**
* Removes all entries tagged by any of the specified tags. Performs the SQL
* operation as a bulk query for better performance.
*
* @param string[] $tags
*/
public function flushByTags(array $tags)
{
$this->throwExceptionIfFrontendDoesNotExist();
if (empty($tags)) {
return;
}
$connection = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable($this->cacheTable);
// A large set of tags was detected. Process it in chunks to guard against exceeding
// maximum SQL query limits.
if (count($tags) > 100) {
$chunks = array_chunk($tags, 100);
array_walk($chunks, [$this, 'flushByTags']);
return;
}
// VERY simple quoting of tags is sufficient here for performance. Tags are already
// validated to not contain any bad characters, e.g. they are automatically generated
// inside this class and suffixed with a pure integer enforced by DB.
$quotedTagList = array_map(static function ($value) {
return '\'' . $value . '\'';
}, $tags);
$queryBuilder = $connection->createQueryBuilder();
$result = $queryBuilder->select('identifier')
->from($this->tagsTable)
->where('tag IN (' . implode(',', $quotedTagList) . ')')
// group by is like DISTINCT and used here to suppress possible duplicate identifiers
->groupBy('identifier')
->executeQuery();
$cacheEntryIdentifiers = $result->fetchFirstColumn();
if(!empty($cacheEntryIdentifiers)) {
$this->flushCacheByCacheEntryIdentifiers($cacheEntryIdentifiers);
}
}
/**
* Removes all cache entries of this cache which are tagged by the specified tag.
*
* @param string $tag The tag the entries must have
*/
public function flushByTag($tag)
{
$this->throwExceptionIfFrontendDoesNotExist();
if (empty($tag)) {
return;
}
$connection = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable($this->cacheTable);
$quotedTag = '\'' . $tag . '\'';
$queryBuilder = $connection->createQueryBuilder();
$result = $queryBuilder->select('identifier')
->from($this->tagsTable)
->where('tag = ' . $quotedTag)
// group by is like DISTINCT and used here to suppress possible duplicate identifiers
->groupBy('identifier')
->executeQuery();
$cacheEntryIdentifiers = $result->fetchFirstColumn();
if(!empty($cacheEntryIdentifiers)){
$this->flushCacheByCacheEntryIdentifiers($cacheEntryIdentifiers);
}
}
private function flushCacheByCacheEntryIdentifiers(array $cacheEntryIdentifiers): void
{
$cacheEntryIdentifiers = array_diff($cacheEntryIdentifiers, $productEntries);
// A large set of tags was detected. Process it in chunks to guard against exceeding
// maximum SQL query limits.
if (count($cacheEntryIdentifiers) > 1000) {
$chunks = array_chunk($cacheEntryIdentifiers, 1000);
array_walk($chunks, [$this, 'flushCacheByCacheEntryIdentifiers']);
return;
}
$queryBuilder = $connection->createQueryBuilder();
$quotedIdentifiers = $queryBuilder->createNamedParameter($cacheEntryIdentifiers, ArrayParameterType::STRING);
$queryBuilder->delete($this->cacheTable)
->where($queryBuilder->expr()->in('identifier', $quotedIdentifiers))
->executeStatement();
$queryBuilder->delete($this->tagsTable)
->where($queryBuilder->expr()->in('identifier', $quotedIdentifiers))
->executeStatement();
}
I added a new function which will used by flushBytag and flushByTags. this function also creates chunks.