Bug #89464
closedPageTree in workspace not loading
Added by Claude Unterleitner almost 5 years ago. Updated over 4 years ago.
100%
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 |
Updated by Oliver Hader almost 5 years 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.
Updated by Claude Unterleitner almost 5 years ago
- File screenshot-www.adfc-nuernberg.ddev.site-2019.10.21-22_37_55.png screenshot-www.adfc-nuernberg.ddev.site-2019.10.21-22_37_55.png added
Yes, these error messages are in the browser's console (it's from my dev environement, as the live environment is now already patched by reusing the old WorkspaceService.php file of TYPO3 v. 8.7.27):
TypeError: response is undefined merged-14de4b534885b83ab57e92f6e0623bb3-d41c8782625143d9a35f149f76e704bb.js.1571649664.gzip:2665:31
addDragDropNodeInsertionFeature https://www.adfc-nuernberg.ddev.site/typo3temp/assets/compressed/merged-14de4b534885b83ab57e92f6e0623bb3-d41c8782625143d9a35f149f76e704bb.js.1571649664.gzip:2665
createDelegate https://www.adfc-nuernberg.ddev.site/typo3temp/assets/compressed/ext-base-f628b6975925566e3abb8fcb80d6c4fa.js.gzip:21
ExtJS 3
doCallback
onData
handleFailure
f https://www.adfc-nuernberg.ddev.site/typo3temp/assets/compressed/ext-base-f628b6975925566e3abb8fcb80d6c4fa.js.gzip:21
abort https://www.adfc-nuernberg.ddev.site/typo3temp/assets/compressed/ext-base-f628b6975925566e3abb8fcb80d6c4fa.js.gzip:21
r https://www.adfc-nuernberg.ddev.site/typo3temp/assets/compressed/ext-base-f628b6975925566e3abb8fcb80d6c4fa.js.gzip:21
createCallback https://www.adfc-nuernberg.ddev.site/typo3temp/assets/compressed/ext-base-f628b6975925566e3abb8fcb80d6c4fa.js.gzip:21
I have now also attached a screenshot.
Updated by Oliver Hader almost 5 years ago
- File 89464_002.png 89464_002.png added
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
Updated by Frank Gerards almost 5 years 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.
Updated by Oliver Hader almost 5 years ago
- Related to Bug #86945: Performance PageTree in Workspace added
Updated by rengaw83 almost 5 years 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
Updated by rengaw83 almost 5 years ago
- File table.dump.zip table.dump.zip added
i have added a cleaned sqldump of the table `tx_powermail_domain_model_answer` which causes the problem in my case.
Updated by Gerrit Code Review almost 5 years 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
Updated by Gerrit Code Review almost 5 years 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
Updated by Oliver Hader almost 5 years 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.
Updated by Oliver Hader almost 5 years 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...
Updated by Oliver Hader almost 5 years ago
- Status changed from Under Review to Accepted
- Target version set to next-patchlevel
Updated by Oliver Hader almost 5 years 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
Updated by Gerrit Code Review almost 5 years 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
Updated by Gerrit Code Review almost 5 years 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
Updated by Gerrit Code Review almost 5 years 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
Updated by Gerrit Code Review almost 5 years 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
Updated by Gerrit Code Review almost 5 years 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
Updated by Oliver Hader almost 5 years ago
- Status changed from Under Review to Resolved
- % Done changed from 0 to 100
Applied in changeset 642c1e678cf821967b02b271b22837b1e8995468.
Updated by Alexander Opitz almost 5 years 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.