Poss. ModTracker issue?
Not sure if this is really a 7.1 issue or if it is older but if I run the following query:
SELECT * FROM vtiger_modtracker_basic LEFT JOIN vtiger_modtracker_detail ON vtiger_modtracker_detail.id = vtiger_modtracker_basic.id WHERE vtiger_modtracker_detail.id IS NULL;On a large database I get 255458 rows where there is no modtracker_detail entry.
| 7680074 | 5744739 | LSContracts | 1 | 2018-02-11 23:32:43 | 1 | NULL | NULL | NULL | NULL |
| 7680075 | 5744740 | LSContracts | 1 | 2018-02-11 23:32:43 | 1 | NULL | NULL | NULL | NULL |
| 7680076 | 5744741 | LSContracts | 1 | 2018-02-11 23:32:43 | 1 | NULL | NULL | NULL | NULL |
| 7680077 | 5744742 | LSContracts | 1 | 2018-02-11 23:32:43 | 1 | NULL | NULL | NULL | NULL |
| 7680078 | 5744743 | LSContracts | 1 | 2018-02-11 23:32:43 | 1 | NULL | NULL | NULL | NULL |
| 7680079 | 5744744 | LSContracts | 1 | 2018-02-11 23:32:43 | 1 | NULL | NULL | NULL | NULL |
| 7680080 | 5744745 | LSContracts | 1 | 2018-02-11 23:32:43 | 1 | NULL | NULL | NULL | NULL |
| 7680081 | 5744746 | LSContracts | 1 | 2018-02-11 23:32:43 | 1 | NULL | NULL | NULL | NULL |
| 7680082 | 5744747 | LSContracts | 1 | 2018-02-11 23:32:43 | 1 | NULL | NULL | NULL | NULL |
| 7681414 | 5745061 | LSContracts | 1 | 2018-02-12 09:12:27 | 1 | NULL | NULL | NULL | NULL |
| 7686371 | 5748312 | LSWater | 272 | 2018-02-12 14:09:22 | 1 | NULL | NULL | NULL | NULL |
| 7706766 | 5759090 | LSContracts | 1 | 2018-02-14 14:08:21 | 1 | NULL | NULL | NULL | NULL |
| 7720325 | 5766831 | LSContracts | 133 | 2018-02-15 14:47:36 | 1 | NULL | NULL | NULL | NULL |
| 7720328 | 5766718 | LSMeters | 133 | 2018-02-15 14:47:46 | 1 | NULL | NULL | NULL | NULL |
| 7720330 | 5766706 | LSAddresses | 133 | 2018-02-15 14:47:49 | 1 | NULL | NULL | NULL | NULL |
+---------+---------+-------------+--------+---------------------+--------+------+-----------+----------+-----------+
255458 rows in set (3 min 23.45 sec)When I look for the crmid the records appear to have been deleted; they are still in crmentity (so in the recycle bin) but have no history in modtracker_detail...
I checked to see if this affected ONLY vtlib (custom) modules but seemingly not:
mysql> SELECT DISTINCT(module) FROM vtiger_modtracker_basic LEFT JOIN vtiger_modtracker_detail ON vtiger_modtracker_detail.id = vtiger_modtracker_basic.id WHERE vtiger_modtracker_detail.id IS NULL;
+-------------+
| module |
+-------------+
| Accounts |
| LSAddresses |
| LSContracts |
| LSDomestics |
| LSMeters |
| LSWater |
+-------------+
6 rows in set (29.52 sec)If I really delete a record from the recycle bin, then the entry in modtracker_basic is also removed (good). But my concern is, what if I were to undelete a record from the recycle bin? The history detail would appear to have been removed before the record is really deleted...
I tried the query the other way around, looking for orphan modtracker_detail records and didn't find many
mysql> SELECT * FROM vtiger_modtracker_detail LEFT JOIN vtiger_modtracker_basic ON vtiger_modtracker_detail.id = vtiger_modtracker_basic.id WHERE vtiger_modtracker_basic.id IS NULL;
+---------+------------------+---------------------+---------------------+------+-------+--------+--------+-----------+--------+
| id | fieldname | prevalue | postvalue | id | crmid | module | whodid | changedon | status |
+---------+------------------+---------------------+---------------------+------+-------+--------+--------+-----------+--------+
| 7720004 | meterno | NULL | MTR572961 | NULL | NULL | NULL | NULL | NULL | NULL |
| 7720004 | lsaddresslink | NULL | 5766706 | NULL | NULL | NULL | NULL | NULL | NULL |
| 7720004 | lsleadstatus | NULL | --None-- | NULL | NULL | NULL | NULL | NULL | NULL |
| 7720004 | lsmetertype | NULL | Electricity | NULL | NULL | NULL | NULL | NULL | NULL |
| 7720004 | vendorlink | NULL | 3486 | NULL | NULL | NULL | NULL | NULL | NULL |
| 7720004 | mpan | NULL | 1200333337384 | NULL | NULL | NULL | NULL | NULL | NULL |
| 7720004 | msn | NULL | 952 | NULL | NULL | NULL | NULL | NULL | NULL |
| 7720004 | lsmeterpc | NULL | 03 | NULL | NULL | NULL | NULL | NULL | NULL |
| 7720004 | mtc | NULL | 801 | NULL | NULL | NULL | NULL | NULL | NULL |
| 7720004 | estrenewal | NULL | 2018-04-01 | NULL | NULL | NULL | NULL | NULL | NULL |
| 7720004 | datasource1 | NULL | J444D9 | NULL | NULL | NULL | NULL | NULL | NULL |
| 7720004 | accountname | NULL | Smith Ltd | NULL | NULL | NULL | NULL | NULL | NULL |
| 7720004 | phone | NULL | 02000003270 | NULL | NULL | NULL | NULL | NULL | NULL |
| 7720004 | assigned_user_id | NULL | 275 | NULL | NULL | NULL | NULL | NULL | NULL |
| 7720004 | CreatedTime | NULL | 2018-02-15 14:35:36 | NULL | NULL | NULL | NULL | NULL | NULL |
| 7720004 | ModifiedTime | NULL | 2018-02-15 14:35:36 | NULL | NULL | NULL | NULL | NULL | NULL |
| 7720004 | contactname | NULL | Mr A smith | NULL | NULL | NULL | NULL | NULL | NULL |
| 7720004 | callbacktime | NULL | 00:00:00 | NULL | NULL | NULL | NULL | NULL | NULL |
| 7720004 | cf_871 | NULL | None | NULL | NULL | NULL | NULL | NULL | NULL |
| 7720004 | lsterm_status | NULL | --None-- | NULL | NULL | NULL | NULL | NULL | NULL |
| 7720004 | record_id | NULL | 5766718 | NULL | NULL | NULL | NULL | NULL | NULL |
| 7720004 | record_module | NULL | LSMeters | NULL | NULL | NULL | NULL | NULL | NULL |
| 7720256 | nextaction | | 2017-09-03 | NULL | NULL | NULL | NULL | NULL | NULL |
| 7720256 | ModifiedTime | 2018-02-15 14:43:15 | 2018-02-15 14:43:28 | NULL | NULL | NULL | NULL | NULL | NULL |
| 7720259 | lsleadstatus | --None-- | Sold | NULL | NULL | NULL | NULL | NULL | NULL |
| 7720259 | ModifiedTime | 2018-02-15 14:43:28 | 2018-02-15 14:43:29 | NULL | NULL | NULL | NULL | NULL | NULL |
+---------+------------------+---------------------+---------------------+------+-------+--------+--------+-----------+--------+
26 rows in set (48.28 sec)Am I going mad or is this an actual problem?
Some info on the table sizes
mysql> SELECT COUNT(*) FROM vtiger_modtracker_basic;
+----------+
| COUNT(*) |
+----------+
| 7609535 |
+----------+
1 row in set (1.02 sec)mysql> SELECT COUNT(*) FROM vtiger_modtracker_detail;
+----------+
| COUNT(*) |
+----------+
| 43443466 |
+----------+
1 row in set (10.76 sec)Edited by Apparao G