4

I have a replication system where my master has a mysql 5.5 version while the slave is mysql 5.1. I know that this is not supported because the slave version should be greater than the master, but we cannot upgrade yet.

Everything is going fine until a new database is created in the master, then the slave replication fails and shows the error:

Query caused different errors on master and slave.     
Error on master: message (format)=
'Column count of mysql.%s is wrong. Expected %d, found %d. Created with MySQL %d, now running %d. 
Please use mysql_upgrade to fix this error.' error code=1558 ; Error on slave: actual message='no error', error code=0. Default database: 'newdb'. Query: 'DROP DATABASE newdb'

My workaround is:

set global sql_slave_skip_counter=2;
create database newdb;
start slave

After doing a checksum of all database tables everything seems ok, but we have an awful application that creates databases very often, so I would like to know if there is a way that doesn't imply coding to solve the problem without manually creating the database.

Improve this question

3 Answers 3

Reset to default
2

Every replication event generated by the master includes the error code that resulted from execution of the query that generated the event. This is usually "success" (0, no error).

When the slave executes a query, it expects the error it encounters to be the same error as the master encountered (again, usually "no error"). When this doesn't happen, replication stops. Note that the error message from the master isn't preserved, just the code. That's why you see placeholders in the error message, because the slave displays the sprintf() template for the error message.

But note, carefully, where the error is.

Check the master server's error log.

It looks as if the master was not correctly upgraded to MySQL 5.5; specifically, it looks like the mysql_upgrade utility was not used to upgrade the system tables to be fully compatible with MySQL 5.5.

http://dev.mysql.com/doc/refman/5.5/en/mysql-upgrade.html

You are indeed running an unsupported configuration, with a newer master and an older slave... but in this case, it appears that your master has a problem that is actually unrelated to replication. 

Error on master: 
message (format)=
'Column count of mysql.%s is wrong. Expected %d, found %d.  Created with MySQL %d, now running %d. 
Please use mysql_upgrade to fix this error.' 
error code=1558 ;

The master encountered an error; you may find similar errors in the master's log, although it's possible that they only occur at startup, if at all. In any event, your application should have seen this error also... perhaps it's ignoring it. :(

The slave didn't encounter any error executing the actual query.

Error on slave: actual message='no error', error code=0.

It occurred to me that it's very important that the actions performed by mysql_upgrade can't be safely replicated, since the slave server is older. It turns out, this was anticipated in the design, so the mysql_upgrade utility appears to be safe to run on a master, without disconnecting the slave, even if the slave is older (or was already upgraded). According to this comment in the source code of mysql_upgrade.c:

Master and slave should be upgraded separately. 
All statements executed by mysql_upgrade will not be binlogged.
Improve this answer
1

The politically correct answer would be that you cannot perform MySQL Replication from a newer Master to an older Slave. You proven the politically correct answer to be wrong. Notwithstanding, the correct answer is you shouldn't do it, at least not for very long. Consequently, I have a very strong warning for you: It can be very unstable and you should upgrade ASAP. Why ?

The book "Understanding MySQL Internals" (ISBN 0-596-00957-7)

sdhkbs

has a section on binary log events (pages 223-227). Those events have unique codes that are being interpreted as follows:

  • codes get interpreted by mysqlbinlog when extracting and displaying SQL commands
  • MySQL Replication
    • IO Thread interprets events and stores the events in the binlog
    • SQL Threads interprets and validates events from relay log and executes the commands

The error message you are getting comes from that validity check by the Replication Threads.

What's funny about this is that the book I quoted was made in April 2007. There have been more binlog events added to MySQL since that book's publication. Thus, there will be occasions when a Slave cannot interpret a binlog event and just consider the relay log corrupt. (It's like a conversation between two people from England and Brooklyn USA, or Brazil and Portugal, or Spain and Puerto Rico). The slight shift in vocabulary between binlogs from two different versions of MySQL is just being misconstrued as unintelligible between them.

About six(6) weeks ago, a client was using 5.5.30 Master and 5.6.21 slave (On the path of migration to MySQL 5.6.21 (Master and All Slaves)). Although this replication setup works at present, there have been occasions when the Slave would break because it could not unpack a binlog event from an older Master. I learned of this phenomenon from my boss when he showed me the error in the error log. It had a rather cryptic error message (when MySQL Replication would either start or rotate relay logs) with the word Unpack (You should do grep -i unpack error.log and see if this is the case for you) Here is a snippet from that error log:

Thread pointer: 0x7ff048000990
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7ff14cb347e0 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x35)[0x8dbbb5]
/usr/sbin/mysqld(handle_fatal_signal+0x494)[0x665f24]
/lib64/libpthread.so.0(+0xf710)[0x7ff14c8b8710]
/usr/sbin/mysqld(_Z10unpack_rowPK14Relay_log_infoP5TABLEjPKhPK9st_bitmapPS5_PmS5_+0x43f)[0x898b7f]
/usr/sbin/mysqld(_ZN14Rows_log_event24do_index_scan_and_updateEPK14Relay_log_info+0x100)[0x873c50]
/usr/sbin/mysqld(_ZN14Rows_log_event14do_apply_eventEPK14Relay_log_info+0x852)[0x877c32]
/usr/sbin/mysqld(_ZN9Log_event11apply_eventEP14Relay_log_info+0x74)[0x87fb54]
/usr/sbin/mysqld(_Z26apply_event_and_update_posPP9Log_eventP3THDP14Relay_log_info+0x263)[0x8aefd3]
/usr/sbin/mysqld[0x8b0cf2]
/usr/sbin/mysqld(handle_slave_sql+0xb09)[0x8b27c9]
/usr/sbin/mysqld(pfs_spawn_thread+0x12a)[0xb0526a]
/lib64/libpthread.so.0(+0x79d1)[0x7ff14c8b09d1]
/lib64/libc.so.6(clone+0x6d)[0x7ff14b61986d]

Please note these four lines

/usr/sbin/mysqld(_Z10unpack_rowPK14Relay_log_infoP5TABLEjPKhPK9st_bitmapPS5_PmS5_+0x43f)[0x898b7f]
/usr/sbin/mysqld(_ZN14Rows_log_event24do_index_scan_and_updateEPK14Relay_log_info+0x100)[0x873c50]
/usr/sbin/mysqld(_ZN14Rows_log_event14do_apply_eventEPK14Relay_log_info+0x852)[0x877c32]
/usr/sbin/mysqld(_ZN9Log_event11apply_eventEP14Relay_log_info+0x74)[0x87fb54]
/usr/sbin/mysqld(_Z26apply_event_and_update_posPP9Log_eventP3THDP14Relay_log_info+0x263)[0x8aefd3]

In my particular case, the Slave would crash trying to interpret the binlog event.

What would happen is the following:

  • STEP 01 : Slave would crash on the unrecognized event
  • STEP 02 : mysqld_safe would restart mysqld
  • STEP 03 : mysqld would try to start replication again (skip-slave-start not configured)
  • STEP 04 : Go back to STEP 01

To correct this madness, I had to do the following:

  • Run kill -9 on the mysqld_safe process
  • Run service mysql start --skip-slave-start
  • Run CHANGE MASTER TO the next available position
  • Run START SLAVE;

From there, replication was off and running. This has happened to my boss once and to me once. Of course, I reloaded the data from the Master in full to get the Slave sync'd (Good thing the database was less than 100MB).

If this crazy scenario was possible from a MySQL 5.5.30 Master to MySQL 5.6.21 Slave, (as I have shown happen and two maintenance cycles to correct), then the reverse is far more likely (the reverse being a new Master and an older Slave).

For the sake of your sanity and your database topology, please upgrade.

Improve this answer
0

when you create MYSQL replication mater that has the newer version than slave this will create compatibility issue, due some changes in the binary logs and SQL base, your workaround may solve the issue for now, but you will encounter many issues in the future

SQL incompatibilities: You cannot replicate from a newer master to an older slave using statement-based replication if the statements to be replicated use SQL features available on the master but not on the slave.

Use of row-based replication: Row-based replication was implemented in MySQL 5.1.5, so you cannot replicate using row-based replication from any MySQL 5.5 or later master to a slave older than MySQL 5.1.5.

references : http://dev.mysql.com/doc/refman/5.5/en/replication-compatibility.html

Should master and slave have same mysql version?

suggested solutions:

1) upgrade Master version to the same version as slave. best solution

2) skip slave error slave-skip-errors= (not the best solution I will leave it to the last option)

Improve this answer
4
  • Thank you for the answer, but as I said in the question, I already know that there are incompatibility issues, and I will update when possible, but I'm looking for a solution for this specific problem at the moment.
    – MazarD
    Commented Jan 2, 2015 at 13:45
  • My bad (forgot to save the edit :)) for suggested solution.
    – Ahmad Abuhasna
    Commented Jan 2, 2015 at 14:00
  • If I use slave-skip-errors, the database in the slave will not exist and I will have a lot of other errors. By your answer I understand you think there is no other way to avoid the "create database" replicated without errors (without upgrading mysql instances).
    – MazarD
    Commented Jan 2, 2015 at 14:24
  • in the first solution you can upgrade MySQL server and that will fix the issue for you (best solution), but for the other solution that I wrote (the last option "not recommended") is to skip the some errors and that will not fix the issue 100%. (some of the replication will be mode manually)
    – Ahmad Abuhasna
    Commented Jan 2, 2015 at 15:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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