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: