Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Migration hanging with HsqlDB 2.3.4 #1346

Closed
bkrahmer opened this issue Jun 20, 2016 · 14 comments
Closed

Migration hanging with HsqlDB 2.3.4 #1346

bkrahmer opened this issue Jun 20, 2016 · 14 comments
Milestone

Comments

@bkrahmer
Copy link

@bkrahmer bkrahmer commented Jun 20, 2016

What version of Flyway are you using?

4.0.2

What database are you using (type & version)?

Hsqldb 2.3.4

What operating system are you using?

Ubuntu linux 14.04

What did you do?

(Please include the content causing the issue, any relevant configuration settings, and the command you ran)
I've been working with flyway for quite some time, actually. I have a project that I had to pick up from git and re-init my database. I was using flyway 4.0 with hsqldb driver 2.3.3 for the last couple months. I start up my server, start my process which normally migrates, it properly parses through my migrations, creates the schema_version table, and then after issuing the create table for my first schema table, it just hangs forever. I can run all of the migration statements in a clean database with the swing manager no problem. Just looking for input. I'm pulling my hair out on this issue.

What did you expect to see?

Normal migration output

What did you see instead?

Hanging creating tables

thanks,
brian

@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Jun 20, 2016

Without additional info and exact steps to reproduce this I am afraid there is not much we can do.

@bkrahmer
Copy link
Author

@bkrahmer bkrahmer commented Jun 21, 2016

Ok, i will isolate a simple repro tonight...

@bkrahmer
Copy link
Author

@bkrahmer bkrahmer commented Jun 21, 2016

I whittled this project practically down to absolutely nothing and it's still not working. I still don't see what could be the problem. I'm now testing with hsqldb 2.3.4 and flyway 4.0.3. I run my server like this: java -cp ~/tools/hsqldb-2.3.4/hsqldb/lib/hsqldb.jar org.hsqldb.server.Server --database.0 vulndb --dbname.0 vulndb

Any ideas would be greatly appreciated.
vulnassist.tar.gz
thanks,
brian

@chrisdadej
Copy link

@chrisdadej chrisdadej commented Jul 12, 2016

I've got exactly the same issue on my end - running gradle flywayMigrate:

11:56:26.516 [INFO] [org.flywaydb.core.internal.command.DbValidate] Successfully validated 7 migrations (execution time 00:00.026s)
11:56:26.526 [DEBUG] [org.flywaydb.core.internal.command.DbSchemas] Schema "PUBLIC" already exists. Skipping schema creation.
11:56:26.535 [DEBUG] [org.flywaydb.core.internal.dbsupport.Table] Locking table "PUBLIC"."schema_version_soa"...
11:56:26.536 [DEBUG] [org.flywaydb.core.internal.dbsupport.Table] Lock acquired for table "PUBLIC"."schema_version_soa"
11:56:26.538 [INFO] [org.flywaydb.core.internal.command.DbMigrate] Current version of schema "PUBLIC": 1
11:56:26.538 [INFO] [org.flywaydb.core.internal.command.DbMigrate] Migrating schema "PUBLIC" to version 1.1 - create soa table
11:56:26.542 [DEBUG] [org.flywaydb.core.internal.dbsupport.SqlScript] Found statement at line 1: CREATE TABLE soa
(
  request_id BINARY(16) NOT NULL PRIMARY KEY
, created TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL
, created_by VARCHAR(30) DEFAULT USER NOT NULL
, updated TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL
, updated_by VARCHAR(30) DEFAULT USER NOT NULL
, error CLOB NULL
)
11:56:26.542 [DEBUG] [org.flywaydb.core.internal.dbsupport.SqlScript] Executing SQL: CREATE TABLE soa_request
(
  request_id BINARY(16) NOT NULL PRIMARY KEY
, created TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL
, created_by VARCHAR(30) DEFAULT USER NOT NULL
, updated TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL
, updated_by VARCHAR(30) DEFAULT USER NOT NULL
, error CLOB NULL
)
> Building 0% > :shared-db:flywayMigrate

And it'll stay there forever...

It looks like other people are starting to report it as well, although the solution re MVCC locking suggested doesn't actually work:
http://stackoverflow.com/questions/38279412/flyway-hsql-db-create-tables-sql-migrate-hangs

@wilkinsona
Copy link
Contributor

@wilkinsona wilkinsona commented Jul 15, 2016

@axelfontaine This is affecting some Spring Boot users now (see spring-projects/spring-boot#6394). We'll probably downgrade HSQLDB for the time being, but I'd be interested to know if you think this something that Flyway could/should be addressing. To that end, here's a minimal sample that will reproduce the problem: https://github.com/wilkinsona/flyway-hsqldb-hang. I hope this helps.

@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Jul 20, 2016

@wilkinsona I am afraid there is not much we can do on our end. Flyway's locking is actually very simple.

Flyway uses 2 connections: 1 for the metadata table and 1 for the migrations. This is necessary to support multiple nodes attempting to migrate in parallel.

With the first connection Flyway starts by doing a select for update on the metadata table.
The second connection that runs a migration within a transaction.
Finally the first connection adds a row to the metadata table containing the results and commits, which releases the lock.
This process then starts again for every additional migration that must be run.

This solution is simple and has proven to be very stable across almost 20 RDBMS for years. Too bad HSQLDB now introduced a bug that breaks this trivial locking :-/

@wilkinsona
Copy link
Contributor

@wilkinsona wilkinsona commented Jul 20, 2016

@axelfontaine Thanks for taking a look. Hopefully HSQLDB will be fixed before too long: https://sourceforge.net/p/hsqldb/bugs/1441/

@lagnat
Copy link

@lagnat lagnat commented Oct 21, 2016

With Fred Toussi insisting that this is not an HSQLDB bug and that it's "documented and intended lock behaviour", we're all kinda stuck. Is there any consideration being given to coding around this issue? Quite frankly, I'd be happy with an option to remove the locking completely because I can guarantee that migrations will run single threaded.

@axelfontaine axelfontaine changed the title Migration hanging Migration hanging with HsqlDB 2.3.4 Nov 3, 2016
@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Nov 3, 2016

@lagnat The workaround for now is to stick with HsqlDB 2.3.3 which doesn't exhibit the problematic behavior.

axelfontaine added a commit to flyway/flywaydb.org that referenced this issue Jan 30, 2017
@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Jan 30, 2017

This has now been fixed. We moved HsqlDB to the newly introduced single-connection mode. The practical consequence is that this removes the ability to migrate the DB from multiple nodes concurrently. However this is not a common usage scenario at all for HsqlDB. And judging from the explanations of Fred Toussi, it may not even have worked properly before anyway.

@axelfontaine axelfontaine added this to the Flyway 4.1 milestone Jan 30, 2017
@lagnat
Copy link

@lagnat lagnat commented Feb 10, 2017

FYI https://sourceforge.net/p/hsqldb/bugs/1441/

"The lock behaviour has been enhanced for the next versions 2.3.5 / 2.4.0 and committed. It will allow the Flyway migration to work, as well as any other software that starts a transaction with one connection and creates tables with another one while the first connection is not committed."

@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Feb 11, 2017

@lagnat Yes, Fred Toussi and I discussed this issue privately by email and we both implemented solutions so that things work as expected for you guys.

@lagnat
Copy link

@lagnat lagnat commented Feb 11, 2017

So for the sake of science, is your change needed at this point if someone is using the latest HSQLDB?

@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Feb 11, 2017

Probably not (I have not tested it though), but it simplifies things and only uses a single connection for HsqlDB.

pradheeps pushed a commit to pradheeps/flyway that referenced this issue Mar 7, 2017
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
5 participants