Message: Executing DDL commands failed. after migration from Modeler 3.3.1 to 4.0.1

2
After migration of my application from Mendix Modeler 3.3.1 to 4.0.1, the modeler asks me if I want to synchronize the database. This process takes about approx. 25 minutes, resulting in the following error: Request action: executeddlcommands Message: Executing DDL commands failed. Cause: Error (SQL State: 23502, Error Code: 0) on executing: UPDATE "system$userroles" SET "system$userid" = ("system$userid" | (SELECT CASE "0eefa65d80b84a1aa14b0e211f066183"."submetaobjectname" WHEN 'Administration.Account' THEN 281474976710656 WHEN 'Configuration.User' THEN 2814749767106560 ELSE 23080948090273792 END FROM "system$user" "0eefa65d80b84a1aa14b0e211f066183" WHERE "0eefa65d80b84a1aa14b0e211f066183"."id" = "system$userroles"."system$userid")), "system$userroleid" = ("system$userroleid" | 23643898043695104) All changes are rolled back. Stack trace: com.mendix.m2ee.api.AdminException: Executing DDL commands failed. at com.mendix.core.MxRuntime.d(SourceFile:528) Caused by: l: Error (SQL State: 23502, Error Code: 0) on executing: UPDATE "system$userroles" SET "system$userid" = ("system$userid" | (SELECT CASE "0eefa65d80b84a1aa14b0e211f066183"."submetaobjectname" WHEN 'Administration.Account' THEN 281474976710656 WHEN 'Configuration.User' THEN 2814749767106560 ELSE 23080948090273792 END FROM "system$user" "0eefa65d80b84a1aa14b0e211f066183" WHERE "0eefa65d80b84a1aa14b0e211f066183"."id" = "system$userroles"."system$userid")), "system$userroleid" = ("system$userroleid" | 23643898043695104) All changes are rolled back. at bq.a(SourceFile:86) Caused by: org.postgresql.util.PSQLException: ERROR: null value in column "system$userid" violates not-null constraint at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2103) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1836) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:366) at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264) at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264) at bq.a(SourceFile:177) at bq.a(SourceFile:48) at h.a(SourceFile:196) at h.a(SourceFile:191) at h.c(SourceFile:172) at com.mendix.core.MxRuntime.d(SourceFile:523) at fJ.a(SourceFile:30) at fO.execute(SourceFile:26) at com.mendix.m2ee.server.handler.HttpAdminHandler.handle(HttpAdminHandler.java:121) at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:111) at org.eclipse.jetty.server.Server.handle(Server.java:351) at org.eclipse.jetty.server.AbstractHttpConnection.handleRequest(AbstractHttpConnection.java:454) at org.eclipse.jetty.server.AbstractHttpConnection.headerComplete(AbstractHttpConnection.java:890) at org.eclipse.jetty.server.AbstractHttpConnection$RequestHandler.headerComplete(AbstractHttpConnection.java:944) at org.eclipse.jetty.http.HttpParser.parseNext(HttpParser.java:642) at org.eclipse.jetty.http.HttpParser.parseAvailable(HttpParser.java:230) at org.eclipse.jetty.server.AsyncHttpConnection.handle(AsyncHttpConnection.java:77) at org.eclipse.jetty.io.nio.SelectChannelEndPoint.handle(SelectChannelEndPoint.java:609) at org.eclipse.jetty.io.nio.SelectChannelEndPoint$1.run(SelectChannelEndPoint.java:45) at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:599) at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:534) at java.lang.Thread.run(Thread.java:662) What can I do to solve this error? Thanks Hans
asked
2 answers
2

I've seen this happen before but so far we haven't found the root cause of the issue. But the exception is caused by the system$userroles table in the database having ids that refer to users that no longer exist. Normally the ids in the association tables are also cleared out when users are rdeleted but it seems that something is broken here.

During update to 4.x all ids are being changed but this fails when there are incorrect associations in the system$userroles table.

If you are familiar with SQL you can check which user ids in the system$userroles table refer to users that aren't actually present in system$user and remove those.

answered
1

Are you still able to open the project in the v3.3.1 modeler? If so, check that you do not have any empty rows in your Account or User table. If you do, delete them and try the upgrade again.

The error seems to be that the upgrade is trying to set a relationship between a Role and User record where the user record has no data. So this sounds like there are empty rows in your User table, which can happen if you tried to create new user records, then abandoned and the transaction was not properly rolled back. This situation is avoided in the new version because new records are now only created in memory before they are committed.

answered