MariaDB 10.2 – Field ‘xxxxxxx’ doesn’t have a default value Error

I update one of my production server MariaDB 10.1 database server to 10.2 thinking everything will be ok, I did a live test in one of my VM machines and everything went smoothly…. well… on the outside that is.
As I finished updating the server, I started getting java.sql.SQLException: Field ‘xxxxxx’ doesn’t have a default value in my logs.
This didn’t happen before.
I soon found out from MariaDB document page….

Since MariaDB 10.2.4, SQL_MODE is by default set to NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO. In earlier versions of MariaDB 10.2, and since MariaDB 10.1.7, SQL_MODE is by default set to NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER. For earlier versions of MariaDB 10.1, and MariaDB 10.0 and before, no default is set.

So, this means that as soon as I updated my server to MariaDB 10.2, my sql_mode was set to “strict” mode by default internally.

The work around(?)/fix…go to the MariaDB config file (my.cnf) and change the sql_mode line to

sql_mode                = NO_ENGINE_SUBSTITUTION

Hope this helps those who ran into this problem.
(Note: Another fix is to manually set all table fields to have a default value – too long, too tiresome, Also you can change your code to set all default values – also too long and tiresome )

Link to other sources:
Here

Leave a Reply

Your email address will not be published. Required fields are marked *