WordPress database error You can't specify target table 'wp_postmeta' / 'wp_termmeta' for update in FROM clause

[yoancutillas]

Hello, I am getting an error right after migrating QTS Slugs:

[25-Jul-2022 12:09:57 UTC] WordPress database error You can't specify target table 'wp_postmeta' for update in FROM clause for query DELETE FROM wp_postmeta WHERE meta_key like 'qtranslate_slug_%' AND post_id in (SELECT DISTINCT(post_id) FROM wp_postmeta WHERE meta_key LIKE '_qts_slug_%') made by require_once('wp-admin/admin.php'), do_action('admin_init'), WP_Hook->do_action, WP_Hook->apply_filters, qtranxf_admin_init, qtranxf_edit_config, qtranxf_executeOnUpdate, qtranxf_slugs_migrate_qts_data, qtranxf_slugs_migrate_qts_meta, {closure}
[25-Jul-2022 12:09:58 UTC] WordPress database error You can't specify target table 'wp_termmeta' for update in FROM clause for query DELETE FROM wp_termmeta WHERE meta_key like 'qtranslate_slug_%' AND term_id in (SELECT DISTINCT(term_id) FROM wp_termmeta WHERE meta_key LIKE '_qts_slug_%') made by require_once('wp-admin/admin.php'), do_action('admin_init'), WP_Hook->do_action, WP_Hook->apply_filters, qtranxf_admin_init, qtranxf_edit_config, qtranxf_executeOnUpdate, qtranxf_slugs_migrate_qts_data, qtranxf_slugs_migrate_qts_meta, {closure}

The reason is probably that: https://stackoverflow.com/a/4562797

you cannot modify the same table from a subquery within the same query. However, you can either SELECT then DELETE in separate queries, or nest another subquery and alias the inner subquery result (looks rather hacky, though):

DELETE FROM posts WHERE id IN (
    SELECT * FROM (
        SELECT id FROM posts GROUP BY id HAVING ( COUNT(id) > 1 )
    ) AS p
)

Indeed it is working properly if you replace slugs-migrate-qts.php line 73 $results = $wpdb->query( "DELETE FROM $table WHERE meta_key like '$new_prefix%' AND $colid in ($id_to_delete)" ); with $results = $wpdb->query( "DELETE FROM $table WHERE meta_key like '$new_prefix%' AND $colid in ( SELECT * FROM ( $id_to_delete ) as M )" );

[herrvigg]

No one else encountered this problem it seems. What DB do you use?

[herrvigg]

@yoancutillas could you tell which DB engine you are using, to understand if it's a specific case or more general?

[herrvigg]

@spleen1981 What do you think about this? Did we miss some cases where this won't work for all DB?

[spleen1981]

Did we miss some cases where this won't work for all DB?

I would say no AFAIK, but it would be interesting to know what DB/engine this issue applies to...

[yoancutillas]

Sorry for the delay, here it is:

General settings
Server connection collation: utf8mb4_unicode_ci

Database server
Server: MySQL (127.0.0.1 via TCP/IP)
Server type: MySQL
Server version: 8.0.29 - MySQL Community Server - GPL
Protocol version: 10
Server charset: UTF-8 Unicode (utf8mb4)

Web server
Apache/2.4.54 (Win64) OpenSSL/1.1.1o PHP/8.0.11 mod_fcgid/2.3.10-dev
Database client version: libmysql - mysqlnd 8.0.11
PHP extension: mysqli curl mbstring 
PHP version: 8.0.11

phpMyAdmin
Version information: 5.2.0 (up to date)

It is a MySQL error, I don't understand why you can't reproduce it:

[spleen1981]

Ok, it seems current query works properly with MariaDB but generates the error above with MySQL. The proposed query seems to work correctly both with MariaDB and MySQL. @yoancutillas I guess you can submit a PR with your proposal.

[herrvigg]

Interesting. In production I have MariaDB. But for development I have MySQL (in a docker container).

With MySQL, I don't have the problem if I run the qTranslate code through PHP (8.0)... But, if I try a similar SQL command with phpMyAdmin, I can reproduce this! I tried with MySQL 5.7 and 8.0, it's the same.

@spleen1981 did you manage to reproduce the error in PHP or phpMyAdmin? @yoancutillas same question but only for the initial issue, was it PHP?

[spleen1981]

@herrvigg reproduced in phpMyAdmin

[herrvigg]

When you wrote it works with MariaDB, did you also try it in phpMyAdmin?

[yoancutillas]

Yes it was in PHP, the SQL query failed during the plugin execution, and the logs you can see in the frist report were found in the PHP error log file. Then, I could reproduce the issue in phpMyAdmin too.

[spleen1981]

When you wrote it works with MariaDB, did you also try it in phpMyAdmin?

yes

[herrvigg]

So the only consistent point is that MariaDB tolerates this. With MySQL it depends on something else that I don't fully understand. The fix is merged so it should be fine. But I'd like to be able to reproduce it from PHP, to prevent similar issues in the future.

[herrvigg]

To be complete, it's possible only from MariaDB 10.3. It failed in MariaDB 10.2 (as for MySQL). https://stackoverflow.com/a/50749204

[herrvigg]

So much fun with SQL, the fix can even fail due to the optimizer... https://stackoverflow.com/a/45498.

[herrvigg]

I tried to reproduce it on PHP and in fact there's something, I see it but only as php:notice. So it's a very soft warning, it still executes the query.

@yoancutillas In your case did the query really fail when run from PHP? Did it propagate as a PHP error (not notice or warning)?

[herrvigg]

Improved the LIKE queries by escaping the _ character (#1217). While doing tests I played with the prefix back and forth and realized it was not working as expected. It was working for the general migration case, but this is a small improvement if developers do something similar.

[herrvigg]

Released in 3.12.1.

[yoancutillas]

In my case, the SQL query failed when run from the plugin's PHP code, the data didn't changed (just like when run from phpMyAdmin).

It was not a PHP error nor a PHP warning, it was a log written by Wordpress into the PHP error log file to inform the developer that the SQL query has failed, with the mySQL error log message (which you can see is the same as in phpMyAdmin): https://github.com/WordPress/WordPress/blob/6.0.2/wp-includes/wp-db.php#L1582

So it doesn't prevent further PHP execution.

Note that such messages don't appear if this code is executed somewhere before: $wpdb->suppress_errors();