Reports with a sort creates "database failure"

SuiteCRM Forum - English Language SuiteCRM General Discussion
#1

Hi,

Just testing and good job.

I have written a report on my system and also on your demo system on the opportunities table. When the report has a field which is sorted on it reports a “Database failure. Please refer to sugarcrm.log for details.” There is no problem without the sort.

Thanks and keep up the good work.

Tim.

#2

Hi Tim,

Did you refer to the sugarcrm.log file? What errors does this give in relation to AOR?

Thanks,

Will.

#3

Hi,

Relevant log entry attached. As I mentioned, also happens to the SuiteCRM demo too.

I am no SQL expert but I think the ORDER BY field should be quoted?

Thanks,

Tim.

Sun Jan 12 20:16:29 2014 [2322][1][FATAL] Error running count query for AOR_Repo
rt List: Query Failed: SELECT count(*) c FROM (SELECT opportunities.id AS ‘oppo
rtunities_id’, opportunities.name AS ‘Opportunity Name0’, opportunities.next_ste
p AS ‘Next Step1’, opportunities.sales_stage AS ‘Sales Stage2’, opportunities.as
signed_user_id AS ‘Assigned User3’ FROM opportunities WHERE (opportunities.sale
s_stage = ‘Lead’ OR opportunities.sales_stage = ‘Proposal/Price Quote’ OR opport
unities.sales_stage = ‘Design’ OR opportunities.sales_stage = ‘Visit’ OR opportu
nities.sales_stage = ‘Negotiation/Review’) AND opportunities.deleted = 0 ORDER
BY Assigned User3 ASC) as n: MySQL error 1064: You have an error in your SQL syn
tax; check the manual that corresponds to your MySQL server version for the righ
t syntax to use near ‘User3 ASC) as n’ at line 1

#4

Hi Tim,

The ordering is done on a column value, which does not need to be a quoted value.

AOR is in early development and there seems to be issues with the AOR generated queries. There are other topics with similar issues.

Thanks for highlighting this.

Regards,

Will.

1 Like
#5

Database failure. Please refer to sugarcrm.log for details. 2/20/2014 1:41:31 PM [5140][1][FATAL] Error inserting into table: aor_fields: Query Failed: INSERT INTO aor_fields (id,name,date_entered,date_modified,modified_user_id,created_by,deleted,aor_report_id,field_order,module_path,field,display,link,label,function,sort_by,group_by)
VALUES (‘38d33bae-63d6-4ed5-a7a7-5305e933e5fd’,’’,‘2014-02-20 11:41:29’,‘2014-02-20 11:41:29’,‘1’,‘1’,0,‘9c94c24d-f97a-f273-9ed9-5305e973e280’,1,‘YToxOntpOjA7czo1OiJDYXNlcyI7fQ==’,‘name’,1,0,‘Subject’,‘COUNT’,‘ASC’,‘0’): MySQL error 1054: Unknown column ‘function’ in ‘field list’
how do i solve this problem

#6

Hi there,

Which version of SuiteCRM are you running? This bug was fixed in SuiteCRM 7.0.2. Have you performed a Quick Repair & Rebuild in Admin -> Repair and cleared your browsers cache?

Thanks,

Will.

#7

hello will
I’m using SuiteCRM 7.0.2.and i have tried Quick Repair & Rebuild in Admin -> Repair and cleared my browsers cache? and still gives me the same result Database failure. Please refer to sugarcrm.log for details.

even tried to do a new installation and gives me errors warning invalid arguement supplied for each () in suitecrm/modules/aor_reports/aor_reports.php line 85
and warning invalid arguement supplied for each () in suitecrm/modules/aow_workflows/aow_workflows.php line 89

#8

Hi Weed,

In regard to the install errors, I had some success by changing the default storage engine in the MySQL my.ini file as follows

#default-storage-engine=INNODB
default-storage-engine=MyISAM

Then create a new database and attempt a clean install.

HTH

Bruce