Speeding up database-bound tests with MySQL, redux

Kinda long between the posts here; hopefully what I do write is worth waiting for. :)

Way back in February I wrote about Speeding up database-bound tests with MySQL; a nice chap named Adam Monsen used the idea to speed up the build for something called Mifos.

I've since thought of something that's worth adding:

When you restart your computer with this setup, not only does your data go away (well, d'oh, you're storin' it in RAM, whaddaya expect?), but MySQL gets really confused about whether or not your table is there.

If you know why this happens and want the short version, skip the exposition.

For the rest of us, let's see this in action:

First we set the scene:

espenhw@demokritos:~$ mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 121
Server version: 5.1.41-3ubuntu12.6 (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database confusion;
Query OK, 1 row affected (0.01 sec)

mysql> use confusion;
Database changed
mysql> create table confusion_levels (id bigint primary key, level varchar(255)) engine=innodb;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into confusion_levels values (1, 'TOTAL');
Query OK, 1 row affected (0.00 sec)

mysql> show tables;
+---------------------+
| Tables_in_confusion |
+---------------------+
| confusion_levels    |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from confusion_levels;
+----+-------+
| id | level |
+----+-------+
|  1 | TOTAL |
+----+-------+
1 row in set (0.00 sec)

All good. Now lets confuse MySQL:

espenhw@demokritos:~$ sudo service mysql stop
mysql stop/waiting
espenhw@demokritos:~$ sudo ls -l /var/lib/mysql/innodb
total 20520
-rw-rw---- 1 mysql mysql 10485760 2010-09-14 00:41 ibdata1
-rw-rw---- 1 mysql mysql  5242880 2010-09-14 00:41 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 2010-09-13 08:16 ib_logfile1
espenhw@demokritos:~$ sudo umount /var/lib/mysql/innodb
espenhw@demokritos:~$ sudo mount /var/lib/mysql/innodb
espenhw@demokritos:~$ sudo ls -l /var/lib/mysql/innodb
total 0
# All gone!
espenhw@demokritos:~$ sudo service mysql start
mysql start/running, process 5388

Let's see what MySQL says:

mysql> show databases;
+--------------------------+
| Database                 |
+--------------------------+
[...redacted...]
| confusion                |
[...redacted...]
+--------------------------+

mmysql> use confusion;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------------+
| Tables_in_confusion |
+---------------------+
| confusion_levels    |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from confusion_levels;
ERROR 1146 (42S02): Table 'confusion.confusion_levels' doesn't exist

Ehm, what? But it does exist, you said so yourself!

Before you get bogged down in existential angst, there is a reasonable explanation.

MySQL actually stores information about a database in separate files on disk. Metainformation (such as table structure) lives in files in a directory named for the database.

Vide:

espenhw@demokritos:~$ sudo ls -l /var/lib/mysql/confusion
total 16
-rw-rw---- 1 mysql mysql 8588 2010-09-14 00:50 confusion_levels.frm
-rw-rw---- 1 mysql mysql   65 2010-09-14 00:50 db.opt

Digression: This is actually why MySQL is case-sensitive when it comes to database and table names, on case-sensitive file systems. So if you wondered why MySQL was more forgiving on Windows... Well, now you know.

Data, on the other hand, lives in separate files. For a MyISAM-backed table, the files are named tablename.MYD and tablename.MYI (for data and indexes, respectively); InnoDB, on the other hand, holds data and indexes for all tables across the MySQL instance in the same files (actually, you can change this, but hardly anyone ever does).

This clues us in to the solution to the above existential question: The table structure is still present, but the data is gone.


"Alright Espen, clever boy," I hear you say, "but what do I DO about it?"

Well, you do what you should be doing anyway: Ensure that your tests start running with a clean slate. That means creating any tables it needs from scratch, i.e. either dropping the tables before recreating or simply drop/create on the whole database.

For Hibernate, that should be as simple as setting the hibernate.hbm2ddl.auto property to create-drop (disclaimer: I haven't actually tested that); if you're running hand-crafted DDL to create your tables you probably know what to do...