MySQL Bugs: #67507: mysqldump from 5.5 fails with an error when trying to dump data from mysql-5.6

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:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
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.

@Sergey .. you know this?

http://bugs.mysql.com/bug.php?id=66765

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.

Any idea of the estimated fix date? Its scary to run without a backup. Is there an alternative to mysqldump?

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.

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.

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:-
"SET        SQL_QUOTE_SHOW_CREATE"

Bump - any ETA to get this fixed soon? We have no way to get around this.

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.

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
message:
  BUG#15844882: MYSQLDUMP FROM 5.5 FAILS WITH AN ERROR WHEN TRYING
                TO DUMP DATA FROM MYSQL-5.6
  
  Analysis
  --------
  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.
  
  Fix:
  ---
  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
  mysql-5.6.
  
  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.