Bug #118722 ERROR 1267 (HY000): Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8mb4_0900_ai_ci,COERCIBLE) for operation '='

• Submitted: 26 Jul 2025 11:08
• Modified: 5 Feb 21:47
• Reporter: fent allen
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 8.0.41
• OS: Linux
• Assigned to: Assigned Account
• CPU Architecture: ARM

[26 Jul 2025 11:08] fent allen

Description:
When inserting Chinese characters into a Latin1 table, an error occurs when querying the Chinese characters.

How to repeat:
drop table if exists tab_charset_latin1_004;
create table tab_charset_latin1_004 (
    id int auto_increment primary key,
    char_col01 char(100) character set latin1 collate latin1_bin,
    char_col02 char(100) character set latin1 collate latin1_danish_ci,
    char_col03 char(100) character set latin1 collate latin1_general_ci,
    varchar_col01 varchar(255) character set latin1 collate latin1_bin,
    varchar_col02 varchar(255) character set latin1 collate latin1_danish_ci,
    varchar_col03 varchar(255) character set latin1 collate latin1_general_ci,
    text_col01 text character set latin1 collate latin1_bin,
    text_col02 text character set latin1 collate latin1_danish_ci,
    text_col03 text character set latin1 collate latin1_general_ci,
    tinytext_col01 tinytext character set latin1 collate latin1_bin,
    tinytext_col02 tinytext character set latin1 collate latin1_danish_ci,
    tinytext_col03 tinytext character set latin1 collate latin1_general_ci,
    blob_col blob,
    tinyblob_col tinyblob,
    json_col json
) engine=dstore default charset=latin1;
alter table tab_charset_latin1_004 add index idx_multi_column (char_col01(50), varchar_col01(100));

insert into tab_charset_latin1_004 (char_col01, char_col02, char_col03, varchar_col01, varchar_col02, varchar_col03, text_col01, text_col02, text_col03, tinytext_col01, tinytext_col02, tinytext_col03, blob_col, tinyblob_col, json_col) values ('hello', 'world', 'test', 'mysql', 'utf8mb4', 'life', 'this is a text', 'another text', 'example', 'tiny text 1', 'tiny text 2', 'tiny text 3', 'blob data','tinyblob data', '{"name": "john", "age": 30}');
insert into tab_charset_latin1_004 (char_col01, char_col02, char_col03, varchar_col01, varchar_col02, varchar_col03, text_col01, text_col02, text_col03, tinytext_col01, tinytext_col02, tinytext_col03, blob_col, tinyblob_col, json_col)values ('测试', '测试', '测试', '这是一个测试
字符串', '这是一个测试字符串', '这是一个测试字符串',  '这是一个测试文本', '这是一个测试文本', '这是一个测试文本','这是一个短文本', '这是一个短文本', '这是一个短文本',null, null, '{"key": "value"}');
insert into tab_charset_latin1_004 (char_col01, char_col02, char_col03, varchar_col01, varchar_col02, varchar_col03, text_col01, text_col02, text_col03, tinytext_col01, tinytext_col02, tinytext_col03, blob_col, tinyblob_col, json_col)values  ('αβγ', 'αβγ', 'αβγ', '测试@#$%^&*()_+特殊符号', '测试@#$%^&*()_+特殊符号', '测试@#$%^&*()_+特殊符号', '带特殊符号的文本: ~`!@#$%^&*()_+-=[]{}|;:\'",.<>/?', '带特殊符号的文本: ~`!@#$%^&*()_+-=[]{}|;:\'",.<>/?', '带特殊符号的文本:~`!@#$%^&*()_+-=[]{}|;:\'",.<>/?', '短特殊文本: !@#', '短特殊文本: !@#', '短特殊文本: !@#', null, null, '{"key": "value with @ # $"}');

select * from tab_charset_latin1_004;
select * from tab_charset_latin1_004 where char_col01 = "hello" or varchar_col01 = "测试@#$%^&*()_+特殊符号";
ERROR 1267 (HY000): Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8mb4_0900_ai_ci,COERCIBLE) for operation '='

[5 Feb 21:47] Roy Lyseng

Thank you for the bug report.

When I try these commands against an InnoDB table, the inserts are rejected because they contain UTF8MB4 characters that cannot be converted to latin1.

The error message that you get is because the UTF8MB4 character string cannot be coerced to latin1.