MediaWiki

User:Kghbln/Error 1271: Illegal mix of collations

< User:Kghbln

MediaWiki edit

Situation
  • Your MySQL server speaks UTF-8
[mysql]
default-character-set=utf8

[mysqld]
collation_server=utf8_unicode_ci
character_set_server=utf8
  • Your database tables speak UTF-8
  • Your columns within the tables are messed up (see Problem)
Problem
Query: SELECT

img_size,img_width,img_height,img_bits,img_media_type,img_major_mime,img_minor_mime,img_metadata,img_timestamp,img_sha1,img_user,img_user_text,img_description  FROM `image`  WHERE img_name = 'xyz.jpg'  LIMIT 1

Function: LocalFile::loadFromDB

Error: 1267 Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' (localhost)
Solution

Replace all appearances of CHARACTER SET latin1 COLLATE latin1_bin NOT NULL with CHARACTER SET utf8 COLLATE utf8_bin NOT NULL:

  1. Dump your MySQL database
  2. Open your dumped MySQL database with your editor, e.g. vim
  3. Do the replacement with %s/CHARACTER SET latin1 COLLATE latin1_bin/CHARACTER SET utf8 COLLATE utf8_bin/g
  4. Save the changes to your MySQL database
  5. Import your MySQL database
Warning

This does not rectify already corrupted characters, i.e. there was no latin1 equivalent for the utf8 character, e.g. ō displaying as ? in your database.

Retrieved from "https://www.mediawiki.org/w/index.php?title=User:Kghbln/Error_1271:_Illegal_mix_of_collations&oldid=1427080"