<div dir="ltr">Hi Yannick,<div><br></div><div>Happy New Year!</div><div><br></div><div>Even after so many trails of migration as you're still facing the issue. <br></div><div><br></div><div>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.</div><div><br></div><div>I assume your server settings satisfy the <a href="https://community.vtiger.com/help/vtigercrm/administrators/index.html">guidelines</a>.</div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Mon, Jan 4, 2021 at 7:07 PM Yannick Berges <<a href="mailto:micker@free.fr">micker@free.fr</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div>
<p>hello and happy new year for all vtiger customers !</p>
<p>i continu to fight with vtiger migration i don't understand why i
soo much error .... (and migration script say that all is ok)<br>
</p>
<p>i have this error after 6.5-7.0</p>
<p>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")</p>
<p>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</p>
<p>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</p>
<p>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<br>
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")<br>
</p>
<p>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)<br>
</p>
<p>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)<br>
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)<br>
</p>
<p>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)<br>
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)</p>
<p>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)<br>
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)<br>
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)<br>
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)<br>
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)<br>
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)<br>
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)<br>
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)<br>
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")<br>
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")<br>
</p>
<p>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,
<a href="http://vtiger_module_dashboard_widgets.id" target="_blank">vtiger_module_dashboard_widgets.id</a> 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'<br>
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'<br>
<br>
</p>
<div>Le 23/12/2020 à 13:55, <a href="mailto:micker@free.fr" target="_blank">micker@free.fr</a> a
écrit :<br>
</div>
<blockquote type="cite">
<div dir="auto">You mean i need to check update file to check ?
<div dir="auto">Or maybe i Can check and repaire data base
before update ?</div>
</div>
<div class="gmail_extra"><br>
<div class="gmail_quote">Le 23 déc. 2020 13:24, Alan Lord
<a href="mailto:alanslists@gmail.com" target="_blank"><alanslists@gmail.com></a> a écrit :<br type="attribution">
<blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<p dir="ltr">On 23/12/2020 08:51, Yannick Berges wrote:
<br>
> ok but why this errors ? no idea ?
<br>
> <br>
> in fact i need to checl all error and add redo sql
request manualy that it ?
<br>
> <br>
> how to be sure all php file remplacement are good ?
like sql ?
<br>
> <br>
> regards
<br>
<br>
In my experience a lot of the foreign key constraint
errors occur when <br>
you are migrating a fairly old database and some of the
tables that are <br>
to be constrained together do not have matching rows.
Typically this <br>
causes the creation of a few (4?) tables for custom
filters to fail <br>
completely. The problems caused by this only show up to
non-admin users.
<br>
<br>
For the first error regarding a missing dashboardtabid
column, you need <br>
to go back further and find out where, in the migration
process, it was <br>
supposed to have been created in the first place and then
why it was not.
<br>
<br>
HTH
<br>
<br>
Al
<br>
<br>
_______________________________________________
<br>
<a href="http://www.vtiger.com/" target="_blank">http://www.vtiger.com/</a>
<br>
</p>
</blockquote>
</div>
<br>
</div>
<br>
<fieldset></fieldset>
<pre>_______________________________________________
<a href="http://www.vtiger.com/" target="_blank">http://www.vtiger.com/</a></pre>
</blockquote>
<div id="gmail-m_5237369874338934479grammalecte_menu_main_button_shadow_host" style="width:0px;height:0px"></div>
<div id="gmail-m_5237369874338934479DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2"><br> <table style="border-top:1px solid rgb(211,212,222)">
<tbody><tr>
<td style="width:55px;padding-top:18px"><a href="http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient" target="_blank"><img src="https://ipmcdn.avast.com/images/icons/icon-envelope-tick-green-avg-v1.png" alt="" width="46" height="29" style="width: 46px; height: 29px;"></a></td>
<td style="width:470px;padding-top:17px;color:rgb(65,66,78);font-size:13px;font-family:Arial,Helvetica,sans-serif;line-height:18px">Garanti sans virus. <a href="http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient" style="color:rgb(68,83,234)" target="_blank">www.avg.com</a> </td>
</tr>
</tbody></table>
<a href="#m_5237369874338934479_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2" width="1" height="1"> </a></div></div>
_______________________________________________<br>
<a href="http://www.vtiger.com/" rel="noreferrer" target="_blank">http://www.vtiger.com/</a></blockquote></div><br clear="all"><div><br></div>-- <br><div dir="ltr" class="gmail_signature"><div dir="ltr">With<br>Best Regards<br>Uma.S<br><div>Vtiger Team</div></div></div>