Problem migrating from MySQL to PostgreSQL

13 views
Skip to first unread message

Kevin Klein

unread,
Sep 12, 2022, 5:50:39 PM9/12/22
to dotCMS User Group
So I have a dotCMS site running version 5.3.8.13 with a MySQL 5.7 database.
I'd like to upgrade this to PostgreSQL, so I followed the steps below:
1. Backup the database
2. Shut down dotCMS
3. Copy the backup ZIP to webapps/ROOT/starter.zip
4. Edit the db.properties to change the db connection to PostgreSQL with a blank database.
5.Start dotCMS

Looking in pgAdmin, it appears that all of the tables are being created without issue, but the import of the data into the tables is failing with the stack trace below. Is this something I can fix?

21:41:45.791  INFO  util.ImportStarterUtil - **** Importing the file: /opt/dotcms/wwwroot/dotcms-5.3.8.13/dotserver/tomcat-8.5.32/webapps/ROOT/dotsecure/backup/temp/com.dotmarketing.portlets.contentlet.business.Contentlet_0000001000.xml *****
21:41:45.791  INFO  util.ImportStarterUtil - Importing: com.dotmarketing.portlets.contentlet.business.Contentlet
21:41:46.981  INFO  util.ImportStarterUtil - Found :    1000 com.dotmarketing.portlets.contentlet.business.Contentlet(s)
21:41:51.292  WARN  util.JDBCExceptionReporter - SQL Error: 0, SQLState: 23503
21:41:51.292  ERROR util.JDBCExceptionReporter - ERROR: insert or update on table "contentlet" violates foreign key constraint "fk_structure_inode"
  Detail: Key (structure_inode)=(cb1f21c7-7d98-4ea3-903f-c5de10e41700) is not present in table "structure".
21:41:51.293  ERROR impl.SessionImpl - Could not synchronize database state with session
21:41:51.293  WARN  util.ImportStarterUtil - Usually not a problem can be that duplicate data or many times a row of data that is created by the system and is trying to be imported again : Unable to save Object with primary key 8527f6cf-df61-42b9-a2bd-3779f6a1f935 to Hibernate Session
21:41:51.300  INFO  util.ImportStarterUtil - Problematic object: com.dotmarketing.portlets.contentlet.business.Contentlet@62b99c02[fAPI=com.dotmarketing.portlets.structure.business.FieldAPIImpl@275ff3e2,structureInode=cb1f21c7-7d98-4ea3-903f-c5de10e41700,languageId=1,categories=<null>,reviewInterval=<null>,lastReview=<null>,nextReview=<null>,disabledWysiwyg=<null>,date1=<null>,date2=<null>,date3=<null>,date4=<null>,date5=<null>,date6=<null>,date7=<null>,date8=<null>,date9=<null>,date10=<null>,date11=<null>,date12=<null>,date13=<null>,date14=<null>,date15=<null>,date16=<null>,date17=<null>,date18=<null>,date19=<null>,date20=<null>,date21=<null>,date22=<null>,date23=<null>,date24=<null>,date25=<null>,text1=<null>,text2=Jason,text3=Smith,text4=,text5=,text6=,text7=,text8=ja...@dotcms.com,text9=,text10=,text11=,text12=A,text13=<null>,text14=<null>,text15=<null>,text16=<null>,text17=<null>,text18=<null>,text19=<null>,text20=<null>,text21=<null>,text22=<null>,text23=<null>,text24=<null>,text25=<null>,text_area1=,text_area2=<null>,text_area3=<null>,text_area4=<null>,text_area5=<null>,text_area6=<null>,text_area7=<null>,text_area8=<null>,text_area9=<null>,text_area10=<null>,text_area11=<null>,text_area12=<null>,text_area13=<null>,text_area14=<null>,text_area15=<null>,text_area16=<null>,text_area17=<null>,text_area18=<null>,text_area19=<null>,text_area20=<null>,text_area21=<null>,text_area22=<null>,text_area23=<null>,text_area24=<null>,text_area25=<null>,integer1=0,integer2=0,integer3=0,integer4=0,integer5=0,integer6=0,integer7=0,integer8=0,integer9=0,integer10=0,integer11=0,integer12=0,integer13=0,integer14=0,integer15=0,integer16=0,integer17=0,integer18=0,integer19=0,integer20=0,integer21=0,integer22=0,integer23=0,integer24=0,integer25=0,float1=0.0,float2=0.0,float3=0.0,float4=0.0,float5=0.0,float6=0.0,float7=0.0,float8=0.0,float9=0.0,float10=0.0,float11=0.0,float12=0.0,float13=0.0,float14=0.0,float15=0.0,float16=0.0,float17=0.0,float18=0.0,float19=0.0,float20=0.0,float21=0.0,float22=0.0,float23=0.0,float24=0.0,float25=0.0,bool1=false,bool2=false,bool3=false,bool4=false,bool5=false,bool6=false,bool7=false,bool8=false,bool9=false,bool10=false,bool11=false,bool12=false,bool13=false,bool14=false,bool15=false,bool16=false,bool17=false,bool18=false,bool19=false,bool20=false,bool21=false,bool22=false,bool23=false,bool24=false,bool25=false,source=DB,title=Jason,friendlyName=Jason,modDate=2012-11-14 20:22:07.0,modUser=anonymous,sortOrder=0,showOnMenu=false,iDate=2012-11-14 20:22:07.0,type=contentlet,owner=anonymous,inode=8527f6cf-df61-42b9-a2bd-3779f6a1f935,identifier=ac2be7e9-cb13-4999-97ac-2c53bbbb8561] prop:8527f6cf-df61-42b9-a2bd-3779f6a1f935

Will Ezell

unread,
Sep 12, 2022, 6:37:26 PM9/12/22
to dot...@googlegroups.com
Maybe try running the fix asset inconsistencies and delete old assets before you export your starter?

--
http://dotcms.com - Open Source Java Content Management
---
You received this message because you are subscribed to the Google Groups "dotCMS User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dotcms+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/dotcms/014bcebe-31c5-4dfc-a90a-89c7a3736900n%40googlegroups.com.


--



382 NE 191st St #92150
Miami, Florida 33179-3899
Main: 
305-900-2001 | Direct: 978.294.9429

Kevin Klein

unread,
Sep 13, 2022, 3:16:53 PM9/13/22
to dotCMS User Group
Cleaning up old assets helped significantly, but now I'm getting stuck at a different error.

18:52:28.487  ERROR util.JDBCExceptionReporter - Batch entry 0 insert into workflow_task (creation_date, mod_date, due_date, created_by, assigned_to, belongs_to, title, description, status, webasset, language_id, id) values ('2015-04-15 03:03:09+00', '2015-04-15 03:03:09+00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2608e1a3-eecf-446b-8aef-2c2f3c863045', 0, 'ffccf156-fa4d-4e91-b3d1-c4bf0cfd570f') was aborted: ERROR: current transaction is aborted, commands ignored until end of transaction block  Call getNextException to see other errors in the batch.
18:52:28.487  WARN  util.JDBCExceptionReporter - SQL Error: 0, SQLState: 25P02

Caused by: org.postgresql.util.PSQLException: ERROR: insert or update on table "workflow_task" violates foreign key constraint "fk_workflow_task_language"
  Detail: Key (language_id)=(0) is not present in table "language".

If I look at the WorkflowTask XML in the backup ZIP, I see the workflow elements, but all are missing a languageId.

  <com.dotmarketing.portlets.workflows.model.WorkflowTask>
    <id>f89c6245-5c26-4b53-9290-b8d5b219ef52</id>
    <creationDate class="sql-timestamp">2012-07-06 04:25:15.0</creationDate>
    <modDate class="sql-timestamp">2014-10-07 00:37:46.0</modDate>
    <createdBy>e7d4e34e-5127-45fc-8123-d48b62d510e3</createdBy>
    <assignedTo>02ae46fa-cb67-4ed8-82d5-f1f9a5e1d744</assignedTo>
    <title>Inventory Report Toolkit</title>
    <description></description>
    <status>f7dc56cd-aa81-4ca8-8174-1bb30756df82</status>
    <webasset>1f15cdfd-c308-4c43-82a6-79f54e890973</webasset>
  </com.dotmarketing.portlets.workflows.model.WorkflowTask>


Looking at the workflow_task table in the database, I can clearly see that on each row language_id is set to 1.

One point of note: the backup was created on 4.3.3 and I am trying to import into 5.3.8. Would that cause this problem?

Kevin Klein

unread,
Sep 14, 2022, 2:11:06 PM9/14/22
to dotCMS User Group
Changed the approach on this and am having more success. Switch from MySQL to PostgreSQL *first*, and then upgrade to 5.3.8.

Will Ezell

unread,
Sep 14, 2022, 7:18:09 PM9/14/22
to dot...@googlegroups.com
I was going to mention, you cannot use the starter zip to upgrade.  You can use it to go from mysql to postgres, and then upgrade.  BTW, you should keep going and get to 22.03.

Reply all
Reply to author
Forward
0 new messages