MySQL replication error

Posted on 2007-10-07
Medium Priority
Last Modified: 2008-09-08
I've had replication working for sometime, but I have an issue now that the slave wont start.  When I START SLAVE, it reports no errors, however when I SHOW SLAVE STATUS it says Slave_IO_Running=No, but Slave_SQL_Running=Yes

When I look in the slaves err log I see:

071007 14:45:08 [Note] Slave I/O thread: connected to master '<user>@<master_server>:3306',  replication started in log 'localhost-bin.038' at position 203793038
071007 14:45:09 [ERROR] Error reading packet from server: Client requested master to start replication from impossible position (server_errno=1236)
071007 14:45:09 [ERROR] Got fatal error 1236: 'Client requested master to start replication from impossible position' from master when reading data from binary log
071007 14:45:09 [ERROR] Slave I/O thread exiting, read up to log 'localhost-bin.038', position 203793038

Any suggestions on what this problem is and how I fix it would be most welcome.

Question by:dealclickcouk
  • 2

Expert Comment

ID: 20031321
Taken from this site:


MASTER_LOG_FILE and MASTER_LOG_POS are the coordinates at which the slave
I/O thread should begin reading from the master the next time the thread
starts, but this pointers got corrupted and is impossible for slave to read
at that position from the master binary log. Do the following steps in the
slave node to recover it.

stop slave;
reset slave;
start slave;

RESET SLAVE makes the slave forget its replication position in the master's
binary logs. This statement is meant to be used for a clean start: It
deletes the master.info and relay-log.info files, all the relay logs, and
starts a new relay log.

Author Comment

ID: 20031560
so what happens to any changes that are in the logs, will the slaves loose these?  

Is there no way to recover the changes from the master from this?

Accepted Solution

chingmd earned 1000 total points
ID: 20033860
My guess is that what's happened is that the slave and the master cannot agree on the where the last logged changes are.    Assuming, correct me if I'm wrong, a one way replication thing.  The master to the slave.  The slave doesn't take changes from anywhere but the master.

So yeah, I would guess that it will need to rebuild all the logs to catch up.

Assisted Solution

christatedavies earned 1000 total points
ID: 21874982
Do you have any ignore errors switched on? Because I did, and it turns out, that my MySQL installation ignores errors in such a way that it just stops the service, doesn't tell you about the errors. I just found out, and I could've uninstalled it and deleted the installation files. I am so mad at it.

My advice (if you are ignoring them) to not ignore them, and see if any appear.

Thanks, Chris

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
What we learned in Webroot's webinar on multi-vector protection.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

750 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question