1

I have actually changed the collations of my tables to utf8_general_ci in mysql but it appears that it is still recognized as latin1 because I got this

Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '=': SELECT dimensions.* FROM dimensions WHERE dimensions.name = '责任心' AND dimensions.category = '态度项' LIMIT 1

I don't write mysql query but I call someones code to generate one and execute it. And I used this statement to change the collation:

ALTER table participants CHARACTER SET utf8 COLLATE utf8_general_ci;

And I also found out that I used the wrong collation instead of the correct one utf8mb4_general_ci. So I try to alter it by using. 

ALTER table participants CHARACTER SET utf8 COLLATE utf8mb4_general_ci;

But I got this error

Error Code: 1253. COLLATION 'utf8mb4_general_ci' is not valid for CHARACTER SET 'utf8

How can I fix it?

---Update---

Now all my tables are using utf8mb4_general_ci but I still get the same error Illegal mix of collations.

I used show table status to see the collations of my table.

Seaky
  • 239
  • 2
  • 13
  • Is it done for table and column? – Ingus Nov 08 '19 at 07:38
  • 1
    Does this answer your question? [Illegal mix of collations for operation 'concat' while using union all](https://stackoverflow.com/questions/57568094/illegal-mix-of-collations-for-operation-concat-while-using-union-all) – Ingus Nov 08 '19 at 07:39
  • @Ingus I don't think so because I am not using concat. – Seaky Nov 08 '19 at 07:57
  • ignore concat focus on the problem. .. – Ingus Nov 08 '19 at 07:57
  • I bet you have different collation for table and columns – Ingus Nov 08 '19 at 07:58
  • @Ingus I don't think that would help because I don't write mysql query but call someones code to generate one and execute it. – Seaky Nov 08 '19 at 07:59
  • 1
    just check and fix it if i m correct! – Ingus Nov 08 '19 at 08:01
  • You ahve alos to check if aby columns have teh "wrong swdish characterset) so do this https://stackoverflow.com/a/4805964/5193536 and change also the column – nbk Nov 08 '19 at 08:01
  • @Ingus My replies are based the fact that I have read the post. And I am curious about how I could know the collation of columns. – Seaky Nov 08 '19 at 08:03
  • 1
    @Seaky if you use phpMyAdmin you can see it when click on table structure – Ingus Nov 08 '19 at 08:05
  • @Ingus Thanks! They do have different collations. Do I need to change the column collation one by one manually or there is a query that could fix all? – Seaky Nov 08 '19 at 08:09
  • I have no current code for that (i use HeidiSQL and it allow to check to convert all at once) So if you have no known code for that it need to be done one by one. – Ingus Nov 08 '19 at 08:14
  • SET NAMES utf8; sometimes seems to solve a bunch of problems – Strawberry Nov 08 '19 at 09:54
  • Does this answer your question? [Illegal mix of collations MySQL Error](https://stackoverflow.com/questions/1008287/illegal-mix-of-collations-mysql-error) – Habib Rehman Jul 22 '22 at 09:17

2 Answers2

1

Have you tried using the proper character set utf8mb4 which should contain the given collation?

Nico Haase
  • 11,420
  • 35
  • 43
  • 69
  • 1
    Thank you! This is the fix for my second problem. What about the first one? – Seaky Nov 08 '19 at 07:53
  • 1
    CHECK THE COLUMNS – Ingus Nov 08 '19 at 07:55
  • 1
    Yeah, what about the first one? Can you share all data for that to make the problem reproducable for others? What are the collations for `category` and `name`? Which character set have you used for the database connection? Have you tried skipping one of the `WHERE` clauses to see which one triggers the error? – Nico Haase Nov 08 '19 at 08:00
  • @NicoHaase I can't skip the `where` because I don't write sql queries but call well-incapsulated code to generate them. – Seaky Nov 08 '19 at 08:05
  • Then why not copy the **exact same** query to some tool like phpMyAdmin, MySQL workbench, or the MySQL shell to reproduce the problem? – Nico Haase Nov 08 '19 at 08:06
  • I mean I know the problem but I just don't know how to fix it. – Seaky Nov 09 '19 at 02:26
0

Correct: CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci

Loliveira Bernhoeft
  • 1
  • 1