Illegal mix of collations after 4.5.0c upgrade

[kirkland]

My 4.5.0c upgrade seems okay, but when I try to open an Account, I get an error on the page as well as an error in the sugarcrm.log:

Mon 02 Oct 2006 09:21:15 AM PDT,808 [5604] FATAL SugarCRM - MySQL error 1267: Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'UNION'

On the page itself:

Error retrieving Account list: Query Failed SELECT meetings.id , meetings.name , meetings.status , ' ' contact_name , ' ' contact_id , ' ' contact_name_owner , ' ' contact_name_mod , meetings.date_modified , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, ' ' filename , 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= '200da463-d390-7773-b346-447f1fcc9429' AND meetings.parent_type='Accounts' AND meetings.deleted=0 AND (meetings.status='Held' OR meetings.status='Not Held')) AND meetings.deleted=0 ) UNION ALL ( SELECT tasks.id , tasks.name , tasks.status , CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')) contact_name , tasks.contact_id , contacts.assigned_user_id contact_name_owner , 'Contacts' contact_name_mod, tasks.date_modified , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, ' ' filename , tasks.assigned_user_id , 'tasks' panel_name 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= '200da463-d390-7773-b346-447f1fcc9429' AND tasks.parent_type='Accounts' AND tasks.deleted=0 AND (tasks.status='Completed' OR tasks.status='Deferred')) AND tasks.deleted=0 ) UNION ALL ( SELECT calls.id , calls.name , calls.status , ' ' contact_name , ' ' contact_id , ' ' contact_name_owner , ' ' contact_name_mod , calls.date_modified , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, ' ' filename , 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= '200da463-d390-7773-b346-447f1fcc9429' AND calls.parent_type='Accounts' AND calls.deleted=0 AND (calls.status='Held' OR calls.status='Not Held')) AND calls.deleted=0 ) UNION ALL ( SELECT notes.id , notes.name , ' ' status , 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.date_modified , ' ' assigned_user_name , ' ' assigned_user_owner , ' ' assigned_user_mod , notes.filename , 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 where ( notes.parent_id= '200da463-d390-7773-b346-447f1fcc9429' AND notes.parent_type='Accounts' AND notes.deleted=0) AND notes.deleted=0 ) UNION ALL ( SELECT emails.id , emails.name , emails.status , ' ' contact_name , ' ' contact_id , ' ' contact_name_owner , ' ' contact_name_mod , emails.date_modified , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, ' ' filename , 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 INNER JOIN emails_accounts ON (emails.id=emails_accounts.email_id AND emails_accounts.account_id='200da463-d390-7773-b346-447f1fcc9429') where ( emails_accounts.deleted=0 AND emails.deleted=0) AND emails.deleted=0 ) ORDER BY date_modified desc LIMIT 0,10::MySQL error 1267: Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'UNION'

[simple]

One of your tables seems to have a wrong charset.

We had the same problem - but after adding a custom field. If the default charset ist not set to utf8_general_ci a new table - e.g. contacts_cstm - will be created with the default charset e.g. latin1_swedish_ci.

[kirkland]

How do I fix this, though? Unfortunately, I'm not a PHP, MySQL or other expert, but have managed to get SugarCRM working for a couple of years now.

In fact, I just restored my database and the sugar folder so I'm back on 4.2.1a and I'm not being attacked by angry sales people anymore!

I'd like to use the latest version, but it seems that something about 4.5.0c wasn't happy. I ran all of the various "repair" scripts for the admin user, but that had no effect.

Obviously, if there are commands I can give ensure the right collation charsets, I'd be happy to do so. I'm just not sure how to do it.

I'm running on Linux with MySQL 4.1.10a, PHP 4.3.10 on RedHat Linux kernel 2.4.21-4.ELsmp and Apache 2.0.46.

Thanks.

[davegreen]

First things first - find out which column is causing the problems.

Do you have a copy of the 4.5 database you can refer to?

If so, run the command
mysqldump -u user -ppassword -d database

where user, password and database are replaced with the appropriate values.

This will give you a list of all the create table statements in the database - and you can then see which columns have the unusual collations.

Hope this helps...

Dave.

[kirkland]

I had to rollback to my 4.2.1a release because the sales folks were completely out of business without their leads and calls.

However, I ran that against this older DB, and I don't see anything on columns per se, but nearly very table ends with ENGINE=MyISAM DEFAULT CHARSET=latin1

There are a few tables that end with ENGINE=MyISAM DEFAULT CHARSET=utf8. And there are some comments at the top that read:

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

Anyway, the key for me is that everything works fine now until 4.2.1, so if the upgrade scripts can do whatever it takes to "fix" the charsets, that would be great. Note that in my case, UTF-8 is fine by me, though right now I don't think I have anything but ASCII data because there's no special languages being used besides en_us.

If there's some step I can take before the 4.5.0c upgrade within MySQL, I'd be happy to try that, too.

Thanks....

[rickcrites]

I am not getting any error messaages that obviously point to a problem with MySQL database collation issues. But, I do notice, when I open phpMyAdmin, that most of the tables in my database still show Latin1 and a few are UTF-8. The default collation is set for UTF-8. But somehow, I still show tables with the old collations.

My question is this: Do I need to go through and change the collation on each of the 100+ tables in order to be sure my system runs right? Or is it really that much of an issue?

I started to wonder about this issue when I noticed that unintended question marks were being inserted in Forum posts whereever there were two or more empty spaces next to each other. But, all the Forum, Thread and Post-related tables in my database seem to be UTF-8.

Anyone have any knowledge on this? I will make the changes to all the tables if it is necessary.

Both my production 4.5.0c and test 4.5.0d instances have the same situation on this issue.

Thanks,

[jhamill]

I am not getting any error messaages that obviously point to a problem with MySQL database collation issues. But, I do notice, when I open phpMyAdmin, that most of the tables in my database still show Latin1 and a few are UTF-8. The default collation is set for UTF-8. But somehow, I still show tables with the old collations.

My question is this: Do I need to go through and change the collation on each of the 100+ tables in order to be sure my system runs right? Or is it really that much of an issue?

I started to wonder about this issue when I noticed that unintended question marks were being inserted in Forum posts whereever there were two or more empty spaces next to each other. But, all the Forum, Thread and Post-related tables in my database seem to be UTF-8.

Anyone have any knowledge on this? I will make the changes to all the tables if it is necessary.

Both my production 4.5.0c and test 4.5.0d instances have the same situation on this issue.

Thanks,

I had same problem and did a yum update mysql to fix the problem.

[rickcrites]

I had same problem and did a yum update mysql to fix the problem.

Thank you very much for the response and the data.

Unfortunately, I am new enough to MySQL that I have no idea what a 'yum update' would be or how to do it. Could you be a bit more specific, or point me to a reference source so I can educate myself? I certainly would like to do this the easy way rather than the hard way.

Thanks again for your answer.

Best,

[kirkland]

Will 4.5.0d fix this issue? I see in the docs some references to collation errors if two systems are sharing the mysql, but that's not the case with my system.

Does anybody know of particular steps that can resolve this? I've had to go back to 4.2.1a until I have a way to resolve it, though I'd like to upgrade.

Are there text changes I can make to my mysqldump output that would cause it to reload the DB as all UTF-8? I'm pretty sure there are few if any international characters in our data since our customers are all in the U.S. and speak English (our keyboards don't even have Spanish characters like the tilde-N or accents).

Thanks.

[rickcrites]

Will 4.5.0d fix this issue? I see in the docs some references to collation errors if two systems are sharing the mysql, but that's not the case with my system.

Does anybody know of particular steps that can resolve this? I've had to go back to 4.2.1a until I have a way to resolve it, though I'd like to upgrade.

Are there text changes I can make to my mysqldump output that would cause it to reload the DB as all UTF-8? I'm pretty sure there are few if any international characters in our data since our customers are all in the U.S. and speak English (our keyboards don't even have Spanish characters like the tilde-N or accents).

Thanks.

I think this link may be what you are looking for. It will help you create a php file to run that will change over your databases.

Hope this helps.