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!

PHPers Summit 2024 Speaker

PHPers Summit 2024

In June 2024, I'll be giving a talk at the PHPers Summit in Poznan, Poland. I'll be covering the quick wins available to backend developers who are asked to help with frontend speed issues - all the tips and tricks to improve load speed of the usual speed-hogs videos, fonts, and images!

Get your ticket now and I'll see you there!

Share This Article

Related Articles

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?

Idempotency - what is it, and how can it help our Laravel APIs?

Idempotency is a critical concept to be aware of when building robust APIs, and is baked into the SDKs of companies like Stripe, Paypal, Shopify, and Amazon. But what exactly is idempotency? And how can we easily add support for it to our Laravel APIs?

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.