0

I'm working on a project that can be installed on a number of servers. The issue I come across is people can have their server and tables set for different collation. On one server, I an error received was

PDOException: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

So I thought I fixed it by adding COLLATE utf8_general_ci to the query. It fixed it there and it worked on my server, but then someone else tried to run it on their server and received

Syntax error or access violation: 1253 COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'

Is there anything I can do to my query to make it "just work" without having to worry about MySQL server config? The query is below, and joins a few tables together. I only included the query itself and not the entire chunk of code. It is a prepared statement in PDO.

SELECT t.tid, a.clientid, tt.status, ts.showactive FROM TABLE1 t INNER JOIN TABLE2 a ON t.related_asset = a.id INNER JOIN TABLE3 tt ON tt.tid = t.tid INNER JOIN TABLE4 ts ON ts.title = tt.status WHERE ts.showactive = 1
CrunchyToast
  • 105
  • 11

1 Answers1

0

It would be better to be consistent in all tables and connections -- all the same CHARACTER SET (utf8 vs latin1) and COLLATION.

Adding COLLATE utf8_general_ci to the query made it work -- but with a performance penalty. If the columns had the same collation (and charset, in the second example), an index would work to add efficiency.

There is no "just work" without having to do work. Consistency (as above) is best. Otherwise, multiple patches here and there will be needed.

See "Best practice" in Trouble with UTF-8 characters; what I see is not what I stored

utf8 is OK in older versions of MySQL; but it you want Emoji and Chinese, use utf8mb4 instead.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • This is delayed, but thanks for your response. I couldn't really control some of the tables due to my script being an addon for a 3rd party program. What I did in the end was modify my script to create the table matching collation of the other 2 tables. That means anyone who has it installed on their server should avoid any collation errors. – CrunchyToast Dec 13 '17 at 22:13