[Vtigercrm-developers] Report Problem...

[Alan Lord]

I've been seeing a fair bit of this recently in several different reports:

> Wed Jun 29 07:16:25 2016,582 [31102] DEBUG VT - Prepared sql query being executed : CREATE TEMPORARY TABLE vtiger_reptmptbl_1577375c98dd1c3313537740 AS select vtiger_modcomments.* from vtiger_modcomments inne
> r join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_modcomments.modcommentsid and vtiger_crmentity.deleted=0
> Wed Jun 29 07:16:31 2016,537 [31102] DEBUG VT - Prepared sql query being executed : ALTER TABLE vtiger_reptmptbl_1577375c98dd1c3313537740 ADD INDEX (modcommentsid)
> Wed Jun 29 07:16:38 2016,504 [31102] DEBUG VT - Prepared sql query being executed : ALTER TABLE vtiger_reptmptbl_1577375c98dd1c3313537740 ADD INDEX (lsevaluationsid)
> Wed Jun 29 07:16:38 2016,504 [31102] INFO VT - PearDatabase ->ADODB error  Query Failed:ALTER TABLE vtiger_reptmptbl_1577375c98dd1c3313537740 ADD INDEX (lsevaluationsid)::->[1072]Key column 'lsevaluationsid'
> doesn't exist in table
> Wed Jun 29 07:16:38 2016,504 [31102] DEBUG VT - Prepared sql query being executed : ALTER TABLE vtiger_reptmptbl_1577375c98dd1c3313537740 ADD INDEX (related_to)
> Wed Jun 29 07:16:47 2016,299 [31102] DEBUG VT - Prepared sql query being executed : select DISTINCT ls_evaluations.lseval_no AS 'LSEvaluations_Evaluation_No.', (CASE WHEN ls_evaluations.vendor_id NOT LIKE ''
> THEN (CASE WHEN trim(vtiger_vendorRelLSEvaluations1213.vendorname) NOT LIKE '' THEN trim(vtiger_vendorRelLSEvaluations1213.vendorname) ELSE '' END) ELSE '' END) AS LSEvaluations_Vendor, ls_evaluations.cmp_pri
> ce AS 'LSEvaluations_Competitiveness_of_Price', ls_evaluations.cmp_price_id AS 'LSEvaluations_Evaluator', vtiger_crmentity.crmid AS "LSEvaluations_LBL_ACTION" from ls_evaluations inner join vtiger_crmentity o
> n vtiger_crmentity.crmid=ls_evaluations.lsevaluationsid left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid left join vtiger_users on vtiger_users.id = vtiger_crmentity.smownerid lef
> t join vtiger_crmentity as vtiger_crmentityRelLSEvaluations1213 on vtiger_crmentityRelLSEvaluations1213.crmid = ls_evaluations.vendor_id and vtiger_crmentityRelLSEvaluations1213.deleted=0 left join vtiger_ven
> dor as vtiger_vendorRelLSEvaluations1213 on vtiger_vendorRelLSEvaluations1213.vendorid = vtiger_crmentityRelLSEvaluations1213.crmid left join vtiger_reptmptbl_1577375c98dd1c3313537740 as vtiger_modcomments on
>  vtiger_modcomments.related_to=ls_evaluations.lsevaluationsid left join vtiger_crmentity as vtiger_crmentityModComments on vtiger_crmentityModComments.crmid=vtiger_modcomments.modcommentsid and vtiger_crmenti
> tyModComments.deleted=0 WHERE ls_evaluations.lsevaluationsid > 0  AND vtiger_crmentity.deleted=0  LIMIT 0, 1000


Mostly this makes little difference, but because the modcomments table 
is rather large in this customer's system, not having an Index on one of 
the columns of the temporary table is hitting the performance of the query.

Could one of the devs explain why this is happening please?

The function initializeTempTables() in ReportRun.php is where this 
problem is happening... The question is why does it get one (or more in 
the case of a crmentityrel relationship) of the 
$tempTableInfo['keycolumns'] wrong. It should be "related_to" not my 
module's crmid index column name...

Thanks

Al