MYSQL replication does not work for all tables

Question

I have a problem with MySQL replication - there is one table on master server which doesn't appear on slave server. Both master and slave has the same master_log_file and master_log_position, both slave_io and slave_sql threads are running, I even tried to add an empty table to the master database but it does appear on the slave database. It's not the first time I got such error but before that my symptopms were new data did not appear in slave database. Are there any other solutions for this problem than stopping replication on the slave, dropping the database, dumping it on master server, rsyncing to slave server and restarting replication from new file/position?

I noticed using

SHOW SLAVE STATUS;

that Relay_Log_Pos is smaller than Read_Master_Log_Pos and Relay_Log_File differ from Master_Log_File but Slave_SQL_Running_State says

Slave has read all relay log; waiting for more updates

Seconds_Behind_Master says 0.

First of all, Relay_Log_Pos and Read_Master_Log_Pos are different values and can differ, that's fine. For your replication: do you use the same mysql versions and configuration? You can get problems like this if you run a statement that relies on a feature that the other server does not support/is disabled. Have you tried creating an identical table (with different name) to the one that is not replicated to check if the table uses something that does not work on the slave? — Solarflare
Are there any values set for replicate_do_db/replicate_do_table? These are in my.cnf, and will be in slave status. Resyncing ins a pain - agreed. What is you binlog_format? Row, Mixed, Statement? Check the binlog contents with mysqlbinlog -- make sure its in there, otherwise, it definitely won't replicate... — Kevin Bott
@KevinBott I don't have values set for replicate_do_db, I filter them on master side with binlog_do_db, my binlog_format is mixed. — baskax
@Solarflare - I use different versions (5.5.55 on the master, 5.7.13 on the slave) because replication server doesn't replicate only one server, I have different replication channels but the other works just fine. I'll try to do this with same table and another name for it — baskax
That might just be it, 5.5->5.7 is a) not (officially) supported and b) 5.7. can behave differently in a lot of things, so some statements will not be supported anymore (mayb depending on your configuration settings). Do you run your slave with the --slave-skip-errors-option (or did you maybe restart it manually after such an error occured that stopped the slave)? — Solarflare

Solarflare Solarflare · Accepted Answer · 2017-09-22T07:39:15

MySQL officially only supports replication to the next higher version (although it will work for 5.7.13+), see Replication Compatibility Between MySQL Versions:

MySQL supports replication from one release series to the next higher release series. For example, you can replicate from a master running MySQL 5.5 to a slave running MySQL 5.6, from a master running MySQL 5.6 to a slave running MySQL 5.7, and so on.

However, you may encounter difficulties when replicating from an older master to a newer slave if the master uses statements or relies on behavior no longer supported in the version of MySQL used on the slave.

By default, replication will stop if an error occurs, and you have to restart it (after fixing the error). If you use the --slave-skip-errors=all-option however, it will skip these errors:

Normally, replication stops when an error occurs on the slave, which gives you the opportunity to resolve the inconsistency in the data manually. This option causes the slave SQL thread to continue replication when a statement returns any of the errors listed in the option value.

Do not use this option unless you fully understand why you are getting errors. If there are no bugs in your replication setup and client programs, and no bugs in MySQL itself, an error that stops replication should never occur. Indiscriminate use of this option results in slaves becoming hopelessly out of synchrony with the master, with you having no idea why this has occurred.

MySQL 5.5. and 5.7. will actually behave differently for a lot of statements, so enabling this option in this scenario will require even more care.

Without seeing your actual table create-statement, it is unclear what exactly caused that problem and how to fix it (or if it is possible), but you should especially check your configuration settings. MySQL 5.7. enables strict mode by default, so a usual suspect for incompatibilites is e.g. a zero default value for date/timestamp-columns like default '0000-00-00' (either explicit or implicit), which is not allowed anymore, see no_zero_date.

Even if you seem to not be too keen about 100% replication (which can snowball very fast, but that is up to you to evaluate for your scenario), resetting your slave (after fixing e.g. the configuration settings) at least once is probably the easiest solution, as there might have been other things you may have missed, and, if executed without errors, will also doublecheck if your tables and data up to that point are compatible with your 5.7-slave now.

MYSQL replication does not work for all tables

1 Answers