Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'

Tags:

mysql

I am trying to fix a character encoding issue - previously we had the collation set for this column utf8_general_ci which caused issues because it is accent insensitive..

I'm trying to find all the entries in the database that could have been affected.

set names utf8; select * from table1 t1 join table2 t2 on (t1.pid=t2.pid and t1.id != t2.id) collate utf8_general_ci; 

However, this generates the error:

ERROR 1253 (42000): COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1' 
  1. The database is now defined with DEFAULT CHARACTER SET utf8
  2. The table is defined with CHARSET=utf8
  3. The "pid" column is defined with: CHARACTER SET utf8 COLLATE utf8_bin NOT NULL
  4. The server version is Server version: 5.5.37-MariaDB-0ubuntu0.14.04.1 (Ubuntu)

Question: Why am I getting an error about latin1 when latin1 doesn't seem to be present anywhere in the table / schema definition?

MariaDB [(none)]> SHOW VARIABLES LIKE '%char%'; +--------------------------+----------------------------+ | Variable_name            | Value                      | +--------------------------+----------------------------+ | character_set_client     | utf8                       | | character_set_connection | utf8                       | | character_set_database   | latin1                     | | character_set_filesystem | binary                     | | character_set_results    | utf8                       | | character_set_server     | latin1                     | | character_set_system     | utf8                       | | character_sets_dir       | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.00 sec)  MariaDB [(none)]> SHOW VARIABLES LIKE '%collation%'; +----------------------+-------------------+ | Variable_name        | Value             | +----------------------+-------------------+ | collation_connection | utf8_general_ci   | | collation_database   | latin1_swedish_ci | | collation_server     | latin1_swedish_ci | +----------------------+-------------------+ 
like image 462
Stephen Avatar asked Sep 15 '14 16:09

Stephen


People also ask

What is charset latin1 in MySQL?

The MySQL latin1 character set is such a one-byte character set for Western Europe, and it is the default character set of MySQL up to and including 5.7. In spite of the name, the character set is actually Windows-1252 compliant, which is a superset of ISO-8859-1, also known as Latin-1.

What collation should I use for utf8mb4?

If you're using MySQL 8.0, the default charset is utf8mb4. If you elect to use UTF-8 as your collation, always use utf8mb4 (specifically utf8mb4_unicode_ci).

What is utf8_general_ci?

utf8_general_ci is a legacy collation that does not support expansions, contractions, or ignorable characters. It can make only one-to-one comparisons between characters.

What is collation and character set in MySQL?

A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set. Let's make the distinction clear with an example of an imaginary character set. Suppose that we have an alphabet with four letters: A , B , a , b .


2 Answers

Firstly run this query

SHOW VARIABLES LIKE '%char%'; 

You have character_set_server='latin1'

If so,go into your config file,my.cnf and add or uncomment these lines:

character-set-server = utf8 collation-server = utf8_unicode_ci 

Restart the server. Yes late to the party,just encountered the same issue.

like image 148
Mihai Avatar answered Sep 30 '22 08:09

Mihai


The same error is produced in MariaDB (10.1.36-MariaDB) by using the combination of parenthesis and the COLLATE statement. My SQL was different, the error was the same, I had:

SELECT * FROM table1 WHERE (field = 'STRING') COLLATE utf8_bin; 

Omitting the parenthesis was solving it for me.

SELECT * FROM table1 WHERE field = 'STRING' COLLATE utf8_bin; 
like image 28
Thomas Lauria Avatar answered Sep 30 '22 08:09

Thomas Lauria