Bug #6676 Illegal mix of collations in slave but not master

• Submitted: 17 Nov 2004 4:05
• Modified: 22 Feb 2005 19:58
• Reporter: Ken Tsang
• Status: Closed
• Category: MySQL Server: Replication
• Severity: S1 (Critical)
• Version: 4.1.7
• OS: Linux (red hat 9)
• Assigned to: Lars Thalmann
• CPU Architecture: Any

[17 Nov 2004 4:05] Ken Tsang

Description:
Error in slave but not in master.

Last_Error: Error 'Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin1_swedish_ci,NONE) for operation '='' on query. Default database: 'mydatabase'. Query: 'update testcoll set testpri = 'test' where testpri = @pri'

How to repeat:
table:
DROP TABLE IF EXISTS `testcoll`;
CREATE TABLE `testcoll` (`testpri` varchar(100) NOT NULL default '',PRIMARY KEY  (`testpri`)) ENGINE=MYISAM DEFAULT CHARSET=latin1;

Run the following stmt in master will show error in slave:
set @pri = 'test';
update testcoll set testpri = 'test' where testpri = @pri;

same character set and collations for both master and slave
mysqld startup parameter:
./mysqld_safe --user=mysql --default-character-set=latin1 --default-collation=latin1_swedish_ci

[17 Nov 2004 20:03] MySQL Verification Team

Verified with 4.1.8-debug-log

[30 Jan 2005 22:09] Leif Zars

I got 

Error 'Illegal mix of collations (utf8_bin,IMPLICIT) and (utf8_general_ci,NONE) for operation '='' on query. Default database: ''. Query: 'UPDATE mysql.user SET Password=Password('gp1a2sx3dc') WHERE User=@new_user'

during replication on the slave, the master took it just fine.

most of my pswds are pre  41 byte so that might have something to do with it

in Windows 2003, MySQL4.1.9

[14 Feb 2005 20:52] peter d

I too am having a similar issue during replication. Error is:
Error 'Illegal mix of collations (utf8_bin,IMPLICIT) and (latin1_swedish_ci,NONE) for operation '='' on query. Default database: ''. Query: 'DELETE FROM mysql.db WHERE User=@new_user'

[15 Feb 2005 9:34] Armin Lorenz

050215 10:18:01 [ERROR] Slave: Error 'Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (binary,NONE) for operation '='' on query. Default database: 'ipc_tlm'. Query: 'create temporary table tmp_config select * from tblhostsconfig where hostname = @x', Error_code: 1267

This query was used:

set @x = 'pc39526';
set @y = 'pc39527';
delete from tblhostsconfig where hostname = @y;
drop table if exists tmp_config;
create temporary table tmp_config select * from tblhostsconfig where hostname = @x;
update tmp_config set hostname = @y;
insert into tblhostsconfig select * from tmp_config;
select * from tblhostsconfig where hostname = @y;

[22 Feb 2005 12:02] Alexander Barkov

Ok to push the fix.

[22 Feb 2005 14:20] Lars Thalmann

This has now been pushed into 4.1 tree and will most likely 
be part of the 4.1.11 release.

ChangeSet@1.2165.16.2, 2005-02-21 17:52:15+01:00, [e-mail]
  BUG#6676: Added comment for the fix

ChangeSet@1.2165.16.1, 2005-02-21 16:26:04+01:00, [e-mail]
  BUG#6676: Derivation of user variables should be of derivation "IMPLICIT"

[22 Feb 2005 19:58] Paul DuBois

Noted in 4.1.11 changelog.