| 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: | |
| Category: | MySQL Server: Command-line Clients | Severity: | S2 (Serious) |
| Version: | 6.0.10-bzr,5.4, M3 (Celosia) | OS: | Linux |
| Assigned to: | Alexey Botchkov | CPU Architecture: | Any |
| Tags: | regression | ||
[30 Jan 2009 9:41]
Valeriy 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.
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.