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.
This saved me, thanks man
Life Saver! Thanks!
thanks!
This seems to have done the job. Thank you for your help!
Legend thanks for posting this, helped me out.
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!
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!
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.
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!
Saved me lots of debugging time. Thanks for taking the time to write this out!
Thanks!! that’s works
How did you figure it out?!
Dwayne,
Many thanks for publishing this – fixed my problem on version SP 1.1.2
Cheers,
Super helpful – thank you. Still helping people out nearly 3 years after publishing – thanks! 🙂
Thank you! This made my day!
Thanks so much.
Life Saver, thanks !!
Thanks, Dwayne!!
Thanks. It helped me
Saved my day. Thanks a ton!
Thanks, it worked like a charm!
Was starting to drive me crazy. Great post!
Thanks it worked for me
Thank you!!
Still saving people in 2019
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
Super buddy. You are Champ! Saved a lot of time… 🙂
Awesome, you’re life saver.
Great! you saved me … and many many others!
You are the first result coming out when writing :
“sequel pro fails to import database dump”.
2022. This still works fine. Thanks.