Problem migrating from MySQL to PostgreSQL
27 views
Skip to first unread message
Kevin Klein
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
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
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.
Kevin Klein
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".
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>
<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
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
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.
To view this discussion on the web visit https://groups.google.com/d/msgid/dotcms/ede078dc-f71a-4149-bdad-c9c9f2c05610n%40googlegroups.com.
Reply all
Reply to author
Forward
0 new messages