MAMP and Navicat and Character Set Hell.
I’ve used Navicat for years. I’ve used MAMP Pro for years. Both tools are must-haves as far as I’m concerned, but I had been having problems with special characters being corrupted for nearly 6 months. Every curly quote, m‑dash or umlaut was torturous.
Briefly, the problem was that those special characters (like curly quotes) would be changed to question marks in my database after I had migrated it to or from my local development environment. I knew it was a character set problem, but all my setting seemed to be correct.
Seemed is the correct word though. Although my database level character sets were fine, I overlooked an important additional setting. First, I’ll show you what I got right initially.
UTF‑8 is what I wanted because I was using ExpressionEngine which uses this character set. In my situation, the online development and production servers both have their databases set to use UTF‑8 and my local MAMP didn’t initially. Setting my local databases’ character set was the right thing to do. The problem was I was still having the problem when I transferred my data using Navicat.
It turns out the connection (aka, bookmark) also had its own character encoding that I hadn’t updated. The Encoding was using “Latin-US DOS”. Why? How? I’m not sure, but that’s was what it was set to. When I finally discovered this preference, switching it to UTF‑8 ended my Character Set Hell once and for all.