MySQL Bugs: #47425: replication breaks with Illegal mix of collations

Bug #47425	replication breaks with Illegal mix of collations
Submitted:	18 Sep 2009 0:22	Modified:	19 Oct 2009 10:36
Reporter:	H F	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.0.85-ius-log	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	ERROR 1267 (HY000): Illegal mix of collations

Description:
Master and slave have the following:

| tbl_xx| CREATE TABLE `tspi_xx` (
  `dm` varchar(50) collate latin1_german1_ci NOT NULL,
  `re` varchar(255) collate latin1_german1_ci NOT NULL,
  `datum` date NOT NULL,
  `q` int(11) NOT NULL,
  PRIMARY KEY  (`dm`,`datum`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci |

mysql> SHOW GLOBAL VARIABLES LIKE 'coll%';
+----------------------+-------------------+
| 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 GLOBAL VARIABLES LIKE 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

However replication breaks with :
Error 'Illegal mix of collations (latin1_german1_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='' on query. Default database: 'db1'. Query: 'update tspi_xx set q=q+1 where dm= NAME_CONST('srv',_latin1'123') and datum=curdate()'

How to repeat:
set up replication

Do you have the same collations declared for this table on master and slave?

Looks like your UPDATE comes from stored procedure. Please, check http://dev.mysql.com/doc/refman/5.1/en/create-procedure.html. Default collation of character_set_database (or declared CHARACTER SET) is used for parameter of procedure. 

If you are working with a table having different collation you'll have to use CAST() or CONVERT(). See http://dev.mysql.com/doc/refman/5.1/en/charset-convert.html.

Well, all databases are a binary copy of the primary - so there are no differences.
"Show create table" shows the same collation and charst, the global variables also. 
The update statement does not!! come out of a stored procedure but out of a php script.
There is no chance to use any cast or convert.

Any other suggestions or areas to investigate?

Thank you for the feedback.

> The update statement does not!! come out of a stored procedure but out of a php script.

This is possible what PHP changed connection collation.

>  `dm` varchar(50) collate latin1_german1_ci NOT NULL,

Query with "where dm= NAME_CONST('srv',_latin1'123')" should lead to same error if run with same settings on master. So this is most likely PHP changed connection collation.

But to be sure please send us binary log affected, so we can see which connection information is in it.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".