Bug #82233

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

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

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

0%

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

History

#1 Updated by Ricky Mathew 3 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 3 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 3 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 3 months ago

  • Assignee deleted (Sascha Egerer)

Also available in: Atom PDF