I got a MediaWiki instance up & running with MySQL as its database backend. Now, I wanted to play around with this wiki in a VM running Fedora 16. Let's prepare the VM for running MediaWiki:

  $ yum install httpd php links mediawiki mediawiki-Cite
  $ grep ^Alias /etc/httpd/conf.d/mediawiki.conf 
  Alias /wiki/skins /usr/share/mediawiki/skins
  $ cp -a /var/www/wiki/ /var/www/html/
  $ systemctl enable httpd.service
  $ systemctl start httpd.service

Note that we did not install a MySQL server here: I did not want to run yet another service in this small virtual machine.

After that, the MediaWiki instance can be accessed & setup via http://fedora.local/wiki/ - be sure to choose SQLite for the database backend.

Now MediaWiki is up & running with an empty SQLite database. But how can I convert my original MySQL database into SQLite?

There's mysql2sqlite, a shell script using mysqldump and awk to do the job. And it did the job pretty well so far:

  mysql2sqlite.sh -u admin -p mw_wiki > wiki.sqlite.raw

I could pipe the whole thing through sqlite already but I had to alter the output a bit: my original database had $wgDBprefix set to "mw_" but somehow this parameter seems to be ignored when an SQLite database is used. So let's cut out the prefix from our preliminary dump whenever a table is created, indexed or inserted¹⁾ into:

  sed -e '/[INDEX|INTO|TABLE] \"mw_/s/mw_//g' -i wiki.sqlite.raw

Now we can generate our SQLite database as simple as:

  sqlite wiki.sqlite < wiki.sqlite.raw

Point $wgDBname to this filename and off we go: the wiki should now be up & running with the original data visible. Yay ;-)

Great, but we could not update pages or create new articles²⁾:

  INSERT INTO text (old_id,old_text,old_flags) VALUES (NULL,...
  Database returned error "19: text.old_id may not be NULL".

Hm, shouldn't old_id be set to AUTOINCREMENT? Let's look at our wiki.sqlite.raw again:

CREATE TABLE "text" (
  "old_id" int(10)  NOT NULL ,
  "old_text" mediumblob NOT NULL,
  "old_flags" tinyblob NOT NULL,
  PRIMARY KEY ("old_id")
);

Again: this is the output of mysql2sqlite and it looks pretty sane to me. But somehow old_id wasn't set to PRIMARY KEY (aka "AUTOINCREMENT") when parsed by sqlite (v3.7.7.1). When the "PRIMARY KEY" is moved right before the "NOT NULL" statement for old_id, it still would not recognize it. We also had to replace the "int(10)" with INTEGER and remove the superfluous space on the same line. Now it reads:

CREATE TABLE "text" (
  "old_id" INTEGER PRIMARY KEY NOT NULL,
  "old_text" mediumblob NOT NULL,
  "old_flags" tinyblob NOT NULL
);

We have to modify all (19) occurences of PRIMARY KEY in our wiki.sqlite.raw. Be sure to omit the INTEGER keyword when the field is declared varbinary. With all that in order now, wiki.sqlite.raw can be fed into sqlite again. FWIW, this is what sqlite makes of the statement above:

  $ sqlite3 data/wikidb.sqlite 
  sqlite> .schema text
  CREATE TABLE text (
    old_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    old_text BLOB NOT NULL,
    old_flags BLOB NOT NULL
  );

Now we should be able to update pages and create articles. Phew, what a ride :-)

Update: Right after finishing this article, I came across this knowledge base article on how to do this with scripts provided by the MediaWiki installation. In short:

  $ php maintenance/dumpBackup.php --full --uploads --conf `pwd`/LocalSettings.php > wiki.xml

Then, in the VM again:

  $ cd /var/www/html/wiki
  $ php /usr/share/mediawiki/maintenance/importDump.php wiki.xml
  $ php /usr/share/mediawiki/maintenance/rebuildrecentchanges.php

This takes quite a lot of time - around 7 minutes for a 13MB .xml dump. The resulting was written into the configured $wgDBname, or data/wiki.sqlite in our case. All pages were in place, only the MainPage was overwritten with its initial version. Going back one version with the page history revealed the most current version of the (imported) article.

¹⁾ Is this really the correct statement? It works, but I thought it should use () instead of [] for the OR statement.
²⁾ We had to set $wgShowExceptionDetails, $wgShowSQLErrors, $wgDebugDumpSql to true for this to be shown.