MySQL illegal mix of collations with all utf8 db/tables/columns

Question

I don't get it. I never really got it, but now I'm really lost. My database is utf8, my tables are utf8 and my columns are utf8. Some of the data is latin1/iso-8859-1, so it just displays wrong. (I don't care for now.) It used to be all latin1 and now it's 50/50 which is even worse.

When I don't speficially tell the connection to use all utf8, all queries work and all data is shown correctly (even though it's stored wrong). That's because storage is wrong (latin1 in utf8) and the connection is 'wrong' (default latin1 I guess), so that cancels eachother out. Character are OK and no errors. But obviously that's unacceptable, because the database contains crap.

So I want everything utf8, so I tell MySQL specifically (first query every time):

SET NAMES 'utf8' COLLATE 'utf8_general_ci'

And then somehow the following query fails:

SELECT
  r.id,
  r.slots,
  r.start_time AS StartA,
  ADDTIME(r.start_time, SEC_TO_TIME(60*r.slots*s.slotsize)) AS EindA,
  '14:30:00' AS StartB,
  '15:30:00' AS EindB
FROM
  club_sports s, resources c, reservations r
WHERE
  r.resource_id = c.id AND
  c.club_sport_id = s.id AND
  r.not_cancelled = '1' AND
  r.resource_id = 25 AND
  r.date = '2013-01-18'
HAVING
  (
    (StartA >= StartB AND StartA < EindB) OR
    (EindA >= StartB AND EindA < StartB) OR
    (StartB >= StartA AND StartB < EindA) OR
    (EindB >= StartA AND EindB < StartA)
  )
LIMIT 1

with the following error:

Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,NUMERIC) for operation '<'

I don't get it! Where does it get latin1_swedish_ci,NUMERIC?? All the parts in the HAVING aren't numeric and they're all the same utf8, right? The source tables and columns are utf8!

I know the query isn't perfect, but that's irrelevant for now. I should be able to create fake/tmp columns with '' and compare them in a HAVING, right?

I really need my db to be utf8, because I really need my data to be utf8, because json_encode really needs utf8, so I really need the connection to be utf8. Help!

If all you wanted is for json_* to work, all you needed is utf-8 connection encoding - to make the database send data to you in utf-8 and interpret your data in utf-8. What encoding is used for db storage would only matter if the encoding doesn't support characters you want to use. But other than that, just connection encoding is enough. — Esailija
And with that the query doesn't work... If the db isn't utf8 and the connection is, the result (display) will be scrambled characters. Currently the db and connection aren't so the result displays 'correctly', but json_encode wants real utf8. — Rudie
I don't know what that means... Everything's utf8, that should be good right? Why does it consider some parts latin1_swedish_ci,NUMERIC? (I don't even know what that means.) I need the SET NAMES query for json_*. What do I change for everything to work? — Rudie

DaveStSomeWhere DaveStSomeWhere · Accepted Answer · 2013-01-18T20:54:50

The HAVING clause is for aggregate values (think of a WHERE Clause for GROUP BY items).

You need to redo the calculation (like the ADDTIME function) in the WHERE clause because you cannot use the alias.

MySQL illegal mix of collations with all utf8 db/tables/columns

1 Answers