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
Tested in joomla 3.8.8 and 3.8.12 running on Windows 2012R2 with MySQL 5.7.9 and PHP 7.2.2.
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
I assume this is something like https://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause/14302701
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.
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
My understanding is that this shouldn't work on any version of MySQL.
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.
I am running 5.7.9 but maybe yours is working because you are using MariaDB and I am just using plain old MySQL?
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)
DELETE FROM test WHERE test_id IN (
SELECT test_id FROM (
SELECT t.test_id FROM test AS t
WHERE t.test_id > 1
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...
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?