Opened 3 years ago
Last modified 3 years ago
#52723 new defect (bug)
Admin options.php default value to NULL for option_value may lead to MySQL Integrity constraint violation error, potential other bugs
Reported by: |
|
Owned by: | |
---|---|---|---|
Milestone: | Awaiting Review | Priority: | normal |
Severity: | normal | Version: | |
Component: | General | Keywords: | has-patch |
Focuses: | administration | Cc: |
Description
It looks like wp-admin/options.php
set a null
value by default for any unchecked option:
https://core.trac.wordpress.org/browser/trunk/src/wp-admin/options.php#L306
Now, this leads to execute queries like this by update_option
:
UPDATE
wp_options
SEToption_value
= NULL WHEREoption_name
= 'default_pingback_flag'
Which is wrong, given the schema explicitly set option_value
to NOT NULL
:
https://core.trac.wordpress.org/browser/trunk/src/wp-admin/includes/schema.php#L144
This would trigger an integrity constraint violation error by MySQL when in (default) strict mode:
Error! SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'option_value' cannot be null
To get around this (and for other reasons too, I presume), WordPress currently tries to disable any MySQL strict mode in the $wpdb
class, with the effect that MySQL silently "fix" the error itself:
https://core.trac.wordpress.org/browser/trunk/src/wp-includes/wp-db.php#L567
https://core.trac.wordpress.org/browser/trunk/src/wp-includes/wp-db.php#L826
But not every environment support this, so there are people out there who cannot save options and they are confused about the reason why, for example:
https://www.reddit.com/r/Wordpress/comments/l61rvs/cannot_disable_avatars/
https://wordpress.org/support/topic/discussion-comment-settings-saved-changes-are-not-taking-effect-at-all/
https://wordpress.org/support/topic/wordpress-database-error-column-option_value-cannot-be-null/
A simple solution would be to set a different default value (0
or even an empty string) in wp-admin/options.php
and, better yet, cast any NULL
value to the same different default value in both update_option
and add_option
.
Please note that, without a fix, this bug may also lead to other nasty side effects.
As a quick fix/test, I successful got around this with this simple filter:
<?php add_action( 'init', 'p_options_fix' ); function p_options_fix() { add_filter( 'pre_update_option', 'p_options_fix_not_null', 10, 3 ); function p_options_fix_not_null( $value, $option, $old_value ) { // The fix: cast NULL values to 0 $value = ( is_null($value) ) ? 0 : $value; return $value; } }
But I think this is something that should be really fixed in the core.
Thank you.
Change History (1)
This ticket was mentioned in PR #1074 on WordPress/wordpress-develop by pinoceniccola.
3 years ago
#1
- Keywords has-patch added
Replaces
null
value as default admin option value to avoid bugs and side-effects. See ticket for details.Trac ticket: https://core.trac.wordpress.org/ticket/52723