• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

I Like Kill Nerds

The blog of Australian Front End / Aurelia Javascript Developer & brewing aficionado Dwayne Charrington // Aurelia.io Core Team member.

  • Home
  • Aurelia 2
  • Aurelia 1
  • About
  • Aurelia 2 Consulting/Freelance Work

Fixing Sequel Pro SQL Encoding Error For Imported SQL Files

General · August 1, 2014

Now that I use a Mac for work I use Sequel Pro for administering databases (at home I use Navicat, but can’t warrant the expense for work paying for a Mac licence). I recently needed to import an .sql file that I dumped via the command line on a remote server. The size of the database dump file was almost 400mb (it’s a big database).

I encountered an issue where the database dump would get half way through the importing process and then error out about the encoding being incorrect. The database dump file I am pretty sure was UTF-8 and it seems autodetect failed to address the issue, then I got digging and worked out the problem.

It seems MySQL databases exported from a Linux environment (in my case it’s Amazon Linux) and then imported into Sequel Pro on Mac results in a weird encoding mismatch.

The Fix

When importing your database dump, you need to select Western (Mac OS Roman) as the encoding format for the file to import without issue.

As for why this fixes the issue, I wish I could give you a great story about deep-diving into the technical aspects of how character encoding works, but alas, I can’t.

This fix actually came out of frustration. The database dump would not import and I got desperate, going down the list of different encoding formats until it worked. I got lucky I got to Western (Mac OS Roman) before giving up on solving the issue.

When you read the Wikipedia article on Mac OS Roman encoding it confuses you even more, because roman encoding is actually an old style of encoding used. The fact this solution appears to still work in 2020 just confuses me even more.

Dwayne

Leave a Reply Cancel reply

30 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Aleksandrs Sverdlovs
Aleksandrs Sverdlovs
7 years ago

This saved me, thanks man

0
Andre
Andre
7 years ago

Life Saver! Thanks!

0
hedii
hedii
7 years ago

thanks!

0
Keir Osborn
Keir Osborn
6 years ago

This seems to have done the job. Thank you for your help!

0
Gaz
Gaz
6 years ago

Legend thanks for posting this, helped me out.

0
Robert
Robert
6 years ago

Thank you so much! I was stuck with an older version of Sequel Pro for several years. After finally updating, I only encountered this problem with one particular set of database dumps, and only several months after the update, so I had no idea that the issue was with Sequel Pro itself.

This post has saved me so much grief!

0
Martin Oxby
Martin Oxby
6 years ago

To anyone who is still finding this post after scouring the web for answers. This fix still works – Sequel Pro 1.1.2. So, thank you for publishing this!

1
Brian Tully
Brian Tully
6 years ago

Where does one “select Western (Mac OS Roman) as the encoding format”? In Sequel Pro I don’t see an option to specify the encoding during import. I’m working with a large MySQL dump from an external provider. Does the .sql file itself need to be modified? Again, I don’t see any option within Sequel Pro to specify the encoding when importing. Any help would be greatly appreciated.

2
Brian Tully
Brian Tully
6 years ago

A-ha! So in the import file open dialog there is a small “Options” button in the bottom left of the window. You need to click that to reveal the option to manually specify an encoding. In my initial dozen or so imports I completely missed the button since it blends right in and is not obvious. It’s a shame the UI hides the options by default.

Thanks for the tip!

2
Dewayne Holden
Dewayne Holden
6 years ago

Saved me lots of debugging time. Thanks for taking the time to write this out!

0
Julio Vega
Julio Vega
6 years ago

Thanks!! that’s works

0
Chris
Chris
5 years ago

How did you figure it out?!

0
Eddie
Eddie
5 years ago

Dwayne,
Many thanks for publishing this – fixed my problem on version SP 1.1.2
Cheers,

0
David
David
5 years ago

Super helpful – thank you. Still helping people out nearly 3 years after publishing – thanks! 🙂

0
Andrada
Andrada
5 years ago

Thank you! This made my day!

0
Doug
Doug
5 years ago

Thanks so much.

0
Arleys Resco
Arleys Resco
5 years ago

Life Saver, thanks !!

0
Matthew
Matthew
5 years ago

Thanks, Dwayne!!

0
balamurugan M
balamurugan M
5 years ago

Thanks. It helped me

0
tanay
tanay
5 years ago

Saved my day. Thanks a ton!

0
Prashanth
Prashanth
4 years ago

Thanks, it worked like a charm!

0
JR
JR
4 years ago

Was starting to drive me crazy. Great post!

0
shrikant
shrikant
4 years ago

Thanks it worked for me

0
Narkan
Narkan
3 years ago

Thank you!!

0
oli
oli
3 years ago

Still saving people in 2019

0
Michael
Michael
3 years ago

And still seems to work in 2020. Exported from a locally hosted docker (Docksal) container (presumably running linux) on a Mac running 10.14.6 Mojave. The recipient DB was created as UTF8mb4, which matches the dump’s header

0
Gaurav Garg
Gaurav Garg
2 years ago

Super buddy. You are Champ! Saved a lot of time… 🙂

0
Yish
Yish
2 years ago

Awesome, you’re life saver.

0
Catia Antonini
Catia Antonini
2 years ago

Great! you saved me … and many many others!

You are the first result coming out when writing :
“sequel pro fails to import database dump”.

0
Precious
Precious
7 months ago

2022. This still works fine. Thanks.

0

Primary Sidebar

Popular

  • Testing Event Listeners In Jest (Without Using A Library)
  • How To Get The Hash of A File In Node.js
  • Waiting for an Element to Exist With JavaScript
  • Thoughts on the Flipper Zero
  • How To Get Last 4 Digits of A Credit Card Number in Javascript
  • How To Paginate An Array In Javascript
  • How To Mock uuid In Jest
  • How to Copy Files Using the Copy Webpack Plugin (without copying the entire folder structure)
  • Reliably waiting for network responses in Playwright
  • Wild Natural Deodorant Review

Recent Comments

  • Dwayne on Is Asking Developers How to Write FizzBuzz Outdated?
  • kevmeister68 on Is Asking Developers How to Write FizzBuzz Outdated?
  • Kevmeister68 on Start-Ups and Companies That Embrace Work From Anywhere Will Be More Likely to Survive the Coming Recession in 2023
  • kevmeister68 on What Would Get People Back Into the Office?
  • Dwayne on PHP Will Not Die

Copyright © 2023 · Dwayne Charrington · Log in

wpDiscuz