Skip to content

Mediawiki & MySQL & SQLite

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 inserted1) 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 articles2):
  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.

Growl 1.3.1

Update Available - A newer version of Growl is available online. Click here to download it right now. What's up with the latest Growl update? Apparently it costs money now? Not that $1.99 for this useful piece of software were too much, but...no announcement about this change? Why not? Users are already upset about this change and I really fail to see why they did this w/o any prior notification.

For the brave and able, Growl 1.3.1 can still be built from source - good luck fighting all the build errors then :-\

Schneierfacts fortune cookies

A (long) while ago I stumbled upon the incredible Schneierfacts and thought "I must have these snippets of wisdom as a fortune(6) file!" Here's how I did that:
  mkdir schneierfacts && cd schneierfacts
  f=1
  while [ $f -lt 1610 ]; do 
      echo "fact: $f"
      wget -q http://geekz.co.uk/schneierfacts/fact/"$f"
      sleep 1
      f=$((f+1))
  done


  grep 'p class="fact"' * | \
          sed 's/.*fact\">//;s/<\/p>//;s/\"\;/\"/g' | \
          sort -u > ../schneierfacts.txt
  sed G ../schneierfacts.txt | sed 's/^$/%/' > ../schneierfacts

  strfile -r ../schneierfacts
  "../schneierfacts.dat" created
  There were 1600 strings
  Longest string: 24713 bytes
  Shortest string:  142 bytes
With that in place, we can install our new fortune file:
  sudo mkdir -p /usr/local/share/games/fortunes
  sudo cp ../schneierfacts{,.dat} /usr/local/share/games/fortunes/

  $ fortune schneierfacts
  When Bruce Schneier does modulo arithmetic, there are no remainders. Ever.
Note: There are currently over 1600 facts, be kind when downloading the facts! (i.e. use sleep(1) after every wget(1) call.)

TCP: Peer 1.2.3.4:64259/51542 unexpectedly shrunk window 4197231805:4197240525 (repaired)

From time to time our Linux 2.6 kernel generates the following message:
  TCP: Peer 1.2.3.4:64259/51542 unexpectedly shrunk window \
          4197231805:4197240525 (repaired)
It was a bit scarier back then when it read something like this:
  TCP: Treason uncloaked! Peer 1.2.3.4:64259/51542 shrinks window \
          3166327388:3166327393. Repaired.
The message has been modified in December 2008. While there are quite a lot of posts and articles on this topic, a pretty good explanation can be found in commit 2ad4106: Clear stale pred_flags when snd_wnd changes.

Just like gambling

Ah yes, that's the stuff:
 > We are Wall Street. It's our job to make money. Whether it's a commodity, 
 > stock, bond, or some hypothetical piece of fake paper, it doesn't matter. We
 > would trade baseball cards if it were profitable. I didn’t hear America 
 > complaining when the market was roaring to 14,000 and everyone’s 401k 
 > doubled every 3 years. Just like gambling, it’s not a problem until you lose.
 > I've never heard of anyone going to Gamblers Anonymous because they 
 > won too much in Vegas.
...and so the leaflet from the financial traders goes on to explain why everything is just OK the way it is. Oh well, that's a relief. I was kinda worried, but not anymore...