Bug #89464

PageTree in workspace not loading

Added by Claude Unterleitner about 2 months ago. Updated about 1 month ago.

Status:
Resolved
Priority:
Must have
Assignee:
-
Category:
Workspaces
Target version:
Start date:
2019-10-21
Due date:
% Done:

100%

TYPO3 Version:
8
PHP Version:
Tags:
workspace, draft
Complexity:
Is Regression:
Yes
Sprint Focus:

Description

In a TYPO3 v. 8.7.28 installation the page tree is not loading anymore in the workspace draft view.
PHP-version: 7.2
no composer mode

It is caused by the modification to resolve the issue 86945 [[https://forge.typo3.org/issues/86945]]

screenshot-www.adfc-nuernberg.ddev.site-2019.10.21-22_37_55.png View (55.1 KB) Claude Unterleitner, 2019-10-21 22:49

89464_002.png View (109 KB) Oliver Hader, 2019-10-22 17:50

table.dump.zip - cleaned sqldump of the table `tx_powermail_domain_model_answer` which causes the problem (838 KB) rengaw83, 2019-11-06 12:05


Related issues

Related to TYPO3 Core - Bug #86945: Performance PageTree in Workspace Resolved 2018-11-16

Associated revisions

Revision 642c1e67 (diff)
Added by Oliver Hader about 1 month ago

Revert "[TASK] Speed up DB query for tables with versions"

This reverts commit 98c74eb212b728db2818c256994c501a6173ab7b.

The change resulted in AJAX timeouts when dealing with lots of
database records on MySQL 5.6 - it was fine however on MySQL 5.7.

Reverts: #86945
Resolves: #89464
Releases: master, 9.5, 8.7
Change-Id: Ied400255186f5e72521d3318521c3edb0f4f7784
Reviewed-on: https://review.typo3.org/c/Packages/TYPO3.CMS/+/62209
Tested-by: TYPO3com <>
Tested-by: Benni Mack <>
Tested-by: Oliver Hader <>
Reviewed-by: Benni Mack <>
Reviewed-by: Oliver Hader <>

Revision 8f3d8dde (diff)
Added by Oliver Hader about 1 month ago

Revert "[TASK] Speed up DB query for tables with versions"

This reverts commit 0dab9b4183208040c76e84ea845396b742d91fab.

The change resulted in AJAX timeouts when dealing with lots of
database records on MySQL 5.6 - it was fine however on MySQL 5.7.

Reverts: #86945
Resolves: #89464
Releases: master, 9.5, 8.7
Change-Id: Ied400255186f5e72521d3318521c3edb0f4f7784
Reviewed-on: https://review.typo3.org/c/Packages/TYPO3.CMS/+/62208
Tested-by: TYPO3com <>
Tested-by: Benni Mack <>
Tested-by: Oliver Hader <>
Reviewed-by: Benni Mack <>
Reviewed-by: Oliver Hader <>

Revision 5c716ee9 (diff)
Added by Oliver Hader about 1 month ago

Revert "[TASK] Speed up DB query for tables with versions"

This reverts commit 28c285e7db92a8d8249c774ef15839c78ef9258c.

The change resulted in AJAX timeouts when dealing with lots of
database records on MySQL 5.6 - it was fine however on MySQL 5.7.

Reverts: #86945
Resolves: #89464
Releases: master, 9.5, 8.7
Change-Id: Ied400255186f5e72521d3318521c3edb0f4f7784
Reviewed-on: https://review.typo3.org/c/Packages/TYPO3.CMS/+/62207
Tested-by: TYPO3com <>
Tested-by: Benni Mack <>
Tested-by: Oliver Hader <>
Reviewed-by: Benni Mack <>
Reviewed-by: Oliver Hader <>

History

#1 Updated by Oliver Hader about 2 months ago

  • Status changed from New to Needs Feedback

Are there any error messages available in the browser's console?
Besides that, could you please provide a screenshot - I could not reproduce the behavior in the TYPO3 backend using a workspace context.

#3 Updated by Oliver Hader about 2 months ago

Thanks for your feedback. Since you seem to be using DDEV, can you please attach your .ddev/config.yaml in case it contains custom services or alternative images?

In order to track JavaScript errors better, please enable the debugging preset via Install Tool (Configuration Presets > Debug Settings > Debug) and post the shown errors again.

Besides that, I'm puzzled since the screenshot you provided does not show the current workspace and according selector in the backend top toolbar (see my screenshot as reference). Actually that would mean that the current user is neither owner or member of that workspace record...

Current status: Could not reproduce (yet) locally with TYPO3 v8.7.28

#4 Updated by Frank Gerards about 1 month ago

  • Priority changed from Should have to Must have

we can reproduce it here on our system:
TYPO3 8.7.28, PHP 7.2, centOS 7.7, Apache 2.4

we also use TemplaVOila+ - could this be related ?
Error is reproducable with every workspace and on every request/reload.

thx 4 digging into this.

#5 Updated by Oliver Hader about 1 month ago

  • Related to Bug #86945: Performance PageTree in Workspace added

#6 Updated by rengaw83 about 1 month ago

I have the same Problem after Upgrading TYPO3 to 8.7.28 or later.

In Bug #86945 the PageTree Performance for Workspace are optimized.

Since this change, the page tree will no longer work in certain cases.

I use Powermail and i have a lot of entries in the Table `tx_powermail_domain_model_answer`. Over 1k of the 100k database entries are versioned.

In `TYPO3\CMS\Workspaces\Service\WorkspaceService::fetchPagesWithVersionsInTable($workspaceId, $tableName)` the folowing statement is gernerated:

SELECT `B`.`pid` AS `pageId` FROM `tx_powermail_domain_model_answer` `B` 
INNER JOIN `tx_powermail_domain_model_answer` `A` ON `B`.`uid` = `A`.`t3ver_oid` 
WHERE (`A`.`pid` = -1) AND (`A`.`t3ver_wsid` = 2) AND (`A`.`t3ver_state` <> 4) AND ((`B`.`deleted` = 0) AND (`A`.`deleted` = 0)) 
GROUP BY `B`.`pid` 
UNION SELECT `B`.`pid` AS `pageId` FROM `tx_powermail_domain_model_answer` `B` 
INNER JOIN `tx_powermail_domain_model_answer` `A` ON `B`.`t3ver_move_id` = `A`.`t3ver_oid` 
WHERE (`A`.`pid` = -1) AND (`A`.`t3ver_wsid` = 2) AND (`A`.`t3ver_state` = 4) AND ((`B`.`deleted` = 0) AND (`A`.`deleted` = 0)) 
GROUP BY `B`.`pid`

This query runs into a timeout and the page tree keeps empty. so workspaces does not work anymore.

Explain select:

+------+---------------+-------------+--------------+---------------------------+-----------------+----------+------------------------+--------+-------------------------------------------------------------------------------+
| id   | select_type   |   table     |    type      |      possible_keys        |      key        | key_len  |          ref           | rows   |                                     Extra                                     |
+------+---------------+-------------+--------------+---------------------------+-----------------+----------+------------------------+--------+-------------------------------------------------------------------------------+
| 1    | PRIMARY       | A           | index_merge  | parent,t3ver_oid,deleted  | parent,deleted  | 4,2      | NULL                   | 719    | Using intersect(parent,deleted); Using where; Using temporary; Using filesort |
| 1    | PRIMARY       | B           | eq_ref       | PRIMARY,parent,deleted    | PRIMARY         | 4        | A.t3ver_oid            | 1      | Using where                                                                   |
| 2    | UNION         | B           | ref          | parent,deleted            | deleted         | 2        | const                  | 69425  | Using where; Using temporary; Using filesort                                  |
| 2    | UNION         | A           | ref          | parent,t3ver_oid,deleted  | t3ver_oid       | 8        | B.t3ver_move_id,const  | 48     | Using where                                                                   |
| NULL | UNION RESULT  | <union1,2>  | ALL          | NULL                      | NULL            | NULL     | NULL                   | NULL   | Using temporary                                                               |
+------+---------------+-------------+--------------+---------------------------+-----------------+----------+------------------------+--------+-------------------------------------------------------------------------------+

I have testet the query directly in the mysql terminal on a mysql 5.6.33 server. Ther query returns an empty set in 1 min 57 sec.

After Downgrading TYPO3 to version 8.7.27 all works like a charm

#7 Updated by rengaw83 about 1 month ago

i have added a cleaned sqldump of the table `tx_powermail_domain_model_answer` which causes the problem in my case.

#8 Updated by Gerrit Code Review about 1 month ago

  • Status changed from Needs Feedback to Under Review

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/c/Packages/TYPO3.CMS/+/62206

#9 Updated by Gerrit Code Review about 1 month ago

Patch set 2 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/c/Packages/TYPO3.CMS/+/62206

#10 Updated by Oliver Hader about 1 month ago

Thanks rengaw83 for the details... I did not spot them before creating my previous patch.
In case it is causing a timeout, the only fix I see currently is to revert the previous change.

#11 Updated by Oliver Hader about 1 month ago

Alright...

  • could reproduce on MySQL v5.6.48 (Docker)
  • could not reproduce on MySQL 5.7.25 (Docker), 5.7.27 (source)
Server version: 5.6.46 MySQL Community Server (GPL)

mysql> SELECT `B`.`pid` AS `pageId` FROM `tx_powermail_domain_model_answer` `B`
    -> INNER JOIN `tx_powermail_domain_model_answer` `A` ON `B`.`uid` = `A`.`t3ver_oid`
    -> WHERE (`A`.`pid` = -1) AND (`A`.`t3ver_wsid` = 2) AND (`A`.`t3ver_state` <> 4) AND ((`B`.`deleted` = 0) AND (`A`.`deleted` = 0))
    -> GROUP BY `B`.`pid`
    -> UNION SELECT `B`.`pid` AS `pageId` FROM `tx_powermail_domain_model_answer` `B`
    -> INNER JOIN `tx_powermail_domain_model_answer` `A` ON `B`.`t3ver_move_id` = `A`.`t3ver_oid`
    -> WHERE (`A`.`pid` = -1) AND (`A`.`t3ver_wsid` = 2) AND (`A`.`t3ver_state` = 4) AND ((`B`.`deleted` = 0) AND (`A`.`deleted` = 0))
    -> GROUP BY `B`.`pid`;
Empty set (1 min 6.29 sec)
Server version: 5.7.25 MySQL Community Server (GPL)

mysql> SELECT `B`.`pid` AS `pageId` FROM `tx_powermail_domain_model_answer` `B`
    -> INNER JOIN `tx_powermail_domain_model_answer` `A` ON `B`.`uid` = `A`.`t3ver_oid`
    -> WHERE (`A`.`pid` = -1) AND (`A`.`t3ver_wsid` = 2) AND (`A`.`t3ver_state` <> 4) AND ((`B`.`deleted` = 0) AND (`A`.`deleted` = 0))
    -> GROUP BY `B`.`pid`
    -> UNION SELECT `B`.`pid` AS `pageId` FROM `tx_powermail_domain_model_answer` `B`
    -> INNER JOIN `tx_powermail_domain_model_answer` `A` ON `B`.`t3ver_move_id` = `A`.`t3ver_oid`
    -> WHERE (`A`.`pid` = -1) AND (`A`.`t3ver_wsid` = 2) AND (`A`.`t3ver_state` = 4) AND ((`B`.`deleted` = 0) AND (`A`.`deleted` = 0))
    -> GROUP BY `B`.`pid`;
Empty set (0.04 sec)

Thus, on MySQL 5.6 this will at least run into an XHR timeout leaving the TYPO3 page tree empty...

#12 Updated by Oliver Hader about 1 month ago

  • Status changed from Under Review to Accepted
  • Target version set to next-patchlevel

#13 Updated by Oliver Hader about 1 month ago

... and the previous queries have been like this (reverted the mentioned change) ...

mysql> SELECT `B`.`pid` AS `pageId` FROM `tx_powermail_domain_model_answer` `B` WHERE (
    -> (`B`.`uid` IN (
    ->     SELECT `A`.`t3ver_oid` FROM `tx_powermail_domain_model_answer` `A` WHERE (`A`.`pid` = -1) AND (`A`.`t3ver_wsid` = 14) AND (`A`.`t3ver_state` <> 4) AND (`A`.`deleted` = 0)))
    -> OR (`B`.`t3ver_move_id` IN (
    ->     SELECT `A`.`t3ver_oid` FROM `tx_powermail_domain_model_answer` `A` WHERE (`A`.`pid` = -1) AND (`A`.`t3ver_wsid` = 14) AND (`A`.`t3ver_state` = 4) AND (`A`.`deleted` = 0))))
    -> AND (`B`.`deleted` = 0) GROUP BY `B`.`pid`;
Empty set (0.10 sec)

In this use-case 0.04 vs. 0.10 on MySQL 5.7

#14 Updated by Gerrit Code Review about 1 month ago

  • Status changed from Accepted to Under Review

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/c/Packages/TYPO3.CMS/+/62207

#15 Updated by Gerrit Code Review about 1 month ago

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

#16 Updated by Gerrit Code Review about 1 month ago

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/c/Packages/TYPO3.CMS/+/62209

#17 Updated by Gerrit Code Review about 1 month ago

Patch set 2 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/c/Packages/TYPO3.CMS/+/62207

#18 Updated by Gerrit Code Review about 1 month ago

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

#19 Updated by Oliver Hader about 1 month ago

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

#20 Updated by Alexander Opitz about 1 month ago

The main issue with the patch (that is reverted) is the changed query generation in the QueryBuilder since the patch was originally worked out. It now generates an 'INNER JOIN' with back references (which is a mess with older MySQL versions) instead of a JOIN with subselect as it was generated with QueryBuilder one year ago. Thats why, this optimization leads to a big missoptimization for MySQL <=5.6. So to get better performance we now need to do more hand optimization instead of using QueryBuilder (or find a way around).

With MySQL 5.7 and newer or MariaDB 10.0 and newer the original issue (which was tried to fix) with the "OR" combination of both selects was resolved by a better SQL execution plan.

As newer MySQL/MariaDB Versions are more and more in use and all users are moving along I'll do not fix the original issue with digging again into QueryBuilding. The old state (which was faster as the state after the patches) is restored with this revert. If you have a old MySQL (<=5.6 or MariaDB <= 5.5), a large database, use workspaces and long waits while loading the page tree please upgrade to a newer version of MySQL (5.7 or newer [latest is 8.0.16]) or MariaDB (Partially 10.0 and fully 10.1 or newer [latest is 10.4.10]). No further development will be done here.

Also available in: Atom PDF