Fixes #437 - Issue with Migration script design 660_to_700.php

Show picklist color as background color for picklist value in Edit / Create view

Adding short cuts in settings page is not working

Fixes #437 (closed) - Issue with Migration script design 660_to_700.php

Added 1 commit:

• 80fe38ad - Fixes #437 (closed) - Issue with Migration script design 660_to_700.php

Added 1 commit:

• 406f183e - Fixes #437 (closed) - Issue with Migration script design 660_to_700.php

Status changed to merged

mentioned in commit 3e679276

Can I ask why don't you do this in one single query?

I do believe this is functionally equivalent and would be much faster!

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;

I just tested this and it completed in about 5 seconds:

Thu Feb 9 11:11:05 2017,476 [9494] DEBUG VT - Prepared sql query being executed : 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 (?,?) OR vtiger_crmentity.setype IS NULL Thu Feb 9 11:11:05 2017,476 [9494] DEBUG VT - Prepared sql query parameters : [HelpDesk,Faq]

That's on ~1.2 million rows of comments.

PS: I would add another clause to the WHERE statement, e.g: "AND modcommentsid IS NOT NULL" so it doesn't try and run on every row of crmentity (it's a left join) which has 3985228 rows in it.

@satish.dvnk @prasad Would you care to comment on my suggestion above?

@lord_alan, I added limit(500) in the query. Have you reviewed ?

Yes, but what is wrong with using MySQL to do the whole thing? Your PHP script approach takes a long time to run when there are a million plus rows...