Illlegal Mix of Collations

[RichH]

Hi,

My OTRS info is:

OTRS Version: 3.3.12
OS: CentOS 6.5
Database: mysql 5.1.73
Perl Version: 5.10

I recently upgraded & migrated OTRS from v3.1 on Windows to v3.3.12 on Centos (MSSQL --> mysql)

We've been getting customer emails that have resulted in the error in the OTRS log below. These emails are broken up into multiple emails with '????' as the sender, and the attachments lost. These multiple emails keep arriving everyday. I moved the 'problem-email-xxx' files out of the .../otrs/var/spool dir in hopes that it stops these emails from reappearing and reopening the ticket.

What can I do to get future emails to process correctly into the OTRS system?

Thanks!

Error:
[Thu Mar 10 05:30:10 2016][Error][Kernel::System::Ticket::Article::_ArticleGetId][1902] Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=', SQL: 'SELECT id FROM article WHERE ticket_id = ? AND a_message_id = ? AND a_from = ? AND a_subject = ? AND incoming_time = ? ORDER BY id DESC LIMIT 1'

[Thu Mar 10 05:30:10 2016][Error][Kernel::System::Ticket::Article::ArticleCreate][305] Can't get ArticleID from INSERT!

[Thu Mar 10 05:30:10 2016][Error][Kernel::System::MailAccount::IMAP::_Fetch][219] IMAPS: Can't process mail, see log sub system (/opt/otrs/var/spool/problem-email-3ba5c28ae08eeac080e62c93cf4a3ca1, report it on http://bugs.otrs.org/)!

[Thu Mar 10 05:30:11 2016][Error][Kernel::System::Ticket::Article::_ArticleGetId][1902] Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=', SQL: 'SELECT id FROM article WHERE ticket_id = ? AND a_message_id = ? AND a_from = ? AND a_subject = ? AND incoming_time = ? ORDER BY id DESC LIMIT 1'

[Thu Mar 10 05:30:11 2016][Error][Kernel::System::Ticket::Article::ArticleCreate][305] Can't get ArticleID from INSERT!

[Thu Mar 10 05:30:11 2016][Error][Kernel::System::MailAccount::IMAP::_Fetch][219] IMAPS: Can't process mail, see log sub system (/opt/otrs/var/spool/problem-email-9cad16b7ccd09fe5f9d0db9c6c527279, report it on http://bugs.otrs.org/)!

[jojo]

fix your database!

[RichH]

How?

[root]

This more an issue for your DBA and less OTRS related

[RichH]

The installation instructions for OTRS in the 3.3 admin manual should have said to set the default character set and collation to utf8, before installing or migrating the OTRS database.

Anyway, in case anyone else has this issue the fix is:

#Add to /etc/my.cnf:

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake

#end my.cnf edit

service mysqld restart
service otrs stop
service httpd stop
service crond stop
su -s /bin/bash - otrs
perl /opt/otrs/bin/otrs.Scheduler.pl -a stop
exit


mysqldump -u root -p -c -e --add-drop-database -B otrs > /opt/otrs/mysql_otrs_dump1.sql
mysqldump -u root -p -c -e --default-character-set=utf8 --add-drop-database --skip-set-charset -B otrs > /opt/otrs/mysql_otrs_dump2.sql
cp mysqldump_otrs_dump2.sql mysqldump_otrs_dump2_fixed.sql
vi mysqldump_otrs_dump2_fixed.sql
:%s/DEFAULT CHARACTER SET latin1/DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci/
:%s/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8/
:wq
mysql -uusername -ppassword < mysqldump_otrs_dump2_fixed.sql
shutdown -r now

Logon and run:
su -s /bin/bash - otrs
perl /opt/otrs/bin/otrs.Scheduler.pl -a start
exit

Fortunately, the default character set for mysql is Latin1 which is a subset of UTF8, so no existing data gets turned into a mojibake.