Opened 12 months ago
Last modified 2 months ago
#60503 new defect (bug)
MySQL VALUES function deprecated in MySQL 8
Reported by: |
|
Owned by: | |
---|---|---|---|
Milestone: | Awaiting Review | Priority: | normal |
Severity: | minor | Version: | |
Component: | Database | Keywords: | close |
Focuses: | Cc: |
Description
e.g. for add_option SQL query (but issue happens in other places too, but not many)
"INSERT INTO `$wpdb->options` (`option_name`, `option_value`, `autoload`) VALUES (%s, %s, %s) ON DUPLICATE KEY UPDATE `option_name` = VALUES(`option_name`), `option_value` = VALUES(`option_value`), `autoload` = VALUES(`autoload`)"
we get the SQL logs spammed with:
'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
Since WP 6.4 minimum SQL is 8+, so this deprecated syntax isn't necessary anymore, since the new syntax is supported in all supported SQL versions.
This should have been fixed in WP 6.4, but at least now with WP 6.5 release, as it's easy to fix and avoid having a performance penalty from the deprecation notice handling/reporting.
Change History (5)
#2
@
12 months ago
- Component changed from General to Database
- Focuses performance php-compatibility removed
- Keywords needs-patch added
- Severity changed from blocker to normal
- Version trunk deleted
#3
@
12 months ago
You're right - the description of https://make.wordpress.org/hosting/handbook/compatibility/#wordpress-php-mysql-mariadb-versions is slightly confusing in that regard (and you have to scroll all the way to the bottom)
Anyway, it's possible to make this work correctly without a deprecation notice in MySQL 8 as well as in MySQL 5.
The fact that WP 6.4 states it recommends MySQL 8.0-8.2 but then causes deprecation errors isn't really consistent, is it?
#4
@
2 months ago
Did some testing.
This newer
...VALUES('what','ever') AS new
ON DUPLICATE KEY UPDATE key=new.key, val=new.val
syntax does not work on MariaDB 10.11.10 (a recent version). Nor on MySQL 5.5.62 or 5.7.42 according to my testing. The old
ON DUPLICATE KEY UPDATE key=VALUES(key), val=VALUES(val)
syntax works in all cases I've tried (not a surprise).
The new syntax does work on MySQL 8.0.27. So, at best we can't get rid of the old syntax.
If the MySQL people follow through on their promise to remove the old syntax from a future version, we'll be faced with a breaking incompatibility between MariaDB and MySQL.
I put a ticket in MariaDB's JIRA. https://jira.mariadb.org/browse/MDEV-35586 .
We can't change anything in WordPress because we can't break recent MariaDB versions. Hopefully we won't be forced to have database-vendor-specific code in future versions if MariaDB's maintainers will chase Oracle on this feature.
#5
@
2 months ago
- Keywords close added; needs-patch removed
- Severity changed from normal to minor
I assume the SQL log you mention, the one getting spammed with warnings about deprecated SQL syntax, is the stuff from the system table, documented here,
performance_schema.events_statements_summary_by_digest
shown in the view
sys.statements_with_errors_or_warnings
If that's not correct, please write a comment showing which log you have in mind.
I am pretty sure that maintaining that log is very low overhead for the server.
If Oracle, as the owners of MySQL, decide to actually remove the languuage features they've deprecated, that will cause a lot of scrambling in the WordPress world. Most of that scrambling will be to switch over to MariaDB. Hopefully people are tracking this.
The deprecations I see are this INSERT ... ON DUPLICATE KEY UPDATE ... VALUES() thing, and SQL_CALC_FOUND_ROWS ... LIMIT.
8.0 or greater is only a recommendation. Requirement is 5.5.5 or greater, which was bumped from 5.0 in [57173].