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

Did you find a bug in Joomla! 3.x but aren't sure? This forum is the place to help figure out if the problem is a bug and how to report it. If you are an experienced Joomla! user and are certain that you have found a bug please use the Bug Tracker to submit your issue.
This forum is for discussion about bugs and to get help with reporting them to the Bug Tracker: https://issues.joomla.org

Moderator: ooffick

Forum rules
Please use the official Bug Tracker to report a bug: https://issues.joomla.org
Post Reply
HeMan321
Joomla! Apprentice
Joomla! Apprentice
Posts: 20
Joined: Thu Apr 05, 2012 3:43 pm

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

Post by HeMan321 » Fri Sep 14, 2018 11:15 am

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.

User avatar
Webdongle
Joomla! Master
Joomla! Master
Posts: 37006
Joined: Sat Apr 05, 2008 9:58 pm

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

Post by Webdongle » Fri Sep 14, 2018 1:39 pm

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
http://www.weblinksonline.co.uk/
https://www.weblinksonline.co.uk/updating-joomla.html
"The definition of insanity is doing the same thing over and over again, but expecting different results": Albert Einstein

HeMan321
Joomla! Apprentice
Joomla! Apprentice
Posts: 20
Joined: Thu Apr 05, 2012 3:43 pm

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

Post by HeMan321 » Mon Sep 17, 2018 11:49 am

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).
Basic Environment :: wrote: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
Detailed Environment :: wrote: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
Folder Permissions :: wrote: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 Information :: wrote: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 |
Extensions Discovered :: wrote: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.8) 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.8) 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.8) 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.8) 1 | System - ZOO Event (3.3.32) 1 | plg_user_ce_contactcreator (3.2.1) 0 |
Templates Discovered :: wrote:Templates :: SITE :: beez3 (3.1.0) 1 | protostar (1.0) 1 | yootheme (1.14.8) 1 | yoo_avion (1.0.5) 1 |
Templates :: ADMIN :: hathor (3.0.0) 0 | isis (1.0) 1 |

User avatar
Per Yngve Berg
Joomla! Master
Joomla! Master
Posts: 25660
Joined: Mon Oct 27, 2008 9:27 pm
Location: Akershus, Norway

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

Post by Per Yngve Berg » Mon Sep 17, 2018 1:19 pm

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

HeMan321
Joomla! Apprentice
Joomla! Apprentice
Posts: 20
Joined: Thu Apr 05, 2012 3:43 pm

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

Post by HeMan321 » Mon Sep 17, 2018 1:59 pm

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?

User avatar
Webdongle
Joomla! Master
Joomla! Master
Posts: 37006
Joined: Sat Apr 05, 2008 9:58 pm

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

Post by Webdongle » Mon Sep 17, 2018 3:14 pm

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?
http://www.weblinksonline.co.uk/
https://www.weblinksonline.co.uk/updating-joomla.html
"The definition of insanity is doing the same thing over and over again, but expecting different results": Albert Einstein

HeMan321
Joomla! Apprentice
Joomla! Apprentice
Posts: 20
Joined: Thu Apr 05, 2012 3:43 pm

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

Post by HeMan321 » Mon Sep 17, 2018 3:37 pm

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?

User avatar
Webdongle
Joomla! Master
Joomla! Master
Posts: 37006
Joined: Sat Apr 05, 2008 9:58 pm

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

Post by Webdongle » Mon Sep 17, 2018 5:26 pm

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.
http://www.weblinksonline.co.uk/
https://www.weblinksonline.co.uk/updating-joomla.html
"The definition of insanity is doing the same thing over and over again, but expecting different results": Albert Einstein

HeMan321
Joomla! Apprentice
Joomla! Apprentice
Posts: 20
Joined: Thu Apr 05, 2012 3:43 pm

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

Post by HeMan321 » Tue Sep 18, 2018 8:19 am

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.

User avatar
Per Yngve Berg
Joomla! Master
Joomla! Master
Posts: 25660
Joined: Mon Oct 27, 2008 9:27 pm
Location: Akershus, Norway

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

Post by Per Yngve Berg » Tue Sep 18, 2018 9:19 am

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

HeMan321
Joomla! Apprentice
Joomla! Apprentice
Posts: 20
Joined: Thu Apr 05, 2012 3:43 pm

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

Post by HeMan321 » Tue Sep 18, 2018 9:44 am

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

User avatar
Per Yngve Berg
Joomla! Master
Joomla! Master
Posts: 25660
Joined: Mon Oct 27, 2008 9:27 pm
Location: Akershus, Norway

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

Post by Per Yngve Berg » Tue Sep 18, 2018 10:09 am

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
Joomla! Apprentice
Joomla! Apprentice
Posts: 20
Joined: Thu Apr 05, 2012 3:43 pm

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

Post by HeMan321 » Tue Sep 18, 2018 10:25 am

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();
	}

User avatar
Per Yngve Berg
Joomla! Master
Joomla! Master
Posts: 25660
Joined: Mon Oct 27, 2008 9:27 pm
Location: Akershus, Norway

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

Post by Per Yngve Berg » Tue Sep 18, 2018 10:39 am

It was changed before 3.7.5.

It has to reported on the Bug Tracker.

User avatar
Webdongle
Joomla! Master
Joomla! Master
Posts: 37006
Joined: Sat Apr 05, 2008 9:58 pm

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

Post by Webdongle » Tue Sep 18, 2018 10:48 am

You can report it at https://issues.joomla.org/ please link to this thread in the report. (and visa versa).
http://www.weblinksonline.co.uk/
https://www.weblinksonline.co.uk/updating-joomla.html
"The definition of insanity is doing the same thing over and over again, but expecting different results": Albert Einstein

HeMan321
Joomla! Apprentice
Joomla! Apprentice
Posts: 20
Joined: Thu Apr 05, 2012 3:43 pm

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

Post by HeMan321 » Tue Sep 18, 2018 12:21 pm

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
Joomla! Apprentice
Joomla! Apprentice
Posts: 20
Joined: Thu Apr 05, 2012 3:43 pm

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

Post by HeMan321 » Wed Sep 19, 2018 8:58 am

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?

User avatar
Webdongle
Joomla! Master
Joomla! Master
Posts: 37006
Joined: Sat Apr 05, 2008 9:58 pm

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

Post by Webdongle » Wed Sep 19, 2018 9:28 am

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 ?
http://www.weblinksonline.co.uk/
https://www.weblinksonline.co.uk/updating-joomla.html
"The definition of insanity is doing the same thing over and over again, but expecting different results": Albert Einstein

HeMan321
Joomla! Apprentice
Joomla! Apprentice
Posts: 20
Joined: Thu Apr 05, 2012 3:43 pm

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

Post by HeMan321 » Wed Sep 19, 2018 12:44 pm

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)

User avatar
Webdongle
Joomla! Master
Joomla! Master
Posts: 37006
Joined: Sat Apr 05, 2008 9:58 pm

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

Post by Webdongle » Wed Sep 19, 2018 1:50 pm

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
You do not have the required permissions to view the files attached to this post.
http://www.weblinksonline.co.uk/
https://www.weblinksonline.co.uk/updating-joomla.html
"The definition of insanity is doing the same thing over and over again, but expecting different results": Albert Einstein

User avatar
Per Yngve Berg
Joomla! Master
Joomla! Master
Posts: 25660
Joined: Mon Oct 27, 2008 9:27 pm
Location: Akershus, Norway

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

Post by Per Yngve Berg » Wed Sep 19, 2018 3:31 pm

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


Post Reply

Return to “Joomla! 3.x Bug Reporting”