COLLATION utf8 general ci is not valid for CHARACTER SET latin1

Question

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     The database is now defined with DEFAULT CHARACTER SET utf8 The table  is defined with CHARSET utf8 The  pid  column is defined with  CHARACTER SET utf8 COLLATE utf8 bin NOT NULL 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   gt  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   gt  SHOW VARIABLES LIKE   collation     ---------------------- -------------------    Variable name          Value                ---------------------- -------------------    collation connection   utf8 general ci       collation database     latin1 swedish ci     collation server       latin1 swedish ci    ---------------------- -------------------

User · Answer

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

User · Answer

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

User · Answer

Firstly run this query SHOW VARIABLES LIKE   char     You have character set server  latin1  for eg if CHARSET utf8mb4 COLLATE utf8mb4 0900 ai ci replace it to CHARSET latin1 and remove the collate You are good to go

User · Answer

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

[mysql] COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'

Examples related to mysql