Sugar 5.0.0 / Sugar Forums 5.0.0 Extension Query Errors

[benk]

Hi everyone,

I just performed a clean install of Sugar 5.0 and noticed a series of MySQL query errors when also installing the Sugar Forums 5.0.0 extension. (I'm using a clean CentOS 5 install, including PHP 5.1.6 and MySQ: 5.0.22.)

Basically, if you go to a Account detail page, Contact detail page, or Lead detail page, all of the sub-panels that should be at the bottom of those pages don't display at all. Instead, there is a long MySQL query error shown. I've confirmed that by uninstalling the Sugar Forums 5.0.0 extension, the query errors go away. (However, there is one other bug: When you uninstall the Sugar Forums extension, the Forums search page persists as well as the module tab in the Grouped Module display.)

Here are the specific MySQL query errors I received...

ERROR ON ACCOUNT DETAIL PAGE:

Error retrieving Account list: Query Failed SELECT meetings.id , meetings.name , meetings.status , ' ' contact_name , ' ' contact_id , ' ' contact_name_owner , ' ' contact_name_mod , meetings.date_modified , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, ' ' filename , meetings.assigned_user_id , 'meetings' panel_name FROM meetings LEFT JOIN users jt1 ON jt1.id= meetings.assigned_user_id AND jt1.deleted=0 AND jt1.deleted=0 where ((meetings.status='Held' OR meetings.status='Not Held')) AND meetings.deleted=0 ) UNION ALL ( SELECT tasks.id , tasks.name , tasks.status , CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')) contact_name, tasks.date_modified , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, ' ' filename , tasks.assigned_user_id , 'tasks' panel_name FROM tasks AND contacts.deleted=0 LEFT JOIN users jt1 ON jt1.id= tasks.assigned_user_id AND jt1.deleted=0 AND jt1.deleted=0 where ((tasks.status='Completed' OR tasks.status='Deferred')) AND tasks.deleted=0 ) UNION ALL ( SELECT calls.id , calls.name , calls.status , ' ' contact_name , ' ' contact_id , ' ' contact_name_owner , ' ' contact_name_mod , calls.date_modified , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, ' ' filename , calls.assigned_user_id , 'calls' panel_name FROM calls LEFT JOIN users jt1 ON jt1.id= calls.assigned_user_id AND jt1.deleted=0 AND jt1.deleted=0 where ((calls.status='Held' OR calls.status='Not Held')) AND calls.deleted=0 ) UNION ALL ( SELECT notes.id , notes.name , ' ' status , contacts.last_name contact_name, notes.date_modified , ' ' assigned_user_name , ' ' assigned_user_owner , ' ' assigned_user_mod , notes.filename , notes.created_by , 'notes' panel_name FROM notes AND contacts.deleted=0 where notes.deleted=0 ) UNION ALL ( SELECT emails.id , emails.name , emails.status , ' ' contact_name , ' ' contact_id , ' ' contact_name_owner , ' ' contact_name_mod , emails.date_modified , jt0.user_name assigned_user_name , jt0.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, ' ' filename , emails.assigned_user_id , 'emails' panel_name FROM emails LEFT JOIN users jt0 ON jt0.id= emails.assigned_user_id AND jt0.deleted=0 AND jt0.deleted=0 INNER JOIN emails_beans ON (emails.id=emails_beans.email_id AND emails_beans.bean_id= 'df34d6f1-f6de-7439-35cc-477887985e1e' AND bean_module='Accounts') where ( emails_beans.deleted=0 AND emails.deleted=0) AND emails.deleted=0 ) UNION ALL ( SELECT emails.id , emails.name , emails.status , ' ' contact_name , ' ' contact_id , ' ' contact_name_owner , ' ' contact_name_mod , emails.date_modified , jt0.user_name assigned_user_name , jt0.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, ' ' filename , emails.assigned_user_id , 'linkedemails' panel_name FROM emails LEFT JOIN users jt0 ON jt0.id= emails.assigned_user_id AND jt0.deleted=0 AND jt0.deleted=0 JOIN (select distinct email_id from emails_email_addr_rel eear join email_addr_bean_rel eabr on eabr.bean_id ='df34d6f1-f6de-7439-35cc-477887985e1e' and eabr.bean_module = 'Accounts' and eabr.email_address_id = eear.email_address_id and eabr.deleted=0 where eear.deleted=0 and eear.email_id not in (select eb.email_id from emails_beans eb where eb.bean_module ='Accounts' and eb.bean_id = 'df34d6f1-f6de-7439-35cc-477887985e1e' and eb.deleted=0) ) derivedemails on derivedemails.email_id = emails.id where emails.deleted=0 ) ORDER BY date_modified desc LIMIT 0,20::MySQL error 1064:


ERROR ON CONTACT DETAIL PAGE:

Error retrieving Contact list: Query Failed SELECT meetings.id , meetings.name , meetings.status , ' ' contact_name , ' ' contact_id , ' ' contact_name_owner , ' ' contact_name_mod , meetings.date_modified , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, ' ' filename , meetings.assigned_user_id , 'meetings' panel_name FROM meetings LEFT JOIN users jt1 ON jt1.id= meetings.assigned_user_id AND jt1.deleted=0 AND jt1.deleted=0 INNER JOIN meetings_contacts ON (meetings.id=meetings_contacts.meeting_id AND meetings_contacts.contact_id= 'c37b379e-39f3-c645-9b8f-47788796298c') where ( meetings_contacts.deleted=0 AND meetings.deleted=0 AND (meetings.status='Held' OR meetings.status='Not Held')) AND meetings.deleted=0 ) UNION ALL ( SELECT tasks.id , tasks.name , tasks.status , CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')) contact_name, tasks.date_modified , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, ' ' filename , tasks.assigned_user_id , 'tasks' panel_name FROM tasks AND contacts.deleted=0 LEFT JOIN users jt1 ON jt1.id= tasks.assigned_user_id AND jt1.deleted=0 AND jt1.deleted=0 where ((tasks.status='Completed' OR tasks.status='Deferred')) AND tasks.deleted=0 ) UNION ALL ( SELECT calls.id , calls.name , calls.status , ' ' contact_name , ' ' contact_id , ' ' contact_name_owner , ' ' contact_name_mod , calls.date_modified , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, ' ' filename , calls.assigned_user_id , 'calls' panel_name FROM calls LEFT JOIN users jt1 ON jt1.id= calls.assigned_user_id AND jt1.deleted=0 AND jt1.deleted=0 INNER JOIN calls_contacts ON (calls.id=calls_contacts.call_id AND calls_contacts.contact_id= 'c37b379e-39f3-c645-9b8f-47788796298c') where ( calls_contacts.deleted=0 AND calls.deleted=0 AND (calls.status='Held' OR calls.status='Not Held')) AND calls.deleted=0 ) UNION ALL ( SELECT notes.id , notes.name , ' ' status , contacts.last_name contact_name, notes.date_modified , ' ' assigned_user_name , ' ' assigned_user_owner , ' ' assigned_user_mod , notes.filename , notes.created_by , 'notes' panel_name FROM notes AND contacts.deleted=0 where notes.deleted=0 ) UNION ALL ( SELECT emails.id , emails.name , emails.status , ' ' contact_name , ' ' contact_id , ' ' contact_name_owner , ' ' contact_name_mod , emails.date_modified , jt0.user_name assigned_user_name , jt0.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, ' ' filename , emails.assigned_user_id , 'emails' panel_name FROM emails LEFT JOIN users jt0 ON jt0.id= emails.assigned_user_id AND jt0.deleted=0 AND jt0.deleted=0 INNER JOIN emails_beans ON (emails.id=emails_beans.email_id AND emails_beans.bean_id= 'c37b379e-39f3-c645-9b8f-47788796298c' AND bean_module='Contacts') where ( emails_beans.deleted=0 AND emails.deleted=0) AND emails.deleted=0 ) UNION ALL ( SELECT emails.id , emails.name , emails.status , ' ' contact_name , ' ' contact_id , ' ' contact_name_owner , ' ' contact_name_mod , emails.date_modified , jt0.user_name assigned_user_name , jt0.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, ' ' filename , emails.assigned_user_id , 'linkedemails' panel_name FROM emails LEFT JOIN users jt0 ON jt0.id= emails.assigned_user_id AND jt0.deleted=0 AND jt0.deleted=0 JOIN (select distinct email_id from emails_email_addr_rel eear join email_addr_bean_rel eabr on eabr.bean_id ='c37b379e-39f3-c645-9b8f-47788796298c' and eabr.bean_module = 'Contacts' and eabr.email_address_id = eear.email_address_id and eabr.deleted=0 where eear.deleted=0 and eear.email_id not in (select eb.email_id from emails_beans eb where eb.bean_module ='Contacts' and eb.bean_id = 'c37b379e-39f3-c645-9b8f-47788796298c' and eb.deleted=0) ) derivedemails on derivedemails.email_id = emails.id where emails.deleted=0 ) ORDER BY date_modified desc LIMIT 0,20::MySQL error 1064:


Please advise how to fix these errors. Also, I wasn't sure where is the proper place in the forums (is there a bug tracker?) to submit a possible bug

Thanks,
Ben

[sandymccarthy]

I have exactly the same problem and solved it by removing the forums module. I have it logged as an active case so awaiting a fix. Will let you know how I get on

[benk]

Thanks for filing the bug. I'm wondering if the Sugar folks will release a new version of the Sugar Forums Extension that fixes this, or if the fix will be in an upcoming patch for SugarCRM itself.

If anyone has luck with the Sugar Forums Extension, please post it on this thread.

[shamaho]

Aha !! So that is the problem I was experiencing !!!

Sheeesh! I wonder why it took so long for this problem to be ermmm posted.... or recognized...

I had already posted bug number 19159 about this....

The problem still happens on 5.0.0.c

[gkeyes]

Ok, Well I only spend a couple hours trying to figure this out, the question is.... Is there a way around this, mySQL statements are simple, just as my reports are going to simple, for now at least.

Does this effect all the reporting ability? SHould I switch over and try one of the others, jasper?, ireports?