Liquibase: Does not honor schema name for H2, but MySQL is fine

Question

I am using maven cargo to deploy my Spring application.
I am trying to run this against MySQL and H2 embedded database.
The configuration for H2 looks like

@Configuration
@Profile("development")
public class H2DatabaseConfig extends JpaCommonConfig {
    @Override
    public DataSource dataSource() {
        // (todo: harit) get rid of hard coding
        System.out.println("This is H2 Database Profile");
        final BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName("org.h2.Driver");
        dataSource.setUrl("jdbc:h2:mem:test_common");
        dataSource.setUsername("sa");
        dataSource.setPassword("");
        return dataSource;
    }

    @Override
    protected Class<? extends Dialect> getDatabaseDialect() {
        return H2Dialect.class;
    }

    @Override
    protected Properties getJpaProperties() {
        final Properties properties = new Properties();
        properties.setProperty(HBM2DDL_AUTO, Hbm2ddlType.CREATE_DROP.toValue());
        properties.setProperty(SHOW_SQL, TRUE.toString());
        properties.setProperty(FORMAT_SQL, TRUE.toString());
        properties.setProperty(USE_SQL_COMMENTS, TRUE.toString());
        properties.setProperty(CONNECTION_CHAR_SET, getHibernateCharSet());
        properties.setProperty(NAMING_STRATEGY, ImprovedNamingStrategy.class.getName());

        return properties;
    }
}

and for MySQL it looks like

@Configuration
@Profile("default")
public class MySqlDatabaseConfig extends JpaCommonConfig {
    @Override
    public DataSource dataSource() {
        // (todo: harit) remove hardcoding to read from environment variables
        System.out.println("This is MySQL Database Profile");
        final BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/test_common?createDatabaseIfNotExist=true");
        dataSource.setUsername("root");
        return dataSource;
    }

    @Override
    protected Class<? extends Dialect> getDatabaseDialect() {
        return MySQL5InnoDBDialect.class;
    }

    @Override
    protected Properties getJpaProperties() {
        final Properties properties = new Properties();
        properties.setProperty(HBM2DDL_AUTO, Hbm2ddlType.UPDATE.toValue());
        properties.setProperty(SHOW_SQL, TRUE.toString());
        properties.setProperty(FORMAT_SQL, TRUE.toString());
        properties.setProperty(USE_SQL_COMMENTS, TRUE.toString());
        properties.setProperty(CONNECTION_CHAR_SET, getHibernateCharSet());
        properties.setProperty(NAMING_STRATEGY, ImprovedNamingStrategy.class.getName());
        return properties;
    }
}

When I run Integration tests using cargo, I see two differences,

When ran against MySQL the schema name is taken care when Liquibase runs, I see test_common

INFO 5/26/14 9:18 AM:liquibase: Successfully acquired change log lock
INFO 5/26/14 9:18 AM:liquibase: Dropping Database Objects in schema: test_common.test_common
INFO 5/26/14 9:18 AM:liquibase: Creating database history table with name: test_common.DATABASECHANGELOG
INFO 5/26/14 9:18 AM:liquibase: Successfully released change log lock
INFO 5/26/14 9:18 AM:liquibase: Successfully acquired change log lock
INFO 5/26/14 9:18 AM:liquibase: Reading from test_common.DATABASECHANGELOG
INFO 5/26/14 9:18 AM:liquibase: Reading from test_common.DATABASECHANGELOG
INFO 5/26/14 9:18 AM:liquibase: Reading from test_common.DATABASECHANGELOG
INFO 5/26/14 9:18 AM:liquibase: liquibase/changelog.xml: liquibase/2014/1-1.xml::05192014.1525::h2: Reading from
test_common.DATABASECHANGELOG INFO 5/26/14 9:18 AM:liquibase: liquibase/changelog.xml: liquibase/2014/1-1.xml::05192014.1525::h2: Table network created INFO 5/26/14 9:18 AM:liquibase: liquibase/changelog.xml: liquibase/2014/1-1.xml::05192014.1525::h2: ChangeSet liquibase/2014/1-1.xml::05192014.1525::h2 ran successfully in 8ms INFO 5/26/14 9:18 AM:liquibase: liquibase/changelog.xml: liquibase/2014/1-1.xml::05192014.1525::h2: Reading from test_common.DATABASECHANGELOG INFO 5/26/14 9:18 AM:liquibase: liquibase/changelog.xml: liquibase/2014/1-2.xml::05192014.1525::h2: Reading from test_common.DATABASECHANGELOG INFO 5/26/14 9:18 AM:liquibase: liquibase/changelog.xml: liquibase/2014/1-2.xml::05192014.1525::h2: New row inserted into network INFO 5/26/14 9:18 AM:liquibase: liquibase/changelog.xml: liquibase/2014/1-2.xml::05192014.1525::h2: New row inserted into network INFO 5/26/14 9:18 AM:liquibase: liquibase/changelog.xml: liquibase/2014/1-2.xml::05192014.1525::h2: New row inserted into network INFO 5/26/14 9:18 AM:liquibase: liquibase/changelog.xml: liquibase/2014/1-2.xml::05192014.1525::h2: New row inserted into network INFO 5/26/14 9:18 AM:liquibase: liquibase/changelog.xml: liquibase/2014/1-2.xml::05192014.1525::h2: New row inserted into network INFO 5/26/14 9:18 AM:liquibase: liquibase/changelog.xml: liquibase/2014/1-2.xml::05192014.1525::h2: New row inserted into network INFO 5/26/14 9:18 AM:liquibase: liquibase/changelog.xml: liquibase/2014/1-2.xml::05192014.1525::h2: ChangeSet liquibase/2014/1-2.xml::05192014.1525::h2 ran successfully in 6ms INFO 5/26/14 9:18 AM:liquibase: liquibase/changelog.xml: liquibase/2014/1-2.xml::05192014.1525::h2: Reading from test_common.DATABASECHANGELOG INFO 5/26/14 9:18 AM:liquibase: Successfully released change log lock

When ran against H2, It applies on PUBLIC schema and not on test_common

INFO 5/26/14 9:45 AM:liquibase: Successfully acquired change log lock
INFO 5/26/14 9:45 AM:liquibase: Dropping Database Objects in schema: TEST_COMMON.PUBLIC
INFO 5/26/14 9:45 AM:liquibase: Creating database history table with name: PUBLIC.DATABASECHANGELOG
INFO 5/26/14 9:45 AM:liquibase: Creating database history table with name: PUBLIC.DATABASECHANGELOG
INFO 5/26/14 9:45 AM:liquibase: Successfully released change log lock
INFO 5/26/14 9:45 AM:liquibase: Successfully acquired change log lock
INFO 5/26/14 9:45 AM:liquibase: Reading from PUBLIC.DATABASECHANGELOG
INFO 5/26/14 9:45 AM:liquibase: Reading from PUBLIC.DATABASECHANGELOG
INFO 5/26/14 9:45 AM:liquibase: Reading from PUBLIC.DATABASECHANGELOG
INFO 5/26/14 9:45 AM:liquibase: liquibase/changelog.xml: liquibase/2014/1-1.xml::05192014.1525::h2: Reading from
PUBLIC.DATABASECHANGELOG INFO 5/26/14 9:45 AM:liquibase: liquibase/changelog.xml: liquibase/2014/1-1.xml::05192014.1525::h2: Table network created INFO 5/26/14 9:45 AM:liquibase: liquibase/changelog.xml: liquibase/2014/1-1.xml::05192014.1525::h2: ChangeSet liquibase/2014/1-1.xml::05192014.1525::h2 ran successfully in 5ms INFO 5/26/14 9:45 AM:liquibase: liquibase/changelog.xml: liquibase/2014/1-1.xml::05192014.1525::h2: Reading from PUBLIC.DATABASECHANGELOG INFO 5/26/14 9:45 AM:liquibase: liquibase/changelog.xml: liquibase/2014/1-2.xml::05192014.1525::h2: Reading from PUBLIC.DATABASECHANGELOG INFO 5/26/14 9:45 AM:liquibase: liquibase/changelog.xml: liquibase/2014/1-2.xml::05192014.1525::h2: New row inserted into network INFO 5/26/14 9:45 AM:liquibase: liquibase/changelog.xml: liquibase/2014/1-2.xml::05192014.1525::h2: New row inserted into network INFO 5/26/14 9:45 AM:liquibase: liquibase/changelog.xml: liquibase/2014/1-2.xml::05192014.1525::h2: New row inserted into network INFO 5/26/14 9:45 AM:liquibase: liquibase/changelog.xml: liquibase/2014/1-2.xml::05192014.1525::h2: New row inserted into network INFO 5/26/14 9:45 AM:liquibase: liquibase/changelog.xml: liquibase/2014/1-2.xml::05192014.1525::h2: New row inserted into network INFO 5/26/14 9:45 AM:liquibase: liquibase/changelog.xml: liquibase/2014/1-2.xml::05192014.1525::h2: New row inserted into network INFO 5/26/14 9:45 AM:liquibase: liquibase/changelog.xml: liquibase/2014/1-2.xml::05192014.1525::h2: ChangeSet liquibase/2014/1-2.xml::05192014.1525::h2 ran successfully in 5ms INFO 5/26/14 9:45 AM:liquibase: liquibase/changelog.xml: liquibase/2014/1-2.xml::05192014.1525::h2: Reading from PUBLIC.DATABASECHANGELOG INFO 5/26/14 9:45 AM:liquibase: Successfully released change log lock

What is going wrong here?

Nathan Voxland Nathan Voxland · Accepted Answer · 2014-06-02T20:22:05

Liquibase tries to handle both "schemas" and "catalogs" where the catalog is the top most container which may or may not contain schemas. For databases like MySQL which have just one level of object groupings, "catalog" and "schema" are interchangeable. For H2, you can have both the catalog/database and also schemas within the database. Your h2 setup is using the test_common database, but the default PUBLIC schema within that database.

If you are wanting to configure Liquibase to use a non-default H2 schema, use the "defaultSchema" SpringLiquibase configuration property.

Liquibase: Does not honor schema name for H2, but MySQL is fine

1 Answers