Errors in sugarlog, mysql and relationships

[sbankhead]

Im getting the following errors in my sugarCRM log. It seems to happen any time I try and click on a task

Tue Mar 20 10:24:41 2012 [40527][3da2667e-bec7-97df-52c2-4f431ca06625][ERROR] Unable to find relationship projecttask_cases_1
Tue Mar 20 10:24:41 2012 [40527][3da2667e-bec7-97df-52c2-4f431ca06625][FATAL] projecttask_cases_1 for projecttask_cases_1 failed to load

Tue Mar 20 10:24:41 2012 [40527][3da2667e-bec7-97df-52c2-4f431ca06625][ERROR] Cannot parse for format m/d/Y h:ia
Tue Mar 20 10:24:41 2012 [40527][3da2667e-bec7-97df-52c2-4f431ca06625][FATAL] Missing relationship definition projecttask_accounts. skipping projecttask_accounts subpanel
Tue Mar 20 10:24:45 2012 [40523][3da2667e-bec7-97df-52c2-4f431ca06625][ERROR] Unable to get proper side for link tasks_activities_tasks
Tue Mar 20 10:24:45 2012 [40523][3da2667e-bec7-97df-52c2-4f431ca06625][FATAL] Error running count query for Task List: Query Failed: ( SELECT count(*) c FROM meetings INNER JOIN tasks tasks_activities_meetings_rel ON meetings.parent_id=tasks_activities_meetings_rel.i d AND tasks_activities_meetings_rel.deleted=0
AND parent_type = 'Tasks'
where ( meetings.parent_id='bfe8d3ce-b7ab-5600-ad3a-4f664a4995b1' AND (meetings.status !='Held' AND meetings.status !='Not Held')) AND meetings.deleted=0 ) UNION ALL ( SELECT count(*) c FROM tasks INNER JOIN tasks tasks_activities_tasks_rel ON tasks.parent_id=tasks_activities_tasks_rel.id AND tasks_activities_tasks_rel.deleted=0
AND parent_type = 'Tasks'
where ( tasks.parent_id='bfe8d3ce-b7ab-5600-ad3a-4f664a4995b1' AND (tasks.status != 'Completed' AND tasks.status != 'Deferred')) AND tasks.deleted=0 ) UNION ALL ( SELECT count(*) c FROM calls INNER JOIN tasks tasks_activities_calls_rel ON calls.parent_id=tasks_activities_calls_rel.id AND tasks_activities_calls_rel.deleted=0
AND parent_type = 'Tasks'
where ( calls.parent_id='bfe8d3ce-b7ab-5600-ad3a-4f664a4995b1' AND (calls.status != 'Held' AND calls.status != 'Not Held')) AND calls.deleted=0 ): MySQL error 1052: Column 'parent_type' in on clause is ambiguous

Now I believe there are 3 errors in here. 2 for relationships and 1 sql query error. Beyond that I really have no idea what the real issue is, as im still getting new to SugarCRM. Some back story, we recently upgraded our servers from 6.2.4 to 6.4.1. I have tried rebuild relationships and quick repair but that did not fix the problem. Can anyone help point me in the right direction as to what my problems really are, and even better a resolution to fixing them? Google searches basically led me to to the repairs and that fixed it for some people but not others, and not me.

[sbankhead]

By the way, we have a copy of our system before the upgrade, and in our old system 6.2.4 this is not an issue, so it is something to do with the upgrade. Any help would really be appreciated, as every task loads in a new window and it looks really bad.

[sbankhead]

SQL Query:
SELECT count(*) c FROM meetings
INNER JOIN tasks tasks_activities_meetings_rel
ON meetings.parent_id=tasks_activities_meetings_rel.i d
AND tasks_activities_meetings_rel.deleted=0
AND parent_type = 'Tasks'
where ( meetings.parent_id='710d45ba-7bc4-c37f-6b9e-4f73d6b428d6'
AND (meetings.status !='Held' AND meetings.status !='Not Held'))
AND meetings.deleted=0 )
UNION ALL (
SELECT count(*) c FROM tasks
INNER JOIN tasks tasks_activities_tasks_rel
ON tasks.parent_id=tasks_activities_tasks_rel.id
AND tasks_activities_tasks_rel.deleted=0
AND parent_type = 'Tasks'
where ( tasks.parent_id='710d45ba-7bc4-c37f-6b9e-4f73d6b428d6'
AND (tasks.status != 'Completed' AND tasks.status != 'Deferred'))
AND tasks.deleted=0 )
UNION ALL (
SELECT count(*) c FROM calls
INNER JOIN tasks tasks_activities_calls_rel
ON calls.parent_id=tasks_activities_calls_rel.id
AND tasks_activities_calls_rel.deleted=0
AND parent_type = 'Tasks'
where ( calls.parent_id='710d45ba-7bc4-c37f-6b9e-4f73d6b428d6'
AND (calls.status != 'Held' AND calls.status != 'Not Held'))
AND calls.deleted=0 )

So this error happens every time you click on a task either from the tasks module or a module which has a tasks subpanel. I know the error is happening because it can't figure out which parent_type column to use from each of the 3 select statements. From what I can tell I just need to modify the sql query to call the appropriate table in front of the parent_type column and it should work fine (Tested the script in phpMyAdmin with the fix and it returned results). Only problem I have is I cant find where this SQL code is generated from in the code. I have been all up down the modules/tasks folder and I don't seem to be able to find it. Can anyone tell me where the call is coming from, or a hint as to a way I can figure that out for myself?

[blak3r]

Definitely not an expert on the database code by any means... But, I dont think you're going to find what looks like a sql statement. They have a wrappers which generate all that to be database agnostic.

You could turn on DEBUG logging and I think you'll probably see some statements which happen right before the SQL query happens. You could then look up the chain from there.

Another idea would be to search for "where" in the appropriate module directories... I think the database method they use is something like addWhereCond()...

[sbankhead]

So I ended up having to change the following function logic in SugarBean.php to prevent the above script from killing everything and loading a new window. Still can't figure out where that sql call is being generated from. Been running grep searches looking for various strings to help lead me to the source which got me here, but haven't had much luck. Changing logic to debug didn't seem to give me any new info either.

function _get_num_rows_in_query($query, $is_count_query=false)
{
$num_rows_in_query = 0;

if (!$is_count_query)
{
$count_query = SugarBean::create_list_count_query($query);
} else
$count_query=$query;

//changed to not die on failed query
$result = $this->db->query($count_query, false, "Error3 running count query for $this->object_name List: ");
//changed so we don't do anything with results if we failed
if ($result)
{
while($row = $this->db->fetchByAssoc($result, true))
{
$num_rows_in_query += current($row);
}
}
else
{
$num_rows_in_query = 0;
}

return $num_rows_in_query;
}