MySQL: Exception restoring original database on a connection to a dropped database

[edquan]

Which version and edition of Flyway are you using?

5.2.0
Originally tried with 5.0.7

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

N/A

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

Java API

Which database are you using (type & version)?

MySQL 5.6.37-82.2 Percona Server

Which operating system are you using?

Ubuntu 14.04.5 LTS

What did you do?

I'm using HikariCP (3.2.0, originally used 2.7.9).

A connection is obtained from the data source. The connection is used to set a catalog and later used to drop the database before returning the connection to the pool.

Later, Flyway is given the same data source to perform a migration on a different schema. Flyway obtains the same connection that was returned to the pool above to perform the migration. As a final step Flyway attempts to restore the schema to the original value (ie. one which has been dropped) and an exception occurs indicating: "Unable to restore original schema".

Here is a sample application which can reproduce the issue:

import java.sql.Connection;
import java.sql.Statement;

import org.flywaydb.core.Flyway;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

public class FlywayCpTest
{
    public static void main(String[] args) throws Exception
    {
        try
        {
            HikariConfig config = new HikariConfig();
            config.setJdbcUrl("jdbc:mysql://localhost");
            config.setUsername("root");
            config.setPassword("");
            config.setMaximumPoolSize(1);
            config.setPoolName("localhost");

            HikariDataSource dataSource = new HikariDataSource(config);

            try (Connection connection = dataSource.getConnection())
            {
                try (Statement statement = connection.createStatement())
                {
                    statement.executeUpdate("CREATE DATABASE IF NOT EXISTS cpdb");
                }

                connection.setCatalog("cpdb");

                try (Statement statement = connection.createStatement())
                {
                    statement.executeUpdate("DROP DATABASE cpdb");
                }
            }

            //
            // File: flyway_cp_test/V00001_Initial_version.sql
            //
            // CREATE TABLE `flyway_cp_table` (
            // `test_col` BOOLEAN
            // );
            //
            Flyway flyway = Flyway.configure()
                    .dataSource(dataSource)
                    .locations("flyway_cp_test")
                    .schemas("flywaydb").load();
            flyway.migrate();
        }
        catch (Exception e)
        {
            e.printStackTrace();

            throw e;
        }
    }
}

What did you expect to see?

I would not expect Flyway to try to restore the schema, or at least gracefully handle the scenario where the original schema does not exist.

What did you see instead?

The following stack trace was produced:

/*
org.flywaydb.core.internal.exception.FlywaySqlException:
        Unable to restore original schema
        ---------------------------------
        SQL State  : 42000
        Error Code : 1049
        Message    : Unknown database 'cpdb'

        at org.flywaydb.core.internal.database.base.Connection$2.call(Connection.java:171)
        at org.flywaydb.core.internal.database.base.Connection$2.call(Connection.java:165)
        at org.flywaydb.core.internal.jdbc.TransactionTemplate.execute(TransactionTemplate.java:74)
        at org.flywaydb.core.internal.database.base.Connection.restoreOriginalSchema(Connection.java:165)
        at org.flywaydb.core.internal.database.base.Connection.close(Connection.java:159)
        at org.flywaydb.core.internal.database.base.Database.close(Database.java:473)
        at org.flywaydb.core.Flyway.execute(Flyway.java:1624)
        at org.flywaydb.core.Flyway.migrate(Flyway.java:1249)
        at FlywayCpTest.main(FlywayCpTest.java:50)
        Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown database 'cpdb'
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
        at com.mysql.jdbc.Util.getInstance(Util.java:387)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:942)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3966)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3902)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2526)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2673)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2545)
        at com.mysql.jdbc.ConnectionImpl.setCatalog(ConnectionImpl.java:4924)
        at com.zaxxer.hikari.pool.ProxyConnection.setCatalog(ProxyConnection.java:416)
        at com.zaxxer.hikari.pool.HikariProxyConnection.setCatalog(HikariProxyConnection.java)
        at org.flywaydb.core.internal.database.mysql.MySQLConnection.doChangeCurrentSchemaOrSearchPathTo(MySQLConnection.java:57)
        at org.flywaydb.core.internal.database.base.Connection$2.call(Connection.java:169)
        ... 8 more
        Exception in thread "main" org.flywaydb.core.internal.exception.FlywaySqlException:
        Unable to restore original schema
        ---------------------------------
        SQL State  : 42000
        Error Code : 1049
        Message    : Unknown database 'cpdb'

        at org.flywaydb.core.internal.database.base.Connection$2.call(Connection.java:171)
        at org.flywaydb.core.internal.database.base.Connection$2.call(Connection.java:165)
        at org.flywaydb.core.internal.jdbc.TransactionTemplate.execute(TransactionTemplate.java:74)
        at org.flywaydb.core.internal.database.base.Connection.restoreOriginalSchema(Connection.java:165)
        at org.flywaydb.core.internal.database.base.Connection.close(Connection.java:159)
        at org.flywaydb.core.internal.database.base.Database.close(Database.java:473)
        at org.flywaydb.core.Flyway.execute(Flyway.java:1624)
        at org.flywaydb.core.Flyway.migrate(Flyway.java:1249)
        at FlywayCpTest.main(FlywayCpTest.java:50)
        Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown database 'cpdb'
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
        at com.mysql.jdbc.Util.getInstance(Util.java:387)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:942)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3966)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3902)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2526)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2673)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2545)
        at com.mysql.jdbc.ConnectionImpl.setCatalog(ConnectionImpl.java:4924)
        at com.zaxxer.hikari.pool.ProxyConnection.setCatalog(ProxyConnection.java:416)
        at com.zaxxer.hikari.pool.HikariProxyConnection.setCatalog(HikariProxyConnection.java)
        at org.flywaydb.core.internal.database.mysql.MySQLConnection.doChangeCurrentSchemaOrSearchPathTo(MySQLConnection.java:57)
        at org.flywaydb.core.internal.database.base.Connection$2.call(Connection.java:169)
        ... 8 more

        Process finished with exit code 1
*/

[axelfontaine]

Thank you for isolating this. It was due to stale data inside the JDBC driver. We have now switched to querying the database server directly instead to determine the current database, which fixed the issue.

[edquan]

Thank you for the quick turnaround on this issue.