vtiger_activity Email records being DELETED during migration...

I'm migrating a customer's system from vtiger 6.2.0 up to the 7.1.0. On the test system they reported some Emails missing from various records (leads, contacts etc.)

the vtiger_activity table contained ~62,000 rows before migration. Afterwards it contained ~35,000! These were activity records containing email metadata.

I've run through the migration a few times to try and work out where they were being removed and it looks like it is happening in this block of code:

http://code.vtiger.com/vtiger/vtigercrm/blob/master/modules/Migration/schema/660_to_700.php#L2172

specifically on this line:

http://code.vtiger.com/vtiger/vtigercrm/blob/master/modules/Migration/schema/660_to_700.php#L2218

In a debug log I noticed the following:

Mon Apr 30 14:44:07 2018,320 [13888] DEBUG VT - Prepared sql query being executed : SELECT 1 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                                                        WHERE CONSTRAINT_SCHEMA=? AND CONSTRAINT_NAME LIKE ?
                                                                AND TABLE_NAME=? AND COLUMN_NAME=?
                                                                AND REFERENCED_TABLE_NAME=? AND REFERENCED_COLUMN_NAME=?
Mon Apr 30 14:44:07 2018,320 [13888] DEBUG VT - Prepared sql query parameters : [vt620,%fk%,vtiger_activity,activityid,vtiger_crmentity,crmid]
Mon Apr 30 14:44:07 2018,320 [13888] DEBUG VT - Entering getColumnFields(Calendar) method ...
Mon Apr 30 14:44:07 2018,320 [13888] DEBUG VT - in getColumnFields Calendar
Mon Apr 30 14:44:07 2018,320 [13888] DEBUG VT - Exiting getColumnFields method ...
Mon Apr 30 14:44:07 2018,320 [13888] DEBUG VT - Entering getColumnFields(Calendar) method ...
Mon Apr 30 14:44:07 2018,321 [13888] DEBUG VT - in getColumnFields Calendar
Mon Apr 30 14:44:07 2018,321 [13888] DEBUG VT - Exiting getColumnFields method ...
Mon Apr 30 14:44:07 2018,321 [13888] DEBUG VT - Prepared sql query being executed : SELECT 1 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                                                        WHERE CONSTRAINT_SCHEMA=? AND CONSTRAINT_NAME LIKE ?
                                                                AND TABLE_NAME=? AND COLUMN_NAME=?
                                                                AND REFERENCED_TABLE_NAME=? AND REFERENCED_COLUMN_NAME=?
Mon Apr 30 14:44:07 2018,321 [13888] DEBUG VT - Prepared sql query parameters : [vt620,%fk%,vtiger_activity_reminder,activity_id,vtiger_activity,activityid]
Mon Apr 30 14:44:07 2018,321 [13888] DEBUG VT - Prepared sql query being executed : ALTER TABLE vtiger_activity_reminder ADD CONSTRAINT fk_activityid_vtiger_activity_reminder FOREIGN KEY (activity_id) REFERENCES vtiger_a
ctivity (activityid) ON DELETE CASCADE
Mon Apr 30 14:44:07 2018,974 [13888] DEBUG VT - Prepared sql query being executed : SELECT 1 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                                                        WHERE CONSTRAINT_SCHEMA=? AND CONSTRAINT_NAME LIKE ?
                                                                AND TABLE_NAME=? AND COLUMN_NAME=?
                                                                AND REFERENCED_TABLE_NAME=? AND REFERENCED_COLUMN_NAME=?
Mon Apr 30 14:44:07 2018,974 [13888] DEBUG VT - Prepared sql query parameters : [vt620,%fk%,vtiger_recurringevents,activityid,vtiger_activity,activityid]
Mon Apr 30 14:44:07 2018,975 [13888] DEBUG VT - Prepared sql query being executed : SELECT 1 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                                                        WHERE CONSTRAINT_SCHEMA=? AND CONSTRAINT_NAME LIKE ?
                                                                AND TABLE_NAME=? AND COLUMN_NAME=?
                                                                AND REFERENCED_TABLE_NAME=? AND REFERENCED_COLUMN_NAME=?
Mon Apr 30 14:44:07 2018,976 [13888] DEBUG VT - Prepared sql query parameters : [vt620,%fk%,vtiger_activitycf,activityid,vtiger_activity,activityid]
Mon Apr 30 14:44:07 2018,976 [13888] DEBUG VT - Prepared sql query being executed : ALTER TABLE vtiger_activitycf ADD CONSTRAINT fk_activityid_vtiger_activitycf FOREIGN KEY (activityid) REFERENCES vtiger_activity (activi
tyid) ON DELETE CASCADE
Mon Apr 30 14:44:10 2018,065 [13888] INFO VT - PearDatabase ->ADODB error  Query Failed:ALTER TABLE vtiger_activitycf ADD CONSTRAINT fk_activityid_vtiger_activitycf FOREIGN KEY (activityid) REFERENCES vtiger_activity (ac
tivityid) ON DELETE CASCADE::->[1452]Cannot add or update a child row: a foreign key constraint fails (`vt620`.`#sql-23c_571`, CONSTRAINT `fk_activityid_vtiger_activitycf` FOREIGN KEY (`activityid`) REFERENCES `vtiger_act
ivity` (`activityid`) ON DELETE CASCADE)
Mon Apr 30 14:44:10 2018,065 [13888] DEBUG VT - Prepared sql query being executed : DELETE FROM vtiger_activity WHERE activityid NOT IN (SELECT crmid FROM vtiger_crmentity WHERE setype=?)
Mon Apr 30 14:44:10 2018,065 [13888] DEBUG VT - Prepared sql query parameters : [Calendar]

Note there is error trying to ADD the foreign key.

Also note the line:

DELETE FROM vtiger_activity WHERE activityid NOT IN (SELECT crmid FROM vtiger_crmentity WHERE setype=?)

And ? is replaced with Calendar.

There are roughly 26,500 records in vtiger_activity that are of setype "Emails"... These will be deleted from vtiger_activity due to the above query.