[Vtigercrm-developers] Migration blocker

[Alan Lord]

On 04/04/18 07:02, Tony Sandman wrote:
> So I decided to do some migration testing and found issue.
> Migration actually never finish and producing giant vtigercrm.log file 
> even if
> logging is switched off in config.performance.php as well as in 
> config.inc.php.

[11965,11966,11967,11968,11969,11970,11971,11972,11973,11974,11975,11976,11977,11978,11979,11980,11981,11982,11983,11984,11985,11986,11987,11988,11989,11990,11991,11992,11993,11994,11995,16538,16539,16540,16541,18925,19194,74440,74756,78177,78178,78779,78892,80832,80839,80887,80938,80939,158705,158775,158986,159085,168799,168800,168825,169062,169100,169104,169114,169178,169257,169258,169280,169325,170548,170780,174565,203431,204681,210203,210686,211867,212093,212616,216264,217504,220276,222747,222993,223094]
> Wed Apr  4 05:47:06 2018,575 [2555] DEBUG VT - Prepared sql query being 
> executed : SELECT vtiger_modcomments.modcommentsid FROM vtiger_modcomments
>                                                  LEFT JOIN 
> vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_modcomments.related_to
>                                                  WHERE 
> vtiger_crmentity.setype NOT IN (?,?)
>                                                  OR 
> vtiger_crmentity.setype IS NULL AND modcommentsid > ? LIMIT 500
> =================
> 
> Any idea how to bite it?

Is this a large database?

If it has many hundreds of thousands of rows - the web-based migration 
*will* timeout without modifications.

I have made several suggestions over the years to improve the 
performance of the migration scripts. This one is a good case-in-point.

The code for this part is in 660_to_700.php and looks a bit like this [1]:

> 	$internalCommentModules = Vtiger_Functions::getPrivateCommentModules();
> 	$lastMaxCRMId = 0;
> 	do {
> 		$commentsResult = $db->pquery('SELECT vtiger_modcomments.modcommentsid FROM vtiger_modcomments 
> 												LEFT JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_modcomments.related_to 
> 												WHERE (vtiger_crmentity.setype NOT IN ('.generateQuestionMarks($internalCommentModules).') 
> 												OR vtiger_crmentity.setype IS NULL) AND modcommentsid > ? LIMIT 500', array_merge($internalCommentModules, array($lastMaxCRMId)));
> 		if (!$db->num_rows($commentsResult)) {
> 			break;
> 		}
> 
> 		$commentIds = array();
> 		while ($row = $db->fetch_array($commentsResult)) {
> 			$commentIds[] = $row['modcommentsid'];
> 		}
> 
> 		if (count($commentIds) > 0) {
> 			$db->pquery('UPDATE vtiger_modcomments SET is_private = 0 WHERE modcommentsid IN ('.generateQuestionMarks($commentIds).')', $commentIds);
> 		}
> 
> 		$commentId = end($commentIds);
> 		if (intval($commentId) > $lastMaxCRMId) {
> 			$lastMaxCRMId = intval($commentId);
> 		}
> 		$commentsResult = NULL;
> 		unset($commentsResult);
> 	} while (true);



So what this does is to read in comments in blocks of 500, create an 
array of these IDs, keep a counter of how many block of 500 have been 
done, generate the 500 question marks then run the update query - then 
repeat until no more comments are found. This all takes quite a while to do.

I re-wrote this so it runs as a single MySQL query. MySQL is very fast.

My code looks like this:

> 	$internalCommentModules = Vtiger_Functions::getPrivateCommentModules();
> 	$query = 'UPDATE vtiger_modcomments
>               LEFT JOIN vtiger_crmentity 
>                 ON vtiger_crmentity.crmid = vtiger_modcomments.related_to 
>               SET vtiger_modcomments.is_private = 0
>               WHERE (vtiger_crmentity.setype NOT IN ('.generateQuestionMarks($internalCommentModules).') 
>               OR vtiger_crmentity.setype IS NULL)
>               AND vtiger_modcomments.modcommentsid IS NOT NULL';
> 	$commentsResult = $db->pquery($query, $internalCommentModules);

I believe it to be functionally equivalent.

Try timing a test of the two approaches and see how you get on :-)

HTH

Al

PS: There was a similar approach taken earlier in the migration process 
for the new crmentity label field in 540_to_600RC.php. On systems with 
several million rows, using the web based migration, it never finishes. 
We re-wrote that one to run much more efficiently too.

PPS: There is also a script which runs to do with the PBXManager in 
600_to_610.php. Again, if you have a large database it takes eons to run 
and in that particular case, the code actually doesn't do anything 
either as there is a bug in the code.

PPPS: At the top of each migration script with little a bit of hacking, 
and fixing a few  minor issues, you can get them to run from the command 
line. Which is a much better approach for large systems IMHO.

I have reported & discussed each of the above on this list when I first 
noticed them.

[1] 
http://code.vtiger.com/vtiger/vtigercrm/blob/master/modules/Migration/schema/660_to_700.php#L621