Bug #66765 Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1'
Submitted: 11 Sep 2012 10:22 Modified: 11 Sep 2012 11:35
Reporter: wei liu Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Options Severity:S3 (Non-critical)
Version:5.6.5 OS:Any
Assigned to:
Tags: 1064, Couldn't execute, error, SET OPTION

[11 Sep 2012 10:22] wei liu
Description:
In MySQL 5.6.5,
mysql>SET OPTION SQL_QUOTE_SHOW_CREATE=1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use ear 'OPTION SQL_QUOTE_SHOW_CREATE=1' at line 1.

But In MySQL 5.5.16
mysql>SET OPTION SQL_QUOTE_SHOW_CREATE=1;
Query OK, 0 rows affected (0.00 sec)

So why it can not be executed in 5.6.5?

How to repeat:
SET OPTION SQL_QUOTE_SHOW_CREATE=1;
[11 Sep 2012 10:33] Peter Laursen
http://dev.mysql.com/doc/refman/5.5/en/set-statement.html

"The SET statement assigns values to different types of variables that affect the operation of the server or your client. Older versions of MySQL employed SET OPTION, but this syntax is deprecated in favor of SET without OPTION." Deprecated in 5.5 (and I think also 5.1) and removed in 5.6 (refer http://dev.mysql.com/doc/refman/5.6/en/set-statement.html)

SET SESSION|GLOBAL SQL_QUOTE_SHOW_CREATE=1;

..works as a charm for me in 5.6.6

Peter
(not a MySQL/Oracle person)
[11 Sep 2012 10:35] Peter Laursen
+ 
http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_sql_quote_show_...
[11 Sep 2012 11:35] Miguel Solorzano
Please see Peter prior comments.
[24 Jan 2014 15:42] Stefan Ring
The problem is that the failing statement is sent by mysqldump 5.1 (in RHEL 6). It's an inconvenience that DBs cannot be dumped remotely anymore as was possible with 5.5 because of this change.
[13 Jun 2014 14:09] Angel Tsvetkov
You must use mysqldump 5.6.x to be able to dump mysql 5.6 database, because MySQL 5.6 discontinued and removed support for SET OPTION syntax.<br>
Under 32/64bit Windows you must do:<br>
1.1. (32bit win) Download Windows (x86, 32-bit), ZIP Archive  5.6.19 212.3M http://dev.mysql.com/downloads/mysql/ (direct link http://dev.mysql.com/downloads/file.php?id=452188) <br>
1.2. (64bit win)  Download Windows (x86, 64-bit), ZIP Archive  5.6.19 217.2M http://dev.mysql.com/downloads/mysql/ (direct link http://dev.mysql.com/downloads/file.php?id=452189) <br>
2. Extract mysqldump.exe from just downloaded file mysql-5.6.19-win32(64).zip (from /mysql-5.6.19-win32(64)/bin/mysqldump.exe) <br>
3. Replace this just extracted file with your already installed mysqldump.exe file (instalation dir/mysql/bin/mysqldump.exe)