Liquibase migration from api-server 5.5.0 to snapshot fails #1737
Description
Current Behavior
Hello,
Coming from api-server 5.5.0, we attempted to use the snapshot version.
One of the DB migration for 5.6.0 fails with the following error:
2025-04-01 12:18:03,796 ERROR [ChangeSet] ChangeSet migration/changelog-v5.6.0.xml::v5.6.0-15::nscuro encountered an exception. liquibase.exception.DatabaseException: ERROR: null value in column "PARENT_PROJECT_ID" of relation "PROJECT_HIERARCHY" violates not-null constraint Detail: Failing row contains (null, 121, 1). [Failed SQL: (0) WITH RECURSIVE cte_project_hierarchy AS ( SELECT "ID" AS child_id , "PARENT_PROJECT_ID" AS parent_id , 1 AS depth FROM "PROJECT" UNION ALL SELECT child_id , "PARENT_PROJECT_ID" AS parent_id , depth + 1 AS depth FROM cte_project_hierarchy INNER JOIN "PROJECT" ON "PROJECT"."ID" = cte_project_hierarchy.parent_id WHERE "PROJECT"."PARENT_PROJECT_ID" IS NOT NULL ) INSERT INTO "PROJECT_HIERARCHY" ("PARENT_PROJECT_ID", "CHILD_PROJECT_ID", "DEPTH") SELECT parent_id, child_id, depth FROM cte_project_hierarchy] at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:497) at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:83) at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:185) at liquibase.executor.AbstractExecutor.execute(AbstractExecutor.java:141) at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1189) at liquibase.changelog.ChangeSet.execute(ChangeSet.java:777) at liquibase.changelog.visitor.UpdateVisitor.executeAcceptedChange(UpdateVisitor.java:126) at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:70) at liquibase.changelog.ChangeLogIterator.lambda$run$0(ChangeLogIterator.java:133) at liquibase.Scope.lambda$child$0(Scope.java:194) at liquibase.Scope.child(Scope.java:203) at liquibase.Scope.child(Scope.java:193) at liquibase.Scope.child(Scope.java:172) at liquibase.changelog.ChangeLogIterator.lambda$run$1(ChangeLogIterator.java:122) at liquibase.Scope.lambda$child$0(Scope.java:194) at liquibase.Scope.child(Scope.java:203) at liquibase.Scope.child(Scope.java:193) at liquibase.Scope.child(Scope.java:172) at liquibase.Scope.child(Scope.java:260) at liquibase.Scope.child(Scope.java:264) at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:91) at liquibase.command.core.AbstractUpdateCommandStep.lambda$run$0(AbstractUpdateCommandStep.java:114) at liquibase.Scope.lambda$child$0(Scope.java:194) at liquibase.Scope.child(Scope.java:203) at liquibase.Scope.child(Scope.java:193) at liquibase.Scope.child(Scope.java:172) at liquibase.command.core.AbstractUpdateCommandStep.run(AbstractUpdateCommandStep.java:112) at liquibase.command.core.UpdateCommandStep.run(UpdateCommandStep.java:100) at liquibase.command.CommandScope.lambda$execute$6(CommandScope.java:231) at liquibase.Scope.child(Scope.java:203) at liquibase.Scope.child(Scope.java:179) at liquibase.command.CommandScope.execute(CommandScope.java:219) at org.dependencytrack.persistence.migration.MigrationInitializer.lambda$runMigration$0(MigrationInitializer.java:125) at liquibase.Scope.lambda$child$0(Scope.java:194) at liquibase.Scope.child(Scope.java:203) at liquibase.Scope.child(Scope.java:193) at liquibase.Scope.child(Scope.java:172) at org.dependencytrack.persistence.migration.MigrationInitializer.runMigration(MigrationInitializer.java:117) at org.dependencytrack.persistence.migration.MigrationInitializer.runMigration(MigrationInitializer.java:108) at org.dependencytrack.persistence.migration.MigrationInitializer.contextInitialized(MigrationInitializer.java:88) at org.eclipse.jetty.ee10.servlet.ServletContextHandler.callContextInitialized(ServletContextHandler.java:1587) at org.eclipse.jetty.ee10.servlet.ServletContextHandler.contextInitialized(ServletContextHandler.java:498) at org.eclipse.jetty.ee10.servlet.ServletHandler.initialize(ServletHandler.java:670) at org.eclipse.jetty.ee10.servlet.ServletContextHandler.startContext(ServletContextHandler.java:1321) at org.eclipse.jetty.ee10.webapp.WebAppContext.startWebapp(WebAppContext.java:1346) at org.eclipse.jetty.ee10.webapp.WebAppContext.startContext(WebAppContext.java:1304) at org.eclipse.jetty.ee10.servlet.ServletContextHandler.lambda$doStart$0(ServletContextHandler.java:1047) at org.eclipse.jetty.server.handler.ContextHandler$ScopedContext.call(ContextHandler.java:1452) at org.eclipse.jetty.ee10.servlet.ServletContextHandler.doStart(ServletContextHandler.java:1044) at org.eclipse.jetty.ee10.webapp.WebAppContext.doStart(WebAppContext.java:503) at org.eclipse.jetty.util.component.AbstractLifeCycle.start(AbstractLifeCycle.java:93) at org.eclipse.jetty.util.component.ContainerLifeCycle.start(ContainerLifeCycle.java:169) at org.eclipse.jetty.server.Server.start(Server.java:641) at org.eclipse.jetty.util.component.ContainerLifeCycle.doStart(ContainerLifeCycle.java:120) at org.eclipse.jetty.server.Handler$Abstract.doStart(Handler.java:491) at org.eclipse.jetty.server.Server.doStart(Server.java:582) at org.eclipse.jetty.util.component.AbstractLifeCycle.start(AbstractLifeCycle.java:93) at alpine.embedded.EmbeddedJettyServer.main(EmbeddedJettyServer.java:131)
We think it could be linked to this PR: #1700
Not sure at all but it might miss a "is not null" where clause on parent_id in this INSERT SELECT statement (line 823):
INSERT INTO "PROJECT_HIERARCHY" ("PARENT_PROJECT_ID", "CHILD_PROJECT_ID", "DEPTH") SELECT parent_id, child_id, depth FROM cte_project_hierarchy;
Steps to Reproduce
- Have the Hyades database in version 5.5.0 with some rows in table "PROJECT" that does not have a "PARENT_PROJECT_ID"
- Attempt to upgrade to snapshot version (5.6.0)
Expected Behavior
Migration should work without error.
Hyades Version
From api-server:5.5.0 to api-server:snapshot
Repository Type
Hyades apiserver
Browser
N/A
Checklist
- I have read and understand the contributing guidelines
- I have checked the existing issues for whether this defect was already reported