Data/rows are missing in the slave server while replication

Hi All,

When are using 2 level replication:

MASTER1 (mysql 5.6.16-log) <-----> MASTER2 (mysql 5.6.16-log) -----> SLAVE1 (Percona 5.6.39-83.1-log)

The SLAVE1 has been setup recently ( a month ago) and after initial replication setup we run checksum between MASTER2 and SLAVE1 and was good.

But later when we compared the the total row count of some core tables between MASTER2 and SLAVE1 there is a mismatch and checksum also show such difference

We identified a huge difference in the total row count (using “select count(*) from table”) and some rows are not even synced to SLAVE1. For getting more information I am attaching the my.cnf of SLAVE1.

The purpose of the new slave is to migrate the database to a new server running on Percona and due to this unusual behaviorI am stucked and under time pressure.

Your analysis and feedback on this is very helpful.

Regards, Johnson

my.cnf_slave1.txt (4.28 KB)

Hello there. The team are likely to need some more information from you… if you can get me that then I will see if I can find someone to advise:
[LIST]
[]the error log from the slave
[
]show slave status \G see here for info if needed [URL=“MySQL :: MySQL 5.6 Reference Manual :: 13.7.5.35 SHOW SLAVE STATUS Statement”]https://dev.mysql.com/doc/refman/5.6...ve-status.html[/URL]
[]the config for Master 2
[
]do you set log_slave_updates? [URL=“https://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html”]https://dev.mysql.com/doc/refman/5.6...ons-slave.html[/URL]
[]are you using pt-table-checksum for your checks?
[
]the count(*) might not be telling you exactly what you think it should be (I understand)
[/LIST] Anyhow, if you could get this info … thanks. And also about your environment, any server error logs etc.

I moved your post to the right place in the forum.

Thank you Lorraine,
[LIST]
[]the error log from the slave - [COLOR=#B22222]Attached
[
]show slave status \G see here for info if needed [URL=“MySQL :: MySQL 5.6 Reference Manual :: 13.7.5.35 SHOW SLAVE STATUS Statement”]https://dev.mysql.com/doc/refman/5.6...ve-status.html[/URL] - [COLOR=#B22222]Attached
[]the config for Master 2 - [COLOR=#B22222]Attached
[
]do you set log_slave_updates? [URL=“https://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html”]https://dev.mysql.com/doc/refman/5.6...ons-slave.html[/URL]
[/LIST] [COLOR=#B22222]On all the servers we have log_slave_updates=1
[LIST]
[]are you using pt-table-checksum for your checks?
[/LIST][B][/B][COLOR=#B22222]Yes, We are using pt-table-checksum in MIXED BinarylLog mode
[LIST]
[
]the count() might not be telling you exactly what you think it should be (I understand)
[/LIST][LIST=1]
[
][COLOR=#B22222]I stopped the slave on Master2 (readonly) to prevent updates from Master1
[][COLOR=#B22222]Ensure that the seconds_behind_master is Zero on Master2 and Slave1 to make sure that both Master2 and Slave1 are in sync
[
][COLOR=#B22222]Run select count() from on both and can see a huge count difference
[][COLOR=#B22222]Also run pt-table-checksum on some selected tables and found data inconsistency
[/LIST]

mysqld_Slave1.txt (52.6 KB)

my.cnf_slave1.txt (4.28 KB)

my.cnf_master2.txt (4.19 KB)

slave_status_Slave1.txt (2.7 KB)

OK thank you for that update. I will see if I can find someone to take a look. If there’s something clear that you can do then hopefully they can help.

A little context though: if it’s something a bit more specific to a particular case - that can sometimes be more difficult to address through this public forum.

Hi Lorraine,

I missed to say that we have dynamically changed innodb_flush_log_at_trx_commit = 2 on Master2(Read_only) and Slave1 to minimize the replication lag.

Also attaching a sample table row count details from all the three servers. (M1, M2 and S1)

Master1 (Production)

mysql> select count(),max(sys_created_on) from task;
±---------±--------------------+
| count(
) | max(sys_created_on) |
±---------±--------------------+
| 4819832 | 2018-08-27 07:44:57 |
±---------±--------------------+
1 row in set (1.38 sec)

Master2 (Read_only)

mysql> select count(),max(sys_created_on) from task;
±---------±--------------------+
| count(
) | max(sys_created_on) |
±---------±--------------------+
| 4819832 | 2018-08-27 07:44:57 |
±---------±--------------------+
1 row in set (1.62 sec)

Slave1

mysql> select count(),max(sys_created_on) from task;
±---------±--------------------+
| count(
) | max(sys_created_on) |
±---------±--------------------+
| [COLOR=#FF0000] 4806350 | 2018-08-27 07:44:57 |
±---------±--------------------+
1 row in set (1.14 sec)

On slave1 there is a count difference of 13482 rows and all the server’s seconds behind master is Zero.

Regards,
Johnson

Hi Team,

Any updates on thsi topic !!

Regards,
Johnson

Can someone help me on this.

Regards,
Johnson