MySQL Forums
Forum List  »  Newbie

Re: Replication Error Illegal Mix of Collations
Posted by: Rick James
Date: August 13, 2014 05:52PM

First, I assume F?rstaller and F³rstaller are supposed to be Fürstaller; is that correct?

Superscript-3 (³) exists in both latin1 and utf8, but is probably not intentionally in your data. '³' is B3 in latin1 and C2B3 in utf8; 'ü' = Fc/C3Bc.

"?" is used when transcoding a utf8 character into latin1, when there is no latin1 equivalent. So, I can half explain the "Duplicate key"s -- If 'Fაrstaller' and 'Fრrstaller' were both inserted, they would each be converted to F?rstaller in order to fit in latin1. That could lead to Duplicate key.

But that only half explains it. Given that 'Fürstaller' is the only spelling you would have used, then it should have stayed 'Fürstaller'; no Duplicate key. And no "?".

Your previous example (with 'Sch÷nauer') did not mention Duplicate key, but was otherwise similar in that '÷' (F7/C3B7) replaced 'ö' (F6/C3B6)? Both characters exist in both latin1 and utf8.

Usually, when there is a mis-configuration of character encoding, one character turns into two, but that is not the case here. I discuss that in
http://mysql.rjweb.org/doc.php/charcoll#disaster_before_insert
with the example of 'é' becoming 'é'.

The following may helps us isolate the problem further... Use the mysqlbinlog utility to dump the binlog (on the master) and the relay-log (on the slave). Try to dump just the UPDATE (or INSERT) that is causing trouble. Furthermore, please dump that in hex immediately (without doing any copy & paste, which _could_ be transcoding things!).

Another help...
SELECT HEX(strJOB) FROM ... WHERE ...
on both Master and Slave for the 'same' record.

One thought... Perhaps 4.1 is writing the binlog in utf8, in spite of everything being declared "latin1". And, perhaps, 5.x is not liking that.

Another note: If you are entering data into the mysql commandline utility via Windows cmd, the "codepage" is likely to be screwing things up. This will show up in the SELECT HEX() suggested above. For example, the "81" in this makes no sense to me:
mysql> select hex('Fürstaller');
+----------------------+
| hex('Fürstaller')    |
+----------------------+
| 4681727374616C6C6572 |
+----------------------+
So, if I had done
mysql> INSERT INTO ... VALUES('Fürstaller', ...);
I would probably have whatever 81 means, not an umlaut-u.
If you have 81, then your data is already garbaged on the Master, and I don't (yet) have a solution for fixing it.
With utf8, it should be 46C3BC727374616C6C6572.
With latin1, it should be 46FC727374616C6C6572.

"chcp" says I am using codepage 437 (OEM - USA).

This is my cheat-sheet on most of the encodings I am quoting:
http://mysql.rjweb.org/doc.php/charcoll#8_bit_encodings

> I can then skip one step with the replication and stop/start it again

In the process, you lost a record on the Slave.

So, it feels like we (you) need to do the following:
1. Validate that you do/don't have garbage on the Master. (HEX...)
2. Figure out why it replicating 'wrong'. (mysqlbinlog)
3. Rebuild the Slave. (Re-dump and load)
It is unclear where in these steps the upgrade to 5.1 should occur; certainly not before step #1.

Options: ReplyQuote


Subject
Written By
Posted
Re: Replication Error Illegal Mix of Collations
August 13, 2014 05:52PM
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.