Converting mediawiki database from postgresql to mysql/mariadb

==Why to use mysql/mariadb instead of postgresql?==
Here is what MediaWiki says []:
Most of the common maintenance scripts work with PostgreSQL however some of the more obscure ones might have problems.
Is PostgreSQL a good choice for MediaWiki?
Wikipedia uses MySQL so MediaWiki gets more testing on MySQL than PostgreSQL. While support for PostgreSQL is maintained by volunteers, most core functionality is working.
==Challenge==
====Why not to use dumpBackup.php script in mediawiki? []====
If you need to save only mediawiki data and pages modify history then the best and easiest way probably would be:
However user accounts, user contrubutions, logs, etc will not be migrated this way.
====Why not to use tools for converting postgresql databases to mysql?====
Mediawiki postgresql version is quite different from mysql version. For example: some table names are different, field order, timestamp format (pg: '2015-11-01 08:42:31+00', mysql: '20151101084231'), many fields that are NULL in postgresql are not allowed to be NULL in mysql. That is why there will be issues when dumping data from postgresql and importing it to mysql.
There is a script mentioned in https://www.winterrodeln.org/trac/wiki/MediaWikiPostgresqlToMysql that should be converting postgresql database to mysql. However in my case it did not work because of the issues mentioned above (that script was written in 2009-01-16).
==Mediawiki location==
In this example mediawiki files root is located in /var/www/wiki.wikiname.org/htdocs/w
Web server is lighttpd. /etc/lighttpd/lighttpd.conf contains lines:
==Disallowing editing mediawiki until database is converted==
 $wgReadOnly = 'This wiki is currently being upgraded to a newer software version.';
==Preparing dump files for importing data to mysql==
====Making postgresql dump====
====Making temporary postgresql database looking like mysql====
====SQL script modifying postgresql database====
For avoiding issues when importing data to mysql we need to modify mediawiki postgresql database so that it would look like mediawiki mysql database. Basically the script will make field order in pg tables like in mysql and will set non-NULL values to the fields that are not allowed to be NULL in mysql
/tmp/pg-prepare-for-mysql.sql
====Making dump files for mysql import====
====Modifying dump files====
Postgresql specific sql should be removed
Postgresql table mwuser corresponds to mysql table user
Postgresql table pagecontent corresponds to mysql table text
Back slashes should be replaced with double backslashes
Timestamp format in postgresql is different from mysql (pg: '2015-10-31 10:10:10+00', mysql: '20151031101010'). All timestamps should be converted to avoid errors like:
... Language::sprintfDate: The timestamp should be a number
In postgresql ipblocks table may contain records with duplicated field ipb_address. mysql requires that ipb_address field is unique.
Check manually ipblocks.sql file. Delete records with duplicated field ipb_address to prevent errors like:
# ERROR 1062 (23000) at line 152: Duplicate entry 'xxxxxx-0-0-0' for key 'ipb_address'
==Changing mysql root password, creating user for mediawiki==
==Setting up new mediawiki==
 
<?php
 header('Location: /wiki');
?>
Open your wiki in browser https://wiki.wikiname.org, go through setup process (DB type:mysql, engin: innodb, charset: binary)
Compare LocalSettings.php file generated by setup process with old mediawiki LocalSettings.php file
Take needed settings from old LocalSettings.php
Copy new LocalSettings.php into mediawiki directory /var/www/wiki.wikiname.org/htdocs/w
Copy needed files from old mediawiki (images/*, extensions/..., resources/...)
==Clearing mysql tables before import==
==Importing data into mysql==
==Running maintenance scripts==
==Regenerating cache==
Old cache in l10n_cache table may couse problems. It can be deleted and regenerated again.
==See also==
* MariaDB
Category:SQL