Make WordPress Core

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: pinoceniccola's profile pinoceniccola Owned by:
Milestone: Awaiting Review Priority: normal
Severity: normal Version:
Component: General Keywords: has-patch
Focuses: administration Cc:


It looks like wp-admin/options.php set a null value by default for any unchecked option:

Now, this leads to execute queries like this by update_option:

UPDATE wp_options SET option_value = NULL WHERE option_name = 'default_pingback_flag'

Which is wrong, given the schema explicitly set option_value to NOT NULL:

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:

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:

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:

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

  • Keywords has-patch added

Replaces null value as default admin option value to avoid bugs and side-effects. See ticket for details.

Trac ticket:

Note: See TracTickets for help on using tickets.