Bug #81208

Invalid SQL query when previewing a workspace with translated relations

Added by Xavier Perseguers over 2 years ago. Updated 27 days ago.

Status:
Under Review
Priority:
Must have
Category:
Extbase + l10n
Target version:
-
Start date:
2017-05-12
Due date:
% Done:

0%

TYPO3 Version:
9
PHP Version:
7.2
Tags:
uid_local
Complexity:
Is Regression:
Sprint Focus:

Description

DISCLAIMER

This is quite tricky and could not understand how to reproduce it in a vanilla install.

Context

  • The problem happens for me when previewing a page from a workspace with a menu of categories associated to EXT:news records.
  • In my case I have a custom extended domain model for news records but it does not seem to be related, I removed corresponding columns/fields from the query below

Preview crashes with:

An exception occurred while executing 'SELECT `uid_local`, `uid_foreign`, `tablenames`, `sorting`, `sorting_foreign`, `fieldname`, `uid`, `pid`, `tstamp`, `crdate`, `cruser_id`, `deleted`, `hidden`, `starttime`,
`endtime`, `t3ver_oid`, `t3ver_id`, `t3ver_wsid`, `t3ver_label`, `t3ver_state`, `t3ver_stage`, `t3ver_count`, `t3ver_tstamp`, `t3ver_move_id`, `t3_origuid`, `sys_language_uid`, `l10n_parent`, `l10n_diffsource`,
`title`, `description`, `parent`, `items`, `fe_group`, `images`, `single_pid`, `shortcut`, `import_id`, `import_source`, `seo_title`, `seo_description`, `seo_headline`, `seo_text`, `l10n_state`
FROM `sys_category` WHERE (`pid` = ?) AND (`t3ver_oid` = ?) AND (`t3ver_wsid` = ?) AND (`sys_category`.`deleted` = 0) LIMIT 1' with params [-1, 29, 2]: Unknown column 'uid_local' in 'field list' 

Call stack shows:

After quite some deep debugging, I could understand the context:

  • This happens when the rows array which need to be overlaid in \TYPO3\CMS\Extbase\Persistence\Generic\Storage\Typo3DbBackend::doLanguageAndWorkspaceOverlay() are the result of a JOIN (in my case a JOIN from sys_category and sys_category_record_mm
  • Each row is thus containing fields coming from both tables
  • The test on $source at the beginning of method doLanguageAndWorkspaceOverlay properly detects that a JOIN is in use and extracts the right table (sys_category)
  • There are some tests whether the "original" record needs to be fetched, for instance when a move is detected, that's not invoked in my case
  • There is another test which detects that current record is a translation already and then would fetch the parent record instead, this is where the problem lies
  • If current record is a default language record AND we are in the context of fetching the content of a workspace (method is invoked multiple times with same record causing no errors until the context of the call is the actual workspace preview), then it bypasses the action of reloading the record from database
  • Since it bypasses this reload, call to versionOL is made with a row containing additional columns from sys_category_record_mm and since the test at the very beginning of method versionOL succeeds, there is a call to method getWorkspaceVersionOfRecord with those invalid additional columns and this is the source of the SQL failure

Solution Found

The test to reload the original record is extended to check if we are in the context of a workspace preview and if so, ensure no additional columns are present

stacktrace.png View (182 KB) Xavier Perseguers, 2017-05-12 12:01

kittensftw.zip - extension (kittensftw) (20.1 KB) Susanne Moog, 2018-03-07 14:02

T3D_tree_PID201_L0_2018-03-07_13-01.t3d - example page t3d export (8.23 KB) Susanne Moog, 2018-03-07 14:02

81208_v9.diff View (7.74 KB) Ralf Merz, 2019-10-08 15:46


Related issues

Related to TYPO3 Core - Bug #82750: Workspace preview doesn't preview MM relations properly New 2017-10-12

History

#1 Updated by Xavier Perseguers over 2 years ago

  • Description updated (diff)

Added new lines to SQL to make it more readable

#2 Updated by Gerrit Code Review over 2 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/52791

#3 Updated by Gerrit Code Review over 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/52791

#4 Updated by Michael Kasten about 2 years ago

Can confirm this bug, same Problem here with TYPO3 8.7.4 and News.

I see, here is a patch, and it will be revied.. since four months, what is the plan here?

Any information about in which release ore when this patch should be available?

We have two big TYPO3 Installation, ready to go online, no chance to patch the core with my self (no permission at this hosting)

Any other hints?

best regards

#5 Updated by Xavier Perseguers about 2 years ago

Hey Michael,

Since you face the very same problem, I would suggest to test the patch to see if it works properly and then give a feedback in the form of a review, following the link to the review system (same credentials as for here).

A patch is good but if nobody confirms the bug and reports that the patch indeed fixes the problem, then it's unlikely to be merged on its own...

#6 Updated by Christian Weiske about 2 years ago

The patch helps here.

#7 Updated by Andreas Kokott about 2 years ago

In my case the patch did not help.
The columns of my custom _mm table are still applied to the query.

#8 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/52791

#9 Updated by Gerrit Code Review about 2 years ago

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

#10 Updated by Sascha no-lastname-given almost 2 years ago

I have the same problem.

The last patch works for me.

#11 Updated by Cristian Buja over 1 year ago

The last patch works for me too.

#12 Updated by Oliver Hader over 1 year ago

To continue on this issue, I'd kindly like to ask for a step-by-step documentation on how to reproduce the behavior - which scenario is required for that?

#13 Updated by Susanne Moog over 1 year ago

Attached is a test extension I just generated with the extension builder and a t3d export of a page which reproduces the error.

Steps (without the t3d export, manually done)

- Extract example extension "kittensftw" and install it
- Create a new page
- Create a content element on the page - choose the plugin from the example extension and use the current page as starting point
- Create a category on the page
- Create an example extension record ("kitten") on the page and assign the category
- Preview (without workspace) in frontend: you should see a list view with your record
- Switch to workspace
- DO NOT change anything
- Preview same page -> Exception


Uncaught TYPO3 Exception
An exception occurred while executing 'SELECT `uid_local`, `uid_foreign`, `tablenames`, `fieldname`, `sorting`, `sorting_foreign`, `uid`, `pid`, `tstamp`, `crdate`, `cruser_id`, `deleted`, `hidden`, `starttime`, `endtime`, `t3ver_oid`, `t3ver_id`, `t3ver_wsid`, `t3ver_label`, `t3ver_state`, `t3ver_stage`, `t3ver_count`, `t3ver_tstamp`, `t3ver_move_id`, `t3_origuid`, `sys_language_uid`, `l10n_parent`, `l10n_diffsource`, `title`, `description`, `parent`, `items`, `l10n_state`, `fe_group`, `images`, `single_pid`, `shortcut`, `import_id`, `import_source`, `seo_title`, `seo_description`, `seo_headline`, `seo_text` FROM `sys_category` WHERE (`pid` = ?) AND (`t3ver_oid` = ?) AND (`t3ver_wsid` = ?) AND (`sys_category`.`deleted` = 0) LIMIT 1' with params [-1, 7, 1]: Unknown column 'uid_local' in 'field list'

#14 Updated by Dennis Metz over 1 year ago

Got the exact same problem.

Last patch is working for me too.

#15 Updated by Frédéric Lebel over 1 year ago

We have the same problem here.

The patch works for us.

#16 Updated by Dennis Metz over 1 year ago

Hey guys,
is there anything new about this?

We really need this fix...

#17 Updated by Gianluca Strafella over 1 year ago

Last patch works fine with TYPO3 8.7.15.

#18 Updated by Tymoteusz Motylewski over 1 year ago

  • Related to Bug #82750: Workspace preview doesn't preview MM relations properly added

#19 Updated by Gerrit Code Review 11 months ago

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

#20 Updated by Marcos Fadul 10 months ago

I've having this issue also with not translated content.

We are using typo3 8.7.20 with Flux and Blog extension. Previewing the default language is generation the same error.

We are using the patch #4. It fixed part of the problem.

#21 Updated by Franz Koch 6 months ago

facing the same issue. Patchset 5 unfortunately no longer applies due to small changes related to TYPO3 context and workspaceID, but applying the changes from the patchset manually did unfortunately not solve the issue for me (or maybe I messed it up somehow).

#22 Updated by Torben Hansen 6 months ago

The problem is still valid in TYPO3 9.5 LTS. I'm facing the problem in a project with models that have simple mm relations to sys_categories and also with a mm relations to a custom model.

In a fresh TYPO3 website, the problem is also reproducible with Susis demo extension mentioned in #81208#note-13

Update: It seems, that $pageRepository->versionOL($tableName, $row, true); in overlayLanguageAndWorkspace is causing the exception.

#23 Updated by Kai Tallafus 3 months ago

Can confirm this still happens in V9.5. Using events2 Extension, which has a flexform with sys_category tree multiselect.

#24 Updated by Jakob Berlin 3 months ago

  • Category changed from Extbase to Extbase + l10n
  • Priority changed from Should have to Must have
  • TYPO3 Version changed from 8 to 9
  • PHP Version set to 7.2
  • Tags set to uid_local

also still have this problem in a TYPO3 9.5.5 with news 7.1 installation :(

#25 Updated by Ralf Merz about 1 month ago

Hi,

I also run into this error on TYPO3 9.5.9 with

"t3g/blog": "^9.1",

installed.
I cannot preview a blog page having a category assigned in workspace.

Getting same exception:

An exception occurred while executing 'SELECT `uid_local`, `uid_foreign`, `tablenames`, `fieldname`, `sorting`, `sorting_foreign`, `uid`, `pid`, `tstamp`, `crdate`, `cruser_id`, `deleted`, `hidden`, `starttime`, `endtime`, `t3ver_oid`, `t3ver_id`, `t3ver_wsid`, `t3ver_label`, `t3ver_state`, `t3ver_stage`, `t3ver_count`, `t3ver_tstamp`, `t3ver_move_id`, `t3_origuid`, `sys_language_uid`, `l10n_parent`, `l10n_diffsource`, `title`, `description`, `parent`, `items`, `fe_group`, `images`, `single_pid`, `shortcut`, `import_id`, `import_source`, `seo_title`, `seo_description`, `seo_headline`, `seo_text`, `l10n_state`, `slug`, `content`, `posts` FROM `sys_category` WHERE (`pid` = ?) AND (`t3ver_oid` = ?) AND (`t3ver_wsid` = ?) AND (`sys_category`.`deleted` = 0) LIMIT 1' with params [-1, 2, 1]: Unknown column 'uid_local' in 'field list'

Are there any news on this?
Will try to debug...

Thx
merzilla

#26 Updated by Xavier Perseguers about 1 month ago

Hello Ralf,

Are there any news on this?
Will try to debug...

No need to debug, just check the patch I created ... 2 years ago :)

#27 Updated by Ralf Merz about 1 month ago

Xavier Perseguers wrote:

Hello Ralf,

Are there any news on this?
Will try to debug...

No need to debug, just check the patch I created ... 2 years ago :)

Hi Xavier,

i have just testet PS 5, but it does not apply to v9 out of the box.
I´ll have a look in the extbase code and try to apply it manually.

Thanks for your fast reply!

Cheers
Ralf

#28 Updated by Ralf Merz about 1 month ago

As I could see now for v9 we need to also patch function overlayLanguageAndWorkspace() in Typo3DbBackend.php as "consistentTranslationOverlayHandling" is enabled by default in v9 and will be always on in v10.

See: https://docs.typo3.org/c/typo3/cms-core/master/en-us/Changelog/9.5/Important-82363-MakeExtBaseTranslationHandlingConsistentWithTyposcript.html

#29 Updated by Ralf Merz about 1 month ago

Hi guys,

I've added the patch manually to extbase in v9.5.9.
So the patch mainly is for doLanguageAndWorkspaceOverlay(), but I've now also added it to overlayLanguageAndWorkspace() in devil's line 666 ;)

We use PHP 7.3, but this should not be an issue.

Can someone please try this out?
I do not exactly remember to push it to gerrit... have to read guidelines again first to be able to push for v9... :( sorry. But I guess I am able to give it a try today.

Cheers
merzilla

#30 Updated by Gerrit Code Review about 1 month ago

Patch set 6 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/+/52791

#31 Updated by Ralf Merz about 1 month ago

Gerrit Code Review wrote:

Patch set 6 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/+/52791

I've implemented the patch now also in the master branch. HTH and will fix this preview bug for you, too.

#32 Updated by Gerrit Code Review about 1 month ago

Patch set 7 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/+/52791

#33 Updated by Lukas Speck 27 days ago

Gerrit Code Review wrote:

Patch set 7 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/+/52791

The Patch has a bug in the line 741. If a workspace ist active and $forceRecordReloadInWorkspace is true, $row is replaced by a result where 'l10n' (or $GLOBALS['TCA'][$tableName]['ctrl']['transOrigPointerField']) is qual to uid. But this field could be 0 and then the result is "false".
I fixed it with this line in line mentioned:

$row[$GLOBALS['TCA'][$tableName]['ctrl']['transOrigPointerField']] == 0 ? $row['uid'] : $row[$GLOBALS['TCA'][$tableName]['ctrl']['transOrigPointerField']]

Cheerio Lukas

Also available in: Atom PDF