Bug #89464

PageTree in workspace not loading

Added by Claude Unterleitner over 1 year ago. Updated about 1 year ago.

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

100%

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


Files

screenshot-www.adfc-nuernberg.ddev.site-2019.10.21-22_37_55.png (55.1 KB) screenshot-www.adfc-nuernberg.ddev.site-2019.10.21-22_37_55.png Claude Unterleitner, 2019-10-21 22:49
89464_002.png (109 KB) 89464_002.png Oliver Hader, 2019-10-22 17:50
table.dump.zip (838 KB) table.dump.zip cleaned sqldump of the table `tx_powermail_domain_model_answer` which causes the problem rengaw83, 2019-11-06 12:05

Related issues

Related to TYPO3 Core - Bug #86945: Performance PageTree in WorkspaceClosedAlexander Opitz2018-11-16

Actions
#1

Updated by Oliver Hader over 1 year 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 over 1 year 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 over 1 year 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 over 1 year ago

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

Updated by rengaw83 over 1 year 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 over 1 year 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 over 1 year 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 over 1 year 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 over 1 year 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 over 1 year 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 over 1 year ago

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

Updated by Oliver Hader over 1 year 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 over 1 year 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 over 1 year 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 over 1 year 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 over 1 year 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 over 1 year 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 over 1 year ago

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

Updated by Alexander Opitz over 1 year 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.

#21

Updated by Benni Mack about 1 year ago

  • Status changed from Resolved to Closed

Also available in: Atom PDF