Installing Wikipedia part 4 of N: getting additional wikipedia metadata

January 30, 2008

Continued from yesterday:

The loading of ‘page’ table data  finished after approx 16 hours on a 2.2GHz dual proc, 4GB machine with approx 6.5 million page records, along with the latest revision and text information (similar number of records). All other tables were blank — which is fine if you want to host wikipedia, but not fine if you want to gather inter-wiki page links and category metadata, which are stored in the pagelinks and categorylinks tables respectively.

Page link data is useful because it provides a basic graph of all wikipedia nodes. Category link data is useful because it provides decent classification information w/o the overhead of classification methods that rely on raw text.

Where to get the metadata:

Pagelink data is available at

Categorylink data is available at

    I used wget and gzip -d to get the raw SQL data.

    How to load the metadata:

    • mysql -u arun -parun wikipedia < enwiki-20080103-categorylinks.sql
    • mysql -u arun -parun wikipedia < enwiki-20080103-pagelinks.sql

    I ran into a problem when trying to load the categorylink sql: I received the following MySQL error:

    ERROR 1071 (42000) at line 12: Specified key was too long; max key length is 1000 bytes

    I googled around and found a thread that said the error was happening because the database was UTF-8, and that the fix was to switch it to Latin-1. When I did this from the mysql commandline client:

    alter database character set latin1;

    and reloaded the categorylink sql, it worked. Moral of the story: create your wikipedia database with latin-1 encoding. If you are going to insert into a UTF-8 database, you will need to convert from Latin-1 to UTF8. I’m using Ruby, so I’m going to use Iconv to convert into UTF-8 prior to inserting into my UTF-8 database.

    One thing that happened when I tried to confirm the number of loaded category/page links in the db was that I received a strange internal MySQL error. I tried to restart the machine and got a ‘/var/lib/mysql: partition too full!” message (and the database wouldnt start up). I fixed this by deleting some data. If you can’t delete any data, try the steps suggested here.

    When I was able to restart the database, the pagelinks table was corrupted. I ran

    repair table pagelinks;

    from the mysql commandline client as described in the mysql documentation. This took about 3 hours to repair the table, but the table is now repaired.

    Wikipedia Download Summary:

    • Total Page Count: 6202531
    • Total Pagelink Count: 77444718
    • Total CategoryLink Count: 18912664

    Installing Wikipedia, part 3 of N, using ActiveRecord and RSpec to build the model layer

    January 30, 2008

    Continued from part 2 of N

    After working around the hidden commandline ordering of mwdumper and going home while the database was still being loaded, I walked in today to find 4.88 million pages and counting. No categorylinks, no pagelinks, etc. I’m hoping that those get populated before the job is complete, because aside from the page data, we’re using wikipedia categories to do a crude classification of entities, and pagelink data to construct a graph of pages and their connections.

    Based on the schema documentation, I’ve constructed an ActiveRecord based model of the tables that I’m interested in. Using ActiveRecord w/o Rails is pretty easy:

    (1) you need to explicitly connect to the database. ActiveRecord maintains a copy of the connection to use for all derived classes once this is done:


    (2) You can then subclass the ActiveRecord::Base class as usual:

    class Page < ActiveRecord::Base
    set_table_name ‘page’
    set_primary_key ‘page_id’

    Pretty (yawn!) straightforward so far. So I decided to mix it up a little by writing my tests in rpsec. I’ve been intrigued by Behavior Driven Development, aka BDD, and wanted to see how useful it would be in my day to day work. As I’ve said may times before, I’m a big fan of TDD. However I still find it an effort to write tests first. When I do, it feels great. But I often slip into a rut where the tests dont drive my coding as much as they should, and as a result I write unnecessary code.

    Perhaps the biggest advantage of rpsec over XUnit type unit testing is that I get to specify my object behavior prior to writing a single line of code for the object in something that looks / feels like English, which is something I’m fairly fluent in. Here is the starter rspec I wrote prior to actually coding the wikipedia model classes: I was treating those classes as a singular entity (aka ‘the model’) at this point:

    describe Page do

    it ‘should retrieve a valid page’ do


    it ‘should retrieve a valid text’ do


    it ‘should retrieve a valid revision’ do


    it ‘should retrieve the latest text associated with a page via the associated revision’ do


    it ‘should retrieve all associated CategoryLinks for a Page’ do


    it ‘should retrieve all associated PageLinks for a Page’ do



    This allows me to really get my head around what the model should be capable of, then using the object extensions should and should_not, I can validate those assertions:

    it ‘should retrieve the latest text associated with a page via the associated revision’ do
    test_text = “test text for page_1”
    @text =

    @text.attributes = load_text_attribs(test_text)

    @page =
    @page.attributes = load_page_attribs(‘page_1’)

    @revision =
    @revision.attributes = load_revision_attribs(@page.page_id,@text.old_id)

    latest_rev = @page.get_latest_revision
    latest_rev.should_not eql(nil)
    text = latest_rev.text
    text.should_not eql(nil)
    text.old_text.should eql(test_text)

    In order to get ActiveRecord Models loading in RSpec, I did a couple of things:

    (1) I created a connection before running any tests. In rspec, the before(:all) method allows me to specify a block of code that runs prior to any test execution.

    before(:all) do


    (2) I made sure that data was getting deleted prior to every test by using the before() method with the :each symbol:

    before(:each) do

    #clear out all data


    One significant difference between Rspec and TestUnit is the lack of Fixture support. I was shoving in fixture support, aka putting a square peg into a round hole, when I decided to see how Rspec users felt about fixtures. Turns out they are not particularly fond of them. One way to do load test code w/o fixtures is to include model specific helper classes to load data.

    module PageHelper

    def load_page_attribs(title)
    :page_random=> 0,
    :page_touched=> 0,
    :page_latest=> 0,
    :page_len=> 200


    page =

    page.attributes = load_attributes

    In the end, even though it co-mingles data with code, which is commonly perceived as a bad thing to do — hence fixtures! — the less code approach seems more manageable b/c I can make the modification directly in the spec file, and the use of Ruby symbols makes the code read as easy as a fixtures file.

    More wikipedia processing progress, same bat time, same bat channel!

    Installing Wikipedia, part 2 of N: getting the !@?! data.

    January 29, 2008

    Continued from Part 1 of N

    Where to start….first of all, the high level steps.

    1. Download the dump file
    2. convert it to a huge, uncompressed SQL file
    3. import that file into mysql

    Downloading the Dump File

    Potential (Dis)Qualifier: I’m doing this on a debian box, assuming standard tools available on that distro. You can find the (english) file dumps at, the subdirectories correspond to the dates of the dumps. There are several compressed (bz2) XML files, and several compressed (gzip) SQL files. Right now, I don’t know the degree of overlap between the two, but I have downloaded the latest pages-articles.xml.bz2, mainly because I didn’t want the extra user/discussion information found in pages-meta-current.xml.bz2.

    Here are the steps I took to download and extract the SQL that I then loaded into MySQL:


    Then I ran

    bzip2 -d enwiki-20080103-pages-articles.xml.bz2

    to uncompress to a huge (14GB) xml file.

    How to Convert that Bad Boy into SQL

    There are many ways to do this, detailed on this page. One thing I hate is 10 different ways to do the same thing, so I did some research and found that most people have had success using mwdumper. I downloaded the latest mwdumper.jar from, and ran it as follows:

    java -jar mwdumper.jar enwiki-latest-pages-articles.xml –format=sql:1.5 > dump.sql

    Note the order of the parameters. It is important to list the file you are dumping from prior to the format, when I didn’t do this I received a bizarre SQL insertion error:

    ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘ …

    Loading into MySQL

    The MWDumper execution line above can be used with a pipe to directly route data into MySQL. I tried this at first, but ran into the out of order parameter problem, and backed up to creating a real SQL file prior to finding out about the parameter order issue. I think breaking XML->SQL conversion and SQL insertion is a good idea in general, because I haven’t gotten all of the SQL in clean yet, and having that file enables me to make multiple passes.

    There are a couple of changes to the mysql config file that have improved the speed of the upload: the file is located at /etc/mysql/my.cnf

    (1) increase the size of the innodb log file and log file buffer, it defaults to 5MB and that means lots of disk I/O. Here are the settings I used, note that these were conservative because I don’t have exclusive use of all 4GB of my machine’s RAM.


    (2) turn off log_bin — logging transactions is a great way to make sure you don’t lose data, but I’m uploading to a single machine and don’t need the overload. I comment out default settings as a general rule:

    #log_bin = /var/log/mysql/mysql-bin.log
    # WARNING: Using expire_logs_days without bin_log crashes the server! See README.Debian!
    #expire_logs_days = 10
    #max_binlog_size = 100M

    After making these changes, I restarted mysql:

    /etc/init.d/mysql restart

    Then I created a UTF-8 encoded database to download into using the mysql commandline:

    mysql -u username -ppassword (note that -p only takes a password if you don’t leave a space)

    Once in the mysql commandline client:

    create database wikipedia CHARACTER SET utf8; (make sure you put the semicolon on the end)

    Now leave the client (\q), because we need to load the database with a schema, otherwise the SQL load wont work — it’s just a table insert script that assumes tables. Download the latest schema from

    — this is the head at the time I wrote this, visit the mediawiki vcview page to get the latest version. This Mediawiki page provides a great overview of the schema.

    You can insert the schema into the database by doing:

    mysql -u username -ppassword wikipedia < tables.sql

    Now, with the wikipedia database schema set up, you can insert data:

    mysql -u username -ppassword wikipedia < dump.sql (assuming dump.sql is the name of the sql file you generated using MWDumper)

    Now it’s time to step away from the machine — I ran this job nohup so I could get on with my life. It’s still running, and I’ll update the blog with the results in my next wikipedia related post.

    Installing Wikipedia, part 1 of N

    January 28, 2008

    One of my mandates at work is to build up a huge repository of information taken from free and not free, structured and unstructured data sources. Naturally we targeted Wikipedia as the best seed source to get us started. After writing some very specialized table scrapers in Ruby, we decided to take another approach and just ingest the entire Wikipedia database.

    Wikipedia has a “don’t scrape our pages, just download our file dumps” policy, which makes complete sense. Because of this (and the IP blacklisting that occurs when you scrape too fast/too much), our goal is to harvest information from an internal Wikipedia site.

    Wikpedia provides a set of tools/instructions to take to install a version of Wikipedia locally, starting with processing the dump into Mysql and then installing/running MediaWiki, the software that converts the contents of the database into displayable and editable wiki content.

    That is the theory, at least. Here is what I’ve found while trying to follow these instructions: Note that some if not most of the drama below is self manufactured, but hopefully, someone, somewhere will find this information useful.

    1. There is too much information — i.e. six different ways to do the same thing — in some places, and not enough — i.e. what does an actual XML dump contain vs the SQL dumps — in others.
    2. As with most batch processes, error handling and recovery are painful. I am going to share my ‘lessons learned’ wrt wikipedia tools in a future post.
    3. There is no mapping of SQL to XML files in the download dirs — this may only be a problem because I was unable to successfully translate all XML into SQL , but it would still be nice to see that pages.sql contains text and revisions (because it’s not obvious that the other SQL files contain any of that information).
    4. There are plenty of ways to go off into the weeds. For example, I just spent 1/2 hour debugging why my sql upload failed at 68K entries. Turns out that the mwdumper output was getting folded into the sql file because I was running the job as nohup. Right now I’m going to run it as a console job, and investigate how to separate stdout from errout when nohup is invoked when I import it into the database.

    In addition, I’m also using ActiveRecord to access/manipulate the data once it (eventually) gets into the database. ActiveRecord w/o Rails merits some discussions, primarily because I’m also using it with RSpec, which I believe is a much more natural way to validate code.

      I’m going to update my daily progress here so that I don’t have to go through this again. Stay tuned!

      So much to do, so little time…

      January 26, 2008

      I’m in (geek) lust after reading over the sample chapter of Making Things Work, a book that details how to DIY a set of interconnected devices that you get to make from scratch. This book touches on the three things that made me fall in love with computers from the first time I touched a mainframe at age 10:

      (1) designing really fun devices (both virtual and physical)

      (2) making them talk to one another

      (3) creating the detailed interactions (and watching the ensuing hilarity).

      Since I haven’t even started to make progress on my exercise tracking/mapping/etc website, I’m going to have to hold off on this one, maybe put it out there as the carrot that will get me through the current home project, which I can only work on when my real job isn’t demanding all of my time, and right now that isn’t the case 😦

      My Early Morning Exercise Commandments

      January 21, 2008

      Tomorrow, at 5:45, I will get up and prepare to go on a nine mile run. It’s the only time of the day, between job and family, that I have to run, and while  I’m not a super hardcore kind of guy, you know, the kind they show in the Nike commercials, but going on a long run or ride in the morning actually helps the rest of the day go much better.

      Over the years I’ve found that the following suggestions are the only things that keep me rolling out of a warm and very comfortable bed:

      1. I give myself bonus points just for being awake, let alone awake and running.  That means I spend a lot of time immediately after I wake up  thinking “way to go! way to be awake! way to be stepping out into the cold and wet!”
      2. Those bonus points mean that  I don’t have to run fast if I don’t feel like it. Which is good, especially in the first few miles, because I usually start running at a pace generously termed as an ‘aggressive shuffle’.
      3. They also mean that I don’t have to run as far as I had previously planned the night before. In fact, if I’ve been running regularly for a while, and don’t feel like getting out of bed one morning, I won’t. I wait until I’m actually looking forward to running to start up again…
      4. That said, it’s best to get out and start going before I wake up and rational thought kicks in. The rational thing to do, 99% of the time, is to go back to bed.
      5. While I love running to heavy metal, I stay away from the iPod, because on a dark and windy road at 6:30 in the morning, I need my ears and everything else fully functioning. So I run more to an internal iPod, I get a good Ronny James Dio tune going in my head and use that to kick it up a notch. We Rock, anyone?
      6. When I get tired I pretend I’m a Kenyan, i.e. I weigh next to nothing, have lungs the size of a small house, and just float over the ground. I’m still a shuffling Indian, but the visualization has gotten me up more steeps than I can remember.
      7. I cycle through my body as I run, relaxing tensed up shoulders, concentrating on good form, analyzing my foot strike, etc. It’s amazing what you can find to focus on for an hour and a half.
      8. I walk home the last 1/4 mile. You would too if you lived at the top of a steep hill. This gives my legs time to warm down, and makes them less sore.
      9. I then ride into work. This is also a good warm down for the legs, it’s about 5 miles in, with one hill of any consequence.
      10. I take the next day off of running. I ride, or climb, or do something else. This lets my 39 year old body recover in time for the next early morning session.
      11. I try to make these morning runs ‘adventures’, by going on new routes, exploring different areas, reversing parts of loops, etc. Nothing sucks worse than being unexcited about a run because you’ve done the same route for the last 6 months.

      This has kept me fresh and excited about running for a long time now. Hopefully I can stay relatively injury free (ankle is bugging me lately) and keep it together for a couple of 1/2 marathons later in the year.

      Garmin Developer Site — Arrgghh

      January 8, 2008

      I’ve been trying (after work, after the wife and kids go to bed, and before I collapse) to make some progress on my GPS site, and ran into a really strange problem running the demos, detailed here. There doesn’t seem to be a workaround, but one of the posts pointed me to the Communicator Plugin Test Page. While the Plugin API was pretty slick, it’s giving me no love, and I’m going to have to try the code referenced in GarminDeviceControlDemo.js when I’m not as beat.

      Speaking of being beat, I ignored the wise rule to never increase distance, time, or intensity by > 10% per week, and took my 8 mile run at 9:30 average pace to 10 miles at 9:08 average pace. Now the 8 mile is a lot hillier, but the 10 mile loop had some hills in it too…I was beat the next day, and my ankle is a little sore. Add to that the excessive eating I’ve been doing and I feel like a total slug. Hopefully a long run on Wednesday, combined with the iron will to stay away from the office candy stash, will get me back to feeling more alert.