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!

Related Articles


Attack of the clones - removing copied websites from the internet

It's an increasingly-common experience for online publishers to discover that their content has been stolen wholesale, and posted on a different domain without permission. What can you do if this happens to your site?

Font Subsetting - shrink down font files to speed up page loads

Fonts are one of the largest resources on any page after images, and can have a big impact on CLS when they vary in size from the underlying system font. Font subsetting allows us to radically shrink font file sizes, speed up initial page loads, and improve our page speed scores.

Avoiding The Google Ads Two-Click Penalty

Google's Two-Click Penalty is intended to protect users and advertisers from accidental clicks on ads. When does Google apply the penalty, and how can we avoid it impacting our sites?

Minimising Cumulative Layout Shift (CLS) When Loading Responsive Ads

Responsive ads are a great way to maximise publisher revenue from display ads. Not knowing the size of the ad to be served in advance can have a big impact on Cumulative Layout Shift (CLS), and, ultimately, Google rankings. How do we maximise revenue while minimising CLS impact?

More