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!

Share This Article

Related Articles


A/B testing headlines on social media

A/B testing is a great way to determine the most compelling headline for an article, but is made difficult by the way social media networks cache link previews. What's the secret to effectively running A/B headline tests, and optimising engagement rates?

Lazy loading background images to improve load time performance

Lazy loading of images helps to radically speed up initial page load. Rich site designs often call for background images, which can't be lazily loaded in the same way. How can we keep our designs, while optimising for a fast initial load?

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.

Calculating rolling averages with Laravel Collections

Rolling averages are perfect for smoothing out time-series data, helping you to gain insight from noisy graphs and tables. This new package adds first-class support to Laravel Collections for rolling average calculation.

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