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

NULL not allowed for column "installed_by" with H2 in Oracle mode #1794

Closed
garretwilson opened this issue Oct 12, 2017 · 6 comments

Comments

@garretwilson
Copy link

commented Oct 12, 2017

What version of Flyway are you using?

4.2.0

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

Java API

What database are you using (type & version)?

H2 1.4.196; MODE=Oracle

What operating system are you using?

Windows 10 Pro 64-bit

What did you do?

(Please include the content causing the issue, any relevant configuration settings, and the command you ran)

final Flyway flyway = new Flyway();
flyway.setDataSource("jdbc:h2:mem:test;MODE=Oracle;DB_CLOSE_DELAY=-1", "", "");
flyway.migrate();

This is about the most basic, fundamental thing I can do with Flyway using H2 in Oracle mode. And I want to use Oracle mode, because Oracle is used as the production database (which one day hopefully will also use Flyway).

What did you expect to see?

I expected it to initialize the metadata table.

What did you see instead?
org.flywaydb.core.internal.dbsupport.FlywaySqlException: 
Unable to insert row for version '1' in metadata table "PUBLIC"."schema_version"
------------------------------------------------------------------------------------
SQL State  : 23502
Error Code : 23502
Message    : NULL not allowed for column "installed_by"; SQL statement:
INSERT INTO "PUBLIC"."schema_version" ("installed_rank","version","description","type","script","checksum","installed_by","execution_time","success") VALUES (?, ?, ?, ?, ?, ?, USER(), ?, ?) [23502-196]

	at org.flywaydb.core.internal.metadatatable.MetaDataTableImpl.addAppliedMigration(MetaDataTableImpl.java:242)
	at org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:360)
	at org.flywaydb.core.internal.command.DbMigrate.access$900(DbMigrate.java:53)
	at org.flywaydb.core.internal.command.DbMigrate$4.call(DbMigrate.java:276)
	at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:75)
	at org.flywaydb.core.internal.command.DbMigrate.applyMigrations(DbMigrate.java:273)
	at org.flywaydb.core.internal.command.DbMigrate.access$800(DbMigrate.java:53)
	at org.flywaydb.core.internal.command.DbMigrate$2.call(DbMigrate.java:210)
	at org.flywaydb.core.internal.command.DbMigrate$2.call(DbMigrate.java:146)
	at org.flywaydb.core.internal.dbsupport.DbSupport$1.call(DbSupport.java:223)
	at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:75)
	at org.flywaydb.core.internal.dbsupport.DbSupport.lock(DbSupport.java:219)
	at org.flywaydb.core.internal.metadatatable.MetaDataTableImpl.lock(MetaDataTableImpl.java:174)
	at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:146)
	at org.flywaydb.core.Flyway$1.execute(Flyway.java:1010)
	at org.flywaydb.core.Flyway$1.execute(Flyway.java:971)
	at org.flywaydb.core.Flyway.execute(Flyway.java:1464)
	at org.flywaydb.core.Flyway.migrate(Flyway.java:971)

If I remove the ;MODE=Oracle part of the JDBC datasource URL above (i.e. "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1"), it works as expected. But when I switch to using a real Oracle database, I need to have Flyway work with it.

I don't know if the root problem is Flyway or H2's Oracle mode, but I suspect it is a Flyway problem, trying to do something that Oracle doesn't allow. (Guessing even more, maybe Flyway made assumptions when creating the metadata table schema, and should have been more explicit if it wanted NULL support. But that's just a wild guess.)

@garretwilson

This comment has been minimized.

Copy link
Author

commented Oct 12, 2017

Actually this is worse than I thought --- the schema creation is one of the most important things for which I need Oracle compatibility, isn't it?

@garretwilson

This comment has been minimized.

Copy link
Author

commented Oct 12, 2017

I found a workaround! I can set the "installed-by" value:

flyway.setInstalledBy("test");

I'm glad I found a workaround. But this bug should still be fixed; if the default value for "installed-by" is null, then the out-of-the-box configuration should support that.

@axelfontaine

This comment has been minimized.

Copy link
Member

commented Nov 1, 2017

This is an H2 issue where USER() somehow returns nullin Oracle mode.

@garretwilson

This comment has been minimized.

Copy link
Author

commented Jul 23, 2018

This issue may have been closely a little prematurely. It was recognized that H2 "somehow" returns NULL for USER() in Oracle mode. But at h2database/h2database#1066 it was pointed out that Oracle (as is well-known) returns NULL for the empty string in queries. Perhaps this is simply another instance of that same rule.

Here H2 allows an in-memory database to be created with "" for the username.

So would Oracle return NULL for USER() if no username were specified for the database? (Or does Oracle even allow a database to be created with no username?)

Where is it documented that USER() should never return NULL, even on Oracle?

This may be one of those cases that is a hard call, with no neat-and-tidy answer. However, based upon the details, I'm leaning towards thinking that the appropriate fix would be for Flyway not to assume that USER() never returns NULL, and if it does substitute some appropriate default user (such as the system user or even the empty string).

@jmeys

This comment has been minimized.

Copy link

commented Jan 24, 2019

I'm a bit late to the party, but I encountered the same issue and want to help others who might suffer from this problem. I managed to work around it by providing the USER in the URL.

"jdbc:h2:mem:test;MODE=Oracle;DB_CLOSE_DELAY=-1"

then becomes

"jdbc:h2:mem:test;MODE=Oracle;DB_CLOSE_DELAY=-1;USER=sa"

After this, the user was correctly set to sa in the schema_version table.

@juliahayward

This comment has been minimized.

Copy link
Member

commented Sep 4, 2019

The workround should be unnecessary in 6.0.2 - a dummy username will be used if USER() returns null and no user is specified in the Flyway config.

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