Make WordPress Core

Opened 6 years ago

Last modified 4 years ago

#45078 new defect (bug)

null is being set on a NOT NULL options_value column in the options table.

Reported by: wpshades's profile wpshades Owned by:
Milestone: Awaiting Review Priority: normal
Severity: normal Version:
Component: Options, Meta APIs Keywords: has-patch
Focuses: Cc:


The options table doesn't allow a NULL value in the options_value column.

But, if an option is unchecked, it is set to null by default here:

This throws a Column ‘option_value’ cannot be null error if strict mode(STRICT_TRANS_TABLES) is enabled in the database.

If strict mode isn't enabled, MySQL converts the null value to a default value, in this case, an empty string, before adding it to the options table.

If you are not using strict mode, then whenever you insert an “incorrect” value into a column, such as a NULL into a NOT NULL column or a too-large numeric value into a numeric column, MySQL sets the column to the “best possible value” instead of producing an error

The proposed fix changes the default value from null to an empty string(following MySQL). This will prevent the error being thrown when strict mode is enabled and otherwise won't alter the values inserted into the option_value column.

Attachments (1)

0001.diff (395 bytes) - added by wpshades 6 years ago.
Set the default value of an unchecked option to empty string().

Download all attachments as: .zip

Change History (5)

6 years ago

Set the default value of an unchecked option to empty string().

#1 @wpshades
6 years ago

Another thing that I wanted to note was if safe mode is turned on and we turn off any options under wp-admin->Settings they are automatically turned back on as the update_option() fails and returns false.

#2 @pento
5 years ago

  • Component changed from Database to Options, Meta APIs
  • Version trunk deleted

#3 @SergeyBiryukov
4 years ago

#49135 was marked as a duplicate.

#4 @SergeyBiryukov
4 years ago

Hi there, welcome to WordPress Trac! Thanks for the report, sorry it took so long for someone to get back to you.

Just noting that WordPress does not support MySQL strict mode per design decisions and the previous discussions in #8857, #14978, #24912, #26847.

Also related: #43589.

Note: See TracTickets for help on using tickets.