MySQL: what a Toy Database that is

Earlier I learned that MySQL only provides foreign keys on the InnoDB storage engine but now I ran into even more troubling issues with MySQL. That makes me think how could Sun pay all that money for such a toy database. I know that MySQL is the de-facto standard as Web database but like Windows this does not say anything about its quality.

Well, I was trying to install a build system and after making a passwordless CVS access working I though I would be done but MySQL took the most of time to make the build running smoothly including the tests where the DB is used. I ran into two major issues:

1. MySQL can only be assigned to one network interface
2. MySQL is case sensitive on Linux even for DB objects

The first issue is problematic because I need the test to run against the local network loop (127.0.0.1) so that the test run smoothly on the developers' computer as well as on the build system. On the other hand this box has no VNC as so I need to use the MySQL administration tool on a remote computer with a UI. This means that I have to switch the assigned network address when I want to administer the DB making the build fail in the meantime.

The second issue is bad because now I have to make sure that my code only uses lower or upper case for table names including tools like dbunit. This is annoying because I don't know of any other DB that does this that way. I never paid attention to the case of table names etc in a SQL / JDBC statement and I think nobody should. I fixed that by making MySQL case-insensitive for table names by adding this line to the my.cfg configuration file that on Linux is normally under /etc directory:

# Make sure that MySQL uses lower case to identify objects (otherwise the names are case sensitive causing all kinds of problems)
set-variable=lower_case_table_names=1

In addition to make sure that I have foreign keys available I had to add this line in the my.cfg file as well:

# Make InnoDB the default storage engine for referencial integrity
default-storage-engine=INNODB

Eventually that did the trick but comparing that to PostgreSQL this is just plain ridiculous where I never encountered issues like that.

Have fun - Andy