Can't save site settings when unchecking an options – NOT NULL constraint violation

[adamziel]

1. Go to discussion options: https://playground.wordpress.net/?url=/wp-admin/options-discussion.php#show_avatars
2. Uncheck "Attempt to notify any blogs linked to from the post"
3. Click save
4. Notice the option is still checked

Devtools error:

<p>MySQL query:</p>
<p>UPDATE `wp_options` SET `option_value` = NULL WHERE `option_name` = 'default_pingback_flag'</p>
<p>Queries made or created this session were:</p>
<ol>
<li>Executing: BEGIN | (no parameters)</li>
<li>Executing: UPDATE `wp_options` SET `option_value` = NULL WHERE `option_name` = :param0  | parameters: default_pingback_flag</li>
<li>Executing: ROLLBACK | (no parameters)</li>
</ol>
</div>
<div style="clear:both;margin-bottom:2px;border:red dotted thin;" class="error_message" style="border-bottom:dotted blue thin;">
Error occurred at line 3449 in Function <code>handle_error</code>. Error message was: SQLSTATE[23000]: Integrity constraint violation: 19 NOT NULL constraint failed: wp_options.option_value.
</div>

This is weird as WordPress does have a NOT NULL constraint on wp_options.values:

https://github.com/WordPress/wordpress-develop/blob/f9b59e940dcb34a9b8cd6faacc5c35a2846dbbd2/src/wp-admin/includes/schema.php#L144

Is it an issue with parsing POST data where the server things something is null when it shouldn't?

Props to @flexseth and @ironnysh for finding and describing the issue and providing a reproduction 🙌

[adamziel]

Additional info:

I can also do it: I did some more testing and got two errors in Safari and Firefox, Chrome "fakes" successful Settings saved but reverted back the same way.

[adamziel]

This code path is interesting:

https://github.com/WordPress/WordPress/blob/6d5d992cf1e1eadba2ac1bb4176fc27c0ef00033/wp-admin/options.php#L331-L340

So WordPress will call update_option() with null as value for options not received as $_POST keys. Does it actually work in wp-admin? Why?

[bgrgicak]

On a Apache site updating the setting works as expected. Running the query with a null value in MySQL returns an error.

I'm going to check the Playground code now. I assume that we interpret '' as undefined when creating POST data.

[bgrgicak]

I traced the option update all the way to the MySQL call it still had null as the value.

[bgrgicak]

This code doesn't produce errors which makes me think it's related to mysqli and how it handles null values in not null columns.

$wpdb->query("UPDATE `wp_options` SET `option_value` = '1' WHERE `option_name` = 'default_pingback_flag';");
if ($wpdb->last_error) {
	wp_die($wpdb->last_error);
}
var_dump($wpdb->get_row("SELECT * FROM `wp_options` WHERE `option_name` = 'default_pingback_flag';"));

$wpdb->query("UPDATE `wp_options` SET `option_value` = NULL WHERE `option_name` = 'default_pingback_flag';");
if ($wpdb->last_error) {
	wp_die($wpdb->last_error);
}
var_dump($wpdb->get_row("SELECT * FROM `wp_options` WHERE `option_name` = 'default_pingback_flag';"));

[bgrgicak]

Running MySQL in the same place results in an error, so there must be something that WP does to the MySQL instance for it to work.

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("", "", "", "");

$mysqli->query("UPDATE `wp_options` SET `option_value` = '1' WHERE `option_name` = 'default_pingback_flag';");
$mysqli->real_query("SELECT * FROM `wp_options` WHERE `option_name` = 'default_pingback_flag';");
var_dump($mysqli->use_result());

$mysqli->query("UPDATE `wp_options` SET `option_value` = NULL WHERE `option_name` = 'default_pingback_flag';");
$mysqli->real_query("SELECT * FROM `wp_options` WHERE `option_name` = 'default_pingback_flag';");
var_dump($mysqli->use_result());

[bgrgicak]

Found it! The difference is in the SQL mode . WordPress updates SQL modes when connecting. This allows it to pass NULL as a value.

Next step, find why modes are not set in Playground.

[bgrgicak]

This is an issue that needs to be resolved in the SQLIte database integration plugin.

I'm checking now if there is a setting in SQLite that allow us to ignore Not null and use the default value instead. If not the only other thing that I can think of is removing not null and setting a default.

[bgrgicak]

This PR should fix the issue https://github.com/WordPress/sqlite-database-integration/pull/91 @adamziel could you please take a look at it when you find some time?

[bgrgicak]

The SQLite plugin update didn't fix the issue. I will keep working on this tomorrow.

[bgrgicak]

The last change to how defaults work fixed the issue.

[flexseth]

The last change to how defaults work fixed the issue.

Thanks for taking a look at this @bgrgicak!