Bug #67507 mysqldump from 5.5 fails with an error when trying to dump data from mysql-5.6
Submitted: 7 Nov 2012 17:06 Modified: 18 Jul 2013 13:55
Reporter: Sergey Petrunya Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: mysqldump Command-line Client Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D3 (Medium)

[7 Nov 2012 17:06] Sergey Petrunya
I am trying use mysqldump from MySQL 5.5 to dump data from a MySQL 5.6 server.  It fails with an error:

mysqldump -uroot dbt3sf1 nation > /tmp/dmp.sql
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 client is:
mysqldump  Ver 10.13 Distrib 5.5.27, for Linux (x86_64)

The server is:
mysql -uroot -e 'select version()'
| version()          |
| 5.6.7-rc-debug-log |

It's bad to break compatibility for such trivial reasons.

How to repeat:
See above.
[7 Nov 2012 18:38] Peter Laursen
@Sergey .. you know this?

[7 Nov 2012 18:39] Sveta Smirnova
Thank you for the report.

Verified as described.

If we deprecate SET OPTION we should not have this in mysqldump from 5.5 distribution in first place.
[10 Jan 2013 20:49] Austin Kalb
Any idea of the estimated fix date? Its scary to run without a backup. Is there an alternative to mysqldump?
[19 Mar 2013 8:40] Kenn Ejima
I think this bug needs to be fixed for practical reasons.

It is because people run mysqldump from crontab, so they won't even notice when they keep generating corrupt (near empty) dumps.

What's happening right now is that when you try to run mysqldump-5.5 against 5.6 server, it generates a small dump file, which only contains some comment lines (up to the point where it hits SET OPTION SQL_QUOTE_SHOW_CREATE=1).

In this case, mysqldump returns non-zero status code for the error, but when you have something like "mysqldump ... | gzip", you always get successful status code returned from gzip, so it is REALLY hard to detect the corruption.

The solution would be either - a) fix mysqldump in a 5.5.x release so that it will use SET instead of SET OPTION and be able to dump from 5.6, or b) fix 5.6 to accept SET OPTION. I think a) is a much better option.
[21 Mar 2013 17:47] Arnaud Adant
Hi Kenn,

Thank you for your feedback. Indeed, a) is a much better solution.

If you try to run mysqldump 5.6 on 5.5 you'll get other errors related to missing variables (GTID, ...).

bin/mysqldump -uroot -p --all-databases --no-data --socket=/tmp/aadant.sock
mysqldump: Couldn't execute 'SELECT @@GTID_MODE': Unknown system variable 'GTID_MODE' (1193)

I pushed to fix a) in 5.5.
[9 Apr 2013 13:34] Steven Hartland
A really dirty hack which works is to edit the 5.5 binary and replace "OPTION" of the string "SET OPTION SQL_QUOTE_SHOW_CREATE" with spaces so you have:-
[22 Jun 2013 1:07] Kenn Ejima
Bump - any ETA to get this fixed soon? We have no way to get around this.
[18 Jul 2013 13:55] Paul Dubois
Noted in 5.1.72, 5.5.34, 5.6.14, 5.7.2 changelogs.

mysqldump tried to execute SET statements as SET OPTION, which failed
when used against 5.6 or higher servers because the deprecated OPTION
keyword has been removed from SET syntax.
[27 Sep 2013 7:35] Laurynas Biveinis
5.1$ bzr log -r 4027
revno: 4027
committer: Nisha Gopalakrishnan <nisha.gopalakrishnan@oracle.com>
branch nick: mysql-5.1-5844882
timestamp: Thu 2013-07-18 11:40:08 +0530
                TO DUMP DATA FROM MYSQL-5.6
  Dumping mysql-5.6 data using mysql-5.1/mysql-5.5 'myqldump'
  utility fails with a syntax error.
  Server system variable 'sql_quote_show_create' which quotes the
  identifiers is set in the mysqldump utility. The mysldump utility
  of mysql-5.1/mysql-5.5 uses deprecated syntax 'SET OPTION' to set
  the 'sql_quote_show_create' option. The support for the syntax is
  removed in mysql-5.6. Hence syntax error is reported while taking
  the dump.
  Changed the 'mysqldump' code to use the syntax
  'SET SQL_QUOTE_SHOW_CREATE' to set the 'sql_quote_show_create'
  option. That syntax is supported on mysql-5.1, mysql-5.5 and
  NOTE: I have not added an mtr test case since it is difficult
  to simulate the condition. Also the syntax may not be further
  simplified in the future.