You can't specify target table for update in FROM clause

[HeMan321]

We are having a problem with saving pages on Joomla 3.8.12 (and the same on another 3.8.8 site) in that about 50% of the time we get the following error when saving an article:

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

I have tracked this to the removeOrphanNodes() function in administrator\components\com_finder\helpers\indexer\taxonomy.php which is constructing the following SQL:

Code: Select all

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)

And, put simply, that isn't valid SQL because, as https://dev.mysql.com/doc/refman/5.6/en/update.html confirms, "You cannot update a table and select from the same table in a subquery."
I don't know why Joomla is deleting from that table or even what that table does, but it seems to me this cannot work and is maybe a bug?
Any help or thoughts would be appreciated?
In case it is relevant this is happening with Joomla 3.8.8 and 3.8.12 running PHP 7.2.2 with MySQL 5.7.9 on Windows.

[Webdongle]

It works for 100's of users 100% of the time. Given that it works spasmodically for you and the file is constant ... then the problem is not the way the file operates. The problem is (periodically) causing that file to operate correctly. Something is hapening on your server that is causing a time out or break in connection.

viewtopic.php?f=714&t=793531 please

[HeMan321]

Your right in that we are somehow special as I am sure this isn't happening for most people. But here is the odd thing, that SQL above, which Joomla is constructing in the taxonomy.php, is not valid SQL and won't execute even if I copy and paste it in to a MySQL query window!

So, even if we have done something 'special' to cause this to happen, why does the taxonomy.php file have code to construct SQL that is invalid in it? Or am I wrong on that (I must admit to being more a MSSQL than a Joomla\MySQL expert).

Further to my post last week, I have since narrowed this down to the Smart Search Plug-in; if I disable that everything returns to normal again.

The below is from one of two sites doing the same thing. The other is running 3.8.8 and has different templates etc. on it (I can add that as well if useful).

Joomla! Instance :: Joomla! 3.8.12-Stable (Amani) 28-August-2018
Joomla! Platform :: Joomla Platform 13.1.0-Stable (Curiosity) 24-Apr-2013
Joomla! Configured :: Yes | Read-Only (444) | Owner: --protected-- . (uid: /gid: ) | Group: --protected-- (gid: ) | Valid For: 3.8
Configuration Options :: Offline: 0 | SEF: 1 | SEF Suffix: 0 | SEF ReWrite: 1 | .htaccess/web.config: Yes | GZip: 0 | Cache: 0 | CacheTime: 15 | CacheHandler: file | CachePlatformPrefix: 0 | FTP Layer: 0 | Proxy: 0 | LiveSite: | Session lifetime: 15 | Session handler: database | Shared sessions: 0 | SSL: 0 | Error Reporting: default | Site Debug: 0 | Language Debug: 0 | Default Access: 1 | Unicode Slugs: 0 | dbConnection Type: pdomysql | PHP Supports J! 3.8.12: Yes | Database Credentials Present: Yes |

Host Configuration :: OS: Windows NT | OS Version: 6.3 | Technology: i586 | Web Server: Microsoft-IIS/8.5 | Encoding: gzip, deflate, br | Doc Root: --protected-- | System TMP Writable: Yes | Free Disk Space : 44.15 GiB |

PHP Configuration :: Version: 7.2.2 | PHP API: cgi-fcgi | Session Path Writable: No | Display Errors: | Error Reporting: 22527 | Log Errors To: | Last Known Error: | Register Globals: | Magic Quotes: | Safe Mode: | Open Base: | Uploads: 1 | Max. Upload Size: 10M | Max. POST Size: 10M | Max. Input Time: 60 | Max. Execution Time: 300 | Memory Limit: 128M

Database Configuration :: Version: 5.7.9-log (Client:mysqlnd 5.0.12-dev - 20150407 - $Id: 38fea24f2847fa7519001be390c98ae0acafe387 $) | Host: --protected-- (--protected--) | Localhost: Yes | Collation: utf8_general_ci (Character Set: utf8) | Database Size: 93.39 MiB | #of Tables: 102

PHP Extensions :: Core (7.2.2) | bcmath (7.2.2) | calendar (7.2.2) | ctype (7.2.2) | date (7.2.2) | filter (7.2.2) | hash (1.0) | iconv (7.2.2) | json (1.6.0) | SPL (7.2.2) | pcre (7.2.2) | readline (7.2.2) | Reflection (7.2.2) | session (7.2.2) | standard (7.2.2) | mysqlnd (mysqlnd 5.0.12-dev - 20150407 - $Id: 38fea24f2847fa7519001be390c98ae0acafe387 $) | tokenizer (7.2.2) | zip (1.15.2) | zlib (7.2.2) | libxml (7.2.2) | dom (20031129) | PDO (7.2.2) | openssl (7.2.2) | SimpleXML (7.2.2) | xml (7.2.2) | wddx (7.2.2) | xmlreader (7.2.2) | xmlwriter (7.2.2) | cgi-fcgi () | curl (7.2.2) | gd (7.2.2) | mbstring (7.2.2) | Phar (2.0.2) | pdo_mysql (7.2.2) | Zend Engine (3.2.0) |
Potential Missing Extensions :: mysqli |

Switch User Environment (Experimental) :: PHP CGI: Yes | Server SU: No | PHP SU: Yes | Custom SU (LiteSpeed/Cloud/Grid): Yes
Potential Ownership Issues: No

Core Folders :: images/ (777) | components/ (777) | modules/ (777) | plugins/ (777) | language/ (777) | templates/ (777) | cache/ (777) | logs/ (777) | tmp/ (777) | administrator/components/ (777) | administrator/modules/ (777) | administrator/language/ (777) | administrator/templates/ (777) | administrator/logs/ (---) |

Elevated Permissions (First 10) :: administrator/ (777) | administrator/cache/ (777) | administrator/components/ (777) | administrator/components/com_admin/ (777) | administrator/components/com_admin/controllers/ (777) | administrator/components/com_admin/helpers/ (777) | administrator/components/com_admin/helpers/html/ (777) | administrator/components/com_admin/models/ (777) | administrator/components/com_admin/models/forms/ (777) | administrator/components/com_admin/postinstall/ (777) |

Database statistics :: Uptime: 265270 | Threads: 1 | Questions: 412688 | Slow queries: 0 | Opens: 7289 | Flush tables: 1 | Open tables: 2000 | Queries per second avg: 1.555 |

Components :: SITE ::
Core :: com_mailto (3.0.0) 1 | com_wrapper (3.0.0) 1 |
3rd Party::

Components :: ADMIN ::
Core :: com_admin (3.0.0) 1 | com_ajax (3.2.0) 1 | com_associations (3.7.0) 1 | com_banners (3.0.0) 1 | com_cache (3.0.0) 1 | com_categories (3.0.0) 1 | com_checkin (3.0.0) 1 | com_config (3.0.0) 1 | com_content (3.0.0) 1 | com_contenthistory (3.2.0) 1 | com_cpanel (3.0.0) 1 | com_fields (3.7.0) 1 | com_finder (3.0.0) 1 | com_installer (3.0.0) 1 | com_joomlaupdate (3.6.2) 1 | com_languages (3.0.0) 1 | com_login (3.0.0) 1 | com_media (3.0.0) 1 | com_menus (3.0.0) 1 | com_messages (3.0.0) 1 | com_modules (3.0.0) 1 | com_newsfeeds (3.0.0) 1 | com_plugins (3.0.0) 1 | com_postinstall (3.2.0) 1 | com_redirect (3.0.0) 1 | com_search (3.0.0) 1 | com_tags (3.1.0) 1 | com_templates (3.0.0) 1 | com_users (3.0.0) 1 | com_weblinks (3.6.0) 1 |
3rd Party:: COM_CONTACTENHANCED (3.8.4.1) 1 | GSD (3.0.2) 1 | COM_OSMAP (4.2.18) 1 | com_rstbox (2.7.0) 1 | Simplified Social Share (5.1) 1 | com_widgetkit (2.9.18) 1 | com_zoo (3.3.32) 1 |

Modules :: SITE ::
Core :: mod_articles_archive (3.0.0) 1 | mod_articles_categories (3.0.0) 1 | mod_articles_category (3.0.0) 1 | mod_articles_latest (3.0.0) 1 | mod_articles_news (3.0.0) 1 | mod_articles_popular (3.0.0) 1 | mod_banners (3.0.0) 1 | mod_breadcrumbs (3.0.0) 1 | mod_custom (3.0.0) 1 | mod_feed (3.0.0) 1 | mod_finder (3.0.0) 1 | mod_footer (3.0.0) 1 | mod_languages (3.5.0) 1 | mod_login (3.0.0) 1 | mod_menu (3.0.0) 1 | mod_random_image (3.0.0) 1 | mod_related_items (3.0.0) 1 | mod_search (3.0.0) 1 | mod_stats (3.0.0) 1 | mod_syndicate (3.0.0) 1 | mod_tags_popular (3.1.0) 1 | mod_tags_similar (3.1.0) 1 | mod_users_latest (3.0.0) 1 | mod_weblinks (3.6.0) 1 | mod_whosonline (3.0.0) 1 | mod_wrapper (3.0.0) 1 |
3rd Party:: Contact Enhanced Alpha Index (3.2.1) 1 | Contact Enhanced Birthday (3.2.1) 1 | mod_ce_category (3.2.1) 1 | Contact Enhanced Slideshow (3.2.1) 1 | Contact Enhanced Form (3.8.4) 1 | Contact Enhanced Latest Submit (3.2.1) 1 | Contact Enhanced Search (3.7.1) 1 | Skillset (v1.4.0) 1 | tawk.to Module (3.0.1) 1 | Widgetkit (2.9.18) 1 | Builder (1.14. 1 | ZOO Category (3.3.32) 1 | ZOO Comment (3.3.32) 1 | ZOO Item (3.3.32) 1 | ZOO Tag (3.3.32) 1 |

Modules :: ADMIN ::
Core :: mod_custom (3.0.0) 1 | mod_feed (3.0.0) 1 | mod_latest (3.0.0) 1 | mod_logged (3.0.0) 1 | mod_login (3.0.0) 1 | mod_menu (3.0.0) 1 | mod_multilangstatus (3.0.0) 1 | mod_popular (3.0.0) 1 | mod_quickicon (3.0.0) 1 | mod_sampledata (3.8.0) 1 | mod_stats_admin (3.0.0) 1 | mod_status (3.0.0) 1 | mod_submenu (3.0.0) 1 | mod_title (3.0.0) 1 | mod_toolbar (3.0.0) 1 | mod_version (3.0.0) 1 |
3rd Party:: Contact Enhanced Latest Submit (3.2.1) 1 | Contact Enhanced Statistics (3.2.1) 1 | ZOO Quick Icons (3.3.32) 1 |

Plugins :: SITE ::
Core :: plg_authentication_cookie (3.0.0) 1 | plg_authentication_gmail (3.0.0) 0 | plg_authentication_joomla (3.0.0) 1 | plg_authentication_ldap (3.0.0) 0 | plg_captcha_recaptcha (3.4.0) 0 | plg_content_emailcloak (3.0.0) 1 | plg_content_fields (3.7.0) 1 | plg_content_finder (3.0.0) 0 | plg_content_joomla (3.0.0) 1 | plg_content_loadmodule (3.0.0) 1 | plg_content_pagebreak (3.0.0) 1 | plg_content_pagenavigation (3.0.0) 1 | plg_content_vote (3.0.0) 1 | plg_editors-xtd_article (3.0.0) 1 | plg_editors-xtd_fields (3.7.0) 1 | plg_editors-xtd_image (3.0.0) 1 | plg_editors-xtd_menu (3.7.0) 1 | plg_editors-xtd_module (3.5.0) 1 | plg_editors-xtd_pagebreak (3.0.0) 1 | plg_editors-xtd_readmore (3.0.0) 1 | plg_extension_joomla (3.0.0) 1 | plg_fields_calendar (3.7.0) 1 | plg_fields_checkboxes (3.7.0) 1 | plg_fields_color (3.7.0) 1 | plg_fields_editor (3.7.0) 1 | plg_fields_imagelist (3.7.0) 1 | plg_fields_integer (3.7.0) 1 | plg_fields_list (3.7.0) 1 | plg_fields_media (3.7.0) 1 | plg_fields_radio (3.7.0) 1 | plg_fields_sql (3.7.0) 1 | plg_fields_text (3.7.0) 1 | plg_fields_textarea (3.7.0) 1 | plg_fields_url (3.7.0) 1 | plg_fields_user (3.7.0) 1 | plg_fields_usergrouplist (3.7.0) 1 | plg_finder_categories (3.0.0) 1 | plg_finder_contacts (3.0.0) 1 | plg_finder_content (3.0.0) 1 | plg_finder_newsfeeds (3.0.0) 1 | plg_finder_tags (3.0.0) 1 | plg_finder_weblinks (3.6.0) 1 | PLG_INSTALLER_FOLDERINSTALLER (3.6.0) 1 | plg_installer_packageinstaller (3.6.0) 1 | PLG_INSTALLER_URLINSTALLER (3.6.0) 1 | plg_quickicon_extensionupdate (3.0.0) 1 | plg_quickicon_joomlaupdate (3.0.0) 1 | plg_quickicon_phpversioncheck (3.7.0) 1 | plg_search_categories (3.0.0) 1 | plg_search_contacts (3.0.0) 1 | plg_search_content (3.0.0) 1 | plg_search_newsfeeds (3.0.0) 1 | plg_search_tags (3.0.0) 1 | plg_search_weblinks (3.6.0) 1 | plg_system_cache (3.0.0) 0 | plg_system_debug (3.0.0) 1 | plg_system_fields (3.7.0) 1 | plg_system_highlight (3.0.0) 1 | plg_system_languagecode (3.0.0) 0 | plg_system_languagefilter (3.0.0) 0 | plg_system_log (3.0.0) 1 | plg_system_logout (3.0.0) 1 | plg_system_p3p (3.0.0) 1 | plg_system_redirect (3.0.0) 1 | plg_system_remember (3.0.0) 1 | plg_system_sef (3.0.0) 1 | plg_system_sessiongc (3.8.6) 1 | plg_system_stats (3.5.0) 1 | plg_system_updatenotification (3.5.0) 1 | plg_twofactorauth_totp (3.2.0) 0 | plg_twofactorauth_yubikey (3.2.0) 0 | plg_user_contactcreator (3.0.0) 0 | plg_user_joomla (3.0.0) 1 | plg_user_profile (3.0.0) 0 |
3rd Party:: Contact Enhanced - Custom Code (3.2.1) 0 | Contact Enhanced - Salesforce (3.7.2) 1 | Content - Contact Enhanced For (3.8.4) 1 | Simplified Social Share (5.1) 1 | Content - Social 2s (3.5.16) 0 | Content - Widgetkit (2.9.18) 1 | Content - ZOO Shortcode (3.3.32) 1 | plg_editors_codemirror (5.38.0) 1 | plg_editors_tinymce (4.5. 1 | Button - Contact Enhanced Form (3.7.2) 1 | Editors-XTD - Widgetkit (2.9.18) 1 | PLG_FINDER_CONTACTENHANCED (3.7.2) 1 | Smart Search - ZOO (3.3.32) 1 | PLG_GSD_CONTENT (1.0) 1 | Installer - YOOtheme (1.0.2) 1 | PLG_OSMAP_JOOMLA (4.2.18) 1 | plg_search_contactenhanced (3.7.2) 1 | Search - ZOO (3.3.32) 1 | System - Contact Enhanced (CE) (3.2.1) 1 | System - Contact Enhanced (CE) (3.8.3) 1 | PLG_CWFACEBOOKJS (1.0. 1 | PLG_CWGEARS (0.5.2) 1 | plg_system_gsd (3.0.2) 1 | System - iSeKeywords (3.1) 1 | System - Mail Links to Contact (3.2.1) 1 | plg_system_nrframework (2.4.1) 1 | plg_system_ossystem (1.3.0) 1 | plg_system_rstbox (3.0) 1 | System - tawk.to Plugin (3.0.1) 1 | System - Widgetkit (2.9.18) 1 | System - Widgetkit K2 (2.9.18) 0 | System - Widgetkit ZOO (2.9.18) 0 | System - YOOtheme Framework (1.14. 1 | System - ZOO Event (3.3.32) 1 | plg_user_ce_contactcreator (3.2.1) 0 |

Templates :: SITE :: beez3 (3.1.0) 1 | protostar (1.0) 1 | yootheme (1.14. 1 | yoo_avion (1.0.5) 1 |
Templates :: ADMIN :: hathor (3.0.0) 0 | isis (1.0) 1 |

[Per Yngve Berg]

I don't understand why there are 2 nested selects.

[HeMan321]

Neither do I, but this is the code from the 3.8.12 installer I just downloaded to check it was the same:

Code: Select all

	public static function removeOrphanNodes()
	{
		// Delete all orphaned nodes.
		$db = JFactory::getDbo();
		$query     = $db->getQuery(true);
		$subquery  = $db->getQuery(true);
		$subquery1 = $db->getQuery(true);
		$subquery1->select($db->quoteName('t.id'))
			->from($db->quoteName('#__finder_taxonomy', 't'))
			->join('LEFT', $db->quoteName('#__finder_taxonomy_map', 'm') . ' ON ' . $db->quoteName('m.node_id') . '=' . $db->quoteName('t.id'))
			->where($db->quoteName('t.parent_id') . ' > 1 ')
			->where($db->quoteName('m.link_id') . ' IS NULL');
		$subquery->select($db->quoteName('id'))
			->from('(' . $subquery1 . ') temp');
		$query->delete($db->quoteName('#__finder_taxonomy'))
			->where($db->quoteName('id') . ' IN (' . $subquery . ')');
		$db->setQuery($query);
		$db->execute();
		return $db->getAffectedRows();
	}

And as I mentioned above, doing an UPDATE\DELETE from the table used in the WHERE is not valid for MySQL.
So although I seem to be the first to report this (presumably because I am the first to cause this bit of code to be executed) it cannot ever work on MySQL can it?

[Webdongle]

So it happens spasmodically because it only happens when that piece of code is executed? If it is a bug ... is it specific to windows server/database?

[HeMan321]

Unfortunately I don't really know Joomla that well, so I don't know what the _finder_taxonomy table is for or when the removeOrphanNodes() function might be called? But this code is in the administrator\components\com_finder\helpers\indexer\taxonomy.php file, which is not platform specific as far as I know. However, it is only MySQL that won't allow the table you are deleting from to be used in the WHERE clause, so this would probably only happen on MySQL (but surely that is the most common DB isn't it?).
Does anyone know any more about this that may shed some light on it?

[Webdongle]

Can you describe the exact process that you go through to replicate the error? If you can then I will see if I can reproduce the error.

[HeMan321]

OK, if I do the following...
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.
At that point I will get the:

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

I notice that if I then save again, even if I close and re-open it before saving, it will work fine with no error, but if I then do it again it will always fail. So it's like it fails every 2nd save after every successful save.

[Per Yngve Berg]

I presume you have to have the Smart Search Plugin enabled for this to occur.

[HeMan321]

Yes, I re-enabled the plug-in before I tested that procedure.

[Per Yngve Berg]

The query can be re-written using the example in the Mysql documentation.

Code: Select all

	public static function removeOrphanNodes()
	{
		// Delete all orphaned nodes.
		$db = JFactory::getDbo();
		$query     = $db->getQuery(true);
		$query->delete($db->quoteName('#__finder_taxonomy', 't'))
			->join('LEFT', $db->quoteName('#__finder_taxonomy_map', 'm') . ' ON ' . $db->quoteName('m.node_id') . '=' . $db->quoteName('t.id'))
			->where($db->quoteName('t.parent_id') . ' > 1 ')
			->where($db->quoteName('m.link_id') . ' IS NULL');
	
		$db->setQuery($query);
		$db->execute();
		return $db->getAffectedRows();
	}

[HeMan321]

Indeed I think you are probably right. Below is the same function from an old V3.4.5 installation, so it seems the query used to be structured this way to start with and somewhere between 3.4.5 and 3.8.8 it was changed in a way that would break MySQL.

But a couple of questions occur...
1) How do we go about getting the code in the next release of Joomla fixed?
2) Why was the change made, did someone have a good reason for it or was it just an error?
3) Why are we the only people to have noticed this?

Code: Select all

	public static function removeOrphanNodes()
	{
		// Delete all orphaned nodes.
		$db = JFactory::getDbo();
		$query = 'DELETE t' .
			' FROM ' . $db->quoteName('#__finder_taxonomy') . ' AS t' .
			' LEFT JOIN ' . $db->quoteName('#__finder_taxonomy_map') . ' AS m ON m.node_id = t.id' .
			' WHERE t.parent_id > 1' .
			' AND m.link_id IS NULL';
		$db->setQuery($query);
		$db->execute();
		return $db->getAffectedRows();
	}

[Per Yngve Berg]

It was changed before 3.7.5.

It has to reported on the Bug Tracker.

[Webdongle]

You can report it at https://issues.joomla.org/ please link to this thread in the report. (and visa versa).

[HeMan321]

OK, done...
Here: https://github.com/joomla/joomla-cms/issues/22231
or here: https://issues.joomla.org/tracker/joomla-cms/22231

So are we thinking this is a bug? And there is nothing more I can do now I guess?

Thanks for your help getting this far though guys, at least I know it is not something we are doing...

[HeMan321]

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?

[Webdongle]

Perhaps https://docs.joomla.org/Technical_requirements needs to be updated? But my site runs on 5.5.60-0 which is even lower version. So perhaps it's the database on windows ?

[HeMan321]

Webdongle, are you able to run the following on your DB to see if it works?

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)

[Webdongle]

I think the answer to that is no because it doesn't create the table ... thus there is no table to delete.

test query 01.JPG

[Per Yngve Berg]

I looked in the docs for Mysql 8.0. It have the same sentence about not able to sub-query and delete.