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

Flyway baselines new schemas, since they always have a schema_version table. #898

Closed
benheilers opened this issue Dec 2, 2014 · 6 comments
Closed
Labels
Milestone

Comments

@benheilers
Copy link

@benheilers benheilers commented Dec 2, 2014

I have a tenant-per-schema system, where each schema has it's own schema_version table.

So I already have some existing schemas, but new schemas will be added as well. My integration tests to create new schemas are failing. I see it is because of this code in Flyway.java starting at line 929:

            new DbSchemas(connectionMetaDataTable, schemas, metaDataTable).create();

            if (!metaDataTable.hasBaselineMarker() && !metaDataTable.hasAppliedMigrations()) {
                List<Schema> nonEmptySchemas = new ArrayList<Schema>();
                for (Schema schema : schemas) {
                    if (!schema.empty()) {
                        nonEmptySchemas.add(schema);
                    }
                }

Line 929, the call to create(), ends up creating the table SCHEMA_VERSION with the schema_marker.
Specifically, the call MetaDataTableImpl.addSchemasMarker() creates this table just after the schema is created.

Line 934, the call to empty(), returns false, because the schema does have this one single table SCHEMA_VERSION.

So now we think this brand new schema is non-empty, and we end up baselining the schema. Then the brand new migration script fails because it is trying to add a column to a table which should have been created in the baselined scripts.

Is this a bug, or am I mis-using baseline somehow? I want my existing schemas to be baselined up to a certain point without my future schemas failing to create.

@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Dec 2, 2014

Could you contribute a small test case that reproduces the bug?

@benheilers
Copy link
Author

@benheilers benheilers commented Dec 3, 2014

Okay, I created a really small test here:

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

import java.io.*;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class BaselineTest {

private static final String url = "fill this in"; //TODO
private static final String user = "fill this in"; //TODO
private static final String pass = "fill this in"; //TODO

private static final String SQL1 = "CREATE TABLE test_table ( id int8 not null, primary key (id) ); \n";
private static final String SQL2 = "ALTER TABLE test_table ADD date_created timestamp; \n";

@BeforeClass
public static void createScripts() throws IOException {
    new File("db/migration").mkdirs();
    createScript("V1__Baseline", SQL1);
    createScript("V2__AddColumn", SQL2);
}

private static void createScript(String fileName, String contents) throws IOException {
    File f = new File("db/migration/" + fileName + ".sql");
    f.createNewFile();

    FileWriter writer = new FileWriter(f);
    writer.write(contents);
    writer.close();
}

@Test
public void testNewSchema() throws SQLException {

    final Statement statement = newStatement();
    statement.execute("DROP SCHEMA IF EXISTS new_schema CASCADE;");
    statement.close();

    migrate("new_schema");
}

@Test
public void testExistingSchema() throws SQLException {

    final Statement statement = newStatement();
    statement.execute("DROP SCHEMA IF EXISTS existing_schema CASCADE;");
    statement.execute("CREATE SCHEMA existing_schema;");
    statement.execute("SET SEARCH_PATH TO existing_schema;");
    statement.execute(SQL1);
    statement.close();

    migrate("existing_schema");
}

private static Statement newStatement() throws SQLException {
    return DriverManager.getConnection(url, user, pass).createStatement();
}

private static void migrate(String schema) {
    Flyway flyway = new Flyway();
    flyway.setLocations("filesystem:db/migration");
    flyway.setDataSource(url, user, pass);
    flyway.setSchemas(schema);

    flyway.setBaselineVersion("1");
    flyway.setBaselineOnMigrate(true);

    flyway.migrate();
}

}

The testExistingSchema() passes and testNewSchema() fails. It fails with:

org.flywaydb.core.internal.dbsupport.FlywaySqlScriptException:

Migration V2__AddColumn.sql failed

SQL State : 42P01
Error Code : 0
Message : ERROR: relation "test_table" does not exist
Location : db/migration/V2__AddColumn.sql (/home/heilers/perforce/ben-linux/main/twopi/overlord/event-channel/realtime-processing/db/migration/V2__AddColumn.sql)
Line : 1
Statement : ALTER TABLE test_table ADD date_created timestamp

at org.flywaydb.core.internal.dbsupport.SqlScript.execute(SqlScript.java:117)
at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.execute(SqlMigrationExecutor.java:71)
at org.flywaydb.core.internal.command.DbMigrate$5.doInTransaction(DbMigrate.java:287)
at org.flywaydb.core.internal.command.DbMigrate$5.doInTransaction(DbMigrate.java:285)
at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:72)
at org.flywaydb.core.internal.command.DbMigrate.applyMigration(DbMigrate.java:285)
at org.flywaydb.core.internal.command.DbMigrate.access$800(DbMigrate.java:46)
at org.flywaydb.core.internal.command.DbMigrate$2.doInTransaction(DbMigrate.java:207)
at org.flywaydb.core.internal.command.DbMigrate$2.doInTransaction(DbMigrate.java:156)
at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:72)
at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:156)
at org.flywaydb.core.Flyway$1.execute(Flyway.java:972)
at org.flywaydb.core.Flyway$1.execute(Flyway.java:919)
at org.flywaydb.core.Flyway.execute(Flyway.java:1320)
at org.flywaydb.core.Flyway.migrate(Flyway.java:919)
at BaselineTest.migrate(BaselineTest.java:71)
at BaselineTest.testNewSchema(BaselineTest.java:42)
@axelfontaine axelfontaine added this to the Flyway 3.2 milestone Dec 3, 2014
@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Dec 3, 2014

Thanks! I'll have a look at this in time for 3.2

@benheilers
Copy link
Author

@benheilers benheilers commented Dec 3, 2014

Thanks for confirming this is an actual bug and not just an issue with my understanding. For now I'll just make two separate codepaths for updating existing tenants and provisioning new tenants, so that only the old tenants are baselined.

@benheilers benheilers closed this Dec 3, 2014
@benheilers
Copy link
Author

@benheilers benheilers commented Dec 3, 2014

oops, I thought "close" was for closing browser, not bug, sorry, re-opening

@benheilers benheilers reopened this Dec 3, 2014
axelfontaine added a commit to flyway/flywaydb.org that referenced this issue Mar 4, 2015
@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Mar 4, 2015

It turns out we even had a testcase for this actually validating the wrong behavior!

Thanks for reporting. Fixed.

Cheers
Axel

jmahonin added a commit to jmahonin/flyway that referenced this issue Jul 15, 2015
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
2 participants
You can’t perform that action at this time.