« Compassionate Breakfast | Home | Not remote enough »
MySQL fatal error 1236
By doug | September 3, 2006
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.
Topics: Technology |
November 30th, 2006 at 11:01 pm
Thanks! Was struggling with the same problem, you saved me lots of time.
January 20th, 2007 at 6:43 am
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!
January 26th, 2007 at 4:53 am
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
January 26th, 2007 at 9:07 am
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.
September 5th, 2007 at 6:11 am
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!
September 5th, 2007 at 6:12 am
But still dont know whats wrong. Using same queries on Server 2000 and XP it worked ok.
September 6th, 2007 at 2:32 pm
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.
September 7th, 2007 at 10:28 am
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!
February 14th, 2008 at 2:44 am
Thank you and all of who leave comment.
I have same problems as Burkhart (January 26, 2007)
You saved me lots of time