count database problem

[andreasbalzer]

Hi,
I have a big problem with my sugarcrm installation at the moment.

I have added a drop down to the company/account module and I get the following error message. However I don't know whether it is related to the new field. Can you please help me to solve this?

Error running count query for Account List: Query Failed SELECT count(*) c FROM meetings AND contacts.deleted=0 LEFT JOIN users jt1 ON jt1.id= meetings.assigned_user_id AND jt1.deleted=0 AND jt1.deleted=0 where ( meetings.parent_id= 'a1d3d600-9029-a5d9-c36e-48623905193f' AND meetings.parent_type='Accounts' AND meetings.deleted=0 AND (meetings.status='Planned')) AND meetings.deleted=0 ) UNION ALL ( SELECT count(*) c FROM tasks LEFT JOIN contacts contacts ON contacts.id= tasks.contact_id AND contacts.deleted=0 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.parent_id= 'a1d3d600-9029-a5d9-c36e-48623905193f' AND tasks.parent_type='Accounts' AND tasks.deleted=0 AND (tasks.status='Not Started' OR tasks.status='In Progress' OR tasks.status='Pending Input')) AND tasks.deleted=0 ) UNION ALL ( SELECT count(*) c FROM calls AND contacts.deleted=0 LEFT JOIN users jt1 ON jt1.id= calls.assigned_user_id AND jt1.deleted=0 AND jt1.deleted=0 where ( calls.parent_id= 'a1d3d600-9029-a5d9-c36e-48623905193f' AND calls.parent_type='Accounts' AND calls.deleted=0 AND (calls.status='Planned')) AND calls.deleted=0 )::MySQL error 1064:

Greetings,
Andreas

[kinshibuya]

Have you tryed to repair DB?

[andopes]

Hi Andreas.

Did you make any customization or code modification in you installation?
Which version and flavor are you using?

Additional details are always welcome.

Best regards.

[andreasbalzer]

I used 5.0.0f but upgraded to 5.0.0g where the same (and several more) error occurs. With both versions a repair didn't help. I also have an own field for the accounts module called sportstype. It is a dropdown menu. I might be that the error started to appear when I created it. How to check?

[andreasbalzer]

I removed the field again, but the error is still there. What to do?

The error appears also if I select an account in the account module. It always appears at the bottom of the page.

[nirmit]

Hey Andreas,

the problem is that the query for meetings is trying to implement a condition on the contacts table. Can you check your vardefs.php for Accounts?

SELECT count( * ) c
FROM meetings
AND contacts.deleted =0 // This line should not be there.
LEFT JOIN users jt1 ON jt1.id = meetings.assigned_user_id
AND jt1.deleted =0
AND jt1.deleted =0
WHERE (
meetings.parent_id = 'a1d3d600-9029-a5d9-c36e-48623905193f'
AND meetings.parent_type = 'Accounts'
AND meetings.deleted =0
AND (
meetings.status = 'Planned'
)
)
AND meetings.deleted =0