Note: I’m in the midst of a web developer bootcamp. I feel like writing this up to help others, but I’m going to show the sausage getting made, because (1) I’m not sure I’d cover everything, and having all the details might help others in this position, and (2) I don’t have the time to make it more concise.

As background: I run a few websites, some for myself, and some for other people. When the sites for other people break, I pay more attention. And when I check the traffic to see what’s being requested, and the most frequent is a page for suicide and depression prevention resources, right after Robin Williams’ taking his own life, when the resources could be timely and helpful, well, that really lit a fire under me.

So, a MediaWiki installation died, after months of puttering along, citing a database error: “A database query error has occurred.” My thought is that the hosting company updated something, and usually, a quick software update handles it. Not this time, though. Some googling suggested adding “$wgShowSQLErrors = 1;” at the end of LocalSettings.php – which I should add to the MediaWiki errors page. Following that led to the more informative error: “1267 Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci”,COERCIBLE) for operation ‘=’ ((snip out my database server)”

My Mediawiki installation fell over due to a database problem involving mismatched collations; utf8 and, for some reason, latin1_swedish_ci. I followed some steps based on Alex King’s notes on a similar situation, with some modifications.

1. “Export the data as Latin-1.” I usually use phpmyadmin, but that ‘Latin-1’ wasn’t a listed export option. So, I used the command line, starting from these directions as a starting basis:
mysqldump -uUSER -p --quick --single-transaction --create-options --skip-set-charset --default-character-set=latin1 -h DB_SERVER TABLE_NAME > db-dump.sql
I opted to enter the database password interactively, so it wouldn’t be saved in the shell history.

2. “Change the character set in the exported data file from ‘latin1’ to ‘utf8’.” I used nano to edit, find and replace: latin1 to utf8, and latin1_bin to utf8_bin. Textmate might have worked, but it asked about character set encodings, and so I worried it could screw things up.

3. See #2.

4. “Import your data normally.” phpmyadmin on the usual host balked at the 15 meg bzipped database file, and I’d hit my breaking point with this hosting, so I set up a Linode instead. Based on the Linode import directions: mysql -u USERNAME -p -h DB_SERVER DB_NAME < FILE.sql – again, interactively prompted for the database password.

Lastly: there may be an issue with accented characters, causing truncated pages. I tried to grep the logs, but the lines are too long. I looked at the Google results for each accented letter, and nothing looked amiss. I started looking at hexdumps, and there were a lot of results, from old spam. I’ve punted on this, and asked the wiki mods and the Mefi mods to let me know if anything breaks.