Tuesday, August 11, 2009

A minimal MySQL ODBC application

I got the question on how small a packaged application with a built-in MySQL server could be. I had an idea that it could be made very small, but I didn't have any exact numbers. The platform was Windows, so I decided to try to build an application like this, to test it.
I had a few requirements:
  • A minimal amount of registry settings.
  • I didn't want to spend too much time on the application itself, so this should be simple.
  • I wanted to have a full blow installation package, i.e. install application and then run it, so no servers to be started or something like that. Just install and run.
  • No assumptions about the Windows PC where this was installed (I actually in the end made one such simplification. I decided to use a fix MySQL port number).
  • Installer was InnoSetup, because I like it and I know it well.
So, I started building a simple dialog based Windows application, using ODBC. Took me very short time, a few hours at the most (202 lines of code, plus a dialog resource). The next step was the ODBC thingy. ODBC really insist that something is installed, at least that Windows knows where an ODBC driver is (but a DSN isn't required. And with some tricks you can get around even having a known driver). The good things was that I can call the driver anything I like, so I choose the name of my application (MiniODBC). I then needed to have my installation program to tell ODBC about this, and this is done with a registry setting:
HKLM\Software\ODBC\ODBCINST.INI\MiniODBC and then set the value Driver to the full path to the ODBC driver (which is part of my application, and is placed in the application directory. This is perfectly OK).

OK, so now for the most important part of this, the MySQL server? What do I need. Note that this is for a minimal setup! I need the server itself of course, mysqld.exe. Then I need a language file, errmsg.sys from an appropriate language directory. Then I need some database files, or rather, to make things really small, I could have ignored this and have the application create the single table I needed, and have MySQL create the InnoDB data and logfiles (yes, I'm using InnoDB). To simplify a bit, I decided to ship with a small default InnoDB file though (10M). I realize this is a large part of what is distributed, but I know it can be fixed, so I leave it at this for now. I also included mysqladmin.exe, so I can shut down the server nicely from the application.

I also need a my.ini. Except for the usual options, I include skip_grant_tables so I don't have to ship with all the usual MySQL host, db, user etc. tables. I used one database only, with one table, so ond db.opt file and one .frm file, and that's it.

As far as the installation goes, this was made really simple, I just copied the files, and the set one registry setting, the ODBC one mentioned above, and tweaked the my.ini file to match the installation directory. So what is missing? Starting the MySQL server maybe you say? I decided to do this in the application. No big deal really, and as I put the MySQL server and .ini file in the same directory as the application. An excerpt of the code looks like this:
char szMysqld[MAX_PATH];
static char szMysqlAdmin[MAX_PATH];
szMyIni[MAX_PATH + 80];
char szFile[MAX_PATH];
size_t i;

switch(nMsg)
{
case WM_INITDIALOG:
/* Get the directory where we are right now. */
GetModuleFileName(NULL, szFile, sizeof(szFile));
for(i = strlen(szFile); szFile[i] != '\\'; i--)
;
szFile[i + 1] = '\0';
strcpy(szMysqld, szFile);
strcat(szMysqld, "mysqld.exe");
strcpy(szMysqlAdmin, szFile);
strcat(szMysqlAdmin, "mysqladmin.exe");
strcpy(szMyIni, "--defaults-file=\"");
strcat(szMyIni, szFile);
strcat(szMyIni, "my.ini\"");

/* Start MySQL. */
ShellExecute(hDlg, NULL, szMysqld, szMyIni, NULL, SW_HIDE);

And yes, I'm aware that this isn't 100% optimal, but it works as an example (no UNICODE string support, limited overflow checking etc).

In conclusion, having built an InnoSetup package with this content, the installation package took up a bit more than 2Mb compressed (and this could have been reduced if I hadn't shipped the InnoDB files). After installation, I ended up with an installation directory with 13 files (14 after the first run: the MySQL .err file) taking up about 25 Mb of disk space, more than half of this being data itself.

If you want a copy of the application and source (well, without the source, this is a pretty limited application, to say the least), drop me an email and I'll fix that for you.

/Karlsson
Who has to do some Linux work soon, I have done much too much Window stuff lately

No comments: