Bug #80898

No page tree in a workspaces

Added by Xavier Perseguers about 2 years ago. Updated 8 months ago.

Status:
Closed
Priority:
Must have
Category:
Workspaces
Target version:
Start date:
2017-04-19
Due date:
% Done:

100%

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

Description

I just debugged a problem that the page tree was not shown when in a workspaces. The AJAX request actually timed out.

I finally nailed it down to method \TYPO3\CMS\Workspaces\Service\WorkspaceService::fetchPagesWithVersionsInTable and its query for tableName sys_file_reference.

This is the culprit query:

SELECT `B`.`pid` AS `pageId` FROM `sys_file_reference` `A`, `sys_file_reference` `B` WHERE (`A`.`pid` = -1) AND (`A`.`t3ver_wsid` = 1) AND (((`A`.`t3ver_oid` = `B`.`uid`) AND (`A`.`t3ver_state` <> 4)) OR ((`A`.`t3ver_oid` = `B`.`t3ver_move_id`) AND (`A`.`t3ver_state` = 4))) AND ((`A`.`deleted` = 0) AND (`B`.`deleted` = 0)) GROUP BY `pageId`

It takes 131 seconds to execute on my table having 53k rows and returns 0 rows.

Query rewritten:

SELECT
    B.pid AS pageId
FROM
    sys_file_reference A,
    sys_file_reference B
WHERE
    A.pid = -1 AND A.t3ver_wsid = 1 AND (
        (A.t3ver_oid = B.uid AND A.t3ver_state <> 4)
        OR
        (A.t3ver_oid = B.t3ver_move_id AND A.t3ver_state = 4)
    ) AND A.deleted = 0 AND B.deleted = 0
GROUP BY pageId

The "GROUP BY" is the part killing everything. From what I understand, it looks like it is used to remove duplicates in the result set. If that's really the case, then using a "DISTINCT" instead of a "GROUP BY" effectively drops the execution time from 131 sec. to around 230 ms.

original_query.png View - original query (59.8 KB) Jens Jacobsen, 2017-04-20 11:10

modified_query.png View - modified query (58.1 KB) Jens Jacobsen, 2017-04-20 11:10


Related issues

Related to TYPO3 Core - Task #76626: Doctrine: migrate ext:workspace Closed 2016-06-14

Associated revisions

Revision 4d056965 (diff)
Added by Oliver Hader about 2 years ago

[BUGFIX] Page tree request in a workspaces times out

Tryign to determine workspace versions for a particular database table
results in a very long process execution time and possible timeout due
to the following reasons:

  • in general a bug was introduced during the Doctrine DBAL migration
    which leads to misbehaviors in resolving versions for pages
  • the SQL query implicitly creates an INNER JOIN with a huge result
    set that takes a long query time
  • invalid types leading to possible flaws when using prepared statements

The SQL query has been split into using sub-queries now.

Change-Id: I4e4f69815bd73f0562f7ffbd6d411b417be7a18a
Resolves: #80898
Releases: master, 8.7
Reviewed-on: https://review.typo3.org/52506
Tested-by: TYPO3com <>
Reviewed-by: Xavier Perseguers <>
Tested-by: Xavier Perseguers <>
Reviewed-by: Christian Kuhn <>
Tested-by: Christian Kuhn <>

Revision cde94b4b (diff)
Added by Oliver Hader about 2 years ago

[BUGFIX] Page tree request in a workspaces times out

Tryign to determine workspace versions for a particular database table
results in a very long process execution time and possible timeout due
to the following reasons:

  • in general a bug was introduced during the Doctrine DBAL migration
    which leads to misbehaviors in resolving versions for pages
  • the SQL query implicitly creates an INNER JOIN with a huge result
    set that takes a long query time
  • invalid types leading to possible flaws when using prepared statements

The SQL query has been split into using sub-queries now.

Change-Id: I4e4f69815bd73f0562f7ffbd6d411b417be7a18a
Resolves: #80898
Releases: master, 8.7
Reviewed-on: https://review.typo3.org/52853
Tested-by: TYPO3com <>
Reviewed-by: Christian Kuhn <>
Tested-by: Christian Kuhn <>

History

#1 Updated by Xavier Perseguers about 2 years ago

I just don't know how to use "DISTINCT" instead of "GROUP BY" with Doctrine DBAL.

#2 Updated by Oliver Hader about 2 years ago

  • Status changed from New to Accepted

GROUP BY

+----+-------------+-------+------+------------------------+---------+---------+-------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys          | key     | key_len | ref         | rows | Extra                                        |
+----+-------------+-------+------+------------------------+---------+---------+-------------+------+----------------------------------------------+
|  1 | SIMPLE      | A     | ref  | parent,deleted         | parent  | 6       | const,const |    1 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | B     | ref  | PRIMARY,parent,deleted | deleted | 2       | const       |   83 | Using index condition; Using where           |
+----+-------------+-------+------+------------------------+---------+---------+-------------+------+----------------------------------------------+

DISTINCT

+----+-------------+-------+------+------------------------+---------+---------+-------------+------+------------------------------------+
| id | select_type | table | type | possible_keys          | key     | key_len | ref         | rows | Extra                              |
+----+-------------+-------+------+------------------------+---------+---------+-------------+------+------------------------------------+
|  1 | SIMPLE      | A     | ref  | parent,deleted         | parent  | 6       | const,const |    1 | Using where; Using temporary       |
|  1 | SIMPLE      | B     | ref  | PRIMARY,parent,deleted | deleted | 2       | const       |   83 | Using index condition; Using where |
+----+-------------+-------+------+------------------------+---------+---------+-------------+------+------------------------------------+

#3 Updated by Oliver Hader about 2 years ago

Thus, using DISTINCT removes the filesort on the temporary joined table.

#4 Updated by Gerrit Code Review about 2 years ago

  • Status changed from Accepted 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/52506

#5 Updated by Gerrit Code Review about 2 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/52506

#6 Updated by Jens Jacobsen about 2 years ago

This problem also applies to TYPO3 7.6.18 LTS where this query is used:

SELECT B.uid as live_uid, B.pid as live_pid, A.uid as offline_uid FROM sys_file_reference A,sys_file_reference B WHERE A.pid=-1 AND A.t3ver_wsid=1 AND (A.t3ver_oid=B.uid AND A.t3ver_state<>4 OR A.t3ver_oid=B.t3ver_move_id AND A.t3ver_state=4) AND A.deleted=0 AND B.deleted=0 GROUP BY live_pid;

I also see a problem with the tt_content query. This also seem to increase in duration in relation to the table size:

SELECT B.uid as live_uid, B.pid as live_pid, A.uid as offline_uid FROM tt_content A,tt_content B WHERE A.pid=-1 AND A.t3ver_wsid=1 AND (A.t3ver_oid=B.uid AND A.t3ver_state<>4 OR A.t3ver_oid=B.t3ver_move_id AND A.t3ver_state=4) AND A.deleted=0 AND B.deleted=0 GROUP BY live_pid;

Do we need a seperate Issue for 7.6 LTS ?

#7 Updated by Xavier Perseguers about 2 years ago

Thanks Jens. I expected it to be true for 7.6 as well but did not test yet. Please go to https://review.typo3.org/52506 and write a comment for the commit message so that branch "7.6" is targeted as well.

#8 Updated by Jens Jacobsen about 2 years ago

Xavier Perseguers wrote:

Thanks Jens. I expected it to be true for 7.6 as well but did not test yet. Please go to https://review.typo3.org/52506 and write a comment for the commit message so that branch "7.6" is targeted as well.

I don't think its that easy because the code for 7.6 is totally different or am I totally wrong about it?

#9 Updated by Jens Jacobsen about 2 years ago

For 7.6.18 LTS a solution could be to add "ORDER BY NULL" for the query in typo3/sysext/workspaces/Classes/Service/WorkspaceService.php:752

#10 Updated by Xavier Perseguers about 2 years ago

  • The target of the patch is not whether the "very same" patch should be applied to another branch but whether the same problem (and fix) - generally speaking - is valjd for another branch
  • ORDER BY NULL seems to be very MySQL specific

#11 Updated by Oliver Hader about 2 years ago

The usage of DISTINCT (or any other statement) has to be checked for other DBMS as well (thus, not only for MySQL).
Besides that, the code seems to be wrong in general which was basically caused by the Doctrine DBAL migration in https://review.typo3.org/#/c/48600/ - $pageIds[$row['uid']] = $row; does not make sense, since uid is not part of the selected columns.

#12 Updated by Oliver Hader about 2 years ago

  • Related to Task #76626: Doctrine: migrate ext:workspace added

#13 Updated by Gerrit Code Review about 2 years ago

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

#14 Updated by Kevin Quiatkowski about 2 years ago

How is the Status for 7.6.18? I can reproduce it, and the ORDER BY NULL increases the performance. Maybe there is a better Solution. But better get this dirty one than nothing. How to get it in the 7.6 branch?

#15 Updated by Gerrit Code Review about 2 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/52853

#16 Updated by Oliver Hader about 2 years ago

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

#17 Updated by Rafal Brzeski almost 2 years ago

No fix for 7.6 ? :(

#18 Updated by Sascha Schieferdecker about 1 year ago

Hi, I'd like to have this bug reopenend. We recently updated a TYPO3 Instance to 8.7.13 with around 40 Workspaces. The patch splits the query into subqueries, but in the end also does a GROUP BY. This led to queries running for 300 seconds and longer. I hotfixed it by using the DISTINCT Variant of Patchset 1, this works without problems.

#19 Updated by Marina Bey 10 months ago

Hi, we have the same problem in TYPO3 8. Question to Sascha or anyone else: would you please post your solution with the DISTINCT-part? Thanks a lot!

#20 Updated by Benni Mack 8 months ago

  • Status changed from Resolved to Closed

Also available in: Atom PDF