Metadata table creation failure with fresh db.

[dustinconrad]

I am getting an exception when I have two instances of my application startup nearly simultaneously while pointing at an empty database (Postgres).

I am pretty sure this is happening because both application instances are trying to create the Metadata table at the same time when they detect there is no metadata table. One of them fails, throws an exception, and that application instance fails to start.

The log file:

2015-07-27 17:55:09.739 INFO 8682 --- [ main] o.f.c.i.metadatatable.MetaDataTableImpl : Creating Metadata table: "schema"."schema_version"
2015-07-27 17:55:09.748 INFO 8680 --- [ main] o.f.c.i.metadatatable.MetaDataTableImpl : Creating Metadata table: "schema"."schema_version"

The SQL exception:

Script failed

SQL State : 23505
Error Code : 0
Message : ERROR: duplicate key value violates unique constraint "pg_type_typname_nsp_index"
Detail: Key (typname, typnamespace)=(schema_version, 12038) already exists.
Line : 17
Statement : CREATE TABLE "schema"."schema_version" (
"version_rank" INT NOT NULL,
"installed_rank" INT NOT NULL,
"version" VARCHAR(50) NOT NULL,
"description" VARCHAR(200) NOT NULL,
"type" VARCHAR(20) NOT NULL,
"script" VARCHAR(1000) NOT NULL,
"checksum" INTEGER,
"installed_by" VARCHAR(100) NOT NULL,
"installed_on" TIMESTAMP NOT NULL DEFAULT now(),
"execution_time" INTEGER NOT NULL,
"success" BOOLEAN NOT NULL
) WITH (
OIDS=FALSE
)

the stacktrace:

at org.flywaydb.core.internal.dbsupport.SqlScript.execute(SqlScript.java:117)
at org.flywaydb.core.internal.metadatatable.MetaDataTableImpl.createIfNotExists(MetaDataTableImpl.java:93)
at org.flywaydb.core.internal.metadatatable.MetaDataTableImpl.lock(MetaDataTableImpl.java:100)
at org.flywaydb.core.internal.command.DbMigrate$2.doInTransaction(DbMigrate.java:158)
at org.flywaydb.core.internal.command.DbMigrate$2.doInTransaction(DbMigrate.java:156)
at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:72)
at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:156)
at org.flywaydb.core.Flyway$1.execute(Flyway.java:972)
at org.flywaydb.core.Flyway$1.execute(Flyway.java:919)
at org.flywaydb.core.Flyway.execute(Flyway.java:1320)
at org.flywaydb.core.Flyway.migrate(Flyway.java:919)

[electrum]

I believe the locking is ineffective if the migration has multiple statements on databases that do not have transactional DDL, such as MySQL, or if the migration is run without transactions. The locking does a SELECT ... FOR UPDATE, which will be released when a commit occurs (as the MetaDataTable.lock() documentation states). The locking only happens once per migration, so the lock will be released after the migration statement.

MySQL could solve both metadata creation and migration locking using get_lock().

PostgreSQL could solve metadata with CREATE TABLE IF NOT EXISTS and LOCK TABLE. Migrations run without transactions could be handled using a separate transaction for the lock, although this secondary connection could be terminated while the main one proceeds. At minimum, it might need a separate thread that periodically runs dummy queries to keep the connection alive.

[axelfontaine]

This now works perfectly, with the exception of H2 where some minor edge cases remain.

[renat-sabitov]

this change introduced a reporting problem: #1636

[roberthunt]

This change introduces a compatibility issue with Percona XtraDB Cluster when the server is configured with pxc_strict_mode = ENFORCING (default, recommended). See: #1556