Table not found after apparently successful migrat

Question

I was using flyway through the CL to migrate my production DB (mySql), while I was using a fixed SQL query to create the DB, tables, etc. in my unit tests, using H2. I'd like now to better integrate flyway and create/delete DB after each unit test.

I have a DB factory and inside its build method I'm using the following code:

flyway.setLocations("filesystem:sql/migrations/common","filesystem:sql/migrations/h2");
flyway.setSchemas("MYSERVER");
flyway.setDataSource(
p.getProperty(DB_URL.getName()), 
p.getProperty(USERNAME.getName()), 
p.getProperty(PASSWORD.getName()));
flyway.setInitOnMigrate(true);
flyway.migrate();

The migrations seems to apply correctly, as I can see my SQL code from the flyway logs. But when I start using the DB, immediately afterwards, I get TABLE NOT FOUND errors. I'm using h2 as in memory DB with the following URL to initialize my client:

jdbc:h2:mem:MYSERVER;MVCC=true

Do you have any idea on what I might be making wrong?

Answer 1

Having the same issue - after digging around, it appears that the H2 database is being recreated between migration scripts somehow.

You can confirm this happens by putting a SELECT * FROM migrations in the first two migrations - it will succeed in the first and fail in the second.

According to the H2 Documentation, specifying a database name and ;DB_CLOSE_DELAY=-1 option should suffice to allow concurrent and subsequent accesses to the in-memory database, however this is not the case.

Upgrading Flyway from 3.1 to 3.2.1 has fixed the problem.

Answer 2

The problem is your JDBC url.

MYSERVER is the name of the database, not the schema.

The easiest thing to do is to let flyway use the same url, and not set a schema. This way you'll find everything in the public schema of the MYSERVER database.