Bug #42465 mysqlhotcopy fails with "Table '<y>' was locked with a READ lock" error message.
Submitted: 29 Jan 2009 21:01 Modified: 23 Mar 2010 15:35
Reporter: Hema Sridharan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S2 (Serious)
Version:6.0.10-bzr,5.4, M3 (Celosia) OS:Linux
Assigned to: Alexey Botchkov
Tags: regression
Triage: Triaged: D2 (Serious)

[29 Jan 2009 21:01] Hema Sridharan
Description:
Create database, tables and load with data.
Use mysqlhotcopy to backup the database.
It always fails giving following error message,

Locked 1 tables in 0 seconds.
DBD::mysql::db do failed: Table 'ss1' was locked with a READ lock and can't be updated at ./mysqlhotcopy line 458.

How to repeat:
CREATE DATABASE mysql_db1;
CREATE TABLE mysql_db1.t1 (c1 VARCHAR(5), c2 int);
INSERT INTO mysql_db1.t1 VALUES ('A',1);
INSERT INTO mysql_db1.t1 SELECT * FROM mysql_db1.t1;
INSERT INTO mysql_db1.t1 SELECT * FROM mysql_db1.t1;
INSERT INTO mysql_db1.t1 SELECT * FROM mysql_db1.t1;

/export/home/tmp/wl-4230/mysql-6.0-backup/scripts: ./mysqlhotcopy  -u root --socket=/export/home/tmp/wl-4230p/mysql-test/var/tmp/master.sock mysql_db1  /export/home/tmp/backup_nw
Locked 1 tables in 0 seconds.
DBD::mysql::db do failed: Table 't1' was locked with a READ lock and can't be updated at ./mysqlhotcopy line 458.

/export/home/tmp/backup_nw is the path to directory where I want to backup database.

Use of unlock tables and then mysqlhotcopy still gives same error message.
[30 Jan 2009 9:41] Valerii Kravchuk
Verified with recent 6.0.10 from bzr. There is no such problem in 5.0.76, for example.
[28 May 2009 14:58] Konstantin Osipov
This is an intentional change as part of WL#3726.
Should have been documented as it is highlighted as an incompatible change of this worklog.
An attempt to flush a table locked for READ could easily lead to deadlocks
if the other connection was doing a concurrent ALTER.
6.0, for example, does not have Bug#43685, which won't be easy to fix 
if we keep allowing FLUSH TABLE under LOCK TABLE READ.

I don't know what to do about this bug yet, before disallowing FLUSH TABLES
under LOCK TABLES READ we performed a fairly extensive research, including 
polling support and training. None remembered this use case of
mysqlhotcopy.
[28 May 2009 15:05] Konstantin Osipov
It would be hard to add the functionality back without breaking something else.

We can, however, implement FLUSH TABLE(S) <table_list> WITH READ LOCK,
that is, make FLUSH TABLES accept a list of tables to take LOCK TABLE lock on
after the flush.
Such a function would first take an exclusive metadata lock on the table,
perform the FLUSH, then take a TL_READ data lock on the table, and downgrade
the exclusive metadata lock to shared.
[11 Jun 2009 11:04] 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/76105

3351 Alexey Botchkov	2009-06-11
      Bug#42465      mysqlhotcopy fails with "Table '<y>' was locked with a READ lock" error message.
         the problem is that this stopped working with 6.0:
             LOCK TABLE t1 READ;
             FLUSH TABLES;
         So mysqlhotcopy was fixed so if the server version is 5.4 and above it does
            FLUSH TABLES READ LOCK
         instead.
         Kostja was planning to implement the
            FLUSH TABLES t1 READ LOCK
         that will be better option as it only will block the copyed table.
      
      per-file comments:
        scripts/mysqlhotcopy.sh
      Bug#42465      mysqlhotcopy fails with "Table '<y>' was locked with a READ lock" error message.
          use FLUSH TABLES READ LOCK instead of LOCK TABLE t1 READ for the 5.4 and above server
[11 Jun 2009 13:00] Konstantin Osipov
WL#5000
[27 Jul 2009 12:13] Konstantin Osipov
After discussion with support and internally, it was agreed to implement WL#5000 in 5.4 in order to fix this bug. Setting the bug back to "Verified": the new fix should use FLUSH TABLE <table_name> WITH READ LOCK, rather than FLUSH TABLES WITH READ LOCK statement.
[3 Oct 2009 15:49] Konstantin Osipov
Another version of the patch reviewed, put back to in progress.
[1 Mar 2010 20:22] 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/101946

3119 Alexey Botchkov	2010-02-28
      Bug#42465      mysqlhotcopy fails with "Table '<y>' was locked with a READ lock" error message.
          Since 5.5 FLUSH TABLES requires unlocked tables so the old LOCK TABLES/FLUSH TABLES
          model fails. Use atomic FLUSH TABLES WITH READ LOCK instead.
      
      per-file comments:
        scripts/mysqlhotcopy.sh
      Bug#42465      mysqlhotcopy fails with "Table '<y>' was locked with a READ lock" error message.
           Use FLUSH TABLES WITH READ LOCK instead of LOCK TABLES / FLUSH TABLES pair.
           Keep the old scheme for older servers.
[2 Mar 2010 8:18] Jim Winstead
why not automatically detect that you're connecting to an old server?

$dbh->get_info( 18 ); # 18 == SQL_DBMS_VER
[6 Mar 2010 10:29] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100306102742-yw9zzgw9ac5r65m5) (version source revid:bar@mysql.com-20100305074327-h09o5lw290s04lcf) (merge vers: 6.0.14-alpha) (pib:16)
[6 Mar 2010 10:31] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100306102638-qna09hbjb5gm940h) (version source revid:alik@sun.com-20100304153932-9hajxhhyanqbckmu) (pib:16)
[6 Mar 2010 11:05] Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:alik@sun.com-20100304153932-9hajxhhyanqbckmu) (merge vers: 5.5.99-m3) (pib:16)
[23 Mar 2010 15:35] Paul Dubois
Noted in 5.5.3, 6.0.14 changelogs.

Due to work done for Bug#989, FLUSH TABLES is disallowed when there
is an active LOCK TABLES ... READ. This caused a problem with
mysqlhotcopy, which used that sequence of statements. mysqlhotcopy
now uses FLUSH TABLES tbl_list WITH READ LOCK to flush and lock
tables. If mysqlhotcopy is used with a server older than MySQL 5.5.3
that does not support this statement, it has a new option
--old_server that causes it to use the previous statement sequence.

Also added --old_server option to mysqlhotcopy section.