MySQL Forums :: Newbie :: Replication Error Illegal Mix of Collations


Advanced Search

Replication Error Illegal Mix of Collations
Posted by: Andreas Kammerer ()
Date: July 04, 2014 03:16AM

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)

Options: ReplyQuote


Subject Written By Posted
Replication Error Illegal Mix of Collations Andreas Kammerer 07/04/2014 03:16AM
Re: Replication Error Illegal Mix of Collations Rick James 07/07/2014 08:00AM
Re: Replication Error Illegal Mix of Collations Andreas Kammerer 08/12/2014 01:42AM
Re: Replication Error Illegal Mix of Collations Rick James 08/13/2014 05:52PM
Re: Replication Error Illegal Mix of Collations Andreas Kammerer 08/14/2014 02:58AM
Re: Replication Error Illegal Mix of Collations Rick James 08/15/2014 09:51PM
HexDumped Binlogs Andreas Kammerer 08/14/2014 03:55AM
Re: HexDumped Binlogs Rick James 08/15/2014 06:33PM
Re: HexDumped Binlogs Andreas Kammerer 08/18/2014 01:02AM
Re: HexDumped Binlogs Rick James 08/19/2014 04:50PM
Dump Andreas Kammerer 08/20/2014 07:36AM
Cant reproduce Problem in Lab Andreas Kammerer 08/20/2014 08:19AM
Re: Cant reproduce Problem in Lab Rick James 08/21/2014 11:52PM
Re: Cant reproduce Problem in Lab Andreas Kammerer 09/05/2014 05:52AM
Re: Cant reproduce Problem in Lab Rick James 09/06/2014 08:12PM
Re: Cant reproduce Problem in Lab Rick James 09/06/2014 08:14PM
Re: Cant reproduce Problem in Lab Andreas Kammerer 09/15/2014 08:58AM
Re: Cant reproduce Problem in Lab Rick James 09/16/2014 11:17PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.