MySQL Forums
Forum List  »  Newbie

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
July 04, 2014 03:16AM
August 14, 2014 03:55AM
August 15, 2014 06:33PM
August 18, 2014 01:02AM
August 19, 2014 04:50PM
August 20, 2014 07:36AM
September 06, 2014 08:12PM
September 06, 2014 08:14PM
September 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.