Empty DB on migration from CUBA 7.2 to Jmix 1.1

samybill · November 1, 2021, 6:19pm

Hello,

I have tested the migration guide from Cuba to Jmix 1.1.0. The sql scripts 10-create-db.sql and others have not been migrated. The database is empty after starting the application, none of the entities/tables defined in the project have been created in the DB.

When I run in studio “generate Liquibase changelog”, I get the following:

Screenshot 2021-11-01 at 19.13.45

Althoug the console logs show “Liquibase: Update has been successful.”, the following event is generated:

19:16 Generate Liquibase Changelog: liquibase.command.CommandExecutionException: java.lang.RuntimeException: java.lang.RuntimeException: java.lang.NullPointerException

Any idea how to make the DB migration from Cuba 7.2 to Jmix 1.1.0 work?

The script “010-init-user.xml” contains only the User table. There are no other XML files in the changelog directory of liquibase.

Thanks,
Samy

Anton.Kozub · November 3, 2021, 10:23am

Hi.

The migration process doesn’t copy SQL files from a Cuba project - Jmix uses its own liquibase scripts, which are placed in libraries, not in the very project.
By default, a new Jmix project after the migration is configured to work with the same database as the original Cuba project was working with. But if you want to run it on an empty database, there is a special section 13 in the guide, please follow it.

Regards,
Anton

samybill · November 3, 2021, 10:37am

Thanks for the response.

I followed exactly the section 13 of the migration guide you mentioned. I get the Liquibase errors I mentioned in my previous post.

By the way, I have tried during the migration to follow step 11 and 12. Since it didn’t work. I decided to recreate the DB from scratch and followed the section 13.

Regards,
Samy

krivopustov · November 3, 2021, 12:31pm

Hi Samy

Could you elaborate what exactly didn’t work?

Did you do this before generating diff changelogs?

Click Recreate in the context menu of the Main Data Store item. Studio will drop/create the database and run Liquibase changelogs from all Jmix modules.

Also, if you have a complete stacktrace of the exception (it can also be in idea.log), please attach it here.

Regards,
Konstantin

samybill · November 3, 2021, 5:55pm

Hi Konstantin,

I followed the steps 11 and 12. Here what I get:

Unexpected error running Liquibase: Migration failed for change set com/haulmont/cuba/liquibase/changelog/002-migrate-security.xml::10::migrator:
 Reason: liquibase.exception.DatabaseException: ERROR: column "username" of relation "sec_user" already exists [Failed SQL: (0) ALTER TABLE public.SEC_USER ADD USERNAME VARCHAR(255)]
For more information, please use the --logLevel flag
[2021-11-03 18:43:54] SEVERE [liquibase.integration] Unexpected error running Liquibase: Migration failed for change set com/haulmont/cuba/liquibase/changelog/002-migrate-security.xml::10::migrator:
 Reason: liquibase.exception.DatabaseException: ERROR: column "username" of relation "sec_user" already exists [Failed SQL: (0) ALTER TABLE public.SEC_USER ADD USERNAME VARCHAR(255)]
liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for change set com/haulmont/cuba/liquibase/changelog/002-migrate-security.xml::10::migrator:
 Reason: liquibase.exception.DatabaseException: ERROR: column "username" of relation "sec_user" already exists [Failed SQL: (0) ALTER TABLE public.SEC_USER ADD USERNAME VARCHAR(255)]
	at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:124)
	at liquibase.Liquibase.lambda$null$0(Liquibase.java:272)
	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.Scope.child(Scope.java:222)
	at liquibase.Liquibase.lambda$update$1(Liquibase.java:271)
	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:2369)
	at liquibase.Liquibase.update(Liquibase.java:217)
	at liquibase.Liquibase.update(Liquibase.java:203)
	at liquibase.integration.commandline.Main.doMigration(Main.java:1802)
	at liquibase.integration.commandline.Main$1.lambda$run$0(Main.java:367)
	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.integration.commandline.Main$1.run(Main.java:366)
	at liquibase.integration.commandline.Main$1.run(Main.java:196)
	at liquibase.Scope.child(Scope.java:169)
	at liquibase.Scope.child(Scope.java:145)
	at liquibase.integration.commandline.Main.run(Main.java:196)
	at liquibase.integration.commandline.Main.main(Main.java:158)
Caused by: liquibase.exception.MigrationFailedException: Migration failed for change set com/haulmont/cuba/liquibase/changelog/002-migrate-security.xml::10::migrator:
 Reason: liquibase.exception.DatabaseException: ERROR: column "username" of relation "sec_user" already exists [Failed SQL: (0) ALTER TABLE public.SEC_USER ADD USERNAME VARCHAR(255)]
	at liquibase.changelog.ChangeSet.execute(ChangeSet.java:672)
	at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:49)
	at liquibase.changelog.ChangeLogIterator$2.lambda$null$0(ChangeLogIterator.java:111)
	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.changelog.ChangeLogIterator$2.lambda$run$1(ChangeLogIterator.java:110)
	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.Scope.child(Scope.java:222)
	at liquibase.changelog.ChangeLogIterator$2.run(ChangeLogIterator.java:94)
	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.Scope.child(Scope.java:222)
	at liquibase.Scope.child(Scope.java:226)
	at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:66)
	... 26 more
Caused by: liquibase.exception.DatabaseException: ERROR: column "username" of relation "sec_user" already exists [Failed SQL: (0) ALTER TABLE public.SEC_USER ADD USERNAME VARCHAR(255)]
	at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:393)
	at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:82)
	at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:150)
	at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1278)
	at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1260)
	at liquibase.changelog.ChangeSet.execute(ChangeSet.java:637)
	... 46 more
Caused by: org.postgresql.util.PSQLException: ERROR: column "username" of relation "sec_user" already exists
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2552)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2284)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:322)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401)
	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:389)
	... 51 more

After the failing of above operation, I decided to recreate the DB by following step 13. I get the following (excerpt from idea.log):

2021-10-31 19:43:21,693 [35293044]   INFO - shot.IntellijSnapshotGenerator - Found primary key PK_TRADE_CUSTOMER_ACCOUNT_TYPE 
2021-10-31 19:43:21,703 [35293054]   INFO - shot.IntellijSnapshotGenerator - Found primary key PK_TRADEITEMINVENTORYJOURNALEN 
2021-10-31 19:43:22,896 [35294247]   INFO -                         STDERR - Oct 31, 2021 7:43:22 PM liquibase.diff 
2021-10-31 19:43:22,896 [35294247]   INFO -                         STDERR - INFO: /private/var/folders/9s/jkcx9cqx07l1cq0562tcmx500000gn/T/liquibase-change-log-1635705798998.xml exists, appending 
2021-10-31 19:43:22,945 [35294296]   WARN - rateDiffChangelogActionProcess - liquibase.command.CommandExecutionException: java.lang.RuntimeException: java.lang.RuntimeException: java.lang.NullPointerException 
liquibase.exception.LiquibaseException: liquibase.command.CommandExecutionException: java.lang.RuntimeException: java.lang.RuntimeException: java.lang.NullPointerException
	at liquibase.integration.commandline.CommandLineUtils.doDiffToChangeLog(CommandLineUtils.java:211)
	at com.haulmont.jmixstudio.liquibase.LiquibaseCommandManager.generateDiffChangeLog(LiquibaseCommandManager.kt:261)
	at com.haulmont.jmixstudio.liquibase.LiquibaseIntellijDiffGenerationHandler$doRun$$inlined$runReadAction$1$lambda$1$1.invoke(LiquibaseIntellijDiffGenerationHandler.kt:104)
	at com.haulmont.jmixstudio.liquibase.LiquibaseIntellijDiffGenerationHandler$doRun$$inlined$runReadAction$1$lambda$1$1.invoke(LiquibaseIntellijDiffGenerationHandler.kt:31)
	at com.haulmont.jmixstudio.liquibase.LiquibaseIntellijDiffGenerationHandler.use(LiquibaseIntellijDiffGenerationHandler.kt:129)
	at com.haulmont.jmixstudio.liquibase.LiquibaseIntellijDiffGenerationHandler.access$use(LiquibaseIntellijDiffGenerationHandler.kt:31)
	at com.haulmont.jmixstudio.liquibase.LiquibaseIntellijDiffGenerationHandler$doRun$$inlined$runReadAction$1$lambda$1.invoke(LiquibaseIntellijDiffGenerationHandler.kt:91)
	at com.haulmont.jmixstudio.liquibase.LiquibaseIntellijDiffGenerationHandler$doRun$$inlined$runReadAction$1$lambda$1.invoke(LiquibaseIntellijDiffGenerationHandler.kt:31)
	at com.haulmont.jmixstudio.liquibase.LiquibaseCommandManagerKt.runWithPluginClassLoader(LiquibaseCommandManager.kt:460)
	at com.haulmont.jmixstudio.liquibase.LiquibaseIntellijDiffGenerationHandler$doRun$$inlined$runReadAction$1.compute(actions.kt:60)
	at com.intellij.openapi.application.impl.ApplicationImpl.runReadAction(ApplicationImpl.java:841)
	at com.haulmont.jmixstudio.liquibase.LiquibaseIntellijDiffGenerationHandler.doRun(LiquibaseIntellijDiffGenerationHandler.kt:148)
	at com.haulmont.jmixstudio.liquibase.LiquibaseIntellijDiffGenerationHandler$run$changeLogFile$1.compute(LiquibaseIntellijDiffGenerationHandler.kt:66)
	at com.haulmont.jmixstudio.liquibase.LiquibaseIntellijDiffGenerationHandler$run$changeLogFile$1.compute(LiquibaseIntellijDiffGenerationHandler.kt:31)
	at com.intellij.openapi.progress.impl.CoreProgressManager$1.run(CoreProgressManager.java:279)
	at com.intellij.openapi.progress.impl.CoreProgressManager.startTask(CoreProgressManager.java:450)
	at com.intellij.openapi.progress.impl.ProgressManagerImpl.startTask(ProgressManagerImpl.java:117)
	at com.intellij.openapi.progress.impl.CoreProgressManager.lambda$runProcessWithProgressSynchronously$8(CoreProgressManager.java:556)
	at com.intellij.openapi.progress.impl.ProgressRunner.lambda$new$0(ProgressRunner.java:81)
	at com.intellij.openapi.progress.impl.ProgressRunner.lambda$submit$3(ProgressRunner.java:243)
	at com.intellij.openapi.progress.impl.CoreProgressManager.lambda$runProcess$2(CoreProgressManager.java:183)
	at com.intellij.openapi.progress.impl.CoreProgressManager.registerIndicatorAndRun(CoreProgressManager.java:705)
	at com.intellij.openapi.progress.impl.CoreProgressManager.executeProcessUnderProgress(CoreProgressManager.java:647)
	at com.intellij.openapi.progress.impl.ProgressManagerImpl.executeProcessUnderProgress(ProgressManagerImpl.java:63)
	at com.intellij.openapi.progress.impl.CoreProgressManager.runProcess(CoreProgressManager.java:170)
	at com.intellij.openapi.progress.impl.ProgressRunner.lambda$submit$4(ProgressRunner.java:243)
	at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1700)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
	at java.base/java.util.concurrent.Executors$PrivilegedThreadFactory$1$1.run(Executors.java:668)
	at java.base/java.util.concurrent.Executors$PrivilegedThreadFactory$1$1.run(Executors.java:665)
	at java.base/java.security.AccessController.doPrivileged(Native Method)
	at java.base/java.util.concurrent.Executors$PrivilegedThreadFactory$1.run(Executors.java:665)
	at java.base/java.lang.Thread.run(Thread.java:829)
Caused by: liquibase.command.CommandExecutionException: java.lang.RuntimeException: java.lang.RuntimeException: java.lang.NullPointerException
	at liquibase.command.AbstractCommand.execute(AbstractCommand.java:24)
	at liquibase.integration.commandline.CommandLineUtils.doDiffToChangeLog(CommandLineUtils.java:209)
	... 33 more
Caused by: java.lang.RuntimeException: java.lang.RuntimeException: java.lang.NullPointerException
	at liquibase.diff.output.changelog.DiffToChangeLog.print(DiffToChangeLog.java:203)
	at liquibase.diff.output.changelog.DiffToChangeLog.print(DiffToChangeLog.java:85)
	at liquibase.command.core.DiffToChangeLogCommand.run(DiffToChangeLogCommand.java:72)
	at liquibase.command.AbstractCommand.execute(AbstractCommand.java:19)
	... 34 more
Caused by: java.lang.RuntimeException: java.lang.NullPointerException
	at liquibase.diff.output.changelog.DiffToChangeLog$1.run(DiffToChangeLog.java:186)
	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.diff.output.changelog.DiffToChangeLog.print(DiffToChangeLog.java:127)
	... 37 more
Caused by: java.lang.NullPointerException
	at liquibase.diff.output.changelog.core.MissingTableChangeGenerator.fixMissing(MissingTableChangeGenerator.java:144)
	at liquibase.ext.intellij.diff.MissingTableChangeGenerator.fixMissing(MissingTableChangeGenerator.java:33)
	at liquibase.diff.output.changelog.ChangeGeneratorChain.fixMissing(ChangeGeneratorChain.java:48)
	at liquibase.diff.output.changelog.ChangeGeneratorFactory.fixMissing(ChangeGeneratorFactory.java:95)
	at liquibase.diff.output.changelog.DiffToChangeLog.generateChangeSets(DiffToChangeLog.java:302)
	at liquibase.ext.intellij.command.HDiffToChangeLog.generateChangeSets(DiffToChangeLogCommand.java:110)
	at liquibase.ext.intellij.command.JmixDiffToChangeLogCommand$createDiffToChangeLogObject$1.generateChangeSets(JmixDiffToChangeLogCommand.kt:38)
	at liquibase.diff.output.changelog.DiffToChangeLog.print(DiffToChangeLog.java:246)
	at liquibase.diff.output.changelog.DiffToChangeLog$1.run(DiffToChangeLog.java:137)
	... 42 more

Hope the above logs help understanding the issue.

Regards,
Samy

krivopustov · November 4, 2021, 9:24am

Looks like your source database already has SEC_USER.USERNAME column. Did you extend the User entity with this attribute in your CUBA project?

Regarding the second problem with creating empty database - do I understand right that it occurs on the step 13.d, after the database is successfully recreated and you run the app or execute “Generate Liquibase changelog” on the data store?

samybill · November 4, 2021, 6:23pm

I did not extend the entity User.

Yes, that is correct. Having the database successfully recreated, but it is empty and none of the tables I had before are created.

krivopustov · November 4, 2021, 7:25pm

But the exception clearly tells that the table already has this column. Please make sure you use the original database from the CUBA project when running “Update” in the new Jmix project for the first time.

Unfortunately the error is inside Liquibase code and looks like it is caused by some specific data type. We couldn’t reproduce it in our environment yet.

samybill · November 6, 2021, 5:41pm

Thanks Konstantin. After many trials (from scratch), I gave up trying to migrate the application for now.

The migration guide is probably not complete. I went through updating and resolving many issues in the screen descriptors but also to resolve various issues around the entities such the missing class BaseStringIdEntity (or the interfaces Updatable and Creatable that create different attributes names with the new Traits approach).

sa.zakharov · December 8, 2021, 1:28pm

Have the same problem.

Any solutions?

krivopustov · December 9, 2021, 6:06am

Please start a new topic and explain your problem.
Also, the migration process was improved since this thread begun, so make sure you use the latest framework and Studio.

Regards,
Konstantin

sa.zakharov · December 9, 2021, 6:36am

The topic was started on russian forum. Everything explained here:

I used 1.1.2 version of Studio and 1.1.1 version of framework

r.walde · February 2, 2022, 6:57am

Hi,
we have the same issue… followed completely the doc but first get the error,
Cannot find the object “SEC_USER” because it does not exist or you do not have permissions.

In the documentation I can read, that we should recreate the DB, but that is not possible,
because we are working with different schema on the same DB.

Then we created the sec_user manually.
Next update run we get:
Column names in each table must be unique. Column name ‘USERNAME’ in table ‘SEC_USER’ is specified more than once

Regards
Roland

r.walde · February 2, 2022, 7:10am

then took out username from the sec_user table, run the update again…
now getting:
No item by the name of ‘SEC_USER.TIME_ZONE’ could be found in the current database ‘qa’

we played with settings, tables… took out jmix.liquibase.contexts = migrated, put it in again…
suddenly it worked…

but then the next issue when starting the app.
the quartz tables do not get created and so the start process fails because they cannot be found

for me it is completely a miracle what is going on there…

update:
sorry, my fault… did not create the quartz tables manually, like you write for MSSQLs
(if possible, pls also change that for MSSQLs )

update:
we have this in the application.properties:
spring.quartz.job-store-type = jdbc
spring.quartz.properties.org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.MSSQLDelegate
spring.quartz.jdbc.initialize-schema = never

but, when starting the app it drops all quartz tables and then shows the error again, that the quartz tables are missing …

again:
no way to understand what is going on

so, in the end we got it running, but only when we do it without the update scripts in the start process

update:
we are now trying to understand this line
main.datasource.studio.liquibase.exclude-prefixes=audit_,email_,sys_,ui_,sec_,qrtz_

…

conclusion:
like you can see from my very confused writing, the migration and update processes are not easy to understand up to not possible to understand…
there are too many settings, combination and sometimes manual things to do, like for quartz

we ended with taking out the scheduler and took out the ‘check jmix database’ when starting the application…

krivopustov · February 3, 2022, 6:58pm

This property tells Studio to not touch tables with names starting with these prefixes. So for example it won’t try to delete Quartz tables even though there are no corresponding entities in the model.

r.walde · February 7, 2022, 7:45am

thx Konstantin