2

I have a pair of Debian 7 servers with MySQL 5.6.23. The slave server is a clone of the master (but the uuid has been regenerated on the slave).

For some reason, whenever MySQL is restarted on the Master via "service mysql restart," the Slave will continue reading the Master_Log_File and Read_Master_Log_Pos, but won't process the Relay_Master_Log_File.

Slave I/O and Slave SQL are both running, but the Seconds_Behind_Master continue to increment.

The Slave_SQL_Running_State says "Waiting for Slave Worker to release partition."

Obviously, I've tried to stop and start the slave, restart the MySQL service on the slave and master, and restart the slave and master servers.

To fix the issue, I have to blank and recreate the slave. I do this via a script I've made to dump the master file and position, use mysqldump to pull the database onto the slave, and then start slaving from the recorded master file and position. The mysqldump flags are --skip-lock-tables --flush-logs --hex-blob --master-data=2 --single-transaction --comments --routines

Am I missing something? Any help is greatly appreciated.

Improve this question
2
  • 1) Are you using multi-threaded replication ? 2) Are you dumping multiple databases ?
    – RolandoMySQLDBA
    Commented Mar 13, 2015 at 14:09
  • Yes, I'm using 4 threads
    – William Carter Baller
    Commented Mar 13, 2015 at 14:26

2 Answers 2

Reset to default
1

Guess what ? You have a nasty little bug (Replication stall with multi-threaded replication)

This bug was first reported in MySQL 5.6.17 on 20 Jun 2014 15:45

SUGGESTION #1

You need to look over all the release notes from MySQL 5.6.18 up to MySQL 5.6.22 to see if it was resolved. If any of those release notes claim the problem was fixed, then someone missed a patch. I do advise that you do not regress to anything before MySQL 5.6.21 (Any known issues upgrading from MySQL 5.1.73 to 5.6.21? under ISSUE #3 : Security Issues)

SUGGESTION #2

Don't use multithreaded replication until this bug is fixed.

SUGGESTION #3

In the aforementioned bug report, it says this somewhere in the middle

When MySQL comes back up again, the log is rotated, and IO thread starts writing from the event group which was partially written.

. . .

You can see that the "end_log_pos" values are the same for the partial event I showed earlier and this one. It is the same UPDATE transaction that was partially written.

Now comes the interesting part. When the coordinator reads the relay log, it sends the partial event to the worker, since the event is partial hence the worker never commits the transaction and the transaction is kept open. The coordinator reads the next event (which is the full version of the partial event) but the coordinator cannot assign the next event to another worker because of one of the workers having an open transaction.

And apparently, MTS waits for workers to commit transactions when it sees log rotated.

I would suggest not using --flush-logs with mysqldump, just in case there are some issues with group commits that are all-of-a-sudden split by the mysqldump and this bug isn't viewing incoming binlog events correctly.

Improve this answer
3
  • I'm certain you hear this many times a day, but you're the man! That fixed it. I remember reading that in the release notes, but honestly I didn't know what it meant at the time as I was coming from MySQL 5.1 (stop laughing).
    – William Carter Baller
    Commented Mar 13, 2015 at 18:41
  • If love to but I don't have enough rep.
    – William Carter Baller
    Commented Mar 13, 2015 at 20:38
  • Not referring to the up and down arrows. There should be a circle with a checkmark in it under the down arrow. If you see it, please click on it. If you do not see, then the new UI has it missing.
    – RolandoMySQLDBA
    Commented Mar 13, 2015 at 20:40
0

I used @RolandoMySQLDBA's suggestion and commented out slave_parallel_workers, which defaults to 0, effectively disabling multi-threaded replication. This fixed the issue, which was addressed in the MySQL 5.6.23 release notes.

Improve this answer

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.