🐥 Fixing the MySQL export from TablePlus

I recently spend a lot of time figuring out why exporting a table from TablePlus was giving me the incorrect output. The setup was that I'm exporting data from a Digital Ocean managed MySQL server and then import this into a local install of MySQL on my mac.

To export, I select one or more table in TablePlus, right-click on them and then choose export to an SQL file.

However, when importing this on my local machine, it failed with:

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 '"migrations" (…

I finally (it took me quite a while) figured out what is causing this: MySQL settings.

If you look at what is exported from the Digital Ocean database, you'll see that the export statements are liked this:

CREATE TABLE "migrations" (
  "id" int unsigned NOT NULL AUTO_INCREMENT,
  "migration" varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  "batch" int NOT NULL,
  PRIMARY KEY ("id")
) ENGINE=InnoDB AUTO_INCREMENT=607 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

What I was expecting was this (note the difference between and"`):

CREATE TABLE `migrations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `migration` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `batch` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=607 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

The difference is caused by the default SQL mode set on Digital Ocean's databases. It has the option ANSI_QUOTES enabled while the local MySQL (done via homebrew) doesn't have this option. As enabling this setting is considered to be bad practice, I decided to turn it off on the Digital Ocean side.

You can do this by going to the settings tab of your DO database, editing "Global SQL modes" and then removing ANSI_QUOTES. After that, reconnect with TablePlus and you'll get the expected result.

You can read more about it here.