Manual talk:$wgDBmysql5

Charset problem after switching to wgDBmysql5 = true

Hello,

After years, we finally solve our charset issue and have been able to switch back to "wgDBmysql5 = false".

One of our symptoms was some special characters (like "φ") were converted silently (corrupt) into literal question marks (?).

How we resolved it is documented here in English and in French.

I hope this will help!

Jean-Luc

Convert MediaWiki database schema using legacy utf8 encoding to binary character set

My MediaWiki 1.31 installation (MariaDB) has a long history and was using $wgDBmysql5 = true; in LocalSettings.php along with utf8 (not utf8mb4) encoding in most of the MediaWiki tables (which is now discouraged in favor of the binary character set). Just omitting $wgDBmysql5 = true; or setting it to false caused database errors (shown to the user) as soon as a four byte unicode character is used (e.g. most emojis). So I decided to convert the database to the "current" SQL schema shipped with MediaWiki and I succeeded to to so (it should work for newer MediaWiki schemas as well). By the way: Using ALTER TABLE table CONVERT TO CHARACTER SET binary; on all tables does not result in the most up-to-date SQL schema (e.g. varchar(15) binary vs. varbinary(15) differences).

Assuming the production database is called mediawiki_production and has some columns deviating from the up-to-date MediaWiki schema (e.g. utf8 instead of binary character set), create a (temporary) fresh database (mediawiki_pristine) and import the schema of the core and the used extensions:

echo 'create database mediawiki_pristine' | mysql
mysql mediawiki_pristine < maintenance/tables.sql
# same for all used extensions that are using tables (otherwise they are not converted), e.g.:
mysql mediawiki_pristine < extensions/ConfirmAccount/backend/schema/mysql/ConfirmAccount.sql
python3 update_mediawiki_sql.py mediawiki_pristine > update_mediawiki.sql
mysqldump mediawiki_production > backup.sql
echo 'create database mediawiki_test' | mysql
mysql mediawiki_test < backup.sql
mysql mediawiki_test < update_mediawiki.sql
# observe the conversion process for errors and verify the modified mediawiki_test database
# if satisfied, run the schema conversion on the production database:

mysql mediawiki_production < update_mediawiki.sql

# If finished, drop the temporary databases ...
echo 'drop database mediawiki_test' | mysql
echo 'drop database mediawiki_pristine' | mysql
# ... and don't forget to remove the line 
# $wgDBmysql5 = True;
# from LocalSettings.php

Mentioned Python3 script: update_mediawiki_sql.py (GPL2+)

To give you a preview how the generated update_mediawiki.sql looks like, here is an excerpt:

ALTER TABLE `oldimage` DEFAULT CHARACTER SET 'binary' COLLATE 'binary';
ALTER TABLE `oldimage` MODIFY `oi_name` varbinary(255) NOT NULL DEFAULT ''  FIRST;
ALTER TABLE `oldimage` MODIFY `oi_archive_name` varbinary(255) NOT NULL DEFAULT ''  AFTER `oi_name`;

Not covered by the script but a good idea is to additionally change the database default character set:

ALTER DATABASE `mediawiki_production` CHARACTER SET 'binary';

--Phispi (talk) 20:10, 10 January 2020 (UTC)