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
1) 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
2):
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.
1) Is this really the correct statement? It works, but I thought it should use () instead of [] for the OR statement.
2) We had to set $wgShowExceptionDetails, $wgShowSQLErrors, $wgDebugDumpSql to true for this to be shown.
What's up with the latest 