Tuesday, April 7, 2009

MySQL Versions and ETL

I've been working on migrating the company from RT to Jira for defect tracking. I've got Jira all set up, I've got the scripts we use for logging bugs automatically reconfigured. I've got the users created and passwords coming out of LDAP. The only thing left is to migrate the old issues from RT to Jira.

This should be a straightforward ETL operation, right?

Well, yeah, should be.

I decide I'm going to do this in Ruby (hey, why not), so here's what I do:
  1. install the Ruby MySQL gem (sudo gem install mysql)
  2. pick an ETL library (ActiveWarehouse)
  3. follow the instructions
Here's the problem: we're running RT 2, which is old. It runs on MySQL 3.23, which is also old (unsupported since 2006). And the standard Ruby gem simply doesn't work with it. Heck, nothing in MySQL 5 works with it.

Here's what happens when you try to log in with the MySQL 5 client:


catherine-powells-macbook-pro:etl cpowell$ mysql -u rt_user -p -h rt rt2
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.


It hangs there for some undetermined period that seems to be about two hours. After that, you're in and it works fine; obviously, this is going to slow down the ETL process a lot. 

What's interesting is if you get on the system from another client and do SHOW PROCESSLIST, you get this:

| Id | User | Host | db | Command | Time | State | Info |
| 615 | rt_user | vpn-21.permabit.com | rt2 | Sleep | 75 | | NULL |
So you've logged in; you're just... hanging.

I finally figured out that it's some version incompatibility. A MySQL 5 client - either the Ruby MySQL gem or the client you get when you install MySQL - will do this every time. MySQLAdmin, curiously, doesn't have this problem.

I must have spent 5 hours searching before I figured this out. So if there's anyone out there with a MySQL client that hangs on login, if you're using an old MySQL (3.23 for sure, not sure what others), try using an old client as well. I wound up using an old Ruby-mysql library.

As for the ETL project, well, now that I've solved this I can actually really start it.

2 comments:

  1. Hello, could you help me a bit on how to use Activewarehouse-etl to run some scripts/methods on mysql database ?? i would like to test the aggregate methods that activewarehouse comes with, but still had no success...

    Thank you

    ReplyDelete
  2. Manosv, I didn't work with it beyond the RT to Jira migration, but I'm happy to take a look. Let me know what you're trying to do specifically, and send me your scripts and the error you're getting, and I can take a look.

    ReplyDelete