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

Schema version table creation fails with H2 in Oracle mode #2436

Closed
lion7 opened this issue Jul 10, 2019 · 2 comments

Comments

@lion7
Copy link

commented Jul 10, 2019

Which version and edition of Flyway are you using?

6.0.0-beta2

If this is not the latest version, can you reproduce the issue with the latest one as well?

(Many bugs are fixed in newer releases and upgrading will often resolve the issue)

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

Java API

Which database are you using (type & version)?

H2 1.4.197 in Oracle mode

Which operating system are you using?

Arch Linux

What did you do?

(Please include the content causing the issue, any relevant configuration settings, the SQL statement that failed (if relevant) and the command you ran.)

I am using flyway with an H2 database in Oracle mode. My JDBC url is jdbc:h2:mem:default;DB_CLOSE_DELAY=-1;MODE=Oracle.

Creating the schema version table fails with the following error:

Migration  failed
-----------------
SQL State  : 23502
Error Code : 23502
Message    : NULL not allowed for column "script"; SQL statement:
CREATE TABLE IF NOT EXISTS "PUBLIC"."KDO_SCHEMA_VERSION" (
    "installed_rank" INT NOT NULL,
    "version" VARCHAR(50),
    "description" VARCHAR(200) NOT NULL,
    "type" VARCHAR(20) NOT NULL,
    "script" VARCHAR(1000) NOT NULL,
    "checksum" INT,
    "installed_by" VARCHAR(100) NOT NULL,
    "installed_on" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "execution_time" INT NOT NULL,
    "success" BOOLEAN NOT NULL,
    CONSTRAINT "KDO_SCHEMA_VERSION_pk" PRIMARY KEY ("installed_rank")
) AS SELECT -1, NULL, '<< Flyway Schema History table created >>', 'TABLE', '', NULL, '', CURRENT_TIMESTAMP, 0, TRUE [23502-197]

In the case of an H2 database a special table created marker is inserted because H2 cannot lock empty tables. In the insert statement (well, actually in the AS SELECT ... part) an empty string is inserted into the script column. The same applies to the installed_by by the way. Oracle's behavior is to replace empty strings with NULL and this is what H2 also does in Oracle mode, meaning that a NULL is inserted into the script column which is not allowed to be NULL.

Running flyway against a real Oracle database does work because this script won't be used there. The above script is generated in org.flywaydb.core.internal.database.h2.H2Database#getRawCreateScript and with a real Oracle database the script generated in org.flywaydb.core.internal.database.oracle.OracleDatabase#getRawCreateScript would be used instead.

What did you expect to see?

That my migrations are executed.

What did you see instead?

The above error during initialization of flyway.

@lion7

This comment has been minimized.

Copy link
Author

commented Jul 10, 2019

A somewhat similar issue was raised for the installed_by column, for which a workaround exists: #1794
Unfortunately I haven't figured out a workaround for this issue yet.

@juliahayward juliahayward added this to the Flyway 6.0.2 milestone Sep 4, 2019

@juliahayward

This comment has been minimized.

Copy link
Member

commented Sep 4, 2019

Thanks for the report! 6.0.2 will substitute a dummy script placeholder in the case where an empty string would not work.

juliahayward added a commit to flyway/flywaydb.org that referenced this issue Sep 4, 2019
alextercete added a commit to flyway/flywaydb.org that referenced this issue Sep 9, 2019
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
2 participants
You can’t perform that action at this time.