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...
