Failing migration from 1.8.1 to v2.0.0-beta04

[Backfisch145]

The migration is stuck, because db/db.changelog-master.yml::2.0.0-expand-report-table::f43nd1r is failing, because of a duplicate column name (marketing_device).

ERROR liquibase.changelog : ChangeSet db/db.changelog-master.yml::2.0.0-expand-report-table::f43nd1r encountered an exception.

WARN 1 ConfigServletWebServerApplicationContext : Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'reportService' defined in file [/application/BOOT-INF/classes/com/faendir/acra/domain/ReportService.class]: Unsatisfied dependency expressed through constructor parameter 0: Error creating bean with name 'reportRepository' defined in file [/application/BOOT-INF/classes/com/faendir/acra/persistence/report/ReportRepository.class]: Unsatisfied dependency expressed through constructor parameter 0: Error creating bean with name 'liquibase' defined in class path resource [org/springframework/boot/autoconfigure/liquibase/LiquibaseAutoConfiguration$LiquibaseConfiguration.class]: liquibase.exception.MigrationFailedException: Migration failed for changeset db/db.changelog-master.yml::2.0.0-expand-report-table::f43nd1r:

Reason: liquibase.exception.DatabaseException: Duplicate column name 'marketing_device' [Failed SQL: (1060) ALTER TABLE acrariumNext.report ADD marketing_device VARCHAR(255) NULL, ADD bug_id INT NULL, ADD app_id INT NULL, ADD version_code INT NULL, ADD version_flavor VARCHAR(255) NULL, ADD stacktrace LONGTEXT NULL, ADD exception_class VARCHAR(255) NULL, ADD message LONGTEXT NULL, ADD crash_line LONGTEXT NULL, ADD cause LONGTEXT NULL]

The state of the Database table while crashing:

[Backfisch145]

It also happens if i migrate to 1.9.0 before

[F43nd1r]

I checked through the migration schema, but there's no error here. The only way I could imagine this happened is when you interrupt the initial migration and liquibase isn't able to roll back the changeset in progress. If you have a backup of your original db, try again. Otherwise you'll have to manually rollback that changeset.

[Backfisch145]

I tried again with a completely empty database. Then i started acrarium with the stable version and let it create its tables. After that, ich changed the label from stable to next and another error happened:

2023-09-04T08:54:44.554Z ERROR 1 --- [           main] liquibase.changelog                      : ChangeSet db/db.changelog-master.yml::2.0.0-expand-bug::f43nd1r encountered an exception.
2023-09-04T08:54:44.565Z  INFO 1 --- [           main] liquibase                                : Update command encountered an exception.
2023-09-04T08:54:44.569Z  INFO 1 --- [           main] liquibase.executor                       : Changelog query completed.
2023-09-04T08:54:44.580Z  INFO 1 --- [           main] liquibase.lockservice                    : Successfully released change log lock
2023-09-04T08:54:44.583Z  WARN 1 --- [           main] ConfigServletWebServerApplicationContext : Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'reportService' defined in file [/application/BOOT-INF/classes/com/faendir/acra/domain/ReportService.class]: Unsatisfied dependency expressed through constructor parameter 0: Error creating bean with name 'reportRepository' defined in file [/application/BOOT-INF/classes/com/faendir/acra/persistence/report/ReportRepository.class]: Unsatisfied dependency expressed through constructor parameter 0: Error creating bean with name 'liquibase' defined in class path resource [org/springframework/boot/autoconfigure/liquibase/LiquibaseAutoConfiguration$LiquibaseConfiguration.class]: liquibase.exception.MigrationFailedException: Migration failed for changeset db/db.changelog-master.yml::2.0.0-expand-bug::f43nd1r:
        Reason: liquibase.exception.DatabaseException: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable) [Failed SQL: (1419) CREATE TRIGGER bug_report_insert AFTER INSERT ON `report` FOR EACH ROW CALL update_bug_report_stats(NEW.`bug_id`)]
...
2023-09-04T08:54:44.730Z ERROR 1 --- [           main] o.s.boot.SpringApplication               : Application run failed
2023-09-04 10:54:44.759 org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'reportService' defined in file [/application/BOOT-INF/classes/com/faendir/acra/domain/ReportService.class]: Unsatisfied dependency expressed through constructor parameter 0: Error creating bean with name 'reportRepository' defined in file [/application/BOOT-INF/classes/com/faendir/acra/persistence/report/ReportRepository.class]: Unsatisfied dependency expressed through constructor parameter 0: Error creating bean with name 'liquibase' defined in class path resource [org/springframework/boot/autoconfigure/liquibase/LiquibaseAutoConfiguration$LiquibaseConfiguration.class]: liquibase.exception.MigrationFailedException: Migration failed for changeset db/db.changelog-master.yml::2.0.0-expand-bug::f43nd1r:
    Reason: liquibase.exception.DatabaseException: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable) [Failed SQL: (1419) CREATE TRIGGER bug_report_insert AFTER INSERT ON `report` FOR EACH ROW CALL update_bug_report_stats(NEW.`bug_id`)]

[Backfisch145]

Another warning from liquibase that might have to do with this: 2023-09-04T08:54:33.576Z WARN 1 --- [ main] liquibase.changelog : Due to mysql SQL limitations, setNullable will lose primary key/autoincrement/not null/comment settings explicitly redefined in the change. Use <sql> or <modifySql> to re-specify all configuration if this is the case 2023-09-04T08:54:33.576Z WARN 1 --- [ main] liquibase.changelog : Due to mysql SQL limitations, addDefaultValue will lose primary key/autoincrement/not null/comment settings explicitly redefined in the change. Use <sql> or <modifySql> to re-specify all configuration if this is the case 2023-09-04T08:54:33.576Z WARN 1 --- [ main] liquibase.changelog : Due to mysql SQL limitations, modifyDataType will lose primary key/autoincrement/not null/comment settings explicitly redefined in the change. Use <sql> or <modifySql> to re-specify all configuration if this is the case

[F43nd1r]

what`s your mysql version?

[Backfisch145]

Should be MySQL version 8. Tomorrow I know for sure.

Update: It is Version: 8.0.32-1.el8

[Backfisch145]

I found one Part of the error, I was using "MysqlDialect" instead of "Mysql57Dialect"

Now the migration is stuck at these Exceptions:

2023-09-06T07:23:41.676Z ERROR 1 --- [           main] liquibase.changelog                      : ChangeSet db/db.changelog-master.yml::2.0.0-expand-report-table::f43nd1r encountered an exception.
2023-09-06T07:23:42.052Z  WARN 1 --- [           main] ConfigServletWebServerApplicationContext : Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'reportService' defined in file [/application/BOOT-INF/classes/com/faendir/acra/domain/ReportService.class]: Unsatisfied dependency expressed through constructor parameter 0: Error creating bean with name 'reportRepository' defined in file [/application/BOOT-INF/classes/com/faendir/acra/persistence/report/ReportRepository.class]: Unsatisfied dependency expressed through constructor parameter 0: Error creating bean with name 'liquibase' defined in class path resource [org/springframework/boot/autoconfigure/liquibase/LiquibaseAutoConfiguration$LiquibaseConfiguration.class]: liquibase.exception.MigrationFailedException: Migration failed for changeset db/db.changelog-master.yml::2.0.0-expand-report-table::f43nd1r:
     Reason: liquibase.exception.DatabaseException: Subquery returns more than 1 row [Failed SQL: (1242) UPDATE acrariumNext.report SET marketing_device = IFNULL((SELECT d.`marketing_name` FROM `device` d WHERE d.`model` = `phone_model` AND d.`device` = `device`), `device`)]

CompleteLog.txt

[Backfisch145]

After many tries I backed up the tables:

• app
• app_report_columns
• user
• user_permissions
• user_roles
• version

Then cleared the daabase and started the acrarium:next version with dialect "org.hibernate.dialect.MySQL8Dialect" and it worked.

Maybe the "Mysql57Dialect" was the problem before