Underscore in schemaName causes error relation "databasechangelog" already exists

[urvanov-ru]

Environment

Liquibase Version : 4.11

Liquibase Integration & Version: ---

Liquibase Extension(s) & Version: ---

Database Vendor & Version: PostgreSQL 11

Operating System Type & Version: Ubuntu 20.04.1 LTS

Description

liquibase.snapshot.JdbcDatabaseSnapshot searchs tables using:

return extract(databaseMetaData.getTables(catalog, schema, ((table == null) ?
SQL_FILTER_MATCH_ALL : table), new String[]{"TABLE"}));

databaseMetaData is an instance of java.sql.DatabaseMetadata. Liquibase passes schemaName to #getTables to find databasechangelog table. It is "test_schema" in our example. DatabaseMetadata#getTables accepts schemaPattern for SQL LIKE, so it uses his argument in something like:

LIKE "test_schema"

our underscore character is a replacement for any other character, so it receives two rows for our table: one for test-schema and the second for test_schema. Then inside Liquibase it goes through compare chain and skips that received table, because it is not in our test_schema, it is in test-schema. Liquibase thinks that databasechangelog does not exist, and it tries to create it. But our test_schema already has databasechangelog table from the first run.

Steps To Reproduce

Example project: https://github.com/urvanov-ru/liquibase-schemaname/tree/main/liquibase-schemaname
We have two schemas: test-schema and test_schema in PostgreSQL database.
The first run of test project always successful. The second run fails.

We see error:

Exception in thread "main" liquibase.exception.DatabaseException: ERROR: relation "databasechangelog" already exists [Failed SQL: (0) CREATE TABLE test_schema.databasechangelog (ID VARCHAR(255) NOT NULL, AUTHOR VARCHAR(255) NOT NULL, FILENAME VARCHAR(255) NOT NULL, DATEEXECUTED TIMESTAMP WITHOUT TIME ZONE NOT NULL, ORDEREXECUTED INTEGER NOT NULL, EXECTYPE VARCHAR(10) NOT NULL, MD5SUM VARCHAR(35), DESCRIPTION VARCHAR(255), COMMENTS VARCHAR(255), TAG VARCHAR(255), LIQUIBASE VARCHAR(20), CONTEXTS VARCHAR(255), LABELS VARCHAR(255), DEPLOYMENT_ID VARCHAR(10))]
	at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:398)
	at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:82)
	at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:154)
	at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:134)
	at liquibase.changelog.StandardChangeLogHistoryService.init(StandardChangeLogHistoryService.java:279)
	at liquibase.Liquibase.checkLiquibaseTables(Liquibase.java:1786)
	at liquibase.Liquibase.lambda$update$1(Liquibase.java:232)
	at liquibase.Scope.lambda$child$0(Scope.java:160)
	at liquibase.Scope.child(Scope.java:169)
	at liquibase.Scope.child(Scope.java:159)
	at liquibase.Scope.child(Scope.java:138)
	at liquibase.Liquibase.runInScope(Liquibase.java:2277)
	at liquibase.Liquibase.update(Liquibase.java:215)
	at liquibase.Liquibase.update(Liquibase.java:201)
	at ru.urvanov.javaexamples.liquibaseschemaname.Main.liquibaseUpdate(Main.java:52)
	at ru.urvanov.javaexamples.liquibaseschemaname.Main.main(Main.java:30)
Caused by: org.postgresql.util.PSQLException: ERROR: relation "databasechangelog" already exists
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:473)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:393)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:322)
	at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:308)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:284)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:279)
	at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:394)
	... 15 more

Actual Behavior

The second run of https://github.com/urvanov-ru/liquibase-schemaname/tree/main/liquibase-schemaname fails.

Expected/Desired Behavior

The second run of https://github.com/urvanov-ru/liquibase-schemaname/tree/main/liquibase-schemaname should be successful. test-schema should use only test-schema objects, test_schema should use only test_schema objects.

I have been using Liquibase for many years. Thank you for really good project. I am really looking forward for any workaround or bugfix for the problem.

┆Issue is synchronized with this Jira Bug by Unito
┆fixVersions: Community 4.x

[molivasdat]

Hi @urvanov-ru Thanks for this excellent repro and an repo that shows the issue. We will add this to our list of issues to process.

[urvanov-ru]

Maybe we can use databaseMetaData.getSearchStringEscape() to escape our special characters.

[tomtrapp]

Hi @urvanov-ru and @molivasdat

Any news on this issue?
I'm facing the same blocking issue on my side.

+1

Thanks &
Cheers
Tom

[raffaelschneider]

+1

[urvanov-ru]

I am sorry, but I had no time to do anything in this issue. I tried to fix it, but it is not one line fixing bug

[tomtrapp]

@urvanov-ru
Oh ok, any news on this one? :)

[tomtrapp]

Hi @urvanov-ru or @molivasdat
I saw you moved the issue into the Bug Backlog, any news on this? When can we expect the fix to be released? :)

Thanks & rest regards
Tom

[molivasdat]

Hi @tomtrapp Yes we are gearing up to start moving quick a bit quicker with our updates. Not quite there yet. The workaround for now would be use another name that does not include _ in the schema name.

[XDelphiGrl]

The fix adds escaping for both _ and % in schema names to prevent schema name clashes caused by treating _ and % as regular expression pattern. This escaping is done for all databases except oracle, mssql, and db2z.

Special Thanks! Thank you, @urvanov-ru, for the project that reproduces the bug. As a QA, I cannot overstate how valuable this is to me.

---

As demonstrated in the github repro project, the steps to see the bug are:

• First Update: Run with a liquibase.properties file configured with liquibase.command.defaultSchema: test-schema and liquibase.liquibaseSchemaName:test-schema
• Second Update: Run with a liquibase.properties file configured with liquibase.command.defaultSchema: test_schema and liquibase.liquibaseSchemaName:test_schema
• Third Update: Run with the same liquibase.properties defined for second update.
  • It is the third update where you see a failure (when running manually).

Postgres Validation

• Validate the third update to a database with schemas test_schema and test-schema is successful. PASS
  • There is no error that DATABASECHANGELOG table already exists PASS
  • The DATABASECHANGELOG table is updated correctly PASS

SQL Server Validation
This test turned out to be a no-op given that SQL Server users are not permitted to change a user's default schema during a session. Given that the bug replicates only when both defaultSchemaName and liquibaseSchemaName are defined in the liquibase.properties file, there was no path into the bug on SQL Server.

• Validate the third update to a database with schemas test_schema and test-schema is successful. PASS
  • There is no error that DATABASECHANGELOG table already exists PASS
  • The DATABASECHANGELOG table is updated correctly PASS

---

Test Environment
Liquibase Core: escape-names-in-getTables/1270/dc4772 Pro: master/463/064632
Passing Functional Tests
Postgres 12.6
SQL Server 17