3

We do Flyway migrations with SQLite (Flyway v3.2.1, SQLite 3.7.13 and the org.xerial:sqlite-jdbc:3.8.7 driver.)

The maximum number of parallel database connections seems to be crucial. But the migration fails with any connection pool size, for different reasons.

1. Using a Connection Pool of size 2 or more

There is a locking problem related to multiple parallel database connections. A single migration on an empty SQLite database ends up with:

o.f.core.internal.command.DbMigrate.(:)()  Current version of schema "main": << Empty Schema >>
o.f.core.internal.command.DbMigrate.(:)()  Migrating schema "main" to version 1 - initial
o.f.c.i.u.jdbc.TransactionTemplate.(:)()  Unable to restore autocommit to original value for connection
java.sql.SQLException: database is locked
    at org.sqlite.core.DB.throwex(DB.java:859) ~[sqlite-jdbc-3.8.7.jar:na]
    at org.sqlite.core.DB.exec(DB.java:142) ~[sqlite-jdbc-3.8.7.jar:na]
    at org.sqlite.jdbc3.JDBC3Connection.setAutoCommit(JDBC3Connection.java:152) ~[sqlite-jdbc-3.8.7.jar:na]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.7.0_71]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) ~[na:1.7.0_71]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.7.0_71]
    at java.lang.reflect.Method.invoke(Method.java:606) ~[na:1.7.0_71]
    at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126) ~[tomcat-jdbc-7.0.56.jar:na]
    at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:109) ~[tomcat-jdbc-7.0.56.jar:na]
    at org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:80) ~[tomcat-jdbc-7.0.56.jar:na]
    at com.sun.proxy.$Proxy76.setAutoCommit(Unknown Source) ~[na:na]
    at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:96) [flyway-core-3.2.1.jar:na]
    at org.flywaydb.core.internal.command.DbMigrate.applyMigration(DbMigrate.java:282) [flyway-core-3.2.1.jar:na]
    at org.flywaydb.core.internal.command.DbMigrate.access$800(DbMigrate.java:46) [flyway-core-3.2.1.jar:na]
    at org.flywaydb.core.internal.command.DbMigrate$2.doInTransaction(DbMigrate.java:207) [flyway-core-3.2.1.jar:na]
    at org.flywaydb.core.internal.command.DbMigrate$2.doInTransaction(DbMigrate.java:156) [flyway-core-3.2.1.jar:na]
    at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:72) [flyway-core-3.2.1.jar:na]
    at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:156) [flyway-core-3.2.1.jar:na]
    at org.flywaydb.core.Flyway$1.execute(Flyway.java:1059) [flyway-core-3.2.1.jar:na]
    at org.flywaydb.core.Flyway$1.execute(Flyway.java:1006) [flyway-core-3.2.1.jar:na]
    at org.flywaydb.core.Flyway.execute(Flyway.java:1418) [flyway-core-3.2.1.jar:na]
    at org.flywaydb.core.Flyway.migrate(Flyway.java:1006) [flyway-core-3.2.1.jar:na]
    ...

That seems to be a general SQLite pitfall, as described here.

Generally, both the Flyway migration as well as our remaining application suffer from frequent locking errors of that type:

java.sql.SQLException: [SQLITE_BUSY] The database file is locked

when two or more connections are active. Thats why we tried ...

2. ...Using a Connection Pool of size 1

We reduced the number of parallel database connections to one (by configuring our Tomcat JDBC connection pool with "maxActive=1").

But now, Flyway fails with

 ... org.flywaydb.core.api.FlywayException: Unable to obtain Jdbc connection from DataSource
    at org.flywaydb.core.internal.util.jdbc.JdbcUtils.openConnection(JdbcUtils.java:56)
    at org.flywaydb.core.Flyway.execute(Flyway.java:1386)
    at org.flywaydb.core.Flyway.migrate(Flyway.java:1006)
    (...)
Caused by: org.apache.tomcat.jdbc.pool.PoolExhaustedException: [localhost-startStop-1] Timeout: Pool empty. Unable to fetch a connection in 10 seconds, none available[size:1; busy:1; idle:0; lastwait:10000].
    at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:674)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.getConnection(ConnectionPool.java:188)
    at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:128)
    at org.flywaydb.core.internal.util.jdbc.JdbcUtils.openConnection(JdbcUtils.java:50)

Why that ? The Flyway migration seems to require two connections in parallel. Here they had a related problem and stated: "It seems that Flyway uses at least two connections: one to lock the schema_version table, and one to actually run the alters..."

Finally, I'm wondering how Flyway can support SQLite at all. Two connections are mandatory, but the database ends up locked.

However, SQLite is officially supported and I think that I screwed up something.

How can Flyway play well with SQLite ?

Community
  • 1
  • 1
shful
  • 394
  • 1
  • 5
  • 8
  • According to: https://github.com/flyway/flyway/blob/master/flyway-core/src/main/java/org/flywaydb/core/internal/dbsupport/sqlite/SQLiteTable.java "Unable to lock " + this + " as SQLite does not support locking. No concurrent migration supported."); – Donald_W May 21 '15 at 20:25
  • With "concurrent migration" they mean 2+ applications running their migration on the same database (see also http://stackoverflow.com/questions/27914833/flyway-concurrent-migration) but that's not our use case here. I managed to fail with a single, non-concurrent migration. – shful May 22 '15 at 07:33
  • I also noticed that the sqlite integration test in flyway uses a memfile, so its possible it simply doesn't work using an actual file. – Donald_W May 22 '15 at 07:36
  • 1
    With a memory database -which I tested for curiosity- the migration is still hanging. But this time with a table lock instead of the whole-database-lock. That test was configured with "Shared Cache" which lets SQLite connections share the same memory database. (However, in the meantime Axel Fontaine was right with the SingleConnectionDataSource solution.) – shful May 26 '15 at 15:26

2 Answers2

5

Use something like Spring's SingleConnectionDataSource and you should be good.

Update: Flyway 4.1 now automatically uses just a single connection when migrating and this should not be needed anymore.

Axel Fontaine
  • 34,542
  • 16
  • 106
  • 137
  • 1
    I have the same issue, but I don't use spring. What else can be done to run the migrations on a SQLite database? – ant1g Oct 30 '16 at 19:28
  • This single-connection approach for SQLite is now built into Flyway 4.1.0 and later according to [this release note](https://flywaydb.org/blog/flyway-4.1.0): *SQLite support is now also based on the new single connection mode, avoiding all kinds of potential locking issues* – Basil Bourque Apr 18 '17 at 06:06
4

Flyway unfortunately uses two connections to the database to do it's thing (one for metadata table, one for user objects). I'm not sure if this is a requirement or not, but regardless since it requires two connections it can't be used by a standard connection pool of size 1. Moreover, even if you have 2 connections in your pool you have a potential for deadlock if multiple threads ever fire up and use Flyway. Each thread might get the first connection it requires and block indefinitely waiting for the second. I actually just hit this in our product where we talk to Flyway quite often to check the schema versions of various tenants. This problem applies to any use of a pool and is not specific to SQLite.

kylejmcintyre
  • 1,898
  • 2
  • 17
  • 19
  • After posting this, I dug through their source code a bit more and discovered that they addressed this in version 4.1. So if you're having issues like this, be sure you're on 4.1+ – kylejmcintyre Apr 18 '17 at 03:26
  • **Update:** As of Flyway 4.1.0 and later according to [this release note](https://flywaydb.org/blog/flyway-4.1.0): *SQLite support is now also based on the new single connection mode, avoiding all kinds of potential locking issues* – Basil Bourque Apr 18 '17 at 06:04