Character set issues after upgrading to 3.20.02

[Breadcrumbs]

I just upgraded our SupportSuite installation from 3.11.01 to the latest 3.20.02.

It appears as though the new version has changed the default character-set settings from latin1 encoding to utf-8. This caused some corrupted character display in knowledgebase articles, tickets, etc.

I changed the character set temporarily back to latin1 (in the config.php file, using the "$_DB['charset']" parameter), which reverted to the previous behavior. However I'd like to update the entire support desk to utf-8 if possible.

The concern is: the underlying MySQL database is using the latin1 encoding (it's the MySQL default, and the database was also created with the previous version of SupportSuite that defaulted to that encoding).

A.) Do I need to convert the MySQL database/tables to use utf-8 (and, if so, how do I accomplish this without causing the character corruption)?
B.) Do I need to do anything else other than changing the 'charset' parameter back to utf-8 to ensure a smooth transition? (i.e. Change PHP's charset? Change MySQL's charset? etc. If necessary, I'd like to make these changes only for SupportSuite to minimize the risk of breaking other apps.)

Thanks in advance.

[GoneShootin]

I was about to upgrade, but after reading this I think I will hold off.

/subscribes

[sveide]

I can only acknowledge.

Mail coming in with charset="iso-8859-1" is not correctly transformed to utf-8 characters and not shown correctly in the web-interface.

/Jan Sveide

[Ryan Lederman]

@sveide, please e-mail me and I'll send you a patch.

[John Haugeland]

A.) Do I need to convert the MySQL database/tables to use utf-8 (and, if so, how do I accomplish this without causing the character corruption)?

Yes. The instructions below account for both A and B.

B.) Do I need to do anything else other than changing the 'charset' parameter back to utf-8 to ensure a smooth transition? (i.e. Change PHP's charset? Change MySQL's charset? etc. If necessary, I'd like to make these changes only for SupportSuite to minimize the risk of breaking other apps.)

You need to change the character set and the collation for any table being converted. You can do those both at the same time.

Please remember to back up your database before working in SQL; especially in the case of character sets, errors can cause loss. Backups are crucial.

For any table that you would like to convert to UTF-8 (these instructions written for a table named "foo"), issue the following command:

alter table foo convert to character set 'utf8' collate utf8_unicode_ci;

Of course, you'll want to go through the table afterwards, and make sure your data is intact. (It should be, but with character set issues and MySQL, one just checks.)

Please be aware that though this is safe for Kayako products, there is a bug in MySQL which causes the conversion of mediumtext and largetext columns to text columns during this conversion. As such, this command would not be safe for applications which stored very large texts (in excess of 2000 printed pages).

If there's anything else, please let us know. I believe this should resolve the issue you're facing, however.

- John Haugeland
Kayako Development Staff

[jcorreia]

Hi,
I have the same problem but only in email parser.
I did not converted the db to utf8, and I changed the "$_DB['charset']" to latin1, to keep what I had.
All is well except email parser. I did apply the ryan patch but the problem remains with emails.

Do I really have to convert the db to utf8 ? I prefer not to do it...thanks

Jcorreia

[Ryan Lederman]

Originally Posted by jcorreia

Hi,
I have the same problem but only in email parser.
I did not converted the db to utf8, and I changed the "$_DB['charset']" to latin1, to keep what I had.
All is well except email parser. I did apply the ryan patch but the problem remains with emails.

Do I really have to convert the db to utf8 ? I prefer not to do it...thanks

Jcorreia

The email parser code I sent you will convert incoming email to whatever the "charset" value is in the active language pack. (Search for that entry in the language pack and also change it in the /locale/lang/lang.php file)

However, I am concerned with why you are trying to move away from UTF-8. There is no good reason that I can imagine to do so.

Regards,

[jcorreia]

Hi,
thanks for your answer, I will check it.
The reason Iám avoiding is because I had issues in past with charset, and I don+t want to fix a thing that ain´t broken...I´m afraid of ruinning anything without the option to test things before..

[jcorreia]

I´ve tested and It works! Thanks...it was set utf-8 in en-us.php file, changed to iso-8859-1 and is well now.

Do you think I could came across with problems if I do not convert to utf-8 ? Do you advice me to do it ?
I have also in php.ini iso.8859-1 and I´m not sure about apache. I have another aplication running on server, and I´m afraid I might brake something.

Thanks for your support.

[Ryan Lederman]

Originally Posted by jcorreia

I´ve tested and It works! Thanks...it was set utf-8 in en-us.php file, changed to iso-8859-1 and is well now.

Do you think I could came across with problems if I do not convert to utf-8 ? Do you advice me to do it ?
I have also in php.ini iso.8859-1 and I´m not sure about apache. I have another aplication running on server, and I´m afraid I might brake something.

Thanks for your support.

If you are using a Latin-based language you will be fine if you use ISO-8859-1. The reason we use UTF-8 natively is to also support multibyte languages like Russian and Japanese.

If you think you will never receive any e-mails that are in multibyte languages, then you will be OK with running the desk in ISO-8859-1. However, if you do receive for example a Russian e-mail, it will be converted to ISO-8859-1 and be completely unreadable.

Just a warning.

[jcorreia]

thanks...portuguese only
But I will have this in mind for future.

Keep the good work

[msmartinwsx]

@Ryan,

Can you advise on this also. We're using Kayako via WHMCS but some characters are getting messed up once they reach Kayako.

In Kayako (and received via watcher emails) the characters are 'corrupt' but when viewed in WHMCS, they are perfect.

I understand this to be because WHMCS uses iso-8859-1 but Kayako uses something else?

All tables in our Kayako DB are latin1_swedish_ci? Can you advise what we would need to do to fix this? We only deal with languages in normal English.

Matt

[jnet]

Sorry to say. You guys all are wasting your time. I have been going through this for months. you can make it this way but there seems to be a complicated way out there which I do not know

[Ryan Lederman]

Originally Posted by msmartinwsx

@Ryan,

Can you advise on this also. We're using Kayako via WHMCS but some characters are getting messed up once they reach Kayako.

In Kayako (and received via watcher emails) the characters are 'corrupt' but when viewed in WHMCS, they are perfect.

I understand this to be because WHMCS uses iso-8859-1 but Kayako uses something else?

All tables in our Kayako DB are latin1_swedish_ci? Can you advise what we would need to do to fix this? We only deal with languages in normal English.

Matt

Please open a support ticket and request that it be assigned to me; in it, please describe step-by-step how to reproduce corrupt characters and provide screenshots if possible.

Thanks

[iczeh]

I've converted the tables to utf-8 by John's instructions, but the messages still go back to the customers with wrong characters. What else can I do?