mysqldump mysql 5.6 problem solved

If you have recently upgraded your MySQL version to 5.6, your mysqldump command will probably throw the following error:

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 MySQL 5.6 has removed support for “SET OPTION” and your mysql client tools are probably on older version. Most likely 5.5 or 5.1. There is more info about this issue on MySQL bugs website.

The quickest solution is to update your mysql client tools to 5.6 and your problem will be solved. Unfortunately, there is now official binary of MySQL 5.6 tools for Ubuntu at the moment. However, I did find a solution on the good old GitHub where you can add this custom MySQL 5.6 client tools to your ubuntu repository. It works like a charm. To install mysql client tools 5.6 on ubuntu run the following commands:

sudo add-apt-repository ppa:ondrej/mysql-experimental
sudo apt-get update
sudo apt-get remove mysql-client-5.5
sudo apt-get install mysql-client-5.6

Now you should be able to run mysqldump backups with MySQL 5.6.

Marko

  • Brian Lucas

    Many thanks! I’m running this on Amazon RDS with MySQL 5.6 and a Ubuntu MySQL 5.5 client (available by default) but the new solution is the proper way to fix this… Rails seems back to being happy now.