J3 Issue MySQL 5.7 No Code Attached Yet
avatar He-Man321
He-Man321
18 Sep 2018

Steps to reproduce the issue

  1. Log in to /Administrator as Admin
  2. Go to the Articles and create a new one. Give it a title and Click Save & Close.
  3. Open the same article again and click Save & Close again.

Expected result

No error.

Actual result

Error
Save failed with the following error: SQLSTATE[HY000]: General error: 1093 You can't specify target table 'abc_finder_taxonomy' for update in FROM clause

System information (as much as possible)

Tested in joomla 3.8.8 and 3.8.12 running on Windows 2012R2 with MySQL 5.7.9 and PHP 7.2.2.

Additional comments

This only happens when the Smart Search plug-in is enabled.

I have established that the cause of this is the removeOrphanNodes() function in administrator\components\com_finder\helpers\indexer\taxonomy.php. Back in 3.4.5 this function deleted from the table without using a subquery but at some point after that (and before 3.7.5 I am told) it was changed to use the subquery. However, https://dev.mysql.com/doc/refman/5.6/en/update.html confirms that in MySQL "You cannot update a table and select from the same table in a subquery". This causes the query to fail on a MySQL system.
I have discussed this in more detail on the forum here: https://forum.joomla.org/viewtopic.php?t=965486

Votes

# of Users Experiencing Issue
1/1
Average Importance Score
5.00

avatar He-Man321 He-Man321 - open - 18 Sep 2018
avatar joomla-cms-bot joomla-cms-bot - labeled - 18 Sep 2018
avatar csthomas
csthomas - comment - 18 Sep 2018

@alikon The problem start at Joomla 3.7.0 (#12313)

avatar alikon
alikon - comment - 18 Sep 2018

@csthomas maybe it depends from the mysql version on 5.7 works without issue

from the op text it's ambiguos the issue arise on 5.6 or in 5.7 more detail are needed....

avatar He-Man321
He-Man321 - comment - 18 Sep 2018

Yes, this isn't a MySQL version issue; there are Stack Overflow pages with people moaning about MySQL not allowing updates to tables in a where subquery going back to 2006 and beyond. It just isn't something MySQL supports, so I suspect this will happen with all versions.

avatar alikon
alikon - comment - 18 Sep 2018

on Mysql 5.7.23 and on Maria DB 10.3.2 it works
so it is a version issue
but this apart , this is a real issue cause for https://docs.joomla.org/Technical_requirements
we still support 5.1. + on 3.x 😢

avatar He-Man321
He-Man321 - comment - 18 Sep 2018

Please can you try and execute this in a query window (I believe it is safe, although you might like to check before running it and you will have to change the table prefixes):

DELETE FROM abc_finder_taxonomy WHERE id IN (
SELECT id FROM (
SELECT t.id FROM abc_finder_taxonomy AS t
LEFT JOIN abc_finder_taxonomy_map AS m ON m.node_id=t.id
WHERE t.parent_id > 1 AND m.link_id IS NULL
) temp)

My understanding is that this shouldn't work on any version of MySQL.

avatar csthomas
csthomas - comment - 18 Sep 2018

I tested above query and I do not get any error on 10.1.36-MariaDB and mysql 5.7 but
I do not have any rows to delete. Maybe if there is some rows then error occurs.

avatar alikon
alikon - comment - 18 Sep 2018

can confirm
@He-Man321 what mysql version are you running exactly ??

avatar He-Man321
He-Man321 - comment - 18 Sep 2018

I am running 5.7.9 but maybe yours is working because you are using MariaDB and I am just using plain old MySQL?

avatar He-Man321
He-Man321 - comment - 18 Sep 2018

OK, the plot thickens! I have written this:
CREATE TABLE IF NOT EXISTS Test (
test_id INT AUTO_INCREMENT,
txt VARCHAR(50) NOT NULL,
PRIMARY KEY (test_id)
) ENGINE=INNODB;
DELETE FROM test WHERE test_id IN (
SELECT test_id FROM (
SELECT t.test_id FROM test AS t
WHERE t.test_id > 1
) temp)

And on my 5.7.9 system it fails, so I installed a 5.7.23 system on a test machine and it works fine! So it seems that MySQL does now allow this but it didn't in v5.7.9...

avatar He-Man321
He-Man321 - comment - 19 Sep 2018

A final update on this...
It appears that somewhere between MySQL 5.7.9 and 5.7.23 they have changed it so that you now can run UPDATE queries with the updated table in a subquery in the WHERE clause!

So I can confirm this issue is resolved by upgrading to MySQL 5.7.23.

I am not sure how many people may be stuck on older versions of MySQL though, so perhaps it might be worth re-structuring the query in the taxonomy.php file so that it will run on all versions?


This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/22231.

avatar franz-wohlkoenig franz-wohlkoenig - change - 20 Sep 2018
Status New Discussion
avatar brianteeman brianteeman - change - 30 Oct 2018
Labels Added: J3 Issue
avatar brianteeman brianteeman - labeled - 30 Oct 2018
avatar Quy Quy - change - 1 Jul 2019
Labels Added: MySQL 5.7
avatar Quy Quy - labeled - 1 Jul 2019
avatar wilsonge wilsonge - change - 7 Jun 2020
Labels Added: ?
avatar wilsonge wilsonge - labeled - 7 Jun 2020
avatar wilsonge wilsonge - change - 7 Jun 2020
Labels Removed: ?
avatar wilsonge wilsonge - unlabeled - 7 Jun 2020

Add a Comment

Login with GitHub to post a comment