sql error after upgrading to 4.0

[jachin]

hi there

I'm getting rather long SQL error message on two different installtions of Sugar after installing the update to 4.0

It comes up just right after the case information when a case's details are viewed:

Error retrieving Case list: Query Failed:( SELECT meetings.id , CONCAT(IFNULL(meetings.date_start,''),' ',IFNULL(meetings.time_start,'')) as date_time, meetings.status , meetings.name , ' ' contact_name , ' ' contact_id , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, meetings.assigned_user_id , meetings.parent_id , meetings.parent_type , ' ' filename , meetings.date_start , meetings.time_start , meetings.description , meetings.assigned_user_id , 'meetings' panel_name FROM meetings LEFT JOIN users jt1 ON jt1.id= meetings.assigned_user_id AND jt1.deleted=0 AND jt1.deleted=0 where ( meetings.parent_id= '44af6c66-b62d-b6ba-34b8-43cf361ad333' AND meetings.parent_type='Cases' AND meetings.deleted=0 AND (meetings.status='Held' OR meetings.status='Not Held')) AND meetings.deleted=0 ) UNION ALL ( SELECT tasks.id , CONCAT(IFNULL(tasks.date_modified,'')) as date_time, tasks.status , tasks.name , CONCAT(IFNULL(jt0.first_name,''),' ',IFNULL(jt0.last_name,'')) contact_name , tasks.contact_id , jt0.assigned_user_id contact_name_owner , 'Contacts' contact_name_mod, tasks.contact_id , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, tasks.assigned_user_id , tasks.parent_id , tasks.parent_type , ' ' filename , tasks.date_due , tasks.time_due , tasks.description , tasks.assigned_user_id , 'tasks' panel_name FROM tasks LEFT JOIN contacts jt0 ON jt0.id= tasks.contact_id AND jt0.deleted=0 AND jt0.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= '44af6c66-b62d-b6ba-34b8-43cf361ad333' AND tasks.parent_type='Cases' AND tasks.deleted=0 AND (tasks.status='Completed' OR tasks.status='Deferred')) AND tasks.deleted=0 ) UNION ALL ( SELECT calls.id , CONCAT(IFNULL(calls.date_start,''),' ',IFNULL(calls.time_start,'')) as date_time, calls.status , calls.name , ' ' contact_name , ' ' contact_id , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, calls.assigned_user_id , calls.parent_id , calls.parent_type , ' ' filename , calls.date_start , calls.time_start , calls.description , calls.assigned_user_id , 'calls' panel_name FROM calls LEFT JOIN users jt1 ON jt1.id= calls.assigned_user_id AND jt1.deleted=0 AND jt1.deleted=0 where ( calls.parent_id= '44af6c66-b62d-b6ba-34b8-43cf361ad333' AND calls.parent_type='Cases' AND calls.deleted=0 AND (calls.status='Held' OR calls.status='Not Held')) AND calls.deleted=0 ) UNION ALL ( SELECT notes.id , CONCAT(IFNULL(notes.date_modified,'')) as date_time, ' ' status , notes.name , CONCAT(IFNULL(jt0.first_name,''),' ',IFNULL(jt0.last_name,'')) contact_name , notes.contact_id , jt0.assigned_user_id contact_name_owner , 'Contacts' contact_name_mod, notes.contact_id , jt1.user_name modified_user_id , jt1.created_by modified_user_id_owner , 'Users' modified_user_id_mod, ' ' assigned_user_id , notes.parent_id , notes.parent_type , notes.filename , ' ' date_start , ' ' time_start , notes.description , notes.created_by , 'notes' panel_name FROM notes LEFT JOIN contacts jt0 ON jt0.id= notes.contact_id AND jt0.deleted=0 AND jt0.deleted=0 LEFT JOIN users jt1 ON jt1.id= notes.modified_user_id AND jt1.deleted=0 AND jt1.deleted=0 where ( notes.parent_id= '44af6c66-b62d-b6ba-34b8-43cf361ad333' AND notes.parent_type='Cases' AND notes.deleted=0) AND notes.deleted=0 ) UNION ALL ( SELECT emails.id , CONCAT(IFNULL(emails.date_start,''),' ',IFNULL(emails.time_start,'')) as date_time, emails.type , emails.name , ' ' full_name , ' ' contact_id , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, emails.assigned_user_id , emails.parent_id , emails.parent_type , emails.date_modified , emails.date_start , emails.time_start , emails.description , emails.assigned_user_id , 'emails' panel_name FROM emails LEFT JOIN users jt1 ON jt1.id= emails.assigned_user_id AND jt1.deleted=0 AND jt1.deleted=0 where ( emails.parent_id= '44af6c66-b62d-b6ba-34b8-43cf361ad333' AND emails.parent_type='Cases' AND emails.deleted=0 AND (emails.status<>'send_error' AND emails.type<>'draft')) AND emails.deleted=0 )::MySQL error 1222: The used SELECT statements have a different number of columns

I'm not sure if this is enough information to solve the problem, so if there's something I'm missing I'd be happy to supply it.

I saw some other posts with SQL errors in them but they all looked like they were different errors.

If I find a solution I'll be sure to post it, otherwise any help or suggestion would be great.

thanks

-jachin

[joey]

Have you run the Rebuild Relationships script under the Admin->Repair screen? This may solve your problem.

Regards,
Joey P.
SugarCRM

[jachin]

hi there

yah I did try that. I tried all the rebuilding and repairing I could do through the Admin->Repare screen.

-jachin

[jachin]

hi there

I found a much shorter version of the eorror message in the sugarcrm.log file.

Mon Jan 23 07:17:56 2006,210 [24150] FATAL SugarCRM - MySQL error 1222: The used SELECT statements have a different number of columns

Basically this happens every time someone looks at a case.

-jachin

[jachin]

hi there

alright... I tried a few more things. We have a couple of different servers we run Sugar on.

I upgraded to instillations of SugarCRM, one on each server from 3 to 4 and they both ended up with this SQL error.

However I also did a clean install of SugarCRM 4 for another client and everything seems to work great.

Just too see if it would help I backed up all the files on one of the installations that was broken and replaced all the files (except for the config files) with the ones the full version (not the patch). In case something went wrong with the patch but that did not seem to help.

I can only assume there is something wrong with the database. Maybe the database didn't get patch right or I ran the wrong patch when I upgraded and something did get set right.

Is there some easy way to make sure the database is setup right? Or maybe rerun the database patches that I might have missed?

-jachin

[jachin]

hi there

I think I know what happend... but I still need help. There are modules installed that are for SugarCRM 3.5. My guess is if I uninstall them the problem will go away and I can just wait for new version of the modules that support SugarCRM 4.

Where I need help is, when I go to the admin->Module Loader the option to uninstall any of the modles is not there. Is there some way to manually remove them?

thanks

-jachin

[jachin]

hi there

I just thought I would say that I have managed to fix the problem.... in a very ugly way.

The basic idea was that I started with a fresh install of sugar. I took the MySQL database dump from the old (broken) SugarCRM install. Got out all the INSERT statements. Then dumped those back into the new (working) SurgarCRM. Now it wasn't quite that simple... there were some insert statments I had to fix, other I had to edit, others I had to skip.... a very messy business.

I still havin't tested everything yet but so far things are looking pretty good.

I would Strongly encourage anyone to just avoid this situation all together by being very careful about installing modules and then upgrading Sugar and back up everything every time.

I would also like to give kutos to who ever made this software.... if I was able to go in and do a manual fix like this (all in one night) and end up with something that appears to be working you must have done quite a few things right. Keep up the good work.

-jachin

[ttlinna]

It seems that the separate SQL SELECT queries select different number of fields. In UNION ALL-clause number of fields dhould be the same.

Has anyone got a clue how to fix this problem??

Where is the sql-clause created?