alan little’s weblog

a tale of three databases

9th March 2005 permanent link

Installing open source software often requires hours of fiddling about with editors and C compilers, whereas big-name commercial software generally has slick packaging including reliable and easy-to-use installers. Right?

Well …

For my project I needed to install up to date versions of Oracle, plus one or two other databases for comparison and testing purposes.

I started with MySQL. MySQL provides a readymade mac installer package. I download and run it; it appears to have worked. But then when I go in to set up my development database it tells me ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql'. I suspect that something is wrong with the setup of the mysql configuration database; but then after half an hour or so of googling and looking around, I realise that the problem is that I haven’t used MySQL for over a year and I’ve forgotten that you have to go in for the first time as its root user to set up other users and databases. From which point on everything is fine.

Time to set up MySQL: forty minutes, thirty of which are my own fault.

I’ve always been curious about Postgresql, partly because it has a reputation as the most industrial strength of the open source databases, and partly because I worked for years with Ingres, which was quite good in its day, and Postgresql was the next project by same guy, Professor Mike Stonebraker. Marc Liyanage provides a Mac installer for Postgres, but this time I decide there is no harm in a bit of fiddling about with C compilers, so I decide to try downloading it from source and building it myself following these intructions here. This also takes forty minutes of downloading stuff, setting options and watching messages from configure and make scroll by, at the end of which everything works first time.

Time to set up Postgresql, voluntarily building from source instead of installing a package: again forty minutes.

Nicely warmed up and on to Oracle, which after all is the real object of the exercise. Sergio Leunissen offers instructions for installing Oracle on the Mac that look promising, although the process itself is ugly. It involves setting up users and directories by hand first, then running a (typically ugly and amateurish-looking for a cross-platform java gui) java gui installer, which walks you through a long process in the course of which you have to go off back to the command line and run shell scripts a couple of times. All very messsy, strange and neither one thing nor the other. If you’re going to provide a gui installer, then why not provide one that does the whole job? On the other hand, why provide one at all when 99% of your users for this part of the proceedings are professional DBAs who probably prefer to run scripts anyway?

I may have made a couple of fatal mistakes here. The first was attempting to install Oracle in a directory which had a space in the path name, which it didn’t like, and then renaming the volume underneath the installer while it was still running in an attempt not to have to abort the installation and start again from scratch. The installer then ran through, reporting lots of warnings and build failures but still claiming success at the end of the day. I’m sceptical but still try to fire up SQLPlus to see what I've got. SQLPLus says it needs one of the libraries that reported a build failure, and dies. My next big mistake is trying to delete the installation from the command line and start again instead of running the nasty gui installer and selecting uninstall. When I realise that that’s what I should try to do instead I try it, but the nasty gui installer says it can’t find one of the directories I deleted (whose name it clearly has lurking in some hard-to-find config file somewhere) and refuses to go on.

Time to fail to install Oracle: several hours. Admittedly some of this may have been due to my own mistakes, but even if it had gone smoothly it would have been at least a couple of hours of fiddling about. My next move: attempt a clean installation on my Powerbook, and/or put it on my Linux web server where is might feel a bit more at home. I’m curious to find out how I’m supposed to go about running the nasty java installer gui over ssh though.

(Note to prospective employers/clients who may one day read this: I do know what I’m doing with Oracle once it is up and running. I’ve just never had to install it from scratch – up until now I’ve always worked with it in places where I had DBAs to do that for me. One might also object that MySQL only does 20% of what Oracle does, so of course it’s easier to install, and I wouldn’t dispute that: the obvious danger for Oracle is that that makes it 80% as useful in many situations)

all text and images © 2003–2008