Mysql error unknown variable sql mode

Operating System               : Ubuntu Linux Xenial Xerus (16.04)

Database                             : MySQL 5.7.12

Problem :

In the middle of restoring a MySQL dump script file for WordPress web-based site, suddenly the process stop. Below is the error which is displayed in the middle of database restore process :

username@hostname:~# mysql -uroot -p wordpress_dbname < /location_path_of_mysql/mysql_dump_file_name.sql
mysql: [ERROR] unknown variable 'sql_mode=NO_ZERO_DATE'
username@hostname:~#

After searching on google for solving the solution above, there are several steps that has been tried and executed as shown below :

One of the main problem is because variable the mode NO_ZERO_DATE which is seems needed in order for the MySQL dump file properly executed is already deprecated. Based on MySQL Reference Manual it is said that :

The NO_ZERO_DATE mode affects whether the server permits ‘0000-00-00’ as a valid date. It effect also depends on whether strict SQL mode is enabled.

The other explanation is shown below :

As of MySQL 5.6.17, NO_ZERO_DATE is deprecated and setting the sql_mode  value to include it generates a warning.

It can be viewed in the following URL : https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html. Below is the snapshot of the information :

The thing is, the version of MySQL used is above MySQL 5.6.17 which is definitely NO_ZERO_DATE sql_mode is already deprecated.

To find out the version of currently used MySQL, we can execute the following command :

mysql --version

The execution of the above command is shown below :

username@hostname:~$ mysql --version
mysql  Ver 14.14 Distrib 5.7.12, for Linux (x86_64) using  EditLine wrapper
username@hostname:~$

Another way to find out the currently used MySQL version is by logging in to MySQL command console as shown below :

username@hostname:~# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 5
Server version: 5.7.12-0ubuntu1.1 (Ubuntu)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql>

In the above output display, it is said that Server version : 5.7.12.

Solution               :

1. Try to fix the sql_mode by doing the following thing :

Set the sql_mode value from MySQL command console.
Try to login first to MySQL command console by typing the following command :

mysql -uroot –p

The execution of the command is shown below :

username@hostname:~# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 5
Server version: 5.7.12-0ubuntu1.1 (Ubuntu)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql>

2. Get the sql_mode first by executing the following command :

mysql>show variables like 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                     
|+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,02 sec)

3. Set the appropriate mode to successfully restore database :

Try to remove the sql_mode contains NO_ZERO_DATE and also NO_ZERO_IN_DATE which is probably related by executing the following command in MySQL command console :

mysql>set sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0,00 sec)
mysql> quit;

4. Reexecute the restore process :

username@hostname:~# mysql -uroot -p wordpress_dbname < /location_path_of_mysql/mysql_dump_file_name.sql Enter password: ERROR 1067 (42000) at line 285: Invalid default value for 'comment_date' username@hostname:~#

The error has changed and the problem is still exist. The restore database process has failed.

Solution :

After looking at the dump database script file at line 285 which seems to be the culprit of the problem, it has been decided to fully disable any sql_mode which has already been set.

Below is the line which caused the problem :

  `comment_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

It is still relates on the problem of datetime which is needed to set the default value of date combined with time. Below is the step which is taken to solve the problem :

1. Directly edit MySQL configuration file as shown below :

In Ubuntu Linux 16.04, the configuration file exist in the following directory :

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

2. Add the following line to the [mysqld] block as follows :

[mysqld]
user                                       = mysql
pid-file                                   = /var/run/mysqld/mysqld.pid
socket                                     =  /var/run/mysqld/mysqld.sock
port                                       = 3306
basedir                                    = /usr
datadir                                    = /var/lib/mysql
tmpdir                                     = /tmp
lc-messages-dir                            = /usr/share/mysql

The line which is added after the ‘lc-messages-dir’ concerning the sql_mode is shown below :

sql_mode                            =

3. Just empty the value and depends on your setting, from my point of view as long as it is placed in the [mysqld] block configuration, it will have impact.

4. Restart the MySQL service as shown below :

username@hostname:~# service mysql restart
username@hostname:~#

5. After restarting MySQL service, directly restore the database by executing the comand as follows :

username@hostname:~# mysql -uroot -p wordpress_dbname < /location_path_of_mysql/mysql_dump_file_name.sql
Enter password:Enter password:
You have new mail in /var/mail/root
username@hostname:~#

As shown in the above output, the process has already success.

For a quick recap, this is the following things that I have done to solve the above problem :

1. Set sql_mode through MySQL Command Console which is not fully succeed.
2. Edit MySQL database server configuration and add the following line under [mysqld] block configuration section :

sql_mode =

Depends on the operating system, the locationof MySQL database server configuration will also vary.

1. Restart MySQL service database.
2. Try to re-execute the restore process of MySQL dump file.