MySQL error message when attempting to start a replication slave:
Got fatal error 1236: ‘Could not find first log file name in binary log index file’ from master when reading data from binary log
I banged my head against a wall for a short period of time trying resolve this one. The resolution is easy, but I didn’t find it in my searching. Hopefully this will allow someone to move forward more quickly than I did, providing I am not the only one that didn’t immediately realize what was going on. The full error reads something like:
060903 11:01:58 [Note] Slave SQL thread initialized, starting replication in log '/var/mysql/logs/mysql-log.000125' at position 97277001, relay log './slave-relay-bin.000001' position: 4
060903 11:01:58 [Note] Slave I/O thread: connected to master 'slave@master:3306', replication started in log '/var/mysql/logs/mysql-log.000125' at position 97277001
060903 11:01:58 [ERROR] Error reading packet from server: Could not find first log file name in binary log index file ( server_errno=1236)
060903 11:01:58 [ERROR] Got fatal error 1236: 'Could not find first log file name in binary log index file' from master when reading data from binary log
060903 11:01:58 [Note] Slave I/O thread exiting, read up to log '/var/mysql/logs/mysql-log.000125', position 97277001
The slave was being started from an InnoDB Hot Backup created on the master. After restoring the backup and starting up MySQL, the following binlog position was reported:
InnoDB: Last MySQL binlog file position 0 97277001, file name /var/mysql/logs/mysql-log.000125
which suggests the change master statement:
CHANGE MASTER TO
MASTER_HOST='master',
MASTER_USER='slave',
MASTER_LOG_FILE='/var/mysql/logs/mysql-log.000125',
MASTER_LOG_POS=97277001;
However, the full path to the log file is what causes the ‘Could not find first log file name in binary log index file’ error. MySQL looks for the log file in the defined log directory, /var/mysql/logs in this case. Simply adjusting the change master statement:
CHANGE MASTER TO
MASTER_HOST='master',
MASTER_USER='slave',
MASTER_LOG_FILE='mysql-log.000125',
MASTER_LOG_POS=97277001;
results in:
060903 11:15:16 [Note] Slave SQL thread initialized, starting replication in log 'mysql-log.000125' at position 97277001, relay log './slave-relay-bin.000001' position: 4
060903 11:15:16 [Note] Slave I/O thread: connected to master 'slave@master:3306', replication started in log 'mysql-log.000125' at position 97277001
And we’re replicating. Maybe if I had more occasion to bring up new MySQL replication slaves, this would have been obvious.
22 Comments
Thanks! Was struggling with the same problem, you saved me lots of time.
Hi Doug,
thanks a million! Setting up mysql replication I accidentally mistyped the logfile name (change master to master_log_file=’mysql-bin-xxx’ instead of ‘… mysql-bin.xxx’) which leads to the same error. Stupid mistake and I overlooked it many times. After having read this website I immediately knew what was wrong. Thanks again!
Hey Doug,
thanx, that was the hint into the right direction. In my setup I found two very similar but non the less other causes (and solutions of course):
Due to a problem on the master (still to be investigated) one bin-log wasn’t closed correctly and depending on the replication progress of different slaves the problem was different:
slave #1
========
… was pointing to log-bin.000032 pos: 422281850. I read the log using the command
shell: mysqlbinlog log-bin.000032 | less
searched for the given position using the command:
/422281850
and found the end of the file. So I pointed the slave to the beginning of the next log-bin
stop slave;
CHANGE MASTER TO MASTER_LOG_FILE=’log-bin.000033′, MASTER_LOG_POS=4;
start slave;
replication worked fine again on this machine.
slave #2
========
… was pointing to log-bin.000032 pos: 399012105.
I think that this slave didn’t finish replication when the problem came up, so the first solution would not fit this slave (unless we could confirm only reading commands in those last few K of queries)
Thinking about the text message: “Could not find first log file name in binary log index file? from master when reading data from binary log”, I opened master.info on the master and found a list of all existing log-bins, but not the log-bin.000032 I found i. Assuming that mysql wasn’t able to update the master.info I added the missing log-bin.000032 and restarted the slave.
replication started again and is now running ok for a few hours.
I did a small script to compare the row numbers of all tables on all servers and found it working so far.
Conclusion
==========
Those two ways of repair might not be the highly official ones, but brought me back on track.
Hope this helps anybody like this thread helped me a lot.
thanx Doug
cheers
Burkhart
Thanks, Burkhart. Indeed not an official solution, I’m sure, but if the alternative is rebuilding the slave from a backup or fresh master dump (which is not recommended if the master db is using InnoDB as the engine and you cannot stop updates), an unofficial solution that works can save both time and money.
We found out that this error came from LOAD DATA INFILE command in MySQL 5.0.41 and only on Windows2003 Server. That is where we encountered it first time!
But still dont know whats wrong. Using same queries on Server 2000 and XP it worked ok.
Similar issue.
I had a space inside the quotes after the file name like this:
MASTER_LOG_FILE=’mysql-bin.000031 ‘
Your post got me thinking about the good ole UE. Thanks.
Hello, I had the same problem. But I did two things to avoid this kind of problem…
I changed the path for the bin log file xxxx.000001 inside the data folder and one more
important thing you should check the read buffer size in mysql variables. make it 256K.
If its in M make it really less about 192-256K. read-buffer-size. In my.ini… I think you will get rid of 1236 errors.. We encountered this errors when using LOAD DATA INFILE…
On MySQL 5.0.27, 5.0.41 and 5.0.45.
C ya and Good luck!
Thank you and all of who leave comment.
I have same problems as Burkhart (January 26, 2007)
You saved me lots of time
I had this problem after replication got messed up, so I tried to start again with it (the system wasn’t live yet). It wouldn’t start whatever I did, until I did a ‘LOAD DATA FROM MASTER’ on all the slaves. Hope this helps somone
@Halfasleep: Thanks for the tip. What version of MySQL are you using? Maybe that makes a difference.
Hi,i too faced similar problem.In my case the replication was broken because of ‘max_allowed_packet’.A big query was logged in the binary log and the same was not able to communicate with slave.By default the packet size will be 1MB.I have increased the ‘max_allowed_packet’ to 16M on both master and slave.
Steps: stop slave,change master,start slave.
Note:When you’re using CHANGE MASTER TO to set start position for the slave you’re specifying position for SQL thread and so you should use Relay_Master_Log_File:Exec_Master_Log_Pos. Otherwise you’re going to ruin your replication.
@Dhananjay: I have seen this happen as well. Thanks for the info.
A HUGE thank you to Burkhart and Doug!!!
Came in today and there was: Could not find first log file name in binary log index file ( server_errno=1236)
It popped up after a master reboot over the weekend. Checked the log file and it was indeed at the end. Set replication to the next file at first position (which was also 4 )and everything came right back up.
14 hours or so saved by not re-replicating from scratch.
This page helped (and especially the comments).
My error was:
ERROR] Got fatal error 1236: ‘Client requested master to start replication from impossible position’ from master when reading data from binary log
My fix on the slave was to be:
LOAD DATA FROM MASTER;
However I first had to grant extra privileges to my account from the master:
grant reload, replication client on *.* to ‘repl-user’@'server-name’
Hi. I had this problem too and there’s another reason this error occurs. Case sensitivity of the table names. I found that it’s best to have these global settings the same on the master and slave. Try this on both to see if they are different.
show variables where
Variable_name = ‘lower_case_table_names’ OR
Variable_name=’lower_case_file_system’;
These should be the same on both master and slave.
Update to the previous post. Here’s a link to the MySQL documentation that explains this – see the “Note” at the end of this page.
How Servers Evaluate Replication Filtering Rules
Awesome post, and comments. Definitely saved me a ton of time (when I had *none* to spare). Thanks to all who contributed.
This is great and I also lost a few moments off of my life with this one. It would, however, have saved me even more time if it was posted on the MySQL Community Server Community pages.
Thanks a lot !! saved lot of time for me.
Thanks for the info! Saved me lots of time!
Although my problem was a bad filename and not a path issue, your post pointed me in the right direction. Thanks.