Bug #27101 mysqlhotcopy --record_log_pos retrieves wrong slave parameters
Submitted: 13 Mar 2007 19:01 Modified: 22 Apr 2008 13:55
Reporter: Alex Tomic Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S3 (Non-critical)
Version:5.0.40-BK, 5.0.27 OS:Linux (Linux)
Assigned to: Iggy Galarza CPU Architecture:Any
Tags: Contribution, mysqlhotcopy, replication
Triage: D2 (Serious)

[13 Mar 2007 19:01] Alex Tomic
Description:
From perldoc for mysqlhotcopy, with relevant portion identified with ****:

--record_log_pos log-pos-table
           Just before the database files are copied, update the record in the
           log-pos-table from the values returned from "show master status"
           and "show slave status". The master status values are stored in the
           log_file and log_pos columns, and establish the position in the
           binary logs that any slaves of this host should adopt if ini-
           tialised from this dump. **** The slave status values are stored in
           master_host, master_log_file, and master_log_pos, and these are
           useful if the host performing the dump is a slave and other sibling
           slaves are to be initialised from this dump. ****

In order to restore a consistent backup of the master from a dump of the slave, we would want to know the log position and coordinate that the slave's SQL thread had executed, not where the I/O thread had retrieved from the master. 

See http://forums.mysql.com/read.php?26,143765,143765#msg-143765 for my posting to the MySQL forums about this problem for more details on why I think this is a bug. 

How to repeat:
Run mysqlhotcopy with --record_log_pos option on a slave database for the purposes of restoring a new slave against master . 

Suggested fix:
Not sure what is the best way of submitting a patch, but here is a diff between the 5.0.27 file and my fixed file that I tested successfully on one of my 5.0 servers (basically just change the two parameters that are read from $row_hash ). I've upgraded all my 4.x servers so I'm not able to test backward compatibility, but both new parameters are in the 4.0 docs for 'show slave status' so I assume it should work:

$ diff -u mysqlhotcopy mysqlhotcopy.new
--- mysqlhotcopy        2007-03-13 14:15:51.000000000 -0400
+++ mysqlhotcopy.new    2007-03-13 14:13:26.000000000 -0400
@@ -754,7 +754,7 @@
              = @{$row_hash}{ qw / Master_Host Log_File Pos / };
        } else {
            ($master_host, $log_file, $log_pos )
-             = @{$row_hash}{ qw / Master_Host Master_Log_File Read_Master_Log_Pos / };
+             = @{$row_hash}{ qw / Master_Host Relay_Master_Log_File Exec_Master_Log_Pos / };
        }
        my $hostname = hostname();
[29 Mar 2007 6:39] Valeriy Kravchuk
Thank you for a problem report and contributed patch. Verified with code review on latest 5.0.40-BK sources - mysqlhotcopy code is still the same. Your patch is surely worth developers' consideration.
[5 Mar 2008 19:13] Andrei Elkin
Imo the problem was identified correctly. The fix is correct as well.
Show slave status of mysql-4.0 output displays the necessary fields so that the patch should be applicable and workable with early version of the server.
[7 Mar 2008 20:46] Bugs System
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/43628

ChangeSet@1.2581, 2008-03-07 15:45:40-05:00, iggy@amd64.(none) +1 -0
  Bug #27101 mysqlhotcopy --record_log_pos retrieves wrong slave parameters
  - Make sure mysqlhotcopy doesn't create unrestorable slaves.
[27 Mar 2008 22:04] Bugs System
Pushed into 5.1.24-rc
[27 Mar 2008 22:11] Bugs System
Pushed into 5.0.60
[28 Mar 2008 11:10] Bugs System
Pushed into 6.0.5-alpha
[22 Apr 2008 13:55] Paul Dubois
Noted in 5.0.60, 5.1.24, 6.0.5 changelogs.

For the --record_log_pos option, mysqlhotcopy now determines the
slave status information from the result of SHOW SLAVE STATUS by
using the Relay_Master_Log_File and Exec_Master_Log_Pos values rather
than the Master_Log_File and Read_Master_Log_Pos values. This
provides a more accurate indication of slave execution relative to
the master.