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

Metadata table creation error is not reported #1636

Closed
renat-sabitov opened this issue May 15, 2017 · 4 comments
Closed

Metadata table creation error is not reported #1636

renat-sabitov opened this issue May 15, 2017 · 4 comments

Comments

@renat-sabitov
Copy link
Contributor

@renat-sabitov renat-sabitov commented May 15, 2017

What version of Flyway are you using?

4.2.1

Which client are you using? (Command-line, Java API, Maven plugin, Gradle plugin, SBT plugin, ANT tasks)

command-line

What database are you using (type & version)?

postgres 9.6

What operating system are you using?

Mac OsX

What did you do?
[rsabitov@rsabitov ~/tmp]$ cat 1.sh
flyway -url=jdbc:postgresql://localhost:5432/test -baselineOnMigrate=true -user=postgres -password=postgres  -locations=filesystem:./migrations migrate &
flyway -url=jdbc:postgresql://localhost:5432/test -baselineOnMigrate=true -user=postgres -password=postgres  -locations=filesystem:./migrations migrate &
wait
[rsabitov@rsabitov ~/tmp]$ cat migrations/V002__sleep.sql 
SELECT pg_sleep(10);
What did you expect to see?

Error reporting why metadata table wasn't created by one of the concurrent flyways

What did you see instead?
[rsabitov@rsabitov ~/tmp]$ bash 1.sh
Flyway 4.1.2 by Boxfuse

Flyway 4.1.2 by Boxfuse

Database: jdbc:postgresql://localhost:5432/test (PostgreSQL 9.6)
Database: jdbc:postgresql://localhost:5432/test (PostgreSQL 9.6)
Successfully validated 1 migration (execution time 00:00.007s)
Successfully validated 1 migration (execution time 00:00.007s)
Creating Metadata table: "public"."schema_version"
Creating Metadata table: "public"."schema_version"
Successfully baselined schema with version: 1
Current version of schema "public": 1
Migrating schema "public" to version 002 - sleep
ERROR: 
Unable to check whether table "public"."schema_version" exists
--------------------------------------------------------------
SQL State  : 25P02
Error Code : 0
Message    : ERROR: current transaction is aborted, commands ignored until end of transaction block

Successfully applied 1 migration to schema "public" (execution time 00:10.091s).

The error produced by this code in flyway:

    protected boolean doExists() throws SQLException {
        return jdbcTemplate.queryForBoolean("SELECT EXISTS (\n" +
                "   SELECT 1\n" +
                "   FROM   information_schema.tables \n" +
                "   WHERE  table_schema = ?\n" +
                "   AND    table_name = ?\n" +
                ")", schema.getName(), name);
    }

The reason why the error thrown is not related to table creation is in org.flywaydb.core.internal.metadatatable.MetaDataTableImpl#createIfNotExists. There's a cycle where flyway checks that table doesn't exist, then tries to create it but any errors got swallowed first 9 retries. However, as the transaction is tainted on the first error, the second check fails with the error mentioned before.

@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented May 15, 2017

Thanks for investigating!

@zetxek
Copy link

@zetxek zetxek commented Sep 14, 2017

I've found this error today too working with RedShift, and Flyway 4.2.0 (Which makes sense, as RedShift is Postgresql 8-compatible).

@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Nov 15, 2017

Could you share a set of minimal migration files that reproduce this issue?

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

@axelfontaine axelfontaine commented Nov 30, 2017

We have now made the schema_version table creation more explicit and move it before the transaction creation for PostgreSQL and Redshift. This should eliminate the tainted transaction issue altogether.

dohrayme pushed a commit to dohrayme/flyway that referenced this issue Feb 3, 2020
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
3 participants
You can’t perform that action at this time.