Mendix database syn issue after migration from 4 to 5

0
Hi, We have migrated our Mendix project from 4.4 to 5.21.5 .After migration(through modeller), we are trying to deploy the deployment package (.mda file created using modeller 5.21.5) in production.While starting the Mendix console, it starts the database syn up  but the syn up is getting failed beacuse of the invalid queries that Medix is generating. I checked the queries(DDL commands), it is trying to create new indexes in the association tables.Already there are indexes with primary key constraint in those tables.Mendix query is just to add new constarint , ideally the query should include drooping off the primary constarint followed by dropping of index and then creating new primary key with new index. It is creating queries like mentioned below- ALTER TABLE "MENDIXSYSTEM$ASSOCIATION"     ADD "PK_INDEX_NAME" nvarchar2(255) NULL;          UPDATE "MENDIXSYSTEM$VERSION"  SET "PREANALYSISMIGRATIONVERSIONNUM" = '2.0.1';    ALTER TABLE "DECISI$EMPLOYEE_APLLICATION" MODIFY PRIMARY KEY USING INDEX ( CREATE UNIQUE INDEX "PKIDX_DECIS$EMPLOYEE_APLLICATI" ON "DECISI$EMPLOYEE_APLLICATION" ("DECISIONS$DECISIONHISTORYID", "DECISIONS$APPLICATIONROUTINGID")); UPDATE "MENDIXSYSTEM$ASSOCIATION" SET "PK_INDEX_NAME" = 'PKIDX_DECIS$EMPLOYEE_APLLICATI' WHERE "ID" = '5c79d155-fdc7-4012-bfed-84b3c4a172d8'; ALTER TABLE "MOIEGYPTUTIL$MOICALLCONF_CLIEN" MODIFY PRIMARY KEY USING INDEX ( CREATE UNIQUE INDEX "PKIDX_MOIEGYPTU$MOICALLCO_CLIE" ON "MOIEGYPTUTIL$MOICALLCONF_CLIEN" ("MOIEGYPTUTILIT$MOICALLCONFIGID", "ADMINISTRATION$CLIENTID")); UPDATE "MENDIXSYSTEM$ASSOCIATION" SET "PK_INDEX_NAME" = 'PKIDX_MOIEGYPTU$MOICALLCO_CLIE' WHERE "ID" = 'dd8251e5-374e-436b-a18a-b416486b1751';   The commands highlighted bold with italicas are not able to execute.The error coming is - SQL Error: ORA-01408: such column list already indexed 01408. 00000 -  "such column list already indexed". This error is coming as the tables have indexes already with primary key.To create new index it should first drop the primary key along with index and then create new primary key with index. There are so many DDL commnads that have the problem. Has anyone faced such type of issue in migration and what could be the possible resolution for this issue? There is one workaroung that I can think is to copy the data syn up queries , correct them, add missing DDL commands and  run them one be one manually in SQL developer. I am not sure whether it will create any problem in the database or not and following this approach will have a large downtime of production database. Please let me know if there is any solution for this issue.  Regards, Himanshu Singh
asked
2 answers
0

This is probably a Mendix bug. However, it could also be the case that you're using an Oracle version that is not supported by Mendix.

Some databases automatically create an index for keys, and some don't. I'm guessing Mendix incorrectly assumes that Oracle does not, while in fact it does.

Your suggested workaround of manually executing the correct commands should work as a workaround (and this will not really result in any additional downtime, since you should prepare and test your ddl update script anyway).

answered
0

Hi Nikel,

Many thanks for your reply. In UAT we never faced this issue.It went fine.Only differnce is in UAT we had successive deployments one by one.In production we are directly doing the deployment of the latest mda file created with Modeller 5.21.5.

We have couple of UAT servers one in Mendix 4 and other one in Mendix 5. We have done functional changes in Mendix 4 and migration changes in Mendix 5  using different branch lines and then we finally Merged both the branches.

For example, we had  deployed MDA file “100” in UAT A (Mendix4) and  production(Mendix 4)   . After that we have deployed mda 101, 102 till 105 in UAT server A (Mendix4) and then we merged the code and  deployed mda 106 , 107 till 109 in UAT server B (Mendix 5).

Since this issue didn't occure in UAT server B, I followed the same approach in pre production environement. I delpoyed mda starting from 101  till 105 in Mendix 4 in  pre production server, then I upgraded the Mendix console to 4.4 and mendix server to 5.21.5 and then deployed mda 106.

While deploying mda 106 it started to give issues in data syn up.

Not sure why it woked in UAT and why not in pre production environment. May be there are some restrictions on the database as you suggested.

 

Regards,

Himanshu Singh

 

answered