The solution of error “ERROR 1067 (42000) at line 211: Invalid default value for ‘blablabla'”

I noticed once when importing a sql file the following error:

ERROR 1067 (42000) at line 211: Invalid default value for ‘blablabla’

It arises because new versions of MySQL server use strict mode and parameters such as NO_ZERO_DATE do not allow entering date values like ‘0000-00-00’ into the database.

Connect to mysql server:

mysql -u root -p

Execute a query that displays the values of sql_mode:

show variables like 'sql_mode';

Copy the string with these values and exit mysql:

exit

Open the configuration file for example in the text editor nano (Ctrl+X for exit, y/n for saving or canceling the changes):

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

I did not have sql_mode = in the file, so at the end of the file I inserted the line with the previously copied values, removing NO_ZERO_IN_DATE, NO_ZERO_DATE from it, in my case, the following happened:

sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Restart mysql to apply the changes:

sudo service mysql restart

Done, now when importing this error should not be.

Join the Conversation

4 Comments

Leave a Reply

  1. Awesome!!! Thanks for sharing knowledge!! this saved me after 3 years!!!

  2. Great! You’re a genius you know ;) This saved me over 2 years later, cheers

  3. Change data structure ‘DATETIME’ to ‘TIMESTAMP’ in new version MYSQL error will solve.

    1. How to change data tructure datetime to timestamp please?

Discover more from IT Blog

Subscribe now to keep reading and get access to the full archive.

Continue reading