MySQL Forums
Forum List  »  Newbie

Re: Replication Error Illegal Mix of Collations
Posted by: Rick James
Date: July 07, 2014 08:00AM

> 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.

First, I need to slap you around...

* "so i cant stop mysql" -- The Slave is already stopped. Do you mean that you cannot stop the Master, but the Slave can be left down while you solve this?
* Use Unix for production systems.
* 4.1.20 is a decade old; upgrade. Recent versions allow certain "online" ALTERs.
* 24/7 without a way to upgrade? Should have put more thought into the design.
* "Can't change any queries" -- bad situation to let yourself into.
* "third-party" -- and they won't help you? You should have written the code yourself. (The CREATE TABLE looks quite sloppy -- a symptom of third-party software; in that case worse than what a novice would do.)

Now, I'll ponder whether there is a solution...

* How big is `lager`.`materialpostings`? If it is small, you can do an ALTER with minimal downtime.
* "WHERE `strJOB`='Weibold K-Sch÷nauer 26.06.'" worries me. Is there supposed to be a divide symbol in that string? I am worried that the data may be corrupt on the Master.
* Do you have a test system (Master+Slave) on which you can experiment with possible solutions?
* The SHOWs look the same between the two machines; this is encouraging.

> 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.

* Manually on the Slave? Otherwise, the "skip" will leave the Slave inconsistent with the Master.
* Do you use SET NAMES?

* Is German involved? There was a collation change: http://mysql.rjweb.org/doc.php/charcoll#german_sharp_s_ . (However, since you are not using the relevant collation, it probably does not matter.)

More discussion:
http://mysql.rjweb.org/doc.php/charcoll

Perhaps (just perhaps), 'Weibold K-Sch÷nauer 26.06.' is the cause and the solution. The divide symbol may have been entered in utf8 and sent through replication that way. (Or mangled somewhere.) Can you avoid that character, plus any other non-ascii characters?

Do think seriously about upgrading. After about 5 years, you can get so stuck in the old software that it becomes "impossible" to upgrade without "starting over".

Options: ReplyQuote


Subject
Written By
Posted
Re: Replication Error Illegal Mix of Collations
July 07, 2014 08:00AM
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.