Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I know you're not supposed to do this, but I have a MySQL 5.5 master and a MySQL 5.1 replication slave. That's supposed to be okay, unless I do something stupid like use utf8mb4 that isn't supported by 5.1. Well, everything has been going well for a while and today I got a replication slave failure because character_set_client #45 isn't supported by MySQL 5.1.

I spent a lot of time trying to find out what #45 was... you see, it doesn't appear in any of MySQL's configuration files -- even on 5.5. I finally determined it was utf8mb4 (which I had suspected) but I'm not sure why it's being used. I performed a static query on the master ... something simple like UPDATE table SET field='value' and it replicated to the slave by setting the character set to utf8mb4. I executed hundreds of similar UPDATE statements at the same time, but some of them triggered the use of utf8mb4.

So... I'm going to be upgrading my slave to 5.5, but in the mean time is there a way to tell MySQL to never use utf8mb4?

share|improve this question
    
Thank you, kind sir !!! and +1 !!! –  RolandoMySQLDBA Feb 12 '13 at 20:02

1 Answer 1

up vote 2 down vote accepted

POLITICALLY CORRECT ANSWER

You cannot do MySQL Replication from a New Master to an Old Slave

I have written many posts about this:

In addition, there is the likelihood of dealing with the internal BINLOG magic number being in the wrong place and replication going senile. Here are my posts on the BINLOG magic number:

Since there is nothing in the meantime you can do, please upgrade your Slave to MySQL 5.5

ALTERNATE PERSPECTIVE

In all fairness, @ChristopherSchultz is correct in that you can implement it. I just yell and scream "RISK !!!" to be more cautious because of the BINLOG magic number's place in the MySQL Replication Universe.

share|improve this answer
    
Just checking: you mean that it's ''inadvisable'' to fo MySQL replication from a newer-versioned master to an older-versioned slave, right? Your other posts seem to indicate that it is not even possible, yet my experience is that it works fine until something odd like this (utf8mb4 is missing on slave) happens. –  Christopher Schultz Feb 12 '13 at 19:46
    
Binlogs start at different places in different versions and can possibly get mangled on rotation (binary logs and relay logs alike). The binary log magic number is also shifted: dba.stackexchange.com/a/27756/877 –  RolandoMySQLDBA Feb 12 '13 at 19:49
    
There are many caveats to attempt such replication. From my past experiences, the best solution is upgrading : dev.mysql.com/doc/refman/5.5/en/replication-compatibility.html –  RolandoMySQLDBA Feb 12 '13 at 19:52
    
To be politically correct, yes it is inadvisable. Sorry if my answer is rather paranoid. Upgrading takes away all the what-ifs and I-should-have-dones. –  RolandoMySQLDBA Feb 12 '13 at 19:54
    
Definitely: upgrading is the best course. It just sounded like you were saying there was a technical reason the two couldn't be set up for replication. The reality is that it works, but there are definite risks that can and -- in my case -- will cause problems. –  Christopher Schultz Feb 12 '13 at 20:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.