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.