I’ve have a weird one.
So in part of our system we store files in mysql as BLOBs but broken up into segments. My issue is someone uploaded a 600MB video, which worked fine on all but 1 slave. For some reason that slave had been build with only 1GB of /tmp space and the server ran out of /tmp space.
We’ve fixed that, but now when I start the slave it only reads the primaries binlog to a certain position (equates to the end of the INSERT for segment 63 of 75) and then appears to stop.
I’ve watched the ‘show engine innodb status’ and I can see enough of the transaction to see what segment it’s on and it slowly grows to segment 63, then resets back to 0 and starts to grow again???
Looking at the relay logs, log 1 starts at the beginning of the large transaction with segment 0 and ends at segment 63 and is 1082041979 in size. Log 2 on the other hand is only 360 bytes and appears to contain nothing. Then log 3 is 1082041979 again and as you might expect starts over again at he beginning of the large transaction with segment 0.
This pattern repeats over and over.
The binlog that this slave is reading from is 1455431416 in size where the transaction starting point is binlog position 173467992. So it’s not all the 1 transaction, but most of it is.
I’ve looked at the binlog with mysqlbinlog and there doesn’t appear to be anything unusual between segments 63 and 64. The binlog appears to finish the transaction and other slaves read and executed this transaction without issue.
I’ve run RESET SLAVE, with no effect. There is the max_binlog_size (set to 1GB), but of course it can only be 1GB in size and it ‘should’ automatically increase to fit whatever a transaction needs.
I’m stumped what is blocking the slave from reading past that 1 binlog position.