Replication Error Illegal Mix of Collations
I have a Replication Problem here which i cant resolve even after spending two days on google. Hope you can help me.
I have a Windows Machine with mysql 4.1.20 as Replication master. This is a 24/7 in use machine controlling system - so i cant stop mysql (without some days pre-warning time) and i cant change any querys, as the software is from a third-party.
I am replicating to another Windows Machine in the datacenter with MySql 4.1.22 as Slave. Replication starts and then fails with:
Last_Error: Error 'Illegal mix of collations (latin1_swedish_ci
,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='' on query. Default
database: 'lager'. Query: 'UPDATE `lager`.`materialpostings` SET `fSAVESTATUS`='
0' WHERE `strJOB`='Weibold K-Sch÷nauer 26.06.' AND `nPARTIALRESULT`='0' AND `str
MAT`='KRO514PE19' AND `fTYPE`='0' AND `nTYP`='1' AND `strNR`='2' AND `fSAVESTATU
S`='1''
for example. If i execute the query manually it works like a charm. I can then skip one step with the replication and stop/start it again. It works some Positions and then fails again with a similar Error.
Here some things that might be useful:
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.21.24
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: PC-SD-PL-04-bin.000001
Read_Master_Log_Pos: 2337988
Relay_Log_File: SRV-PLATTENLAG-relay-bin.000039
Relay_Log_Pos: 162414
Relay_Master_Log_File: PC-SD-PL-04-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1267
Last_Error: Error 'Illegal mix of collations (latin1_swedish_ci
,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='' on query. Default
database: 'lager'. Query: 'UPDATE `lager`.`materialpostings` SET `fSAVESTATUS`='
0' WHERE `strJOB`='Weibold K-Sch÷nauer 26.06.' AND `nPARTIALRESULT`='0' AND `str
MAT`='KRO514PE19' AND `fTYPE`='0' AND `nTYP`='1' AND `strNR`='2' AND `fSAVESTATU
S`='1''
Skip_Counter: 0
Exec_Master_Log_Pos: 231332
Relay_Log_Space: 2269413
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
MASTER:
mysql> select version();
+----------------------+
| version() |
+----------------------+
| 4.1.20-community-log |
+----------------------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'char%';
+--------------------------+--------------------------+
| Variable_name | Value |
+--------------------------+--------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | c:\mysql\share\charsets/ |
+--------------------------+--------------------------+
7 rows in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)
mysql> show create table materialpostings;
+------------------+------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------+
| Table | Create Table
|
+------------------+------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------+
| materialpostings | CREATE TABLE `materialpostings` (
`strJOB` varchar(255) NOT NULL default '',
`nPARTIALRESULT` int(10) unsigned NOT NULL default '0',
`strMAT` varchar(255) NOT NULL default '',
`fTYPE` tinyint(1) NOT NULL default '0',
`nTYP` int(11) NOT NULL default '0',
`strNR` varchar(63) NOT NULL default '',
`nLENGTH` double default NULL,
`nWIDTH` double default NULL,
`nCOUNT` int(11) default NULL,
`strTYPENR` int(11) default NULL,
`fSAVESTATUS` tinyint(1) default NULL,
`idJOB` bigint(20) default NULL,
PRIMARY KEY (`strJOB`,`nPARTIALRESULT`,`strMAT`,`fTYPE`,`nTYP`,`strNR`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+------------------+------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------+
1 row in set (0.00 sec)
SLAVE:
mysql> SHOW VARIABLES LIKE 'char%';
+--------------------------+--------------------------+
| Variable_name | Value |
+--------------------------+--------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | C:\mysql\share\charsets/ |
+--------------------------+--------------------------+
7 rows in set (0.00 sec)
mysql> select version();
+---------------------+
| version() |
+---------------------+
| 4.1.22-community-nt |
+---------------------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)
mysql> show create table materialpostings;
+------------------+------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------+
| Table | Create Table
|
+------------------+------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------+
| materialpostings | CREATE TABLE `materialpostings` (
`strJOB` varchar(255) NOT NULL default '',
`nPARTIALRESULT` int(10) unsigned NOT NULL default '0',
`strMAT` varchar(255) NOT NULL default '',
`fTYPE` tinyint(1) NOT NULL default '0',
`nTYP` int(11) NOT NULL default '0',
`strNR` varchar(63) NOT NULL default '',
`nLENGTH` double default NULL,
`nWIDTH` double default NULL,
`nCOUNT` int(11) default NULL,
`strTYPENR` int(11) default NULL,
`fSAVESTATUS` tinyint(1) default NULL,
`idJOB` bigint(20) default NULL,
PRIMARY KEY (`strJOB`,`nPARTIALRESULT`,`strMAT`,`fTYPE`,`nTYP`,`strNR`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+------------------+------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------+
1 row in set (0.00 sec)