Skip to content

MediaWiki problems with MySQL VARBINARY padding

I have been wanting to protect a certain page on my Dutch 15Monkeys wiki for weeks. I could install the ReCaptcha extension already (the best Captcha there is), but it’s just that one page that’s being spammed. So far, I’ve been unsuccessful in regaining my Sysop right for that wiki. I need these rights if I’m going to protect these pages.

In the previous post about this problem, I reported how there appeared to be padding problem caused by a MySQL upgrade. Fixing the padding didn’t solve the problem, however. If I look at the user list in MediaWiki, I notice that MediaWiki doesn’t seem to like it that MySQL returns all the \0x0 padding characters on SELECT:

15Monkeys MediaWiki NL user list with unrecognized characters

15Monkeys MediaWiki NL user list with unrecognized characters

The same problem in the EN wiki

The same problem in the EN wiki

BigSmoke ‎(bureaucrat������, sysop�����������)

Repair was suggested by the check operation in phpMyAdmin. (CHECK TABLE `mw_nl_user_groups`) I couldn’t find a repair operation (only OPTIMIZE), so I tried that and I was told that it went ok. In the database structure view, the repair option was available ; when I tried to repair all the tables belonging to this installation, I was told for this particular table (and one other) “The storage engine for the table doesn’t support repair”. I found out that the reason this error didn’t appear the first time because it appears quite randomly. Sometimes it just silently fails in true MySQL style.

Googling for the error message produced the advice to dump and recreate the table. Then, when recreating the table from my backup didn’t work, I found out that REPAIR really isn’t supported for InnoDB. Only CHECK is. If you want to repair an InnoDB table, you use OPTIMIZE or you have to restart mysqld with the innodb_force_recovery option enabled.

Then, it turned out that I misinterpreted the MySQL documentation for the VARBINARY type, although I still can’t figure out how I am supposed to deduce this from said documentation. Anyway, if I UPDATE the rows, all I can achieve is that whitespace is converted to \0 characters. But, if I reinsert them without the padding, it does work. 😕 Go figure…

    2 Comments ( Add comment / trackback )

    1. (permalink)
      Comment by Patrick
      On June 19, 2009 at 17:17

      I wanted to thank you for posting this–my ISP just upgraded from MySQL 4 to 5, and suddenly I could no longer login to my MW sites. After a lot of searching I realized this was likely the same problem you were describing–I ended up running REPAIR TABLE on all tables in my MW database and that appears to have fixed it. Thought the MW was a goner!


    2. (permalink)
      Comment by Rowan Rodrik
      On June 19, 2009 at 17:42

      Hi Patrick,

      Wow! I’m so glad that my post has helped you to solve your problem. Thanks for telling me and making me smile! 🙂