Mysqldump syntax error fixed

Recently I needed to move a database I had running in RDS to a machine on a new digitalocean account. A simple enough process, the first step of which was to export the database from RDS, but I pretty quickly ran in to a mysqldump syntax 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)

What causes this problem?

The issue here is that I was running the 12.04 Ubuntu LTS on machine, which has mysql client version 5.5 as it’s repo version, but was running version 5.6 on the server in RDS. Oops. It turns out that “SET OPTION” was removed in 5.6, having been deprecated in earlier versions, as warned of in the 5.5 docs:

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.

The Fix

The quickest fix here is to update the Mysql client version to 5.6. Fortunately, there’s a handy PPA to enable this update.

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

Once the above commands have been run, the server and client versions are back in sync. The mysqldump syntax error is no more, and the database export goes ahead with no problem!