Validation fails on migrate when schema doesn't exist

[azalesky]

What version of Flyway are you using?

4.0.1

What database are you using (type & version)?

Derby 10.10.2.0

What operating system are you using?

CentOS 7

What did you do?

Run flyway migrate on a new database which doesn't have initial schema.

Flyway flyway = new Flyway();
...
flyway.setValidateOnMigrate(true);
flyway.migrate();

What did you expect to see?

I expected that new schema will be created and then all other migration tasks will be executed.
And it works so in v3.2.1

What did you see instead?

Exception happens on validation step and as a result migration doesn't continue:
Caused by: org.flywaydb.core.api.FlywayException: Error restoring current schema to its original setting
at org.flywaydb.core.internal.dbsupport.DbSupport.restoreCurrentSchema(DbSupport.java:132)
at org.flywaydb.core.internal.command.DbValidate.validate(DbValidate.java:191)
at org.flywaydb.core.Flyway.doValidate(Flyway.java:994)
at org.flywaydb.core.Flyway.access$100(Flyway.java:72)
at org.flywaydb.core.Flyway$1.execute(Flyway.java:921)
at org.flywaydb.core.Flyway$1.execute(Flyway.java:917)
at org.flywaydb.core.Flyway.execute(Flyway.java:1365)
at org.flywaydb.core.Flyway.migrate(Flyway.java:917)

Workaround is to switch off validateOnMigrate but it is not convenient for general solution.

[axelfontaine]

This works for me. Please reopen with an exact series of steps to reproduce this or a pull request (including a test) if this happens again.

[jochenw]

I can (must) confirm the issue. It arises with an completely empty database, due to some chicken/egg problem:

a) Completely empty means, in particular, that my schema (SA) does not exist.
b) Now, when I configure Flyway to use schema SA by invoking Flyway.setSchemas(SA), it will
in the end attempt to find the schema by executing

  SET SCHEMA SA

in DerbyDbSupport.doChangeCurrentSchemaTo. That fails, of course, because the schema does not
exist, and Flyway throws an exception.
c) Everything would be fine, if Flyway would go on, and create the Metadata table, because at that point, the schema would start to exist.

I am unsure, how to handle this situation. Nevertheless, it exists: (See the following stacktrace.)

org.flywaydb.core.api.FlywayException: Error restoring current schema to its original setting
    at org.flywaydb.core.internal.dbsupport.DbSupport.restoreCurrentSchema(DbSupport.java:132)
    at org.flywaydb.core.internal.command.DbValidate.validate(DbValidate.java:191)
    at org.flywaydb.core.Flyway.doValidate(Flyway.java:1002)
    at org.flywaydb.core.Flyway.access$100(Flyway.java:72)
    at org.flywaydb.core.Flyway$1.execute(Flyway.java:921)
    at org.flywaydb.core.Flyway$1.execute(Flyway.java:917)
    at org.flywaydb.core.Flyway.execute(Flyway.java:1373)
    at org.flywaydb.core.Flyway.migrate(Flyway.java:917)
    at com.dbsystel.me.woda.be.core.internal.DbInitializer.start(DbInitializer.java:47)
    at com.dbsystel.me.woda.be.core.internal.LifecycleController.start(LifecycleController.java:39)
    at com.dbsystel.me.woda.be.core.internal.GuiceComponentFactoryBuilder.newComponentFactory(GuiceComponentFactoryBuilder.java:71)
    at com.dbsystel.me.woda.be.core.internal.GuiceComponentFactoryBuilder.build(GuiceComponentFactoryBuilder.java:57)
    at com.dbsystel.me.woda.be.core.WodaBe.newComponentFactory(WodaBe.java:9)
    at com.dbsystel.me.woda.be.core.internal.InitDbTest.testInitDb(InitDbTest.java:12)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:86)
    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:678)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)
Caused by: java.sql.SQLSyntaxErrorException: Schema 'SA' does not exist
    at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedPreparedStatement.execute(Unknown Source)
    at org.flywaydb.core.internal.dbsupport.JdbcTemplate.execute(JdbcTemplate.java:219)
    at org.flywaydb.core.internal.dbsupport.derby.DerbyDbSupport.doChangeCurrentSchemaTo(DerbyDbSupport.java:55)
    at org.flywaydb.core.internal.dbsupport.DbSupport.restoreCurrentSchema(DbSupport.java:130)
    ... 36 more
Caused by: ERROR 42Y07: Schema 'SA' does not exist
    at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
    at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
    at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getSchemaDescriptor(Unknown Source)
    at org.apache.derby.impl.sql.execute.SetSchemaConstantAction.executeConstantAction(Unknown Source)
    at org.apache.derby.impl.sql.execute.MiscResultSet.open(Unknown Source)
    at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown Source)
    at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
    ... 42 more

[axelfontaine]

@jochenw Could you attach a sample test case?

[jochenw]

Proposed fix: https://github.com/jochenw/flyway/commit/63f6dbd8a9a65aeebdfc186bf2ef7c83aeefdf3c

[jochenw]

I believe, that you can repropduce this problem by changing the database user and password from "", "" to "sa", "sa" in the Derby related tests.

[jochenw]

I can reproduce the problem with this simple test case:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import org.flywaydb.core.Flyway;
import org.junit.AfterClass;
import org.junit.Test;

public class Flyway1331 {
    private static final String DB_URL_CREATE = "jdbc:derby:memory:fw1331db;create=true";
    private static final String DB_URL_SHUTDOWN = "jdbc:derby:memory:fw1331db;shutdown=true";
    private static final String DB_USER = "sa";
    private static final String DB_PWD = "sa";

    @Test
        public void testFlyway1331() throws Exception {
         Flyway flyway = new Flyway();
         flyway.setDataSource(DB_URL_CREATE, DB_USER, DB_PWD);
         flyway.setBaselineOnMigrate(true);
         flyway.migrate();
       }

       @AfterClass
        public static void shutdownDb() throws SQLException {
       Connection conn = DriverManager.getConnection(DB_URL_SHUTDOWN, DB_USER, DB_PWD);
       conn.close();
        }
}

[Shorttail]

I'm having the same issue with Derby 10.13.1.1. In Flyway 3.2.1 it works fine.