H2 + LiquiBase + Spring == Table not found?

General Discussion
1

Hi all,

I use Liquibase to update our ‘main’ MySQL databases on each environment as part of a Maven build.

I’m trying to hook up Liquibase and H2 to my Spring application, and run integration tests against it. I The Spring context starts fine, the H2 database appears to start, and Liquibase starts running the changelog (including all the table creates to pre-populate it), but after about 90 structural changes, throws an error saying my “user” table can’t be found when doing a SELECT COUNT(*) id FROM user WHERE id = 885 AND nickname = ‘test’ on it for a precondition.

The odd part is that the user table changeset logs as being run, and subsequent changesets add foreign keys and columns tot he user table without throwing an exception.

Any ideas where I might start to look? I’m new to H2, so maybe I’m missing something obvious there. I’ve got it running in MySQL mode and case sensitivity turned off.

2

I’ve switched to getting H2 to persist its data to a file instead of in memory, and if I open this database using a GUI like RazorSQL I can see it’s empty.

Liquibase still insists that all the DDL changesets are being executed though. Is there anything that would prompt Liquibase to report changesets as ran, when actually they’re not?

3

Something (I suspect the H2 driver) was renaming my table called user to “user”, quotes included! Whatever code it was must’ve thought that user was a reserved word, but it’s not a reserved word in H2.

4

Liquibase does have user as a reserved word in h2 and so tries to quote it. I found http://www.h2database.com/html/advanced.html which lists the reserved words and set the list based on that.


Nathan

5

You have it running in MySQL compatibility mode?  Is driver itself say?

Best,
Laird

–
http://about.me/lairdnelson

6

@deejaybinarytweed yes, the h2 list doesn’t include user, I rebuilt the reserved words list for the next release to not include it.


Automatically including the results from DatabaseMetaData is a good idea, I’ll add that in for the next release as well.


Nathan

7

Laird - good idea. I’m running in MySQL compatibility mode, but MySQL doesn’t reserve usage of the word “user”: http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

8

Hi Nathan,

The page you linked to doesn’t list “user” as a reserved word, and I can create a table called user without any quote marks in an H2 database.