changelogSchemaName/changelogCatalogName configuration options will not work on Oracle DB

Description

Unable to use changelogSchemaName/changelogCatalogName configuration parameters on Oracle DB.

Steps to reproduce:

Testplan 1.

  1. Configure Liquibase against Oracle DB instance, user A;

  2. Set changelogSchemaName to B;

  3. Launch update procedure;

  4. Note that DATABASECHAGELOG* tables are created in A schema.

Testplan 2.

  1. Configure Liquibase against Oracle DB instance, user A;

  2. Set changelogCatalogName to B;

  3. Launch update procedure;

  4. Note that DATABASECHAGELOG* tables are created in A schema.

Testplan 3.

  1. Perform steps outlined in Testplan 2.

  2. Launch Liquibase again with the same configuration.

  3. Notice an exception similar to this:

    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 Error setting up or running Liquibase: liquibase.exception.DatabaseException: Error executing SQL CREATE TABLE <changelogCatalogName>.DATABASECHANGELOGLOCK (ID NUMBER(10) NOT NULL, LOCKED NUMBER(1) NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR2(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID)): ORA-00955: name is already used by an existing object at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:216) at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:153) at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:145) at org.apache.maven.lifecycle.internal.LifecycleModuleBuilder.buildProject(LifecycleModuleBuilder.java:108) at org.apache.maven.lifecycle.internal.LifecycleModuleBuilder.buildProject(LifecycleModuleBuilder.java:76) at org.apache.maven.lifecycle.internal.builder.singlethreaded.SingleThreadedBuilder.build(SingleThreadedBuilder.java:51) at org.apache.maven.lifecycle.internal.LifecycleStarter.execute(LifecycleStarter.java:116) at org.apache.maven.DefaultMaven.doExecute(DefaultMaven.java:361) at org.apache.maven.DefaultMaven.execute(DefaultMaven.java:155) at org.apache.maven.cli.MavenCli.execute(MavenCli.java:584) at org.apache.maven.cli.MavenCli.doMain(MavenCli.java:213) at org.apache.maven.cli.MavenCli.main(MavenCli.java:157) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at org.codehaus.plexus.classworlds.launcher.Launcher.launchEnhanced(Launcher.java:289) at org.codehaus.plexus.classworlds.launcher.Launcher.launch(Launcher.java:229) at org.codehaus.plexus.classworlds.launcher.Launcher.mainWithExitCode(Launcher.java:415) at org.codehaus.plexus.classworlds.launcher.Launcher.main(Launcher.java:356) Caused by: org.apache.maven.plugin.MojoExecutionException: Error setting up or running Liquibase: liquibase.exception.DatabaseException: Error executing SQL CREATE TABLE <changelogCatalogName>.DATABASECHANGELOGLOCK (ID NUMBER(10) NOT NULL, LOCKED NUMBER(1) NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR2(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID)): ORA-00955: name is already used by an existing object at org.liquibase.maven.plugins.AbstractLiquibaseMojo.execute(AbstractLiquibaseMojo.java:371) at org.apache.maven.plugin.DefaultBuildPluginManager.executeMojo(DefaultBuildPluginManager.java:133) at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:208) ... 19 more Caused by: liquibase.exception.LockException: liquibase.exception.DatabaseException: Error executing SQL CREATE TABLE <changelogCatalogName>.DATABASECHANGELOGLOCK (ID NUMBER(10) NOT NULL, LOCKED NUMBER(1) NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR2(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID)): ORA-00955: name is already used by an existing object at liquibase.lockservice.StandardLockService.acquireLock(StandardLockService.java:214) at liquibase.lockservice.StandardLockService.waitForLock(StandardLockService.java:153) at liquibase.Liquibase.update(Liquibase.java:182) at liquibase.Liquibase.update(Liquibase.java:174) at org.liquibase.maven.plugins.LiquibaseUpdate.doUpdate(LiquibaseUpdate.java:31) at org.liquibase.maven.plugins.AbstractLiquibaseUpdateMojo.performLiquibaseTask(AbstractLiquibaseUpdateMojo.java:24) at org.liquibase.maven.plugins.AbstractLiquibaseMojo.execute(AbstractLiquibaseMojo.java:367) ... 21 more Caused by: liquibase.exception.DatabaseException: Error executing SQL CREATE TABLE <changelogCatalogName>.DATABASECHANGELOGLOCK (ID NUMBER(10) NOT NULL, LOCKED NUMBER(1) NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR2(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID)): ORA-00955: name is already used by an existing object at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:62) at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:122) at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:112) at liquibase.lockservice.StandardLockService.init(StandardLockService.java:88) at liquibase.lockservice.StandardLockService.acquireLock(StandardLockService.java:187) ... 27 more Caused by: java.sql.SQLSyntaxErrorException: ORA-00955: name is already used by an existing object at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587) at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:210) at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:30) at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:931) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1150) at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1792) at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1745) at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:334) at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:310) at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:55) ... 31 more

Code analysis showed that:

Oracle does not support schemas "in Liquibase's opinion".

I.e. liquibase.database.core.OracleDatabase.supportsSchemas() always return false, hence liquibase.database.AbstractJdbcDatabase.getDefaultSchemaName() always return default catalog (usually <jdbc_connection_username>).

Code, which creates DATABASECHANGELOG/DATABASECHANGELOGLOCK tables also affected.

liquibase.sqlgenerator.core.CreateDatabaseChangeLogLockTableGenerator.generateSql(CreateDatabaseChangeLogLockTableStatement, Database, SqlGeneratorChain) will create an SQL statement containing catalog only, whereas schema (set to <changelogSchemaName>) ignored by liquibase.database.AbstractJdbcDatabase.escapeObjectName(String, String, String, Class<? extends DatabaseObject>). The same issue with liquibase.sqlgenerator.core.CreateDatabaseChangeLogTableGenerator.generateSql(CreateDatabaseChangeLogTableStatement, Database, SqlGeneratorChain).

Note on DB metadata check when <changelogCatalogName> is set.

In liquibase.snapshot.SnapshotGeneratorFactory.hasDatabaseChangeLogLockTable(Database) DATABASECHANGELOGLOCK table object recieves catalog = <changelogCatalogName>, schemaName = <jdbc_connection_username> (see note about AbstractJdbcDatabase.getDefaultSchemaName() above) upon creation.
DB metadata loading code - liquibase.snapshot.JdbcDatabaseSnapshot.CachingDatabaseMetaData.getTables(String, String, String, String[]) - called prior to the existance check, recieves catalogName = null, schemaName = <changelogCatalogName>, but eventually calls CatalogAndSchema catalogAndSchema = new CatalogAndSchema(catalogName, schemaName).customize(database);(JdbcDatabaseSnapshot.java:356, fastFetchQuery() method). customize(Database) method, in turn, calls liquibase.CatalogAndSchema.standardize(Database), which nullifyes schema name:

CatalogAndSchema.java

1 2 3 4 5 6 7 if (accordingTo.supportsSchemas()) { if (schemaName != null && schemaName.equalsIgnoreCase(accordingTo.getDefaultSchemaName())) { schemaName = null; } } else { schemaName = null; }

This effectively eliminates configured <changelogCatalogName> value and resets it to default schema (usually <jdbc_connection_username>).
queryOracle(CatalogAndSchema, String) that called furhter creates a query of the form:

1 SELECT null as TABLE_CAT, a.OWNER as TABLE_SCHEM, a.TABLE_NAME as TABLE_NAME, 'TABLE' as TABLE_TYPE, c.COMMENTS as REMARKS from ALL_TABLES a join ALL_TAB_COMMENTS c on a.TABLE_NAME=c.table_name and a.owner=c.owner WHERE a.OWNER='<jdbc_connection_username>' AND a.TABLE_NAME not in (select mv.name from all_registered_mviews mv where mv.owner='<jdbc_connection_username>')

which will return information about tables from <jdbc_connection_username> schema only; table snapshot will retain this info and, hence, the DATABASECHANGELOGLOCK table object passed to liquibase.snapshot.SnapshotGeneratorFactory.has(DatabaseObject, Database) by liquibase.snapshot.SnapshotGeneratorFactory.hasDatabaseChangeLogLockTable(Database) will not be matched because schema names differ. Liquibase will decide that this table does not exist (even if it is not the case, and it existis within the default schema) and attempt to recreate a lock table.

Environment

Oracle Database 11.2.0.3.0, Maven 3.2.1, JDK 1.7.0_51.

Status

Assignee

Unassigned

Reporter

Alexander

Labels

None

Fix versions

Affects versions

3.2.2

Priority

Critical