Getting Mono and MySQL to play together politely

When I first used Mono, shortly after its first appearance in 2004, one of my most important requirements was to get connected to a MySQL back end. This was quickly achieved at the time with the ByteFX data connector. Following the basic Mono tutorial quickly had me up and running, with access to MySQL databases. Last week I needed to reopen and modernize my application and found that there have been several important changes in Mono between 2004 and 2008. First, I found some “then and now” differences with the installation of the data connector. Second, I needed to make some modifications to my programming.

My test platform for this article was openSUSE 11. I thought that since Mono is run from Novell/SUSE that this might give me the most up-to-date Mono files. To some extent this was correct, as you will see.

Installation issues

The first issue is that the recommended data connector for MySQL is no longer ByteFX. The ByteFX connector will still work for some basic needs, but the Mono project now recommends the MySQL connector direct from MySQL. The DVD install of SuSE 11 includes ByteFX but does not include a MySQL connector, even though there are connectors for other backends such as Firebird and Postgresql.

From the changelog of the ByteFX package it is clear that SUSE is putting work into keeping ByteFX connector up to date. However, according to Miguel de Icaza, ByteFX should only be used in those cases where the MySQL connector will not work.

So I visited the downloads page for the latest and greatest MySQL .Net connector, where I was offered sources for Visual Studio together with precompiled Windows binaries. No mention of Linux binaries. Actually, the Windows binaries worked fine on Linux; I just needed to download the Windows binaries (no installer) package, extract the MySql.Data.dll file, copy it into the /usr/lib/mono/2.0/ (or other directory according to need) and register the dll with the gac:

cd /usr/lib/mono/2.0/
gacutil -i MySql.Data.dll

…and the file became available for adding in the normal manner as a reference in Mono or Monodevelop.

If you follow the excellent little tutorial on connecting your Mono frontend to MySql you will soon run into a small problem: the Mono compiler will not like the references to IDbConnection and IDataReader. Classes are now prefixed with ‘MySql’ and not ‘ID,’ as in MySqlConnection and MySqlDataReader.

In addition, let’s say you have a connect string for your database in this format (note in particular the single quotes):

String connectionstring = "Server='localhost';Database='test';User Id='aaaa';Password='bbbb'";

This will work if you are using the ByteFX connector, but the MySql connector will fail with a message that the specified host could not be found. Removing the single quotes around ‘localhost’ will allow the host to be found, but it will not log you in since the user will not be found. The MySql connection string needs to be stripped of single quotes. (In this format it will work with the ByteFX connector, too.)

Password hashing in MySql definitely places some restrictions on which connector can be used. ByteFX will not deal with password hashing in MySql 4.1+, but the MySql connector will.

When setting up your first connection to a back end of any kind, it is helpful to have concrete feedback about why things are not running as expected. Using the try - catch structure will help to demystify the sometimes cryptic, sometimes lengthy information from the compiler:

try
{
... statements to be tested go in here
}
catch (Exception ex)
{
  Console.WriteLine("Error was: "+ex.Message);
}

It’s not the Mono developers’ fault that specifications have to change and that documents get out of date, but we users need a reference to the changes that need to be made. Luckily, there’s plenty of detailed assistance available on the Mono forums.

In the absence of a sophisticated replacement for Access on a Linux machine, having a convenient front end to a MySQL back end in C# — as provided by Mono — gives us an alternative. And the more easily and efficiently we can get our first connection going, the more ready we are to attempt the more complex process of actually editing, adding, and deleting data in the database.