The short version: it’s a bit bloody hairy! I’d avoid it if I were you.

The long version:

I was running MySQL 4.0, and trying to get Django to work.

Running ./manage.py syncdb, it said “server is too old to set charset”

It seemed to need 4.1 to get some Django stuff working, so thought I’d upgrade. I’m running Drupal 4.7 on the same server, which is a Mac running 10.3.9. I was (fairly) confident that Drupal was backed up ok, so decided to just download the DMG for MySQL 4.1 and give it a whirl.

So, after installing and restarting, I tried to access my website - hello? anyone there? Oh bollocks… no website.

Looking at the /usr/local/mysql/data/ folder I was horrified to discover that all my databases had disappeared. Brown trousers time…

At this point I should point out that I am an idiot. If this happens to you, don’t panic. The installer has not eaten your databases, which is what I thought it had done!

If you look in /usr/local/ you will discover that mysql is aliased to the actual installation, for example mysql-max-4.1.22-apple-darwin7.9.0-powerpc. I didn’t realise this, and I had the directory aliased from somewhere else too, so I thought my databases were gone.

I was remarkably calm. Oh well, I said, time to discover if the backup script actually works.

I am using mysqlhotcopy to back my databases up, and I’d never had to actually do a restore (I can’t believe I’m saying that - I hadn’t tested my backups - but then testing them isn’t that easy when you have no time and no spare server). Anyway, I had no idea how to turn the backup back into a working database.

The answer, in case you are wondering, is remarkably simple - copy it back into the right place. Wow - it’s almost like using a Mac (apologies to the hardened unix geeks out there, who must be wondering what kind of an idiot I am. The answer is, I am a very technical idiot who, due to a career spent programming macs and the occasional pc, has never had to deal with any of this shit before). I love that the Mac is based on unix now, I really do. I just wish unix would catch up sometimes…

Anyway, I copied the backup back into the right place, and as luck would have it, it seemed to work.

Of course, I had to spend a while fixing up all the sql users and passwords and privileges and all of that malarky. I always forget my MySQL root password for some reason, which really doesn’t help. Lucky I wrote it down ;)

So, I had the databases back, and everything should be hunky dory. Hit reload in the web browser, and… Drupal was complaining that it didn’t have access. To be precise, it was saying “Client does not support authentication protocol requested by server; consider upgrading MySQL client”. Eh?

I rummaged around a bit, and started thinking, uh-oh, maybe Drupal 4.7 doesn’t work with MySQL 4.1. After a bit of searching, I got the vague impression that there were some problems, but there seemed no clear solutions.

Right, I figured. In for a penny… lets upgrade Drupal to the very latest 4.7 variant, that’ll fix it.

So I did, and it didn’t.

Oh so it, maybe Drupal 5.1 will fix it.

It didn’t.

Oh, I’ve found a note saying you have to go to 5.1 via 5.0. I’ll install that.

It didn’t help.

At this point I was wishing that I hadn’t started. However, a bit more googling finally gave me this: http://drupal.org/node/13977. Aha! Nothing to do with Drupal at all.

Turns out that the password format has somehow changed and you have to do a bit of mucking around to fix it. Ok, fine, I can do that. So try again, and thank something or other, it worked. The web site came back.

Whoohooo.

This is probably where I should have stopped… except that when I tried to log in to the site to blog all of this, I couldn’t. It just silently failed to log me in, and I ended up back on the log in page. What???

I had a vague notion that a similar passwordy sort of problem was perhaps occuring, this time with Drupal’s own user table, but I couldn’t get it to work and I didn’t know how to change Drupal’s own password entries with SQL. I had gone back to 4.7 by this time, but I still couldn’t log in.

I tried running the various Drupal update scripts for various different versions of Drupal, but no joy. In fact, they didn’t work because of various other permissions problems. Some of them turned out to be the fact that I hadn’t got the permissions for the database files right when I restored them.

Ah well, thought I. I’m in a right pickle now, but at least the site sort of works. What the hell, maybe MySQL 5.0 will fix it. You can tell I was feeling optimistic tonight.

So, another DMG install later, and no further on. Actually, things were worse. Drupal 4.7 wasn’t talking to the database again, at all.

Luckily it was around this point that I realised that the multiple MySQL installations were happily residing in different directories on my server, along with my original databases. This is exactly how I’d expected it to work in the first place, I’d just got a bit confused.

So, realising that I still had my original installs of MySQL, Drupal, and the original data files, I figured that I could probably just get things working as they originally were.

I did. It works.

Interesting evening that.

The only problem is, I still can’t get Django to talk to MySQL. Arse.

Well I finally managed to figure out some of the problems I’ve been having and upgrade both MySQL and Drupal.

As you will notice, the site theme has changed as a result, and it’ll probably take me a while to get things straight.

The route that I took in the end was to use mysqldump to back up the entire database, upgrade to MySQL 5, and restore the database from the dump. This seemed to work fine and left me with a working database with my existing 4.7.0 install of Drupal.

I then upgraded Drupal in stages from 4.7.0 -> 4.7.6 -> 5.0 -> 5.1, running update.php at each stage. A bit laborious perhaps, but it worked without a hitch.

Thanks to D’Arcy Norman for a handy link, which put me on the right path.

I keep meaning to work on a new one, and was googling around today when I came across this.

I got sufficiently distracted to post a comment:

Link http://climbing-the-hill.blogspot.com/2007/06/on-severely-misguided-nature-of.html

I'm currently writing code for four platforms, using three IDEs (Eclipse, XCode and VisualStudio), and it's driving me bonkers!

I've said it before, but I'll say it again - why oh why doesn't Apple consider freezing XCode development and switching over to using Eclipse. I know that XCode has some nice features but generally I find it pretty clunky and I can't believe that all the good stuff couldn't be reimplemented as Eclipse plug ins.

Eclipse, on the other hand, is really solid but a little bit lumbering and confusing at times - I'm sure it would really benefit from some Apple user interface input.

So once that's happened (I wish), I just need to convince Whole Tomato software to reimplement Visual Assist for Eclipse, and then I'd be laughing...

Every now and then I come back to using XCode for some reason or other.

And every time that this happens, I am once again amazed at just how bad it is in many ways, at least for C++ development.

My current bug-bear is the code auto-completion - when you start typing the name of a function or variable and it tries to jump in there and suggest something for you.

On the whole, the suggestions that it makes seem to have got a bit better since the last time I used it, and it seems to manage to make a suggestion in a reasonable amount of time (maybe that's just because I've got a MacPro now).

The interface for making the choice, however, is awful. The thing that's really annoying me is that there appears to be no way of having it pop up suggestions, but allowing you manual control over whether to accept a suggestion.

It seems that you either have to manually pop up the suggestion box (which I don't want to do - I always want it to suggest things), or you can have the box come up automatically, but as soon as you hit any key other than escape, the currently selected suggestion will get inserted. This is truly awful user interface, since I'd put the success rate for it's suggestions at 50% at best. What this means in practice is that it is endelessly inserting the wrong text, as I type quickly and often the suggestion box has come up, an insertion has been made, and it has gone away again - before I've even registered that it was there.

I really hope that I'm just being stupid, and that there's actually an option to make it put up the suggestion box, but only insert the suggestion if I press a particular key (e.g. Return). This is the way that Visual Assist works, and it's a lot more intuitive (not to mention a lot safer).