Bug #67741 mysqlhotcopy failes with --record_log_pos
Submitted: 28 Nov 2012 14:24 Modified: 28 Nov 2012 17:13
Reporter: Erik Dubbelboer Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S2 (Serious)
Version:5.5.24, 5.5.30, 5.7.1 OS:Any
Assigned to: CPU Architecture:Any
Tags: mysqlhotcopy

[28 Nov 2012 14:24] Erik Dubbelboer
Description:
mysqlhotcopy using --record_log_pos will fail for mysql versions that have the new "FLUSH TABLES WITH READ LOCK". The record_log_pos function is unable to write to the specified table because the lock is read-only.

mysqlhotcopy using --record_log_pos is used by many people to set up replication slaves for masters that are already running and can't be interrupted.

How to repeat:
CREATE DATABASE IF NOT EXISTS test;

USE test;

CREATE TABLE IF NOT EXISTS log_pos (
  `host` varchar(60) NOT NULL,
  `time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `log_file` varchar(32) DEFAULT NULL,
  `log_pos` int(11) DEFAULT NULL,
  `master_host` varchar(60) DEFAULT NULL,
  `master_log_file` varchar(32) DEFAULT NULL,
  `master_log_pos` int(11) DEFAULT NULL,
  PRIMARY KEY (`host`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Then on the command-line run: mysqlhotcopy --record_log_pos=test.log_pos mysql .

Suggested fix:
The only fix for now is to use "--old_server" and change the READ lock into a WRITE lock.

Just using "--old_server" will fail because it tries to FLUSH TABLES after the LOCK TABLES which will fail after 5.5:

As of MySQL 5.5.3, FLUSH TABLES is not permitted when there is an active LOCK TABLES ... READ. To flush and lock tables, use FLUSH TABLES tbl_name ... WITH READ LOCK instead.

Modifying the READ lock to a WRITE lock (and removing the part that does this for the table you insert into, line 454 for my version 1.23 (Ubuntu)) made it work as long as I didn't include the mysql table in the dump (you aren't always allowed to lock the mysql table with a write lock).

A better way to fix would be for mysql to include a "FLUSH TABLES WITH WRITE LOCK".
[28 Nov 2012 17:13] Sveta Smirnova
Thank you for the report.

Verified as described.
[28 Nov 2012 17:14] Sveta Smirnova
test case for MTR

Attachment: bug67741.test (application/octet-stream, text), 630 bytes.