Updated : Jul 25, 2019 in Uncategorized

Solution for MySQL/MariaDB Field ‘xxx’ doesn’t have a default value

Situation:
Using PhpMyAdmin I was trying to add a user and this error kept on coming up and I could not add the user. After some research here are 2 solutions:

The following articles are so good and helpful that as soon as I saw them in Internet I wanted to copy them here. The first solution was taken from the following site.
//www.farbeyondcode.com/Solution-for-MariaDB-Field–xxx–doesn-t-have-a-default-value-5-2720.html

Thanks for providing this short but great article.

————————————————————————————————————

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,STRICT_TRANS_TABLES

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!

Here is another solution taken from the following site:
//stackoverflow.com/questions/18523861/error-1364-1364-field-ssl-cipher-doesnt-have-a-default-value

The ‘SSL_cypher’ column in your table structure has been marked ‘NON-NULL’ but your ‘INSERT’ query isn’t providing a value for it. MySQL will try to assign the default value in these circumstances, but your column hasn’t been given one.

You need either to set a default value for ‘ssl_cypher’ or alter the table structure such that the ‘ssl_cypher’, ‘x509_issuer’ and ‘x509_subject’ columns are marked as ‘NULL’


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: