Add task (activity) to custom module in 6.5.5 corrupts database

[Ramblin]

This will sound strange, but hopefully I can explain it.

Has anyone else had issues with database errors when adding tasks to custom modules after upgrading to 6.4.x?

I have been working on SugarCRM 6.2.6 successfully now for some time now and was very happy with it.

I recently had several reasons to upgrade so went to 6.5.5, via 6.4 first
The upgrade path was
6.2.6 -> 6.4 using Upgrade Wizard
6.4 -> 6.4.5 using Silent Upgrade (Wizard would not work) : database problems first appeared
6.4.5 -> 6.5.5 using upgrade wizard : database problems still appear

Now I have problems with a custom module (Event Manager) I created to help manage events.
(see Event Manager - installable pkg + documentation for the Event Manager I created)

The Event Manager was created in Module Builder (in the v6.2.6 version of SugarCRM) and then customized to suit.

It worked fine while I was in v 6.2.6

Now that I have upgraded to v 6.5.5, whenever I add a task (in the Activities subpanel of the Event Manager), I get a database error and the user interface gets polluted, with the new view of the Event manager looking like there is no CSS being applied.

I know the database architecture was changed in 6.4.x but I thought the upgrades would fix the standard as well as custom modules to keep them working.

Has anyone else had issues with database errors when adding tasks to custom modules after upgrading to 6.4.x?

[jmertic]

Can you post the database error you are getting? Probably a bad query.

Also, did you try a quick rebuild and repair?

[Ramblin]

I did try a Repair and Rebuild. I also tried flushing the Cash and then doing a repair and rebuild. No luck.

The error(s) I am getting is (from this morning when I tried again to add a task to a new Event:

(Part 1 of 2 - # char exceeded in the post when I included the entire error set

Thu Sep 27 07:34:19 2012 [5030][1][FATAL] Error retrieving EvMgr_Ev list:  Query Failed: (SELECT meetings.id ,  meetings.name ,  meetings.status ,  '                                                                                                                                                                                                                                                              ' contact_name ,  '                                    '  contact_id ,  meetings.date_start  ,  LTRIM(RTRIM(CONCAT(IFNULL(jt1.first_name,''),' ',IFNULL(jt1.last_name,'')))) assigned_user_name ,  meetings.assigned_user_id  ,  jt1.created_by assigned_user_name_owner  ,  'Users' assigned_user_name_mod,  '                                                                                                                                                                                                                                                              ' contact_name_owner ,  '                                                                                                                                                                                                                                                              ' contact_name_mod ,  meetings.recurring_source ,  meetings.created_by  ,  'meetings' panel_name  FROM meetings   LEFT JOIN  users jt1 ON meetings.assigned_user_id=jt1.id AND jt1.deleted=0

 AND jt1.deleted=0 INNER JOIN  evmgr_ev evmgr_ev_activities_meetings_rel ON meetings.parent_id=evmgr_ev_activities_meetings_rel.id AND evmgr_ev_activities_meetings_rel.deleted=0
 AND parent_type = 'EvMgr_Ev'
  where ( meetings.parent_id='c5842af9-0876-6b6d-f93a-5064398e6cf8' AND (meetings.status !='Held' AND meetings.status !='Not Held')) AND meetings.deleted=0) UNION ALL ( SELECT tasks.id ,  tasks.name ,  tasks.status  ,  LTRIM(RTRIM(CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')))) contact_name ,  tasks.contact_id ,  tasks.date_due  as date_start  ,  LTRIM(RTRIM(CONCAT(IFNULL(jt1.first_name,''),' ',IFNULL(jt1.last_name,'')))) assigned_user_name ,  tasks.assigned_user_id  ,  jt1.created_by assigned_user_name_owner  ,  'Users' assigned_user_name_mod,  '                                                                                                                                                                                                                                                              ' contact_name_owner ,  '                                                                                                                                                                                                                                                              ' contact_name_mod , ' ' recurring_source,  tasks.created_by  ,  'tasks' panel_name  FROM tasks   LEFT JOIN  contacts contacts ON tasks.contact_id=contacts.id AND contacts.deleted=0

 AND contacts.deleted=0  LEFT JOIN  users jt1 ON tasks.assigned_user_id=jt1.id AND jt1.deleted=0

 AND jt1.deleted=0 INNER JOIN  evmgr_ev evmgr_ev_activities_tasks_rel ON tasks.parent_id=evmgr_ev_activities_tasks_rel.id AND evmgr_ev_activities_tasks_rel.deleted=0
 AND parent_type = 'EvMgr_Ev'
  where ( tasks.parent_id='c5842af9-0876-6b6d-f93a-5064398e6cf8' AND (tasks.status != 'Completed' AND tasks.status != 'Deferred')) AND tasks.deleted=0 ) UNION ALL ( SELECT calls.id ,  calls.name ,  calls.status ,  '                                                                                                                                                                                                                                                              ' contact_name ,  '                                    '  contact_id ,  calls.date_start  ,  LTRIM(RTRIM(CONCAT(IFNULL(jt1.first_name,''),' ',IFNULL(jt1.last_name,'')))) assigned_user_name ,  calls.assigned_user_id  ,  jt1.created_by assigned_user_name_owner  ,  'Users' assigned_user_name_mod,  '                                                                                                                                                                                                                                                              ' contact_name_owner ,  '                                                                                                                                                                                                                                                              ' contact_name_mod ,  calls.recurring_source ,  calls.created_by  ,  'calls' panel_name  FROM calls   LEFT JOIN  users jt1 ON calls.assigned_user_id=jt1.id AND jt1.deleted=0

 AND jt1.deleted=0 INNER JOIN  evmgr_ev evmgr_ev_activities_calls_rel ON calls.parent_id=evmgr_ev_activities_calls_rel.id AND evmgr_ev_activities_calls_rel.deleted=0
 AND parent_type = 'EvMgr_Ev'
  where ( calls.parent_id='c5842af9-0876-6b6d-f93a-5064398e6cf8' AND (calls.status != 'Held' AND calls.status != 'Not Held')) AND calls.deleted=0 ) ORDER BY date_start  desc LIMIT 0,10: MySQL error 1052: Column 'parent_type' in on clause is ambiguous
Thu Sep 27 07:34:21 2012 [5025][1][FATAL] Error retrieving EvMgr_Ev list:  Query Failed: (SELECT meetings.id ,  meetings.name ,  meetings.status ,  '                                                                                                                                                                                                                                                              ' contact_name ,  '                                    '  contact_id ,  meetings.date_start  ,  LTRIM(RTRIM(CONCAT(IFNULL(jt1.first_name,''),' ',IFNULL(jt1.last_name,'')))) assigned_user_name ,  meetings.assigned_user_id  ,  jt1.created_by assigned_user_name_owner  ,  'Users' assigned_user_name_mod,  '                                                                                                                                                                                                                                                              ' contact_name_owner ,  '                                                                                                                                                                                                                                                              ' contact_name_mod ,  meetings.recurring_source ,  meetings.created_by  ,  'meetings' panel_name  FROM meetings   LEFT JOIN  users jt1 ON meetings.assigned_user_id=jt1.id AND jt1.deleted=0

 AND jt1.deleted=0 INNER JOIN  evmgr_ev evmgr_ev_activities_meetings_rel ON meetings.parent_id=evmgr_ev_activities_meetings_rel.id AND evmgr_ev_activities_meetings_rel.deleted=0
 AND parent_type = 'EvMgr_Ev'
  where ( meetings.parent_id='c5842af9-0876-6b6d-f93a-5064398e6cf8' AND (meetings.status !='Held' AND meetings.status !='Not Held')) AND meetings.deleted=0) UNION ALL ( SELECT tasks.id ,  tasks.name ,  tasks.status  ,  LTRIM(RTRIM(CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')))) contact_name ,  tasks.contact_id ,  tasks.date_due  as date_start  ,  LTRIM(RTRIM(CONCAT(IFNULL(jt1.first_name,''),' ',IFNULL(jt1.last_name,'')))) assigned_user_name ,  tasks.assigned_user_id  ,  jt1.created_by assigned_user_name_owner  ,  'Users' assigned_user_name_mod,  '                                                                                                                                                                                                                                                              ' contact_name_owner ,  '                                                                                                                                                                                                                                                              ' contact_name_mod , ' ' recurring_source,  tasks.created_by  ,  'tasks' panel_name  FROM tasks   LEFT JOIN  contacts contacts ON tasks.contact_id=contacts.id AND contacts.deleted=0

[Ramblin]

part 2 of 2

 AND contacts.deleted=0  LEFT JOIN  users jt1 ON tasks.assigned_user_id=jt1.id AND jt1.deleted=0

 AND jt1.deleted=0 INNER JOIN  evmgr_ev evmgr_ev_activities_tasks_rel ON tasks.parent_id=evmgr_ev_activities_tasks_rel.id AND evmgr_ev_activities_tasks_rel.deleted=0
 AND parent_type = 'EvMgr_Ev'
  where ( tasks.parent_id='c5842af9-0876-6b6d-f93a-5064398e6cf8' AND (tasks.status != 'Completed' AND tasks.status != 'Deferred')) AND tasks.deleted=0 ) UNION ALL ( SELECT calls.id ,  calls.name ,  calls.status ,  '                                                                                                                                                                                                                                                              ' contact_name ,  '                                    '  contact_id ,  calls.date_start  ,  LTRIM(RTRIM(CONCAT(IFNULL(jt1.first_name,''),' ',IFNULL(jt1.last_name,'')))) assigned_user_name ,  calls.assigned_user_id  ,  jt1.created_by assigned_user_name_owner  ,  'Users' assigned_user_name_mod,  '                                                                                                                                                                                                                                                              ' contact_name_owner ,  '                                                                                                                                                                                                                                                              ' contact_name_mod ,  calls.recurring_source ,  calls.created_by  ,  'calls' panel_name  FROM calls   LEFT JOIN  users jt1 ON calls.assigned_user_id=jt1.id AND jt1.deleted=0

 AND jt1.deleted=0 INNER JOIN  evmgr_ev evmgr_ev_activities_calls_rel ON calls.parent_id=evmgr_ev_activities_calls_rel.id AND evmgr_ev_activities_calls_rel.deleted=0
 AND parent_type = 'EvMgr_Ev'
  where ( calls.parent_id='c5842af9-0876-6b6d-f93a-5064398e6cf8' AND (calls.status != 'Held' AND calls.status != 'Not Held')) AND calls.deleted=0 ) ORDER BY date_start  desc LIMIT 0,10: MySQL error 1052: Column 'parent_type' in on clause is ambiguous

[jmertic]

Looks like some sort of customizations to the query is causing the problem ( the 'AND parent_type' clause in each UNIONed query ). Could you try to see how that got added in there?

[Ramblin]

I did not create this query; it must have been a system-generated query created when I built the custom module in Module Builder (in v 6.2.4 when I built it)

I do not use the meetings module nor the calls module in any of my CRM and whatever parent_id or parent_name in this module would be from Module Builder

Where I did create a special parent relationship was in Contacts. I added three additional Account Types: Facilitator_Company, Caterer_Company and Venue so I could have the information for each Account type display differently. So on my Contacts, I added a Flex-Relate field where I chose the Account Type and then chose the actual "Account" from the resulting choices. Could that be it? I do not use the Flex_Relate field in the Event Manager module.

Richard

[jmertic]

Hmm, so you are saying that parent_type is in your custom module as well, right? Assuming there is nothing hardcoded here for the join, I'm betting there is a bug here. Could you report it at http://bugs.sugarcrm.com and I'll have our maint team look more into it.

[Ramblin]

#jmertic @jmertic

john,

I created a bug report number 56998
see
Bug Portal | SugarCRM.com English (Americas)

Note that, in case it was an issue with the upgrade process from 6.2.6 to 6.5.5, I tried it on a fresh install of 6.5.5 and the issue was still there.

I am guessing that the new database architecture introduced in 6.4 has caused something to go awry with a custom module designed around 6.2.6 ?

This is a mission-critical thing for me; I cannot lose the custom module functionality so I cannot upgrade past 6.2.6 unless we solve this. I'd like to take advantage of some of the security fixes and other enhancements in 6.5.5 ...

Thank for helping.

Ramblin

[Ramblin]

It turns out the issue was not restricted to adding tasks in the custom module.

The issue was not present in 6.2.4 or 6.2.6
It became present in 6.3
It stayed present in 6.4
It went away with 6.5.4
It returned when I went above 6.5.4 (6.5.5, 6.5.7 tested)

This is a repeatable issue as described.

So, apparently there is something wrong in the core SugarCRM code.

I will submit a separate bug report re this with more details.
( Bug 58643 at http://www.sugarcrm.com/support/bugs.html#issue_58643 )

[Ramblin]

John, #jmertic @jmertic

Could you report it at Bug Portal | SugarCRM.com English (Americas) and I'll have our maint team look more into it.

My initial request was about the custom module code but, after some work, it was determined that there was a SugarCRM bug. (see bug report shown in previous post)

Any help you can provide in getting the bug report dealt with and getting the answer on what I do to get the coed fix would be appreciated

Thanks

Richard