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".