I have been looking for a while for a tool to create Entity Relationship Diagrams (ERDs) on Linux. On Windows I used Dezign for Databases, an excellent tool which I would still be using if it was available for Linux. Linux tools that do as much as it does are few and far between. DBDesigner4 is open-source but wouldn’t run at all on my system and doesn’t seem to have any visible means of support. After much searching I ended up evaluating a commercial (i.e. non-free) product, Data Architect, from theKompany. After a few false starts trying to install the demo from an RPM, I managed to get it to work by the simple expedient of downloading and unzipping the tar archive. I had to edit the shell script to replace ksh with bash, but then it worked.

However, it requires ODBC to talk to databases (not an essential feature if you are just doing design and then generating the necessary SQL scripts, but definitely nice to have). Being a Microsoft invention, ODBC is not that easy to get working on Linux. It took me all morning, but I succeeded in the end. So here’s how to do it on Xandros Home Premium:

1) Use Xandros Networks to search for and install unixODBC, the package that provides ODBC support (or use apt-get, or whatever package manager you use, or download direct from unixodbc.org).
2) As far as I could tell this did not install the GUI front-end that allows you to manage ODBC data sources in a similar way to the ODBC manager on Windows. This is not essential because you can simply edit the relevant configuration files if you know what you are doing. Not knowing this, I visited unixodbc.org and downloaded unixODBC-gui-qt-2.2.11-1.i386.rpm, which I then installed using Xandros networks.
3) Great, ODBC support is now available and the GUI works! However, I don’t have any drivers. I want to use it with MySQL, so it’s off to MySQL to download the necessary driver. Again I downloaded an RPM and installed using Xandros networks. I quickly found that this didn’t work with Xandros. So a quick Google sent me off here for a Debian package. Goodness knows why MySQL don’t provide a Debian version.
4) Now I again ran the GUI front end installed in step 2, and hey presto, the driver was there. The bad news: when I tried to set up and connect to a data source I got a message saying that the driver couldn’t find /var/mysqld/mysqld.sock. Not surprising, since this file did not exist. More googling and a poke around my file system established that the file was actually at /opt/lampp/var/mysql/mysql.sock. 5) So I needed to edit the odbc.ini file to tell it where to find the socket:

[MySQL-test]
Description = MySQL database test
Driver = MySQL
Server = localhost
Database = test
Port = 3306
Socket = /opt/lampp/var/mysql/mysql.sock
Option =
Stmt =

Progress! Now I can connect, but when I try to authenticate with a username and password, “Client does not support authentication protocol requested by server; consider upgrading MySQL client.” Aha, I’ve encountered this before; it’s to do with the password hashing in newer versions of MySQL being incompatible with clients expecting older version. So, simple solution:
6) Set up a user for ODBC purposes, with the appropriate privileges, and issue the command:

SET PASSWORD FOR odbcuser@localhost = OLD_PASSWORD('mypassword');

7) Try again, and yes, I can browse the databases and tables now! Nice added bonus: ODBC is also available in OpenOffice, so I can access my databases from there too. Let’s just hope that DataArchitect does the job …