Liquibase exception recreating database

mortoza_khan · October 17, 2022, 12:37pm

Hi
After I got the liquibase exception as posted here, I tried to recreate the database from the studio.
I am getting a strange error when I try to recreate the already existing database.

I recreate the database:

Then it starts creating all the tables, etc. and at some stages, it gives the following exception. I tried twice, and both attempts failed but for different tables.

liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for change set com/inteacc/pf/liquibase/changelog/2022/09/22-081917-b5c068c4.xml::1::pf:
     Reason: liquibase.exception.DatabaseException: Cannot find the object "PF_CONTROL_ACCOUNT_LINE" because it does not exist or you do not have permissions. [Failed SQL: (1088) CREATE NONCLUSTERED INDEX IDX_PFCONTROLACC_CONTROLACCOU ON PF_CONTROL_ACCOUNT_LINE(CONTROL_ACCOUNT_ID)]
	at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:126)
	at liquibase.Liquibase.lambda$null$0(Liquibase.java:265)
	at liquibase.Scope.lambda$child$0(Scope.java:180)
	at liquibase.Scope.child(Scope.java:189)
	at liquibase.Scope.child(Scope.java:179)
	at liquibase.Scope.child(Scope.java:158)
	at liquibase.Scope.child(Scope.java:243)
	at liquibase.Liquibase.lambda$update$1(Liquibase.java:264)
	at liquibase.Scope.lambda$child$0(Scope.java:180)
	at liquibase.Scope.child(Scope.java:189)
	at liquibase.Scope.child(Scope.java:179)
	at liquibase.Scope.child(Scope.java:158)
	at liquibase.Liquibase.runInScope(Liquibase.java:2405)
	at liquibase.Liquibase.update(Liquibase.java:211)
	at liquibase.Liquibase.update(Liquibase.java:197)
	at liquibase.integration.commandline.Main.doMigration(Main.java:1869)
	at liquibase.integration.commandline.Main$1.lambda$run$0(Main.java:398)
	at liquibase.Scope.lambda$child$0(Scope.java:180)
	at liquibase.Scope.child(Scope.java:189)
	at liquibase.Scope.child(Scope.java:179)
	at liquibase.Scope.child(Scope.java:158)
	at liquibase.integration.commandline.Main$1.run(Main.java:397)
	at liquibase.integration.commandline.Main$1.run(Main.java:221)
	at liquibase.Scope.child(Scope.java:189)
	at liquibase.Scope.child(Scope.java:165)
	at liquibase.integration.commandline.Main.run(Main.java:221)
	at liquibase.integration.commandline.Main.main(Main.java:164)
Caused by: liquibase.exception.MigrationFailedException: Migration failed for change set

And actually that Entity definition exists

I am using platform version 1.3.4 and the latest version of the studio today and SQL Server database.

Thanks for your suggestions

mortoza_khan · October 17, 2022, 3:59pm

I just noticed that it was not working for Oracle and MySQL database as the fix is done in upcoming release but I’m getting this issue in SQL server as database, not sure if this fix will also work for SQL server.

gaslov · October 18, 2022, 6:23pm

Hi

Could you please attach idea.log?
As we see in the error message, PF_CONTROL_ACCOUNT_LINE table not found. While the index should be created on that table. Please check if this table exists and review /changelog/2022/09/22-081917-b5c068c4.xml changelog. It is possible that due to error that you reported in your previous post or some other issue this changelog is invalid.

The issue with Oracle and MySQL database is different. It was caused by JDBC drivers specifics.

mortoza_khan · October 19, 2022, 1:06pm

Hi
I have attached the log file as requested.
Note, this is now from the new project I have created in v1.4 (Flow UI)
idea.log.zip (408.0 KB)

mortoza_khan · October 19, 2022, 1:50pm

Hi Alexander
The previous log was when I was using SQL server.

Please see the error when I tried to use PostgreSQL

Caused by: org.postgresql.util.PSQLException: ERROR: relation "pf_user" already exists
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675) ~[postgresql-42.3.7.jar:42.3.7]
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365) ~[postgresql-42.3.7.jar:42.3.7]
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355) ~[postgresql-42.3.7.jar:42.3.7]
Caused by: liquibase.exception.DatabaseException: ERROR: relation "pf_user" already exists [Failed SQL: (0) CREATE TABLE public.PF_USER (ID UUID NOT NULL, VERSION INTEGER NOT NULL, USERNAME VARCHAR(255) NOT NULL, FIRST_NAME VARCHAR(255), LAST_NAME VARCHAR(255), PASSWORD VARCHAR(255), EMAIL VARCHAR(255), ACTIVE BOOLEAN, TIME_ZONE_ID VARCHAR(255), CONSTRAINT "PF_USER_pkey" PRIMARY KEY (ID))]

	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490) ~[postgresql-42.3.7.jar:42.3.7]
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408) ~[postgresql-42.3.7.jar:42.3.7]
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:329) ~[postgresql-42.3.7.jar:42.3.7]
	at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:315) ~[postgresql-42.3.7.jar:42.3.7]
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:291) ~[postgresql-42.3.7.jar:42.3.7]
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:286) ~[postgresql-42.3.7.jar:42.3.7]
	at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94) ~[HikariCP-4.0.3.jar:na]
	at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) ~[HikariCP-4.0.3.jar:na]
	at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:393) ~[liquibase-core-4.11.0.jar:na]
	... 61 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: relation "pf_user" already exists