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:


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:


Restart mysql to apply the changes:

sudo service mysql restart

Done, now when importing this error should not be.

Подписаться на IT Blog (RU) по Email
Subscribe to IT Blog (EN) by Email

Did my article help you? How about buying me a cup of coffee as an encouragement? Buy me a coffe.

Leave a Reply