Bug #82233

File Abstraction Layer: Update storage index (scheduler) failure due to query constraints.

Added by Ricky Mathew 6 months ago. Updated 3 months ago.

Status:
Resolved
Priority:
Must have
Assignee:
-
Category:
scheduler
Target version:
Start date:
2017-08-29
Due date:
% Done:

100%

TYPO3 Version:
8
PHP Version:
Tags:
Complexity:
medium
Is Regression:
Sprint Focus:

Description

The File Abstraction Layer: Update storage index (scheduler) fails due to placeholder constraint in the query.This issue is related with https://forge.typo3.org/issues/80875 which addresses the same bug but related to tt_content records.But here it's sys_file records

Execution of task "File Abstraction Layer: Update storage index (scheduler)" 

failed with the following message: An exception occurred while executing 

'SELECT `uid`, `pid`, `missing`, `type`, `storage`, `identifier`, `identifier_hash`, `extension`, `mime_type`, `name`, `sha1`, `size`, `creation_date`, `modification_date`, `folder_hash` 
FROM `sys_file` 
WHERE (`storage` = ?) 
AND (`uid` NOT IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ................
.............48873, 139473, 139472, 139474, 155698, 139470, 139468, 46837, 135648, 159659, 163106, 135649]: Prepared statement contains too many placeholders

After investigating the issue i found the root cause in findInStorageAndNotInUidList() of typo3/sysext/core/Classes/Resource/Index/FileIndexRepository.php (TYPO3\CMS\Core\Resource\Index\FileIndexRepository class).I have attached a patchfile which delivers the solution.It would be great if it is possible to deliver this to gerrit with much more clean code as i'm not a member of the core team yet to directly push the patch to gerrit.

FileIndexRepository_patch.patch View (3.3 KB) Ricky Mathew, 2017-08-29 08:45


Related issues

Related to TYPO3 Core - Bug #80875: Multiple problems with relation handler if too many records exist on a page Accepted 2017-06-12

Associated revisions

Revision 93f26c24 (diff)
Added by Pawel Cieslik 3 months ago

[BUGFIX] Prepared statement contains too many placeholders

Fix method findInStorageAndNotInUidList where $uidList
contains more then 65536 records in MySQL, 64000 in Oracle,
34464 in PostgreSQL by avoiding using prepared statements
and just passing values directly in query.

Resolves: #82233
Releases: master, 8.7
Change-Id: Ic72fa540936631274ae542d3c02128e159bd3fb1
Reviewed-on: https://review.typo3.org/54801
Tested-by: TYPO3com <>
Reviewed-by: Łukasz Uznański <>
Reviewed-by: Manuel Selbach <>
Tested-by: Manuel Selbach <>
Reviewed-by: Markus Klein <>
Tested-by: Markus Klein <>

Revision d94eab59 (diff)
Added by Pawel Cieslik 3 months ago

[BUGFIX] Prepared statement contains too many placeholders

Fix method findInStorageAndNotInUidList where $uidList
contains more then 65536 records in MySQL, 64000 in Oracle,
34464 in PostgreSQL by avoiding using prepared statements
and just passing values directly in query.

Resolves: #82233
Releases: master, 8.7
Change-Id: Ic72fa540936631274ae542d3c02128e159bd3fb1
Reviewed-on: https://review.typo3.org/54805
Tested-by: TYPO3com <>
Reviewed-by: Markus Klein <>
Tested-by: Markus Klein <>

History

#1 Updated by Ricky Mathew 6 months ago

  • Related to Bug #80875: Multiple problems with relation handler if too many records exist on a page added

#2 Updated by Ricky Mathew 6 months ago

Here is the patch

--- FileIndexRepository_orig.php    2017-07-25 18:08:02.000000000 +0530
+++ FileIndexRepository.php    2017-08-28 16:44:23.837613533 +0530
@@ -26,6 +26,7 @@
 use TYPO3\CMS\Core\Utility\GeneralUtility;
 use TYPO3\CMS\Extbase\Object\ObjectManager;
 use TYPO3\CMS\Extbase\SignalSlot\Dispatcher;
+use TYPO3\CMS\Core\Database\Platform\PlatformInformation;

 /**
  * Repository Class as an abstraction layer to sys_file
@@ -459,29 +460,51 @@
      */
     public function findInStorageAndNotInUidList(ResourceStorage $storage, array $uidList)
     {
-        $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($this->table);
-
-        $queryBuilder
-            ->select(...$this->fields)
-            ->from($this->table)
-            ->where(
-                $queryBuilder->expr()->eq(
-                    'storage',
-                    $queryBuilder->createNamedParameter($storage->getUid(), \PDO::PARAM_INT)
-                )
-            );
-
+        $rows = [];
+        $connection = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable($this->table);
+        $maxBindParameters = PlatformInformation::getMaxBindParameters($connection->getDatabasePlatform());
         if (!empty($uidList)) {
-            $queryBuilder->andWhere(
+            foreach (array_chunk($uidList, $maxBindParameters - 10, true) as $chunk) {
+                $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($this->table);
+                $queryBuilder
+                    ->select(...$this->fields)
+                    ->from($this->table)
+                    ->where(
+                        $queryBuilder->expr()->eq(
+                            'storage',
+                            $queryBuilder->createNamedParameter($storage->getUid(), \PDO::PARAM_INT)
+                        )
+                    );
+                if (empty($chunk)) {
+                        continue;
+                }
+                $queryBuilder->andWhere(
                 $queryBuilder->expr()->notIn(
                     'uid',
-                    $queryBuilder->createNamedParameter($uidList, Connection::PARAM_INT_ARRAY)
+                    $queryBuilder->createNamedParameter($chunk, Connection::PARAM_INT_ARRAY)
                 )
-            );
+                );
+                $rowsChunks = $queryBuilder->execute()->fetchAll();
+                foreach ($rowsChunks as $rowChunk) {
+                    if (!in_array($rowChunk['uid'], $uidList)) {
+                        $rows[$rowChunk['uid']] = $rowChunk;
+                    }
+                }
+            }
+        }
+        else{
+                $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($this->table);
+                $queryBuilder
+                    ->select(...$this->fields)
+                    ->from($this->table)
+                    ->where(
+                        $queryBuilder->expr()->eq(
+                            'storage',
+                            $queryBuilder->createNamedParameter($storage->getUid(), \PDO::PARAM_INT)
+                        )
+                    );
+                $rows = $queryBuilder->execute()->fetchAll();
         }
-
-        $rows = $queryBuilder->execute()->fetchAll();
-
         return $rows;
     }

#3 Updated by Markus Klein 6 months ago

.. i'm not a member of the core team yet to directly push the patch to gerrit.

anyone can push to gerrit!!

https://docs.typo3.org/typo3cms/ContributionWorkflowGuide/

#4 Updated by Wouter Wolters 6 months ago

  • Assignee deleted (Sascha Egerer)

#5 Updated by Gerrit Code Review 3 months ago

  • Status changed from New to Under Review

Patch set 1 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/54801

#6 Updated by Gerrit Code Review 3 months ago

Patch set 2 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/54801

#7 Updated by Gerrit Code Review 3 months ago

Patch set 1 for branch TYPO3_8-7 of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/54805

#8 Updated by Pawel Cieslik 3 months ago

  • Status changed from Under Review to Resolved
  • % Done changed from 0 to 100

Also available in: Atom PDF