Errors when updating options (SQLSTATE[23000]: Integrity constraint violation)

[pinoceniccola]

It looks like there are errors when you try to save some options on the backend.

From what I can see, schema for option_value is TEXT NOT NULL but the query executed try to set value to NULL.

Steps to reproduce: In the backend, go to Settings -> Discussion, deselect any option and try to save.

Wordpress Debug Output:

WordPress database error: [
 
Queries made or created this session were

    Raw query: UPDATE `wp_options` SET `option_value` = NULL WHERE `option_name` = 'default_pingback_flag'
    Rewritten: UPDATE wp_options SET option_value = NULL WHERE option_name = 'default_pingback_flag'
    With Placeholders: UPDATE wp_options SET option_value = NULL WHERE option_name = :param_0
    Prepare: UPDATE wp_options SET option_value = NULL WHERE option_name = :param_0
    Executing: array ( 0 => 'default_pingback_flag', )

Error occurred at line 1723 in Function execute_query.
Error message was: Error while executing query! Error message was: SQLSTATE[23000]: Integrity constraint violation: 19 NOT NULL constraint failed: wp_options.option_value

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

debug.txt:

Line 1723, Function: execute_query, Message: Error while executing query! Error message was: SQLSTATE[23000]: Integrity constraint violation: 19 NOT NULL constraint failed: wp_posts.comment_status 
Line 1723, Function: execute_query, Message: Error while executing query! Error message was: SQLSTATE[23000]: Integrity constraint violation: 19 NOT NULL constraint failed: wp_options.option_value 

WordPress version: 5.6.2
PHP version: 7.4

Note: When WP_DEBUG is set to false it just fails to update the options silently.

[pinoceniccola]

As a quick fix/test I tried to filter new option values and change false/NULL values to just 0 and it seems to work.

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 ) {

		// cast false-y values to 0
		if ( $value === false || $value === NULL ) {
   			$value = 0;
   		}

   		return $value;
	}
}

But it still does look very fragile to me. Also, this may still happen in other tables (from reading debug.txt, at least on wp_posts.comment_status too).

@aaemnnosttv Is this a known issue or something that happens only to me?

I like the portability of a file-only, lightweight WordPress powered by SQLite, but this issue may bring some serious side effects.

Thanks.

[aaemnnosttv]

I haven't seen this before but I was able to reproduce what you're seeing. I'm not really sure what's going on because WP sets those to NULL in the query with MySQL as well which you would think would raise the same integrity constraint violation 🤔

Will look into this more soon.

[pinoceniccola]

Looking further, it looks like WordPress explicit set the (unchecked) option values to null by default :

https://github.com/WordPress/WordPress/blob/master/wp-admin/options.php#L306

But I don't know why this doesn't trigger the same error with MySQL. I tried to set sql_mode to both STRICT_TRANS_TABLES (which is the default mode, by the way) and STRICT_ALL_TABLES on my localhost but was unable to reproduce this error.

At the same time, random people seems to experience this even on MySQL:

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/

But yes, this looks like something related with WordPress itself. Nevertheless, executing something like `UPDATE `wp_options` SET `option_value` = NULL` looks very wrong, will report this in the WP trac.

[pinoceniccola]

Ok, this has taken more time than expected but, for completeness, the reason why this does not fail with MySQL is that WordPress explicitly disable any strict mode by default, and without it, MySQL silently "fix" any incorrect values:

https://github.com/WordPress/WordPress/blob/f0078d043e0f2805c2400bd5e869eb3533712eec/wp-includes/wp-db.php#L567
https://github.com/WordPress/WordPress/blob/f0078d043e0f2805c2400bd5e869eb3533712eec/wp-includes/wp-db.php#L826

All of this of course has no effect on this SQLite implementation.

While this should be fixed in WordPress core, I don't know how easily or even if it would be a good idea to patch db.php to cast any NULL value to 0 (which should work at least on both TEXT and INTEGER fields) before executing any query.

At least, my previous fix seems to fix this issue but should be installed as a separate plugin or included in a theme functions.php, for now.

Thanks.

[ajusa]

Was there any update on this? I ran into this issue just today, is there a Wordpress issue to track? Or any reccommended workarounds?

[pinoceniccola]

At the time I submitted a PR but went unnoticed:

WordPress/wordpress-develop#1074

The simple filter inside the trac ticket or at the top of this thread should fix most of the issues, though.

[ajusa]

Got it, thanks for the update! I'm not super familiar with Wordpress scripts/plugins, how would you recommend I use that filter? Do I just put it inside of my wordpress config PHP file, or should it go somewhere else?

[pinoceniccola]

@ajusa You can put it in a simple, dedicated plugin (you can use something like this as a template) or in your theme functions.php file.

[SlavaAurim]

Simplest fix in the my db.php: (for PHP 7.4+)

// replace NULL to ''
add_filter('pre_update_option', fn($val) => $val ?? '');

// include original dropin
include __DIR__ . '/dropins/wp-sqlite-db/src/db.php';