Error when running Liquibase: Table already exists

[phxql]

Hello,

i'm getting an error when running Liquibase against a Cache database:

>liquibase status
Unexpected error running Liquibase: [SQLCODE: <-201>:<Table or view name not unique>]
[Location: <ServerLoop>]
[%msg: <Table 'SQLUser.DATABASECHANGELOG' already exists>]

When the database is empty, the first update works:

>liquibase update
Liquibase Update Successful

But every subsequent update call fails:

>liquibase update
Unexpected error running Liquibase: java.sql.SQLException: [SQLCODE: <-201>:<Table or view name not unique>]
[Location: <ServerLoop>]
[%msg: <Table 'SQLUser.DATABASECHANGELOGLOCK' already exists>]

┆Issue is synchronized with this Jira Bug by Unito

[onkar5891]

Hello,
I had same problem when I used MySQL. The problem was 'mysql' schema does not recognise 'DATABASECHANGELOGLOCK' as it treats its lower case name differently. So, I changed the variable 'lower_case_table_names' to 1 [But as this variable is read only, we need to change in mysqld.cnf], restarted the mysql service and then, subsequent update of liquibase started working.

Have a look at similar variable in your cache database so that 'DATABASECHANGELOGLOCK' is treated in the same way regardless of the case.

Thanks,
Onkar

[molivasdat]

Hi @phxql . Are you still using this Liquibase Cache database extension?
How about @onkar5891?
@elktamer @ericfrancis @kurbatov @vivekmore?
Any feedback?

[kurbatov]

I never used this extension but experienced the same issue using liquibase for H2 database with database_to_upper=false parameter in URL. I believe it should be addressed in liqubase (actually didn't notice it is an extension repo until now).

I got around my problem by monkey-patching liquibase.database.core.H2Database class. Namely, I commented out the first line of the constructor which reads:

super.unquotedObjectsAreUppercased=true;

Before I did that I researched sources of liquibase. I found out that when it checks if DATABASECHANGELOGLOCK table exists it does it in a different way than checking DATABASECHANGELOG for existence. I don't remember exactly what the difference was but probably it checks for uppercased name of the table adding quotes nonetheless which leads to a negative result and subsequent attempt to create DATABASECHANGELOGLOCK again.

How I did monkey-patching: I created a package liquibase.database.core in my project and copied H2Database modifying it there. When a classloader is looking for that class, my version gets priority because my artifact is the first one in the classpath.

[phxql]

Hi @phxql . Are you still using this Liquibase Cache database extension?
How about @onkar5891?
@elktamer @ericfrancis @kurbatov @vivekmore?
Any feedback?

I don't use it anymore.

[molivasdat]

@kurbatov Thanks for the feedback. Are you still using H2 with Liquibase and are you still experiencing the issue?

[molivasdat]

Thanks @phxql for the feedback.

[kurbatov]

@kurbatov Thanks for the feedback. Are you still using H2 with Liquibase and are you still experiencing the issue?

Yes, I do, in that project with monkey-patched H2Database class. It is not the latest version though. The issue exists in 3.8.7. Tomorrow I'll check 3.10.1.

[kurbatov]

It looks like the issue won't reproduce for H2 with database_to_upper=false in liqiuibase 3.10.1.

[matze999]

I am still seeing the issue. Tried it with 3.10.1 and 4.0.0. I am running multiple spring.boot tests. The error i am seeing is:
Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled. 2020-07-22 07:56:51.187 ERROR [org.springframework.boot.SpringApplication] [Test worker:13]: Application run failed org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'liquibase' defined in class path resource [org/springframework/boot/autoconfigure/liquibase/LiquibaseAutoConfiguration$LiquibaseConfiguration.class]: Invocation of init method failed; nested exception is liquibase.exception.DatabaseException: Table "DATABASECHANGELOG" already exists; SQL statement: CREATE TABLE PUBLIC.DATABASECHANGELOG (ID VARCHAR(255) NOT NULL, AUTHOR VARCHAR(255) NOT NULL, FILENAME VARCHAR(255) NOT NULL, DATEEXECUTED TIMESTAMP NOT NULL, ORDEREXECUTED INT 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)) [42101-197] [Failed SQL: (42101) CREATE TABLE PUBLIC.DATABASECHANGELOG (ID VARCHAR(255) NOT NULL, AUTHOR VARCHAR(255) NOT NULL, FILENAME VARCHAR(255) NOT NULL, DATEEXECUTED TIMESTAMP NOT NULL, ORDEREXECUTED INT 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))]

[matze999]

Hi, i can workaround the issue by changing the code a bit following @kurbatov suggestion. I implemented this for version liquibase version 4.0.0 and posted what i did over on stackoverflow (https://stackoverflow.com/questions/63036299/spring-boot-testing-with-liquibase-fails/63173456#63173456).

I do think that this is something which should be addressed in liquibase. Based on my understanding we should never fail if the "DATABASECHANGELOG" table creation fails because it already exists (there are legitimate reason to fail if the creation fails in general, but not due to it already existing).

Anyway, if my reasoning is sound, is there any way to work that into the official code?

[r2-lf]

Hey, @molivasdat ! Do you need any help with this?

[molivasdat]

Hi @r2liquibase. Sure I'll take the help if you have a fix for this.

[r2-lf]

Sounds like we need to override the method that deals with databasechangelog table creation. If this is realated to H2, can we get help from internal engineering?