CODEWITHSUNDEEP
mysql
Stephen
Stephen

Reputation: 3432

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

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 |
+----------------------+-------------------+

Upvotes: 52

Views: 246703

Answers (3)

Mihai
Mihai

Reputation: 26784

First, run this query:

SHOW VARIABLES LIKE '%char%';

You have character_set_server='latin1' shown in your post ...

So, go into your my.cnf and add or uncomment these lines:

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

Restart the server.

Upvotes: 56

sh6210
sh6210

Reputation: 4540

In my case I created a database and gave the collation 'utf8_general_ci' but the required collation was 'latin1'. After changing my collation type to latin1_bin the error was gone.

Upvotes: 3

Thomas Lauria
Thomas Lauria

Reputation: 908

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;

Upvotes: 8

Related Questions