Hello, since my upgrade from 7.1.5 to 7.2, the box global search searches only in the opportunity module. In advanced, all the modules are selected. AOD is disabled. Any idea ?
Works ok for me. Did your install run successfully and did you run a Quick Repair & Rebuild in Admin -> Repair?
Are there any errors in your sugarcrm.log file?
Can you attach some screen shots?
Thanks,
Will.
I have this error when searching :
Wed Mar 4 03:06:15 2015 [12378][1][FATAL] Query Failed: SELECT accounts.id , accounts.name , accounts.billing_address_city , accounts.account_type , accounts.phone_office , jt0.user_name assigned_user_name , jt0.created_by assigned_user_name_owner , ‘Users’ assigned_user_name_mod, accounts.date_entered , accounts.assigned_user_id FROM accounts LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c LEFT JOIN users jt0 ON accounts.assigned_user_id=jt0.id AND jt0.deleted=0
AND jt0.deleted=0 where (((accounts.name like ‘Govex%’ ) OR ( accounts.phone_office like ‘Govex%’ ) OR ( email IN (select bean_id from (SELECT eabr.bean_id FROM email_addr_bean_rel eabr JOIN email_addresses ea ON (ea.id = eabr.email_address_id) WHERE eabr.deleted=0 AND ea.email_address LIKE ‘Govex%’) email_derived)))) AND accounts.deleted=0 ORDER BY accounts.date_entered DESC LIMIT 0,11: MySQL error 1054: Unknown column ‘email’ in ‘IN/ALL/ANY subquery’
Wed Mar 4 03:06:15 2015 [12378][1][FATAL] Query Failed: SELECT contacts.id , LTRIM(RTRIM(CONCAT(IFNULL(contacts.first_name,’’),’ ‘,IFNULL(contacts.last_name,’’)))) as name, contacts.first_name , contacts.last_name , contacts.salutation , accounts.name account_name, jtl0.account_id account_id, contacts.title , contacts.phone_work , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , ‘Users’ assigned_user_name_mod, contacts.date_entered , contacts.assigned_user_id FROM contacts LEFT JOIN contacts_cstm ON contacts.id = contacts_cstm.id_c LEFT JOIN accounts_contacts jtl0 ON contacts.id=jtl0.contact_id AND jtl0.deleted=0
LEFT JOIN accounts accounts ON accounts.id=jtl0.account_id AND accounts.deleted=0
AND accounts.deleted=0 LEFT JOIN users jt1 ON contacts.assigned_user_id=jt1.id AND jt1.deleted=0
AND jt1.deleted=0 where (((contacts.first_name like ‘Govex%’ ) OR ( contacts.last_name like ‘Govex%’ ) OR ( ( contacts.phone_mobile like ‘Govex%’ OR contacts.phone_work like ‘Govex%’ OR contacts.phone_other like ‘Govex%’ OR contacts.phone_fax like ‘Govex%’ OR contacts.assistant_phone like ‘Govex%’ ) ) OR ( contacts.assistant like ‘Govex%’ ) OR ( email IN (select bean_id from (SELECT eabr.bean_id FROM email_addr_bean_rel eabr JOIN email_addresses ea ON (ea.id = eabr.email_address_id) WHERE eabr.deleted=0 AND ea.email_address LIKE ‘Govex%’) email_derived) ) OR ( accounts.name like ‘Govex%’ ) OR ( ( contacts.first_name like ‘Govex%’ OR contacts.last_name like ‘Govex%’ )))) AND contacts.deleted=0 ORDER BY contacts.date_entered DESC LIMIT 0,11: MySQL error 1054: Unknown column ‘email’ in ‘IN/ALL/ANY subquery’
Wed Mar 4 03:06:15 2015 [12378][1][FATAL] Query Failed: SELECT leads.id , LTRIM(RTRIM(CONCAT(IFNULL(leads.first_name,’’),’ ‘,IFNULL(leads.last_name,’’)))) as name, leads.first_name , leads.last_name , leads.salutation , leads.status , leads.account_name , leads.account_id , leads.phone_work , jt0.user_name assigned_user_name , jt0.created_by assigned_user_name_owner , ‘Users’ assigned_user_name_mod, leads.date_entered , leads.assigned_user_id FROM leads LEFT JOIN leads_cstm ON leads.id = leads_cstm.id_c LEFT JOIN users jt0 ON leads.assigned_user_id=jt0.id AND jt0.deleted=0
AND jt0.deleted=0 where (((leads.first_name like ‘Govex%’ ) OR ( leads.last_name like ‘Govex%’ ) OR ( ( leads.phone_mobile like ‘Govex%’ OR leads.phone_work like ‘Govex%’ OR leads.phone_other like ‘Govex%’ OR leads.phone_fax like ‘Govex%’ OR leads.phone_home like ‘Govex%’ ) ) OR ( leads.assistant like ‘Govex%’ ) OR ( leads.account_name like ‘Govex%’ ) OR ( email IN (select bean_id from (SELECT eabr.bean_id FROM email_addr_bean_rel eabr JOIN email_addresses ea ON (ea.id = eabr.email_address_id) WHERE eabr.deleted=0 AND ea.email_address LIKE ‘Govex%’) email_derived) ) OR ( ( leads.first_name like ‘Govex%’ OR leads.last_name like ‘Govex%’ )))) AND leads.deleted=0 ORDER BY leads.date_entered DESC LIMIT 0,11: MySQL error 1054: Unknown column ‘email’ in ‘IN/ALL/ANY subquery’
And also this type of error : [04-Mar-2015 03:09:22 America/Denver] PHP Fatal error: Cannot redeclare pollMonitoredInboxesCustomAOP() (previously declared …
Can you see if there are any changes to execute after running a Quick Repair & Rebuild in Admin -> Repair?
When the repair completes, there will be a long list of items it has done, at the bottom there may be a box with queries in it.
Click ‘Execute’ if this is the case, and re-try.
Thanks,
Will.
No, but I had something yesterday to re-synchronize and I clicked it. Today I have this :
Clearing Vardefs from cache…done
Clearing language files from cache…done
Clearing Template files from cache…done
Clearing JS files from cache…done
Clearing Vardefs from cache…done
Clearing JS Language files from cache…done
Clearing language files from cache…done
Clearing Sugar Dashlet files from cache…done
Clearing Sugar Feed Cache…done
Clearing Smarty templates from cache…done
Clearing Theme Cache…done
Clearing XML files from cache…done
Clearing Unified Search Cache…done
Clearing External API Cache File…done
Rebuilding Extensions…
Rebuilding Include…
Rebuilding Language…en_us
Rebuilding Language…es_es
Rebuilding Language…ru_ru
Rebuilding ActionViewMap…
Rebuilding ActionFileMap…
Rebuilding ActionReMap…
Rebuilding Administration…
Rebuilding EntryPointRegistry…
Rebuilding Extensions…
Rebuilding FileAccessControlMap…
Rebuilding Layoutdefs…
Rebuilding GlobalLinks…
Rebuilding LogicHooks…
Rebuilding Menus…
Rebuilding Include…
Rebuilding ScheduledTasks…
Rebuilding UserPage…
Rebuilding Utils…
Rebuilding Vardefs…
Rebuilding JSGroupings…
Rebuilding Actions…
Rebuilding DC Actions…
Rebuilding RelationshipsRebuilding TableDictionary…
Updating the admin warning message…
Rebuilding Audit Tables…
ACLAction not Audit Enabled…
ACLRole not Audit Enabled…
Relationship not Audit Enabled…
Audit table for Leads already exists. skipping…
Audit table for Cases already exists. skipping…
Audit table for Bugs already exists. skipping…
User not Audit Enabled…
CampaignLog not Audit Enabled…
Project not Audit Enabled…
Audit table for ProjectTasks already exists. skipping…
Audit table for Campaigns already exists. skipping…
ProspectList not Audit Enabled…
Prospect not Audit Enabled…
EmailMarketing not Audit Enabled…
CampaignTracker not Audit Enabled…
Release not Audit Enabled…
EmailMan not Audit Enabled…
Scheduler not Audit Enabled…
SchedulersJob not Audit Enabled…
Audit table for Contacts already exists. skipping…
Audit table for Accounts already exists. skipping…
Audit table for Opportunitys already exists. skipping…
EmailTemplate not Audit Enabled…
Note not Audit Enabled…
Call not Audit Enabled…
Email not Audit Enabled…
Meeting not Audit Enabled…
Task not Audit Enabled…
User not Audit Enabled…
Employee not Audit Enabled…
Currency not Audit Enabled…
Tracker not Audit Enabled…
ImportMap not Audit Enabled…
UsersLastImport not Audit Enabled…
Administration not Audit Enabled…
UpgradeHistory not Audit Enabled…
vCal not Audit Enabled…
Version not Audit Enabled…
Role not Audit Enabled…
Document not Audit Enabled…
DocumentRevision not Audit Enabled…
FieldsMetaData not Audit Enabled…
InboundEmail not Audit Enabled…
SavedSearch not Audit Enabled…
UserPreference not Audit Enabled…
MergeRecord not Audit Enabled…
EmailAddress not Audit Enabled…
EmailText not Audit Enabled…
SugarFeed not Audit Enabled…
EAPM not Audit Enabled…
OAuthKey not Audit Enabled…
OAuthToken not Audit Enabled…
Audit table for FP_eventss already exists. skipping…
Audit table for FP_Event_Locationss already exists. skipping…
Audit table for Calls_Reschedules already exists. skipping…
Audit table for jjwg_Mapss already exists. skipping…
Audit table for jjwg_Markerss already exists. skipping…
Audit table for jjwg_Areass already exists. skipping…
Audit table for jjwg_Address_Caches already exists. skipping…
Audit table for AM_ProjectTemplatess already exists. skipping…
Audit table for AM_TaskTemplatess already exists. skipping…
Audit table for AM_ProjectHolidayss already exists. skipping…
Audit table for AOD_IndexEvents already exists. skipping…
Audit table for AOD_Indexs already exists. skipping…
Audit table for AOR_Reports already exists. skipping…
AOR_Field not Audit Enabled…
AOR_Chart not Audit Enabled…
AOR_Condition not Audit Enabled…
AOR_Scheduled_Reports not Audit Enabled…
AOW_Action not Audit Enabled…
Audit table for AOW_WorkFlows already exists. skipping…
AOW_Processed not Audit Enabled…
AOW_Condition not Audit Enabled…
Audit table for AOP_Case_Eventss already exists. skipping…
Audit table for AOP_Case_Updatess already exists. skipping…
Audit table for SecurityGroups already exists. skipping…
Audit table for AOS_Contractss already exists. skipping…
Audit table for AOS_Invoicess already exists. skipping…
Audit table for AOS_PDF_Templatess already exists. skipping…
Audit table for AOS_Product_Categoriess already exists. skipping…
Audit table for AOS_Productss already exists. skipping…
Audit table for AOS_Products_Quotess already exists. skipping…
Audit table for AOS_Line_Item_Groupss already exists. skipping…
Audit table for AOS_Quotess already exists. skipping…
Done
Database tables are synced with vardefs
And I also have a lot of : [FATAL] Bad subpanel definition, it has incorrect value for get_subpanel_data property calls_accounts_1
Up ?
I am hit by the same bug. Have a very long SQL query in my log file, ending with this error:
[ ... ]
AND jt0.deleted=0 where (((accounts.name like 'MotionElements%' ) OR ( accounts.phone_office like 'MotionElements%' ) OR ( email IN (select bean_id from (SELECT eabr.bean_id FROM email_addr_bean_rel eabr JOIN email_addresses ea ON (ea.id = eabr.email_address_id) WHERE eabr.deleted=0 AND ea.email_address LIKE 'MotionElements%') email_derived)))) AND accounts.deleted=0 ORDER BY accounts.date_entered DESC LIMIT 0,11: MySQL error 1054: Unknown column 'email' in 'IN/ALL/ANY subquery'Quick Repair ends up with: (no corrections to do)
Database tables are synced with vardefsHere a full query:
Thu Mar 12 10:19:25 2015 [14742][e9a32fc7-b33f-f114-7b99-5458db9418ae][FATAL] Query Failed: SELECT contacts.id , LTRIM(RTRIM(CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')))) as name, contacts.first_name , contacts.last_name , contacts.salutation , accounts.name account_name, jtl0.account_id account_id, contacts.title , contacts.phone_work , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, contacts.date_entered , contacts.assigned_user_id FROM contacts LEFT JOIN contacts_cstm ON contacts.id = contacts_cstm.id_c LEFT JOIN accounts_contacts jtl0 ON contacts.id=jtl0.contact_id AND jtl0.deleted=0
LEFT JOIN accounts accounts ON accounts.id=jtl0.account_id AND accounts.deleted=0
AND accounts.deleted=0 LEFT JOIN users jt1 ON contacts.assigned_user_id=jt1.id AND jt1.deleted=0
AND jt1.deleted=0 where (((contacts.first_name like 'tunay%' ) OR ( contacts.last_name like 'tunay%' ) OR ( ( contacts.phone_mobile like 'tunay%' OR contacts.phone_work like 'tunay%' OR contacts.phone_other like 'tunay%' OR contacts.phone_fax like 'tunay%' OR contacts.assistant_phone like 'tunay%' ) ) OR ( contacts.assistant like 'tunay%' ) OR ( email IN (select bean_id from (SELECT eabr.bean_id FROM email_addr_bean_rel eabr JOIN email_addresses ea ON (ea.id = eabr.email_address_id) WHERE eabr.deleted=0 AND ea.email_address LIKE 'tunay%') email_derived) ) OR ( accounts.name like 'tunay%' ) OR ( ( contacts.first_name like 'tunay%' OR contacts.last_name like 'tunay%' )))) AND contacts.deleted=0 ORDER BY contacts.date_entered DESC LIMIT 0,11: MySQL error 1054: Unknown column 'email' in 'IN/ALL/ANY subquery'
Now I try to figure out where the email column is expected and how I can fix this. Thanks for any help.
Hi, our fix for this issue for our SuiteCRM 7.3, and he add me the solution for it.
His message:
------------------start----------------
The problem is than on this sql part:
OR ( email IN (select bean_id …
should be
OR ( contacts.id IN (select bean_id …
This wrong sql is produced by include/SearchForm/SearchForm2.php + …metadata/SearchFileds.php for Contacts, Accounts, Leads or any ‘person’ module available in global search
A solution is to change
/custom/modules/Contacts/metadata/SearchFileds.php
/custom/modules/Accounts/metadata/SearchFileds.php
/custom/modules/Leads/metadata/SearchFileds.php
files and set the email field like this:
‘email’ =>
array (
‘query_type’ => ‘default’,
‘operator’ => ‘subquery’,
‘subquery’ => ‘SELECT eabr.bean_id FROM email_addr_bean_rel eabr JOIN email_addresses ea ON (ea.id = eabr.email_address_id) WHERE eabr.deleted=0 AND ea.email_address LIKE’,
‘db_field’ =>
array (
0 => ‘contacts.id’,
),
),
I only replaced db_field array value with “0 => ‘contacts.id’” instead of “0 => ‘id’”
Another solution is to install “SecurityGroups - Full Edition” module. This module replaces the SearchForm2.php file with another one, which seems to resolve bugs from original SearchForm2.php
------------------------end-----------------------------------
We hope, that help you guys 
Miky