Project

General

Profile

Actions
Copy link

Bug #82233

closed

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

Added by Ricky Mathew about 7 years ago. Updated about 6 years ago.

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

100%

Estimated time:
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.

Files

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

Related issues 1 (1 open — 0 closed)

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

Actions
Actions
Copy link
 #1

Updated by Ricky Mathew about 7 years ago

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

Updated by Ricky Mathew about 7 years 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;
     }
Actions
Copy link
 #3

Updated by Markus Klein about 7 years 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/

Actions
Copy link
 #4

Updated by Wouter Wolters about 7 years ago

  • Assignee deleted (Sascha Egerer)
Actions
Copy link
 #5

Updated by Gerrit Code Review almost 7 years 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

Actions
Copy link
 #6

Updated by Gerrit Code Review almost 7 years 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

Actions
Copy link
 #7

Updated by Gerrit Code Review almost 7 years 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

Actions
Copy link
 #8

Updated by Pawel Cieslik almost 7 years ago

  • Status changed from Under Review to Resolved
  • % Done changed from 0 to 100
Actions
Copy link
 #9

Updated by Benni Mack about 6 years ago

  • Status changed from Resolved to Closed
Actions
Copy link

Also available in: Atom PDF