MAMP and Navicat and Character Set Hell.

I’ve used Nav­i­cat for years. I’ve used MAMP Pro for years. Both tools are must-haves as far as I’m con­cerned, but I had been hav­ing prob­lems with spe­cial char­ac­ters being cor­rupt­ed for near­ly 6 months. Every curly quote, m‑dash or umlaut was tor­tur­ous.

Briefly, the prob­lem was that those spe­cial char­ac­ters (like curly quotes) would be changed to ques­tion marks in my data­base after I had migrat­ed it to or from my local devel­op­ment envi­ron­ment. I knew it was a char­ac­ter set prob­lem, but all my set­ting seemed to be cor­rect.

Seemed is the cor­rect word though. Although my data­base lev­el char­ac­ter sets were fine, I over­looked an impor­tant addi­tion­al set­ting. First, I’ll show you what I got right ini­tial­ly.

UTF‑8 is what I want­ed because I was using Expres­sio­nEngine which uses this char­ac­ter set. In my sit­u­a­tion, the online devel­op­ment and pro­duc­tion servers both have their data­bas­es set to use UTF‑8 and my local MAMP didn’t ini­tial­ly. Set­ting my local data­bas­es’ char­ac­ter set was the right thing to do. The prob­lem was I was still hav­ing the prob­lem when I trans­ferred my data using Nav­i­cat.

It turns out the con­nec­tion (aka, book­mark) also had its own char­ac­ter encod­ing that I hadn’t updat­ed. The Encod­ing was using ​“Latin-US DOS”. Why? How? I’m not sure, but that’s was what it was set to. When I final­ly dis­cov­ered this pref­er­ence, switch­ing it to UTF‑8 end­ed my Char­ac­ter Set Hell once and for all.