Accounts disappearing

[lrojas]

Hi Guys, I am running Sugar CRM Version 6.0.3 (Build 4339), php 5.2.11 and mysql 5.0.77. I am having a problem where it seems like accounts are disappearing. I do a search for a name and nothing comes up, I clear the search filters and search again and still nothing comes up. But when I look in the database I see that record that I am looking for in the accounts table. I checked the delete column and its set to 0. Does anyone have any idea why this is happening? Thanks for the help.

[andopes]

Follow the tips at my second post here.

Regards

[lrojas]

Follow the tips at my second post here.

Regards

Hi Andope, thanks for the help. I added the code to that page as you suggested and this is what I get in my CRM:

# SELECT calls.id , calls.status , calls.name , calls.parent_id , calls.parent_type , calls.date_start , calls.assigned_user_id FROM calls where ((calls.status IN ('Planned') )) AND calls.deleted=0 AND calls.id in ( SELECT call_id FROM calls_users WHERE calls_users.deleted = 0 AND (calls.assigned_user_id = '5e213e6e-d858-8d8f-2553-4ced65e428fc' OR calls_users.user_id = '5e213e6e-d858-8d8f-2553-4ced65e428fc') )
# SELECT meetings.id , meetings.status , meetings.name , meetings.parent_id , meetings.parent_type , meetings.date_start , meetings.assigned_user_id FROM meetings where ((meetings.status IN ('Planned') )) AND meetings.deleted=0 AND meetings.id IN ( SELECT meeting_id FROM meetings_users WHERE meetings_users.deleted = 0 AND (meetings.assigned_user_id = '5e213e6e-d858-8d8f-2553-4ced65e428fc' OR meetings_users.user_id = '5e213e6e-d858-8d8f-2553-4ced65e428fc') ) ORDER BY meetings.name asc
# SELECT opportunities.id , opportunities.name , accounts.name account_name, jtl0.account_id account_id, opportunities.amount_usdollar , opportunities.currency_id , opportunities.date_closed , opportunities.assigned_user_id FROM opportunities LEFT JOIN accounts_opportunities jtl0 ON opportunities.id=jtl0.opportunity_id AND jtl0.deleted=0 LEFT JOIN accounts accounts ON accounts.id=jtl0.account_id AND accounts.deleted=0 AND accounts.deleted=0 where ((opportunities.sales_stage IN ('Prospecting','Qualification','Needs Analysis','Value Proposition','Id. Decision Makers','Perception Analysis','Proposal/Price Quote','Negotiation/Review') ) AND (opportunities.assigned_user_id = '5e213e6e-d858-8d8f-2553-4ced65e428fc')) AND opportunities.deleted=0 ORDER BY opportunities.date_closed ASC
# SELECT accounts.id ,accounts_cstm.transcription_c,accounts_cstm.patie nt_coordination_c,accounts_cstm.new_clinic_set_up_ c,accounts_cstm.finders_service_c,accounts_cstm.in fusion_injection_coordin_c,accounts_cstm.insurance _renewals_c,accounts_cstm.alberta_health_initiativ es_c,accounts_cstm.health_and_wellness_c,accounts_ cstm.biologic_managment_c,accounts_cstm.call_centr e_c,accounts_cstm.nursing_support_c,accounts_cstm. clerical_support_c,accounts_cstm.last_visit_c,acco unts_cstm.next_visit_due_c,accounts_cstm.service_r emains_same_c,accounts_cstm.type_of_service_phone_ c,accounts_cstm.type_of_service_visit_c,accounts_c stm.type_of_service_review_email_c,accounts_cstm.d ate_of_birth_c,accounts_cstm.communication_by_emai l_c,accounts_cstm.communication_by_fax_c,accounts_ cstm.communication_by_phone_c,accounts_cstm.signed _premeds_c,accounts_cstm.presigned_insurance_templ ate_c,accounts_cstm.monthly_report_c,accounts_cstm .quarterly_report_c,accounts_cstm.yearly_reports_c ,accounts_cstm.changes_services_c,accounts_cstm.cl inic_c,accounts_cstm.contact_c,accounts_cstm.pager _c,accounts_cstm.cell_c,accounts_cstm.clinic_packa ges_c,accounts_cstm.date_last_sent_c,accounts_cstm .other_email_c,accounts_cstm.service_start_date_c, accounts_cstm.signed_contract_date_c,accounts_cstm .otherfax_c,accounts_cstm.otherpager_c,accounts_cs tm.mobile_c,accounts_cstm.community_c,accounts_cst m.hospital_c,accounts_cstm.hospitalname_c,accounts _cstm.type_of_specialist_c,accounts_cstm.nurse_pra cticitoner_c,accounts_cstm.option1_c,accounts_cstm .option2_c,accounts_cstm.diphenhydramine_c,account s_cstm.acetaminophen_c,accounts_cstm.hydrocortison e_c,accounts_cstm.dimenhydrinate_c,accounts_cstm.d esloratadine_c,accounts_cstm.other_c,accounts_cstm .diphenhydramine1_c,accounts_cstm.acetaminophen1_c ,accounts_cstm.hydrocortisone1_c,accounts_cstm.dim enhydrinate1_c,accounts_cstm.desloratadine1_c,acco unts_cstm.other1_c,accounts_cstm.nurserphone_c,acc ounts_cstm.nursefax_c,accounts_cstm.nursepager_c,a ccounts_cstm.nurseemail_c,accounts_cstm.nursingpho ne_c,accounts_cstm.nursingfax_c,accounts_cstm.nurs ingemail_c,accounts_cstm.nursingpager_c,accounts_c stm.clericalphone_c,accounts_cstm.clericalpager_c, accounts_cstm.clericalfax_c,accounts_cstm.clerical email_c,accounts_cstm.datesent_c,accounts_cstm.sho rtenedinfusion_c, accounts.name , accounts.website , accounts.phone_office , accounts.billing_address_country , accounts.assigned_user_id FROM accounts LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c where ((accounts.assigned_user_id = '5e213e6e-d858-8d8f-2553-4ced65e428fc')) AND accounts.deleted=0
# SELECT leads.id , CONCAT(IFNULL(leads.first_name,''),' ',IFNULL(leads.last_name,'')) as name, leads.first_name , leads.last_name , leads.title , leads.phone_work , leads.assigned_user_id ,leads.account_name FROM leads where ((leads.assigned_user_id = '5e213e6e-d858-8d8f-2553-4ced65e428fc')) AND leads.deleted=0
# SELECT sugarfeed.id , sugarfeed.name , sugarfeed.description , sugarfeed.date_entered , sugarfeed.created_by , sugarfeed.link_url , sugarfeed.link_type , sugarfeed.assigned_user_id FROM sugarfeed where ( sugarfeed.related_module in ('UserFeed','Contacts','Opportunities','Cases','Le ads')) AND sugarfeed.deleted=0 ORDER BY sugarfeed.date_entered DESC

What does all that mean?

[andopes]

Go to Accounts ListView and let us know the query.

Regards

[lrojas]

Go to Accounts ListView and let us know the query.

Regards

this is what I get

SELECT accounts.id ,accounts_cstm.transcription_c,accounts_cstm.patie nt_coordination_c,accounts_cstm.new_clinic_set_up_ c,accounts_cstm.finders_service_c,accounts_cstm.in fusion_injection_coordin_c,accounts_cstm.insurance _renewals_c,accounts_cstm.alberta_health_initiativ es_c,accounts_cstm.health_and_wellness_c,accounts_ cstm.biologic_managment_c,accounts_cstm.call_centr e_c,accounts_cstm.nursing_support_c,accounts_cstm. clerical_support_c,accounts_cstm.last_visit_c,acco unts_cstm.next_visit_due_c,accounts_cstm.service_r emains_same_c,accounts_cstm.type_of_service_phone_ c,accounts_cstm.type_of_service_visit_c,accounts_c stm.type_of_service_review_email_c,accounts_cstm.d ate_of_birth_c,accounts_cstm.communication_by_emai l_c,accounts_cstm.communication_by_fax_c,accounts_ cstm.communication_by_phone_c,accounts_cstm.signed _premeds_c,accounts_cstm.presigned_insurance_templ ate_c,accounts_cstm.monthly_report_c,accounts_cstm .quarterly_report_c,accounts_cstm.yearly_reports_c ,accounts_cstm.changes_services_c,accounts_cstm.cl inic_c,accounts_cstm.contact_c,accounts_cstm.pager _c,accounts_cstm.cell_c,accounts_cstm.clinic_packa ges_c,accounts_cstm.date_last_sent_c,accounts_cstm .other_email_c,accounts_cstm.service_start_date_c, accounts_cstm.signed_contract_date_c,accounts_cstm .otherfax_c,accounts_cstm.otherpager_c,accounts_cs tm.mobile_c,accounts_cstm.community_c,accounts_cst m.hospital_c,accounts_cstm.hospitalname_c,accounts _cstm.type_of_specialist_c,accounts_cstm.nurse_pra cticitoner_c,accounts_cstm.option1_c,accounts_cstm .option2_c,accounts_cstm.diphenhydramine_c,account s_cstm.acetaminophen_c,accounts_cstm.hydrocortison e_c,accounts_cstm.dimenhydrinate_c,accounts_cstm.d esloratadine_c,accounts_cstm.other_c,accounts_cstm .diphenhydramine1_c,accounts_cstm.acetaminophen1_c ,accounts_cstm.hydrocortisone1_c,accounts_cstm.dim enhydrinate1_c,accounts_cstm.desloratadine1_c,acco unts_cstm.other1_c,accounts_cstm.nurserphone_c,acc ounts_cstm.nursefax_c,accounts_cstm.nursepager_c,a ccounts_cstm.nurseemail_c,accounts_cstm.nursingpho ne_c,accounts_cstm.nursingfax_c,accounts_cstm.nurs ingemail_c,accounts_cstm.nursingpager_c,accounts_c stm.clericalphone_c,accounts_cstm.clericalpager_c, accounts_cstm.clericalfax_c,accounts_cstm.clerical email_c,accounts_cstm.datesent_c,accounts_cstm.sho rtenedinfusion_c, accounts.name , accounts.billing_address_city , accounts.billing_address_country , accounts.phone_office , jt0.user_name assigned_user_name , jt0.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, accounts.assigned_user_id FROM accounts LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c LEFT JOIN users jt0 ON jt0.id= accounts.assigned_user_id AND jt0.deleted=0 AND jt0.deleted=0 where accounts.deleted=0 ORDER BY accounts.name ASC

[andopes]

What happen if you run this query at phpMyAdmin?
If I remove all the accounts_cstm fields (obviously they don´t exist at my database) it works great, so I believe one of the custom fields is buggy.

Cheers

[lrojas]

What happen if you run this query at phpMyAdmin?
If I remove all the accounts_cstm fields (obviously they don´t exist at my database) it works great, so I believe one of the custom fields is buggy.

Cheers

Here is the result from phpMyAdmin:

Error

SQL query: Documentation

SELECT accounts.id ,accounts_cstm.transcription_c,accounts_cstm.patie nt_coordination_c,accounts_cstm.new_clinic_set_up_ c,accounts_cstm.finders_service_c,accounts_cstm.in fusion_injection_coordin_c,accounts_cstm.insurance _renewals_c,accounts_cstm.alberta_health_initiativ es_c,accounts_cstm.health_and_wellness_c,accounts_ cstm.biologic_managment_c,accounts_cstm.call_centr e_c,accounts_cstm.nursing_support_c,accounts_cstm. clerical_support_c,accounts_cstm.last_visit_c,acco unts_cstm.next_visit_due_c,accounts_cstm.service_r emains_same_c,accounts_cstm.type_of_service_phone_ c,accounts_cstm.type_of_service_visit_c,accounts_c stm.type_of_service_review_email_c,accounts_cstm.d ate_of_birth_c,accounts_cstm.communication_by_emai l_c,accounts_cstm.communication_by_fax_c,accounts_ cstm.communication_by_phone_c,accounts_cstm.signed _premeds_c,accounts_cstm.presigned_insurance_templ ate_c,accounts_cstm.monthly_report_c,accounts_cstm .quarterly_report_c,accounts_cstm.yearly_reports_c ,accounts_cs[...]

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.biologic_managment_c,accounts_cstm.call_centr e_c,accounts_cstm.nursing_support' at line 1

[andopes]

The error I can see here is a blank space here "accounts_cstm.call_centr e_c".
Perhaps it had been added by browser.
Go to Admin -> System Settings and update Log level to debug.
Try again Accounts ListView.
Edit sugarcrm.log and look for the query you see at browser.
Copy this query from sugarcrm.log and run in phpmyadmin.

Let us know the issue.

Cheers

[lrojas]

I just notice that there is some weird spaces on that query. I removed them and ran it again, but it keeps coming back with Syntax errors.

[lrojas]

The error I can see here is a blank space here "accounts_cstm.call_centr e_c".
Perhaps it had been added by browser.
Go to Admin -> System Settings and update Log level to debug.
Try again Accounts ListView.
Edit sugarcrm.log and look for the query you see at browser.
Copy this query from sugarcrm.log and run in phpmyadmin.

Let us know the issue.

Cheers

When I change the Log level to Debug and save, it keeps going back to Fatal. what could be causing that?