Rails test data not getting rolled back

03 Mar 2010

Here begins a MySQL new bee tale of woe. I was working on a Rails app, writing tests, all was well. Then a co-worker told me the tests were failing for him. I couldn't reproduce it; they were working fine for me.

Later I noticed odd behavior - I would create records in the setup method and they would stick around. I flailed around for a bit, poking myself in the eye and such, and then finally got down to business and did a show create table my_table_name and whoa, it's MyISAM! That's not good. So I set this option in /etc/my.cnf:

default-storage-engine = innodb

Restarted MySQL - what's this, it wouldn't start! Checked the logs:

100303 18:37:37 [ERROR] Unknown/unsupported table type: INNODB
100303 18:37:37 [ERROR] Aborting

Now we're getting somewhere. The clincher:

mysql> show engines;
+------------+---------+-----------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                   | Transactions | XA   | Savepoints |
+------------+---------+-----------------------------------------------------------+--------------+------+------------+
| CSV        | YES     | CSV storage engine                                        | NO           | NO   | NO         |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                     | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance    | NO           | NO   | NO         |
+------------+---------+-----------------------------------------------------------+--------------+------+------------+
4 rows in set (0.00 sec)

Notice what's not there? InnoDB! This was a MySQL 5.1.44 install that I had merrily compiled from source, feeling like quite the l33t h@x0r. But what I hadn't realized was that the InnoDB storage engine was now a plugin. After some more blundering this way and that, I settled on:

./configure --prefix=/usr/local/mysql51 \
--with-unix-socket-path=/usr/local/mysql51/run/mysql_socket \
--with-mysqld-user=mysql --enable-thread-safe-client --with-plugins=innobase

A make && make install and a MySQL restart later I'm up and running; show engines now displays InnoDB. Best of all, I could see and fix those test failures. Huzzah!

In retrospect I should have dove into the problem as soon as it was reported to me... live and learn.