mysqldump: Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1':

After upgraded MySQL db to MySQL 5.6.16, mysqldump resulting the following error.

shell> mysqldump -h sandbox -P9216 -u root -p dumptest > dumptest.sql
Enter password:
mysqldump: Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1': You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OPTION SQL_QUOTE_SHOW_CREATE=1' at line 1 (1064)

The reason for this is that, before starting to take backup of given database or tables mysqldump first sets an option SQL_QUOTE_SHOW_CREATE by running  "SET OPTION SQL_QUOTE_SHOW_CREATE=1" statement in the server, earlier versions of MySQL i.e MySQL < 5.6 there is no problem with those servers because of those servers support "SET OPTION" statements. Since MySQL 5.6.5 the "SET OPTION" is deprecated in favor of SET without OPTION. Thats the reason why mysqldump fails with the above mentioned error.

In the newer versions of MySQL client libraries(mysqldump) the "SET OPTION SQL_QUOTE_SHOW_CREATE=1" is replace by "SET SQL_QUOTE_SHOW_CREATE=1".

NOTE : The statement "SET SQL_QUOTE_SHOW_CREATE=1" will execute on all MySQL versions i.e (MySQL 5.1, 5.5 and 5.6).

The solution to this problem is, upgrade your MySQL client binaries.

Let see, what makes difference the use of "SET SQL_QUOTE_SHOW_CREATE=1".

mysql> show variables like 'SQL_QUOTE_SHOW_CREATE';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| sql_quote_show_create | OFF   |
+-----------------------+-------+
1 row in set (0.00 sec)

mysql> show create table rpl3\G
*************************** 1. row ***************************
       Table: rpl3
Create Table: CREATE TABLE rpl3 (
  id int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(15) DEFAULT NULL,
  sid varchar(100) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> SET SQL_QUOTE_SHOW_CREATE=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'SQL_QUOTE_SHOW_CREATE';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| sql_quote_show_create | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

mysql> show create table rpl3\G
*************************** 1. row ***************************
       Table: rpl3
Create Table: CREATE TABLE `rpl3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(15) DEFAULT NULL,
  `sid` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`).
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

If we observe the output of above 2 show create statements, the result is different based on sql_quote_show_create. if enable "sql_quote_show_create" it will quote the table name and column names, which won't cause any reserved words usage errors.

For more information about this bug, go through MySQL Bug Report.




MySQL Internals, MySQL HighAvailability, MySQL Performance Tuning, MySQL Query Optimization, MySQL performance, MySQL Fabric, MySQL HA, MySQL Installation, MySQL Upgrade, InnoDB Performance Tuning, InnoDB Buffer Pool Size, MySQL Performance Tuning, MySQL ClusterMySQL Latest News, News and Events, MySQL Customers