1

What I can do with this error: #1267

Illegal mix of collations (utf8_czech_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

Thanks:)

Mi-Creativity
  • 9,554
  • 10
  • 38
  • 47
Honza Hála
  • 87
  • 1
  • 9
  • 1
    It's an illegal mix of collations. Most likely some of your tables are `utf8_czech_ci` but others are `utf8_general_ci`. Make sure they're the same. – h2ooooooo Feb 22 '16 at 14:50
  • pls providee the statement you issue returning this error msg – Dylan Su Feb 22 '16 at 14:50
  • 3
    Possible duplicate http://stackoverflow.com/questions/3029321/troubleshooting-illegal-mix-of-collations-error-in-mysql – Wintermute Feb 22 '16 at 14:52
  • I go to mariaDB and I set: `SET character_set_server = 'utf8'; SET collation_server = utf8_bin';` it did not help .. – Honza Hála Feb 22 '16 at 14:59
  • check columns that you are matching either in `on` or in `where` condition. if you are matching `col1=col2` then `col1` and `col2` must have same character set. (utf8_general_ci/utf8_czech_ci in your case) – Jimish Gamit Feb 22 '16 at 15:01
  • Jimish Gamit - I do it and it not help. – Honza Hála Feb 22 '16 at 15:16
  • @HonzaHála You did what? Make sure the collations were the same? Changed the collation in your query? – h2ooooooo Feb 22 '16 at 15:17
  • @h2ooooooo I go to Adjustments and I change Comparisons in all tables to utf8_czech_ci. – Honza Hála Feb 22 '16 at 15:25
  • @HonzaHála Have you made sure that your VARCHAR's and columns in general are all utf8_czech_ci? FYI, we have no idea what program you're doing this with, so I don't know what "Adjustments" and/or "Comparisons" is in the program you're using. – h2ooooooo Feb 22 '16 at 15:28
  • Please provide `SHOW CREATE TABLE` and `SHOW VARIABLES LIKE '%coll%`;` and the query that is giving the error. – Rick James Feb 22 '16 at 16:36
  • Possible duplicate of [Troubleshooting "Illegal mix of collations" error in mysql](https://stackoverflow.com/questions/3029321/troubleshooting-illegal-mix-of-collations-error-in-mysql) – nkatsar Oct 13 '17 at 12:36

2 Answers2

0

Execute the following and then run your original query again.

SET NAMES 'utf8';
set collation_connection = 'utf8_general_ci';
Dylan Su
  • 5,975
  • 1
  • 16
  • 25
0

If you are using variables, you can add the correct collation next to your variable declaration, i.e. instead of 

SET @my_var = 'text';

declare the variables as

SET @my_var = 'text' COLLATE utf8_general_ci;

Replace utf8_general_ci with the collation that is used in the table. More details on Troubleshooting "Illegal mix of collations" error in mysql

nkatsar
  • 1,580
  • 17
  • 15