MySQL Installer

I spent much of the last week fiddling with Inno Setup to create a MySQL installer for win32. The installer asks for the program install directory (and suggests a default) and the location of the data directory (and suggests a default). It independently installs MySQL server, ODBC, and GUI tools based on check boxes. That's it. It's pretty slick.

Why not just use the .msi installers that MySQL provides, you ask? Well, my installer is going out to dozens of sites and the configuration is set beforehand and is bound to our commercial application. The MySQL .msi installers ask dozens of configuration questions and we're looking to reduce that and to prevent the sysadmins who are installing our application from making wrong choices. For example, we must run the InnoDB engine only and never the non-transactional MyISAM engine. That's a choice the the MySQL .msi installer gives but we do not. My installer also automatically configures users, passwords, databases, tables, and access.

Sadly, this work invalidates my company's decision to standardize on PostgreSQL. Management decided to opt for short-term expediency instead of long-term robustness and scalability. Brilliant. (sarcasm) That won't come back to haunt them. (dripping sarcasm)

Inno Setup

I have been tasked at work with writing a one-click MySQL installer for win32. Luckily, I have the awesome and amazing Inno Setup packaging tool at my disposal. Inno Setup is full-featured and capable, however, it isn't easy to use for complicated packaging scenarios. One has to discover and use helper scripts to simplify tasks such as decompression and manipulating environment variables. My secret weapon is that I have access to a great set of example scripts that a previous colleague wrote a few years ago. He was a super bright guy but it still took him months to produce our main Inno Setup script. That just shows the power and complexity of the tool. I expect that it'll take me 3-5 more days to get the MySQL installer just right with the proper configuration and addition of odbc and the gui tools.

Ssh port forwarding, gateway ports, and timeouts

We have a client that firewalls its network to us except for tcp 22 (ssh) from one particular address -- our development server. The goal for us is to be able to remotely use the deployed web application that we developed for them so that we can check out the effect of bug fixes on the live site.

Enter ssh...

I'm not going to talk much about ssh and its better-known capabilities because that's going to be all over the web anyway. Really, go see for yourself.

Instead, we log into our local development server and issue this command:

ssh -g -f -L 8082:localhost:80 user@w.x.y.z sleep 60

where w.x.y.z represents an IP address of the client's server.

What that command does is to forward traffic from localhost:8082 to w.x.y.z:80. It also says to go into the background and to bind port 8082 to all interfaces and not just loopback. That means that anyone who can get to our server's port 8082 can use the tunnel but our development server is behind two firewalls so I'm not worried.

You can then log out of the development server since the ssh tunnel is backgrounded. Open a web browser on your local box, point it to the development server's port 8082, and you'll be seeing the client's web application. Nifty!

Finally, there's a "sleep" command at the end. That's just an example of ssh's ability to perform remote commands. The intent here is to keep the tunnel up for at least x number of sleep seconds but if another application is actively using that tunnel at the same time the tunnel will not close even though the sleep may have already expired. Once the sleep duration has passed and nothing is using the tunnel, the tunnel automatically closes.

That almost works in our case with testing the web application except that http is stateless. That is, it establishes a socket connection every time a request is made and then the socket is closed after the requester receives its response. (Actually, it takes a few seconds for the opened socket to disappear.) For all practical purposes, you probably have a 5-10 second inactivity timeout no matter what you set "sleep" to be as long as you're only using a stateless connection. Hopefully, that'll be enough time for you to test the bug fix on the client's box! One could always just set the sleep duration much higher. Remember, the auto-logout only takes effect once the sleep duration interval has passed.

I got the basics for this auto-timeout solution from http://www.g-loaded.eu/2006/11/24/auto-closing-ssh-tunnels/.

Better XML needed in Python

I must say that I have just about lost patience with the heroic maintainers and developers of python xml libraries. I'm grateful to have those tools at my disposal but am frustrated that so many key ones seem to languish or are somewhat incomplete. For example, I love elementtree but there still is incomplete XPath support. I really need XPath and have to use 4Suite to get it. I also use 4Suite for applying style sheets. 4Suite is good but is just awkward enough to be not joyful to use. It went for a while without any updates and sat at beta status seemingly forever. Then there's ZSI for SOAP. Well, SOAP is pretty unhappy stuff anyway so you just kind of have to make the best of a bad situation.

Believe it or not, I have to use elementtree, 4Suite, and ZSI on a single project. Oh well, at least I was able to ditch PyXML on that project.

Thanks for the rant space.

mod_rails is smokin' fast

I recently switched a heavily used production Ruby on Rails server from using a cluster of mongrels proxied by Apache's load balancer to using only mod_rails in Apache. No more mongrels.

Right away, I could tell that the application was noticeably more responsive. I plan to let this run for a week and then I may put up some pre and post benchmark data.

My first impressions:

• mod_rails was easy to install using the documentation provided. I needed to install two pre-requisite packages on my CentOS 5.1 server: gcc-c++ and httpd_devel along with their own dependencies. The Yum package manager made quick work of that.
• mod_rails has a friendly installer which interpreted into plain English what otherwise would have been hairy looking build errors.
• mod_rails was easy to configure. I already had a virtual host set up for the Apache proxied mongrels. I only had to load the mod_rails module, remove the rewrite engine rules (as the docs instructed), restart Apache, and that was it.

My takeaways:

• Deploying with mod_rails is much simpler than deploying with proxied mongrels.
• It appears that mod_rails is much quicker than mongrel.
• There also appears to be a substantial memory savings in not have to run so many mongrels.

Case sensitivity in databases

Recently, I had to clean up some SQL to be case-insensitive. It was originally written to be run in MySQL which defaults to case-insensitive searches but can be set otherwise. Now, however, the SQL run is being run in PostgreSQL which always performs case-sensitive searches.

Here's an example using Ruby on Rails's Active Record:

Suppose that a name is stored in the providers table as "SMITH".

Below, the set collection will be empty because Smith is not like SMITH.
@search_field = "Smith"
set = Provider.find(:all, :conditions => "name LIKE '%#{@search_field}%'")


But this yields the expected results:
@search_field = "Smith"
set = Provider.find(:all, :conditions => ["LOWER(name) LIKE LOWER(?)", "%#{@search_field}%"])

The reason the second example works is because it uses the database's LOWER() string function to change Smith to smith and SMITH to smith.

And, as it so happens, the UPPER() and LOWER() string functions both exist and work identically in MySQL and PostgreSQL so the code above is portable.

Rails and OS X

I spent hours today getting a Ruby on Rails application running on an OS X 10.5.2 server.

The problems were numerous but, honestly, it took so long mainly because I never use OS X and didn't have familiarity with some things that I thoroughly understand in Linux and Windows.

Before I could even begin the process, I had to export the tagged version from my company's CVS repository. CVS cannot export empty directories. From the CVS manual:

(Note that cvs export always removes empty directories.) Probably the best way to do this is to always specify `-P'; if you want an empty directory then put a dummy file (for example `.keepme') in it to prevent `-P' from removing it.

The person who initially applied the version tag didn't know to take care with the empty directories. As a result, I had to add an empty '.cvsignore' file into each directory, commit those dozens of .cvsignore files, and then tag them. That wasn't a big deal to do with "find" once I knew what was needed. This was a shotgun approach but was benign.

First, I could not get mysql to listen to the TCP/IP interface despite what was in the /etc/my.cnf file. Turns out that one has to fiddle with the setting with:
serveradmin settings mysql:allowNetwork = yes
(Is that going to persist after a reboot?)

Second, I couldn't get the C-based mysql ruby gem installed. I simply didn't have the header and library files against which it could build. This should not have been a big deal except that there wasn't a clear source package to download from the mysql site. I had to rely on a tech. support person to do that.

Finally, I had to make sure that the various mongrel instances would start automatically. That took some doing and I finally got them configured using launchd and launchctl. Launchd functions as a complete replacement for inetd, init.d, cron, and a few other things. Basically, one has to configure some XML files. It took some time to get my xml files just right.

One weird thing that I encountered is that if I told the mongrel service to bind to the "localhost" interface it would only do so using IP v.6. I had to specifically tell it to use "127.0.0.1" to coerce it to use IP v.4.

The Good:
Once I got into launchd, I began to appreciate how it could simplify a lot of the traditional unix utilities and configurations.

The Bad:
OS X and running Rails on OS X is a niche market for my company. I can't help but to think what a waste of time this was because I won't be able to leverage this experience over many installations. Even if I could, I wouldn't want to because I have zero interest in OS X.

The Ugly:
In reading the launchd documentation, the Apple folks come off as unjustifiably arrogant. Check out this quote from http://developer.apple.com/macosx/launchd.html:

If this is not sufficient for your job, you need to modify your program or file an enhancement request against launchd with Apple.