Solution for MariaDB Field 'xxx' doesn't have a default value

  Follow me: Follow Bruce Kirkpatrick by email subscription Bruce Kirkpatrick on Twitter Bruce Kirkpatrick on Facebook
Mon, Feb 04, 2013 at 2:20PM

In the process of migrating from MySQL 5.5 to MariaDB 5.5, I made a configuration mistake.

When I installed MariaDB with the default options, it had enabled the sql mode, "STRICT_TRANS_TABLES".

It took me a while to realize that I needed to change the my.ini / my.cnf value for sql-mode to be the following:

sql-mode="NO_ENGINE_SUBSTITUTION"

My application is not designed for the strict_trans_tables behavior, which seems to require that a default value is explicitly set on every column or insert/update/replace statements will fail.  Rather then update the schema on every table, I choose to just change the sql-mode back to the behavior my app was designed for.  The mysql docs do say that some of the strict modes can decrease mysql's performance, so it doesn't seem like much would be gained from trying to enable this - though you could have it on in the test environment, but off in production.

I couldn't find any resources online that posted this as a fix.

The mysql docs for STRICT_TRANS_TABLES state:

Strict mode controls how MySQL handles input values that are invalid or missing. A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range. A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition. (For a NULL column, NULL is inserted if the value is missing.)

This will also fix the same error when using "triggers".  I did find someone on the mariadb mailing list citing this as a "bug", but it seems like it's just a matter of configuration / schema design. 

I hope this helps someone else!


Bookmark & Share



Popular tags on this blog

Performance |