Nous sommes très heureux d'annoncer la collaboration du Colibri et du Papillon!

Un article de Ekopedia, l'encyclopédie pratique.

This page intends to explain how we solve our charset problem on our French Mediawiki database.

Sommaire 1 Warning 2 Our problem 3 Our symptoms 4 Our solution 5 Comment

Warning

Our solution have little chance to be used as-is, but could be used as inspiration on how to solve other problems.

Our problem

In 2004 or 2005, I made a really bad choice during a Mediawiki upgrade: $wgDBmysql5 = true; (documented as Experimental charset support for MySQL 4.1/5.0).

Our symptoms

• If anyone tried to make an edit and add a character like "φ", then, during the save, a conversion silently (corrupt) turn it into literal question marks (?).
• It was possible to detect the issue when trying to add some Mediawiki extensions (like TeX). Error message: "Illegal mix of collations".

Our solution

An Ekopedia supporter (Szabolcs Pap) made a script in order to dump the database and convert it in the right charset.

Here is the script:

#!/bin/sh

TEMP_DIR=/tmp/
DB_NAME=ekopediafr
DB_USER=ekopedia
DB_PASS=XXXXXXXXXXX

echo "start...";
echo "dumping database (fr)\n"
mysqldump $DB_NAME --default-character-set=utf8 -u $DB_USER -p$DB_PASS > $TEMP_DIR/allnew.sql
echo "dump is ready\n";

echo "create first sed to change UTF8\n";
sed -i 's/\/\*\!40101 SET NAMES utf8 \*\/\;/\/\*\!40101 SET NAMES latin1 \*\/\;/g' $TEMP_DIR/allnew.sql

echo "create second sed to change UTF8\n";
sed -i 's/SET character_set_client = utf8;/SET character_set_client = latin1;/g' $TEMP_DIR/allnew.sql

echo "importing into new database\n";
cat $TEMP_DIR/allnew.sql|mysql $DB_NAME --default-character-set=latin1 -u $DB_USER -p$DB_PASS

echo "DONE\n";

After applying this script, I have been able to switch back "$wgDBmysql5" to "false" in LocalSettings.php.

Our problem is now solved!

Comment

I hope that this could help someone else.