Akom's Tech Ruminations

Various tech outbursts - code and solutions to practical problems

MySQL replication dies with fatal error 1236: Client requested master to start replication from impossible position Linux

Posted by Admin • Friday, February 4. 2011 • Category: Linux

This time, my master-master setup stopped replicating with no explanation at all - running START SLAVE yielded no warnings or errors. Inspecting the log yielded the aforementioned error (on Ubuntu, mysql logs to /var/log/syslog by default):
[ERROR] Error reading packet from server: Client requested master to start replication from impossible position ( server_errno=1236) [ERROR] Got fatal error 1236: 'Client requested master to start replication from impossible position' from master when reading data from binary log [4654]: 110204 10:09:20 [Note] Slave I/O thread exiting, read up to log 'mysqld-bin.000742', position 4274

So at first I thought this was a weird bug or a stunnel issue, but looking at 'mysqld-bin.000742' on the master it turns out that it's only 3980 bytes - position 4274 is indeed impossible. But how did it get this way?

Investigation

Without being able to "just resume and let it fix itself" I will need to CHANGE MASTER myself, but to what position?

  1. Let's see what happened in that binlog file - it's not very large: (See syntax) . Runing this on the MASTER
    SHOW BINLOG EVENTS in 'mysqld-bin.000742' from 0;
  2. I have a bunch of INSERT's in there, so I check the slave to see if that data exists - and I have everything to the very last statement.
  3. So I pull up the next binlog, carefully:
    SHOW BINLOG EVENTS in 'mysqld-bin.000743' from 0 limit 10;
  4. The very first INSERT's data doesn't exist, nor the next, so I will assume that I should start from the beginning of this binlog file. Perhaps there was a power outage on the master and the previous file got cut off earlier than the slave expected - not really sure.

Repairs

  1. Change the slave to read from a different position (see MySQL Change Master Syntax): Running this on the SLAVE
    STOP SLAVE; CHANGE MASTER TO MASTER_LOG_FILE = 'mysqld-bin.000743', MASTER_LOG_POS = 0;
  2. And away it goes!

0 Trackbacks

  1. No Trackbacks

0 Comments

Display comments as (Linear | Threaded)
  1. No comments

Add Comment


You can use [geshi lang=lang_name [,ln={y|n}]][/geshi] tags to embed source code snippets.
Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
Standard emoticons like :-) and ;-) are converted to images.

To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.
CAPTCHA

What is the primary language of this blog? (Anti-SPAM question)


Submitted comments will be subject to moderation before being displayed.