Drift Into View 2: Personal SQL

Sunday, 03 October 2004

After sketching out the idea for a new web site called Electron Drift, it was time to turn to the technology. First up was MySQL. Now I am no stranger to SQL, but installing and setting up anything new is painful. Period. There are no exceptions. Development environments are the worst because they are written by devvies expecting technically astute people to install them. Even though I tried to cloak myself in the ways of the layman, they knew I was a developer too. There was no escape.

I had MySimplePlan. I would install and run MySQL on MyPC. I would throw together some scripts to create the DB structure. I would then test my local MySQL implementation. Unfortunately, the latest version of MySQL when I started, version 4.1.1, did not support stored procedures. This meant all SQL code would have to be written inside the PHP implementation and so I couldn't create a data interface layer to the DB separate from the front-end. I'm not always happy adding in layers and layers of abstraction, but in this case I wanted a layer to be able to test the DB work independently. No dice.

Do Not Install

I downloaded one of the windows binaries from the MySQL site and took the so-called easy option: an installer. It has turned out that setting it up yourself is a lot cleaner. So here it is, the Electron Drift guide to getting MySQL up and running.

The downloaded MySQL zip file was extracted into C:\Program Files\MySQL; while most of the files are mandatory, only two files are of direct interest. One is bin\mysql.exe, which is the command line interface similar to utilities such as isql which I was raised with. The other is the MySQL engine, bin\mysqld-nt.exe, one of several different variations that are available.

I then have a folder, say M:\Data, to hold all of the databases. I copied the standard MySQL DB from the MySQL file set - found under data\mysql - into here, because I never found out how to create one of those. The MySQL DB is the master database which holds user information and other junk like that.

Then I created a my.ini file for the MySQL server, based on the one that comes with the MySQL package. Here it is:

basedir=C:/Program Files/mysql
bind-address= # loopback adapter, no network access
log-long-format # add full logging
old-passwords=1 # this is to make sure php 4 can connect without problems

As you can see, basedir tells MySQL where all of its own files are and datadir tells it where the databases are stored. I then created a MySQL service by issuing the command:

mysqld-nt --install "Test MySQL" --defaults-file="M:\my.ini"

And that was that. But...

Take Precautions

MySQL was very friendly to strangers with its default configuration and such promiscuity is not usually required in a database server. Although a firewall is always essential with today's internet-enabled desktops, there are three important things that must be done before getting intimate with the server.

For simplicity, I created just one user which had full permissions to select and update. This model could be refined of course.

How the SQL Expert Fared

I had plenty of experience with both Sybase and Microsoft SQL Server before starting out with MySQL and was thinking it wouldn't be too difficult to get the hang of it. And it wasn't too difficult, although there were plenty of snags and hiccups.

For example, I installed version MySQL v4.1.1 on my PC, but my web hosting service supported MySQL v3.2.3. I think you can see where this was going. Some scripts I wrote on my PC fell apart when I tried running them on the web site. That reminds me of an issue or feature, depending on your perspective, of open source development. There can be a lot of versions out in the public domain each with their own bugs and enhancements...

As for interacting with the server on the fly, my web host offers PHPMyAdmin for maintenance of MySQL databases which, although kind of annoying, is certainly adequate enough. On the PC side, I can recommend MySQLCC as an SQL Query alternative; few frills, but enables rapid interaction with any local MySQL DB.

Let's go back to examples of those snags and hiccups. The MySQL server could not parse CAST (xx) unless the space between the CAST keyword and parenthesis was removed. And then I discovered that the CAST keyword was not available before version v4.0.2, and had to remove it from my code altogether. There were issues with the keywords TRUE and FALSE so I gave up and used 0 and 1. MySQL did not support subqueries either, which made some more complicated queries a little difficult. Finally, as my PC runs on Windows and the web site runs on Unix, there were problems with the table names; all queries suddenly became case-sensitive once they running on the web server.

I'd be interested to see how MySQL copes with a larger amount of data but, for this small site, it was fine. With a small number of tables, mildly normalized, it wasn't too difficult to get to where I wanted. No, the biggest problems were in PHP itself as it was quite a different kind of programming environment for me. More in the next episode.