Feb 242005

I’ve been waiting patiently for the updated WordPress to come out. Once it did, I decided I should really update MySQL as well, so that I could finally serve pages properly as UTF-8. I’d been running MySQL 3.23, and so I needed to upgrade to 4.0 to get real UTF-8 support. I also contemplated upgrading to MySQL 4.1.

The process was just complicated enough that I figured I should do the backup brain thing and dump the procedure here; it might help someone else to have the gory details documented, and it will certainly help me when I come to upgrade!

I haven’t really used a Unix system since I had to move to Windows in 1996; fortunately Debian makes upgrading and installing really easy (whoever came up with the apt-get system deserves lots of praise). I run the stable version of Debian because it was set up on stable in the first place and I’ve never really needed to change that and I’d rather have the security patches be applied automatically. Except for, the Debian stable version of MySQL is, of course, 3.23 and not 4.0. The best solution to this seemed to be to install the backport of MySQL 4.0 from backports.org. In theory this is really easy, of course in practice it does take a certain knowledge of how Debian works. This is because Debian Does Everything Differently (DDED). I’m sure there is lots of logic in how Debian does things, I just have never quite taken the time to figure it out. And up till now, I didn’t need to. I did have one big advantage though, Edd Dumbill was online and was able to help me figure out those little details that make the difference between frustration and success.

So here’s the step-by-step upgrade details I needed. Debian experts have no need to read any further…

  1. Add the line deb http://www.backports.org/debian stable mysql-dfsg into your /etc/apt/sources.list file (I don’t know why it’s called dfsg and I’m not sure I want to know).
  2. Run apt-get update and then apt-get upgrade, or apt-get dist-upgrade.
  3. Find that although the package list gets updated, nothing happens and the message is “0 to install”
  4. Edd suggests checking the policy with apt-cache policy mysql-server.
  5. This shows that Surprise! the upgrade isn’t being installed.
  6. To increase the priority, we need to pin the backport. Edd tells me the magic incantation, which is to create a /etc/apt/preferences file with the contents
    Package: *
    Pin: release a=backports.org
    Pin-Priority: 995
  7. apt-get upgrade now works! Hurrah!
  8. Next to run mysql_fix_privilege_tables to fix the GRANT tables etc. Except for, the MySQL database root account has spaces in the password and this script doesn’t like that. Even hardcoding the password in the script doesn’t work.
  9. Use mysqladmin to change the root password so it no longer has spaces in it (BTW, does anyone else find it annoying that some MySQL things use executable u=username and others use executable u username? Fortunately PHPMyAdmin makes most of my MySQL admin duties much easier).
  10. Success! The blog still works, the tables appear to have been fixed, and life goes back to normal. I decide to not upgrade to MySQL 4.1 just yet, since I don’t really need to. This box in the basement acts as my firewall, DSL connection, and website host; sticking with a stable configuration strikes me as being a good idea most of the time.
/* ]]> */