OSTicket Database Errors (Polish Characters)

16 Feb

I’ve had a few emails from OSTicket recently, telling me that there was a DB error, specifically:

Incorrect string value: ‘xC2x0AThan…’ for column ‘message’ at row 1

The email had originated from a Yahoo address and so my initial thought was that this the code equivalent of some sort of smiley face character or similar. After a bit of Googling, I found this article which mentions that there could problems with Polish characters and the collation being used on the MySQL database. Although I wasn’t fully convinced on the Polish side of things it at least got me in the right direction.

I setup a yahoo email address and proceeded to create a few fake tickets and updated them via email. The first few went through fine, even the ones where I added the various emoticons. I then found this site which gave me a load of random Polish characters to test, ie Żźśóńłćąęćą

As soon as I sent an email containing the string “Żźśóńłćąęćą” I got an error straight away. I first tried changing the collation on the tables ost_ticket and ost_ticket_messages from uft8_unicode_ci to utf8_general_ci but that didn’t seem to make a difference. I then spotted that the fields themselves can have their of collation, so I changed the messages field in ost_ticket_messages to utf8_general_ci and that seems to fix it. The next email I sent containing the Polish characters came through fine.