[Vtigercrm-developers] Checking database after migration
Uma S
uma.s at vtiger.com
Sun Jan 10 13:46:17 GMT 2021
Hi Yannick,
Happy New Year!
Even after so many trails of migration as you're still facing the issue.
Let's try the process of migration from 6.5 -> 7.0 with logging and
PearDatabase setting and see if you could reproduce the same flow.
I assume your server settings satisfy the guidelines
<https://community.vtiger.com/help/vtigercrm/administrators/index.html>.
On Mon, Jan 4, 2021 at 7:07 PM Yannick Berges <micker at free.fr> wrote:
> hello and happy new year for all vtiger customers !
>
> i continu to fight with vtiger migration i don't understand why i soo much
> error .... (and migration script say that all is ok)
>
> i have this error after 6.5-7.0
>
> Mon Jan 4 11:06:00 2021,419 [9157] INFO VT - PearDatabase ->ADODB error
> Query Failed:ALTER TABLE vtiger_seattachmentsrel ADD CONSTRAINT
> fk_2_vtiger_seattachmentsrel FOREIGN KEY (crmid) REFERENCES
> vtiger_crmentity(crmid) ON DELETE CASCADE::->[1005]Can't create table
> `comehbjd_vtig923`.`vtiger_seattachmentsrel` (errno: 121 "Duplicate key on
> write or update")
>
> Mon Jan 4 11:06:09 2021,553 [9157] INFO VT - PearDatabase ->ADODB error
> Query Failed:ALTER TABLE vtiger_module_dashboard_widgets ADD COLUMN
> dashboardtabid INT(11) DEFAULT ::->[1064]You have an error in your SQL
> syntax; check the manual that corresponds to your MariaDB server version
> for the right syntax to use near '' at line 1
>
> Mon Jan 4 11:06:09 2021,554 [9157] INFO VT - PearDatabase ->ADODB error
> Query Failed:ALTER TABLE vtiger_module_dashboard_widgets ADD CONSTRAINT
> FOREIGN KEY (dashboardtabid) REFERENCES vtiger_dashboard_tabs(id) ON DELETE
> CASCADE::->[1072]Key column 'dashboardtabid' doesn't exist in table
>
> Mon Jan 4 11:06:11 2021,169 [9157] INFO VT - PearDatabase ->ADODB error
> Query Failed:ALTER TABLE vtiger_cvstdfilter DROP KEY
> cvstdfilter_cvid_idx::->[1553]Cannot drop index 'cvstdfilter_cvid_idx':
> needed in a foreign key constraint
> Mon Jan 4 11:06:11 2021,180 [9157] INFO VT - PearDatabase ->ADODB error
> Query Failed:ALTER TABLE vtiger_cvstdfilter ADD CONSTRAINT
> fk_1_vtiger_cvstdfilter FOREIGN KEY (cvid) REFERENCES
> vtiger_customview(cvid) ON DELETE CASCADE::->[1005]Can't create table
> `comehbjd_vtig923`.`vtiger_cvstdfilter` (errno: 121 "Duplicate key on write
> or update")
>
> Mon Jan 4 11:06:13 2021,278 [9157] INFO VT - PearDatabase ->ADODB error
> Query Failed:ALTER TABLE vtiger_notescf ADD CONSTRAINT
> fk_notesid_vtiger_notescf FOREIGN KEY (notesid) REFERENCES vtiger_notes
> (notesid) ON DELETE CASCADE::->[1452]Cannot add or update a child row: a
> foreign key constraint fails (`comehbjd_vtig923`.`#sql-250f_256b25`,
> CONSTRAINT `fk_notesid_vtiger_notescf` FOREIGN KEY (`notesid`) REFERENCES
> `vtiger_notes` (`notesid`) ON DELETE CASCADE)
>
> Mon Jan 4 11:06:13 2021,611 [9157] INFO VT - PearDatabase ->ADODB error
> Query Failed:ALTER TABLE vtiger_activitycf ADD CONSTRAINT
> fk_activityid_vtiger_activitycf FOREIGN KEY (activityid) REFERENCES
> vtiger_activity (activityid) ON DELETE CASCADE::->[1452]Cannot add or
> update a child row: a foreign key constraint fails
> (`comehbjd_vtig923`.`#sql-250f_256b25`, CONSTRAINT
> `fk_activityid_vtiger_activitycf` FOREIGN KEY (`activityid`) REFERENCES
> `vtiger_activity` (`activityid`) ON DELETE CASCADE)
> Mon Jan 4 11:06:15 2021,242 [9157] INFO VT - PearDatabase ->ADODB error
> Query Failed:ALTER TABLE vtiger_quotes ADD CONSTRAINT
> fk_crmid_vtiger_quotes FOREIGN KEY (quoteid) REFERENCES vtiger_crmentity
> (crmid) ON DELETE CASCADE::->[1452]Cannot add or update a child row: a
> foreign key constraint fails (`comehbjd_vtig923`.`#sql-250f_256b25`,
> CONSTRAINT `fk_crmid_vtiger_quotes` FOREIGN KEY (`quoteid`) REFERENCES
> `vtiger_crmentity` (`crmid`) ON DELETE CASCADE)
>
> Mon Jan 4 11:06:15 2021,524 [9157] INFO VT - PearDatabase ->ADODB error
> Query Failed:ALTER TABLE vtiger_inventoryproductrel ADD CONSTRAINT
> fk_crmid_vtiger_inventoryproductrel FOREIGN KEY (id) REFERENCES
> vtiger_crmentity (crmid) ON DELETE CASCADE::->[1452]Cannot add or update a
> child row: a foreign key constraint fails
> (`comehbjd_vtig923`.`#sql-250f_256b25`, CONSTRAINT
> `fk_crmid_vtiger_inventoryproductrel` FOREIGN KEY (`id`) REFERENCES
> `vtiger_crmentity` (`crmid`) ON DELETE CASCADE)
> Mon Jan 4 11:06:15 2021,778 [9157] INFO VT - PearDatabase ->ADODB error
> Query Failed:ALTER TABLE vtiger_salesorder ADD CONSTRAINT
> fk_crmid_vtiger_salesorder FOREIGN KEY (salesorderid) REFERENCES
> vtiger_crmentity (crmid) ON DELETE CASCADE::->[1452]Cannot add or update a
> child row: a foreign key constraint fails
> (`comehbjd_vtig923`.`#sql-250f_256b25`, CONSTRAINT
> `fk_crmid_vtiger_salesorder` FOREIGN KEY (`salesorderid`) REFERENCES
> `vtiger_crmentity` (`crmid`) ON DELETE CASCADE)
>
> Mon Jan 4 11:06:15 2021,524 [9157] INFO VT - PearDatabase ->ADODB error
> Query Failed:ALTER TABLE vtiger_inventoryproductrel ADD CONSTRAINT
> fk_crmid_vtiger_inventoryproductrel FOREIGN KEY (id) REFERENCES
> vtiger_crmentity (crmid) ON DELETE CASCADE::->[1452]Cannot add or update a
> child row: a foreign key constraint fails
> (`comehbjd_vtig923`.`#sql-250f_256b25`, CONSTRAINT
> `fk_crmid_vtiger_inventoryproductrel` FOREIGN KEY (`id`) REFERENCES
> `vtiger_crmentity` (`crmid`) ON DELETE CASCADE)
> Mon Jan 4 11:06:15 2021,778 [9157] INFO VT - PearDatabase ->ADODB error
> Query Failed:ALTER TABLE vtiger_salesorder ADD CONSTRAINT
> fk_crmid_vtiger_salesorder FOREIGN KEY (salesorderid) REFERENCES
> vtiger_crmentity (crmid) ON DELETE CASCADE::->[1452]Cannot add or update a
> child row: a foreign key constraint fails
> (`comehbjd_vtig923`.`#sql-250f_256b25`, CONSTRAINT
> `fk_crmid_vtiger_salesorder` FOREIGN KEY (`salesorderid`) REFERENCES
> `vtiger_crmentity` (`crmid`) ON DELETE CASCADE)
> Mon Jan 4 11:06:16 2021,076 [9157] INFO VT - PearDatabase ->ADODB error
> Query Failed:ALTER TABLE vtiger_inventoryproductrel ADD CONSTRAINT
> fk_crmid_vtiger_inventoryproductrel FOREIGN KEY (id) REFERENCES
> vtiger_crmentity (crmid) ON DELETE CASCADE::->[1452]Cannot add or update a
> child row: a foreign key constraint fails
> (`comehbjd_vtig923`.`#sql-250f_256b25`, CONSTRAINT
> `fk_crmid_vtiger_inventoryproductrel` FOREIGN KEY (`id`) REFERENCES
> `vtiger_crmentity` (`crmid`) ON DELETE CASCADE)
> Mon Jan 4 11:06:16 2021,261 [9157] INFO VT - PearDatabase ->ADODB error
> Query Failed:ALTER TABLE vtiger_invoice ADD CONSTRAINT
> fk_crmid_vtiger_invoice FOREIGN KEY (invoiceid) REFERENCES vtiger_crmentity
> (crmid) ON DELETE CASCADE::->[1452]Cannot add or update a child row: a
> foreign key constraint fails (`comehbjd_vtig923`.`#sql-250f_256b25`,
> CONSTRAINT `fk_crmid_vtiger_invoice` FOREIGN KEY (`invoiceid`) REFERENCES
> `vtiger_crmentity` (`crmid`) ON DELETE CASCADE)
> Mon Jan 4 11:06:16 2021,478 [9157] INFO VT - PearDatabase ->ADODB error
> Query Failed:ALTER TABLE vtiger_inventoryproductrel ADD CONSTRAINT
> fk_crmid_vtiger_inventoryproductrel FOREIGN KEY (id) REFERENCES
> vtiger_crmentity (crmid) ON DELETE CASCADE::->[1452]Cannot add or update a
> child row: a foreign key constraint fails
> (`comehbjd_vtig923`.`#sql-250f_256b25`, CONSTRAINT
> `fk_crmid_vtiger_inventoryproductrel` FOREIGN KEY (`id`) REFERENCES
> `vtiger_crmentity` (`crmid`) ON DELETE CASCADE)
> Mon Jan 4 11:06:16 2021,736 [9157] INFO VT - PearDatabase ->ADODB error
> Query Failed:ALTER TABLE vtiger_campaign ADD CONSTRAINT
> fk_crmid_vtiger_campaign FOREIGN KEY (campaignid) REFERENCES
> vtiger_crmentity (crmid) ON DELETE CASCADE::->[1452]Cannot add or update a
> child row: a foreign key constraint fails
> (`comehbjd_vtig923`.`#sql-250f_256b25`, CONSTRAINT
> `fk_crmid_vtiger_campaign` FOREIGN KEY (`campaignid`) REFERENCES
> `vtiger_crmentity` (`crmid`) ON DELETE CASCADE)
> Mon Jan 4 11:06:17 2021,127 [9157] INFO VT - PearDatabase ->ADODB error
> Query Failed:ALTER TABLE vtiger_producttaxrel ADD CONSTRAINT
> fk_crmid_vtiger_producttaxrel FOREIGN KEY (productid) REFERENCES
> vtiger_crmentity (crmid) ON DELETE CASCADE::->[1452]Cannot add or update a
> child row: a foreign key constraint fails
> (`comehbjd_vtig923`.`#sql-250f_256b25`, CONSTRAINT
> `fk_crmid_vtiger_producttaxrel` FOREIGN KEY (`productid`) REFERENCES
> `vtiger_crmentity` (`crmid`) ON DELETE CASCADE)
> Mon Jan 4 11:06:17 2021,344 [9157] INFO VT - PearDatabase ->ADODB error
> Query Failed:ALTER TABLE vtiger_servicecontracts ADD CONSTRAINT
> fk_crmid_vtiger_servicecontracts FOREIGN KEY (servicecontractsid)
> REFERENCES vtiger_crmentity (crmid) ON DELETE CASCADE::->[1452]Cannot add
> or update a child row: a foreign key constraint fails
> (`comehbjd_vtig923`.`#sql-250f_256b25`, CONSTRAINT
> `fk_crmid_vtiger_servicecontracts` FOREIGN KEY (`servicecontractsid`)
> REFERENCES `vtiger_crmentity` (`crmid`) ON DELETE CASCADE)
> Mon Jan 4 11:06:17 2021,541 [9157] INFO VT - PearDatabase ->ADODB error
> Query Failed:ALTER TABLE vtiger_servicecontractscf ADD CONSTRAINT
> fk_servicecontractsid_vtiger_servicecontractscf FOREIGN KEY
> (servicecontractsid) REFERENCES vtiger_servicecontracts
> (servicecontractsid) ON DELETE CASCADE::->[1005]Can't create table
> `comehbjd_vtig923`.`vtiger_servicecontractscf` (errno: 150 "Foreign key
> constraint is incorrectly formed")
> Mon Jan 4 11:06:17 2021,611 [9157] INFO VT - PearDatabase ->ADODB error
> Query Failed:ALTER TABLE vtiger_servicecontracts_user_field ADD CONSTRAINT
> fk_servicecontractsid_vtiger_servicecontracts_user_field FOREIGN KEY
> (recordid) REFERENCES vtiger_servicecontracts (servicecontractsid) ON
> DELETE CASCADE::->[1005]Can't create table
> `comehbjd_vtig923`.`vtiger_servicecontracts_user_field` (errno: 150
> "Foreign key constraint is incorrectly formed")
>
> Mon Jan 4 11:07:01 2021,367 [10408] INFO VT - PearDatabase ->ADODB error
> Query Failed: SELECT vtiger_links.*,
> vtiger_module_dashboard_widgets.userid,
> vtiger_module_dashboard_widgets.filterid,
> vtiger_module_dashboard_widgets.data, vtiger_module_dashboard_widgets.id
> as widgetid, vtiger_module_dashboard_widgets.position as position,
> vtiger_links.linkid as id FROM vtiger_links INNER JOIN
> vtiger_module_dashboard_widgets ON
> vtiger_links.linkid=vtiger_module_dashboard_widgets.linkid WHERE
> vtiger_module_dashboard_widgets.userid = ? AND linktype = ? AND tabid = ?
> AND dashboardtabid = ?::->[1054]Unknown column 'dashboardtabid' in 'where
> clause'
> Mon Jan 4 11:07:01 2021,368 [10408] INFO VT - PearDatabase ->ADODB error
> Query Failed:SELECT reportid FROM vtiger_module_dashboard_widgets WHERE
> userid = ? AND linkid= ? AND reportid IS NOT NULL AND dashboardtabid =
> ?::->[1054]Unknown column 'dashboardtabid' in 'where clause'
>
> Le 23/12/2020 à 13:55, micker at free.fr a écrit :
>
> You mean i need to check update file to check ?
> Or maybe i Can check and repaire data base before update ?
>
> Le 23 déc. 2020 13:24, Alan Lord <alanslists at gmail.com>
> <alanslists at gmail.com> a écrit :
>
> On 23/12/2020 08:51, Yannick Berges wrote:
> > ok but why this errors ? no idea ?
> >
> > in fact i need to checl all error and add redo sql request manualy that
> it ?
> >
> > how to be sure all php file remplacement are good ? like sql ?
> >
> > regards
>
> In my experience a lot of the foreign key constraint errors occur when
> you are migrating a fairly old database and some of the tables that are
> to be constrained together do not have matching rows. Typically this
> causes the creation of a few (4?) tables for custom filters to fail
> completely. The problems caused by this only show up to non-admin users.
>
> For the first error regarding a missing dashboardtabid column, you need
> to go back further and find out where, in the migration process, it was
> supposed to have been created in the first place and then why it was not.
>
> HTH
>
> Al
>
> _______________________________________________
> http://www.vtiger.com/
>
>
>
> _______________________________________________http://www.vtiger.com/
>
>
>
> <http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient> Garanti
> sans virus. www.avg.com
> <http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient>
> <#m_5237369874338934479_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
> _______________________________________________
> http://www.vtiger.com/
--
With
Best Regards
Uma.S
Vtiger Team
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20210110/8bd158d8/attachment-0001.html>
More information about the vtigercrm-developers
mailing list