MySQL Bugs: #12133: master.index file keeps mysqld from starting if bin log has been moved

Bug #12133	master.index file keeps mysqld from starting if bin log has been moved
Submitted:	23 Jul 2005 18:44	Modified:	19 Dec 2011 12:18
Reporter:	Jonathan Miller	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.0, 5.1	OS:	Linux (Linux)
Assigned to:	Zhenxing He	CPU Architecture:	Any

Description:
Due to running out of disk space during stress testing I decided to move the mysqld files off to a drive with more space.

After moving the files, and editing the my.cnf file for the new location mysqld would not start.

It kept returning errors:

050723 19:06:03  mysqld started
050723 19:06:03  InnoDB: Started; log sequence number 0 43665
050723 19:06:03 [Note] Starting Cluster Binlog
/home/ndbdev/jmiller/builds/libexec/mysqld: 
File '/home/ndbdev/jmiller/builds/var/master3.000002' not found (Errcode: 2)
050723 19:06:03 [ERROR] 
Failed to open log (file '/home/ndbdev/jmiller/builds/var/master3.000002', errno 2)
050723 19:06:03 [ERROR] Could not open log file
050723 19:06:03 [ERROR] Can't init tc log
050723 19:06:03 [ERROR] Aborting

I found that master.index has stored the old directory location. If you remove the files MySQLD starts and stops just fine.

How to repeat:
Start replication running. After a while stop repilication, mv mysqld files, edit my.cnf for new location and try to start mysqld using instance manager. 
"mysql.server start"
Check master.err for error listed above.

Try to stop "mysql.server stop". Sometime returns failed or okay. Either way there is usually a mysqld process hung and you will need to do kill -9 to remove it.

rm master.index, and restart using instance manager. Mysqld now stops and starts without issue.

Copy of my my.cnf
[manager]
default-mysqld-path = /home/ndbdev/jmiller/builds/libexec/mysqld
socket=/tmp/manager.sock
pid-file=/tmp/manager.pid
monitoring-interval = 50
port = 1999

[mysqld]
server-id=2
log-bin    = /space/var1/master2
log        = /space/var1/master2.log
log-error  = /space/var1/master2.err
socket     = /tmp/mysql.sock
port       = 3306
pid-file   = /space/var1/hostname.pid2
datadir    = /space/var1/
language   = /home/ndbdev/jmiller/builds/share/mysql/english
#user      = root
ndbcluster                      # run NDB engine
ndb-connectstring=ndb08:14000  # location of MGM node
default-storage-engine=NDB

Suggested fix:
Starting Mysqld should update the correct file location in master.index and should not abort the start.

Hi!

I was able to reproduce this under FC4 using 5.0 from bk

I'm using 5.0.24 on Debian Sarge (3.1) x86.
I came across this bug when I wanted to move my binary logs from the default location of my data directory (/var/lib/mysql/) and give them their own folder (/var/lib/mysql/binlog/). 

Steps:
1) Stopped the server
2) Edited the path of the binlog files in my.cnf
3) Copied all the existing binlogs into that folder (including the index). I checked the index to see if it needed changing but it said './mysql-bin.0000X' for the logs which was still right
4) Restarted the server

It gave me the following error (from mysql-bin-error.log):
****************************************************
060905 14:45:24  mysqld started
060905 14:45:24  InnoDB: Started; log sequence number 0 43665
/usr/local/mysql/bin/mysqld: File './mysql-bin.000017' not found (Errcode: 2)
060905 14:45:24 [ERROR] Failed to open log (file './mysql-bin.000017', errno 2)
060905 14:45:24 [ERROR] Could not open log file
060905 14:45:24 [ERROR] Can't init tc log
060905 14:45:24 [ERROR] Aborting

060905 14:45:24  InnoDB: Starting shutdown...
060905 14:45:26  InnoDB: Shutdown completed; log sequence number 0 43665
060905 14:45:26 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

060905 14:45:26  mysqld ended
*******************************************

I spent probably 1/4 a day searching for the answer to this problem when I realised that if I deleted the existing logs the new binlog index gave the full path of the file rather than './mysql-bin.000017'. I restored the old binlogs from backup, put them in a new folder and gave the full path name to all the entries in the index and everything worked fine.

I would suggest if this bug isn't going to be fixed anytime soon perhaps a note could be added in the binary log section of the manual about this problem as that would've saved me hours of searching when I came across the issue (the binary log manual was the first page I looked).

Thanks,
Nathan.

workaround: remove/edit entries from index file when moving the binlog files

Bug#42576 is the same problem for relay log, will fix both case in this bug.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/128384

3481 He Zhenxing	2011-01-11
      BUG#12133 master.index file keeps mysqld from starting if bin log has been moved
      
      Absolute path or relative path to the data directory was recorded
      in the binlog or relay log index files, which caused the server
      unable to locate the log files and failed to start when user moved
      the binlog or relay log files to another directory and restart the
      server with binlog or relay log option set to the new path.
      
      Fixed the problem by only record the basename in the binlog or relay
      log index files, and use the directory of the binlog or relay log
      option to calculate the path to access the log files.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/128385

3481 He Zhenxing	2011-01-11
      BUG#12133 master.index file keeps mysqld from starting if bin log has been moved
      
      Absolute path or relative path to the data directory was recorded
      in the binlog or relay log index files, which caused the server
      unable to locate the log files and failed to start when user moved
      the binlog or relay log files to another directory and restart the
      server with binlog or relay log option set to the new path.
      
      Fixed the problem by only record the basename in the binlog or relay
      log index files, and use the directory of the binlog or relay log
      option to calculate the path to access the log files.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/128473

3481 He Zhenxing	2011-01-12
      BUG#12133 master.index file keeps mysqld from starting if bin log has been moved
      
      Absolute path or relative path to the data directory was recorded
      in the binlog or relay log index files, which caused the server
      unable to locate the log files and failed to start when user moved
      the binlog or relay log files to another directory and restart the
      server with binlog or relay log option set to the new path.
      
      Fixed the problem by only record the basename in the binlog or relay
      log index files, and use the directory of the binlog or relay log
      option to calculate the path to access the log files.
      
      When reading an index file that was generated by a server before
      the fix, the directory part will be stripped before calculating
      the path.
     @ mysql-test/include/setup_fake_relay_log.inc
        After fix of BUG#12133, there is no need to prepend ./ or .\ for
        filenames in the binlog or relay log index file.
     @ mysql-test/suite/rpl/t/rpl_manual_change_index_file.test
        After fix of BUG#12133, there is no need to prepend ./ or .\ for
        filenames in the binlog or relay log index file.

See also: BUG#55460.

See also: BUG#57771.

BUG#55460, BUG#57771 was marked as duplicate of this bug.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/128897

3481 He Zhenxing	2011-01-17
      BUG#12133 master.index file keeps mysqld from starting if bin log has been moved
      
      Absolute path or relative path to the data directory was recorded
      in the binlog or relay log index files, which caused the server
      unable to locate the log files and failed to start when user moved
      the binlog or relay log files to another directory and restart the
      server with binlog or relay log option set to the new path.
      
      Fixed the problem by only record the basename in the binlog or relay
      log index files, and use the directory of the binlog or relay log
      option to calculate the path to access the log files.
      
      When reading an index file that was generated by a server before
      the fix, the directory part will be stripped before calculating
      the path.
     @ mysql-test/include/setup_fake_relay_log.inc
        After fix of BUG#12133, there is no need to prepend ./ or .\ for
        filenames in the binlog or relay log index file.
     @ mysql-test/suite/rpl/t/rpl_manual_change_index_file.test
        After fix of BUG#12133, there is no need to prepend ./ or .\ for
        filenames in the binlog or relay log index file.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/129534

3481 He Zhenxing	2011-01-25
      BUG#12133 master.index file keeps mysqld from starting if bin log has been moved
      
      Absolute path or relative path to the data directory was recorded
      in the binlog or relay log index files, which caused the server
      unable to locate the log files and failed to start when user moved
      the binlog or relay log files to another directory and restart the
      server with binlog or relay log option set to the new path.
      
      Fixed the problem by only record the basename in the binlog or relay
      log index files, and use the directory of the binlog or relay log
      option to calculate the path to access the log files.
      
      When reading an index file that was generated by a server before
      the fix, the directory part will be stripped before calculating
      the path.
     @ mysql-test/include/setup_fake_relay_log.inc
        After fix of BUG#12133, there is no need to prepend ./ or .\ for
        filenames in the binlog or relay log index file.
     @ mysql-test/suite/rpl/t/rpl_manual_change_index_file.test
        After fix of BUG#12133, there is no need to prepend ./ or .\ for
        filenames in the binlog or relay log index file.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/129620

3481 He Zhenxing	2011-01-26
      BUG#12133 master.index file keeps mysqld from starting if bin log has been moved
      
      Absolute path or relative path to the data directory was recorded
      in the binlog or relay log index files, which caused the server
      unable to locate the log files and failed to start when user moved
      the binlog or relay log files to another directory and restart the
      server with binlog or relay log option set to the new path.
      
      Fixed the problem by only record the basename in the binlog or relay
      log index files, and use the directory of the binlog or relay log
      option to calculate the path to access the log files.
      
      When reading an index file that was generated by a server before
      the fix, the directory part will be stripped before calculating
      the path.
     @ mysql-test/include/begin_include_file.inc
        Add $keep_silent to suppress the output of the include line.
     @ mysql-test/include/setup_fake_relay_log.inc
        After fix of BUG#12133, there is no need to prepend ./ or .\ for
        filenames in the binlog or relay log index file.
     @ mysql-test/suite/rpl/t/rpl_manual_change_index_file.test
        After fix of BUG#12133, there is no need to prepend ./ or .\ for
        filenames in the binlog or relay log index file.

BUG#42576 is a duplicate is this bug.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix and behaviour change in the 5.5.20 and 5.6.5 changelogs as follows:

        Moving the binary log file, relay log file, or both files to a
        new location, then restarting the server with a new value for
        --log-bin, --relay-log, or both, caused the server to abort on
        start. This was because the entries in the index file overrode
        the new location. In addition, paths were calculated relative to
        datadir (rather than to the --log-bin or --relay-log values).

        The fix for this problem means that, when the server reads an
        entry from the index file, it now checks whether the entry
        contains a relative path. If it does, the relative part of the
        path is replaced with the absolute path set using the --log-bin
        or --relay-log option. An absolute path remains unchanged; in
        such a case, the index must be edited manually to enable the new
        path or paths to be used.

Also updated the descriptions of the referenced options in the 5.5/5.6
Manuals.

Closed.