Ekopedia:Fix:Major database issue on French SQL database

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.