| Bug #20667 | Truncate table fails for a write locked table | ||
|---|---|---|---|
| Submitted: | 23 Jun 2006 15:58 | Modified: | 28 Apr 2010 13:58 |
| Reporter: | Paul Rivers (Candidate Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DDL | Severity: | S3 (Non-critical) |
| Version: | 5.0.23-BK, 5.0.22 | OS: | Linux (RHEL 4) |
| Assigned to: | Ingo Strüwing | CPU Architecture: | Any |
| Tags: | qc | ||
[23 Jun 2006 15:58]
Paul Rivers
[25 Jun 2006 16:44]
Valeriy Kravchuk
Thank you for a problem report. It is clearly documented (in "13.2.9. TRUNCATE Syntax" section of the manual): "Truncate operations drop and re-create the table, which is much faster than deleting rows one by one." Surely you can not drop LOCKed table, hence the error message. So, I think, it is not a bug, but expected and even (somehow) documented behaviour.
[29 Jun 2006 15:36]
Paul Rivers
I respectfully disagree with this assessment. Also from the manual, from 13.4.5. LOCK TABLES and UNLOCK TABLES Syntax : "A table lock protects only against inappropriate reads or writes by other clients. The client holding the lock, even a read lock, can perform table-level operations such as DROP TABLE." Even were this sentence not in the documentation, I would argue it's a bad design, if not a bug, that tables cannot be truncated by the same session holding a lock. It would be, in my humble opinion, behavior that ought to be at least reconsidered as it stands.
[29 Jun 2006 16:14]
Valeriy Kravchuk
Thank you for your persistence. I agree with you. It is a bug, because we have inconsistent behaviour: mysql> create table t ( f1 int ) engine=myisam; Query OK, 0 rows affected (0.01 sec) mysql> lock tables t write; Query OK, 0 rows affected (0.00 sec) mysql> drop table t; Query OK, 0 rows affected (0.00 sec) mysql> create table t ( f1 int ) engine=myisam; Query OK, 0 rows affected (0.01 sec) mysql> lock tables t write; Query OK, 0 rows affected (0.00 sec) mysql> truncate table t; ERROR 1192 (HY000): Can't execute the given command because you have active lock ed tables or an active transaction mysql> select version(); +-----------+ | version() | +-----------+ | 5.0.23 | +-----------+ 1 row in set (0.00 sec) I do not understand, why TRUNCATE is impossible while DROP is possible. It is a bug.
[8 Jan 2008 23:01]
Omer Barnir
workaround: is to drop the table and recreate it instead of truncating it
[1 Jul 2008 0:15]
Arjen Lentz
Omer: dropping the table is not a solution. Won't that drop the lock? If you put a lock on a table, it's there for a reason. The ability to truncate a table and load something new in, is a perfectly sensible reason. Truncating can be faster than delete, and holding the lock for a shorter amount of time is good. Hence... The point is really, TRUNCATE behaves like a DDL statement, even though users are well within their right to treat it like a DML statement. With some code tweaking, I'm sure the users can be made happy. How it works internally is irrelevant from the user perspective. It's the result and speed that count.
[10 Jul 2008 14:20]
Ron Albers
I see this bug was submitted in 2006, today (2008) I encountered this bug using mysql 5.0.45 Since the drop table is no workaround (would also drop the lock) I was wondering is there is a better workaround or if anybody knows when this bug will be fixed.
[11 Jan 2009 23:52]
Brian Blood
January 2009, using MySQL 5.0.51 and this bug still hasn't been fixed. Ran into it today with the following set of statements trying to prune an ErrorLog table: LOCK TABLE ErrorLog WRITE, ErrorLog2 WRITE; INSERT INTO ErrorLog2 SELECT * FROM `ErrorLog` WHERE RecordID >= 5509715; TRUNCATE ErrorLog; INSERT INTO ErrorLog SELECT * FROM `ErrorLog`; UNLOCK TABLES; DROP TABLE ErrorLog2;
[5 Feb 2009 15:02]
Konstantin Osipov
Table locking changed substantially between 5.0 and 5.1, and was rewritten completely in 6.0. Asking this to be fixed in 5.0 and 5.1 will require 3 different fixes for the bug. This will triple the risk to introduce a regression. Inability to truncate a WRITE-LOCKED table is a documented limitation that has been present since 3.23: http://dev.mysql.com/doc/refman/5.0/en/truncate.html --quote Truncate operations are not transaction-safe; an error occurs when attempting one in the course of an active transaction or active table lock. --end quote The workaround is to use an unqualified DELETE: DELETE FROM t1; It doesn't reset auto-increment, so DELETE can be followed by ALTER: ALTER TABLE t1 auto_increment=1; Having said that I fully agree that we're dealing with a gotcha, that should be removed. But the place to remove it is 6.0, where ground have been prepared for the task. Example: mysql> create table t1 (a int primary key auto_increment); Query OK, 0 rows affected (0.01 sec) mysql> lock table t1 write; Query OK, 0 rows affected (0.00 sec) mysql> insert into t1 (a) values (null), (null), (null); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from t1; +---+ | a | +---+ | 1 | | 2 | | 3 | +---+ 3 rows in set (0.00 sec) mysql> delete from t1; Query OK, 3 rows affected (0.00 sec) mysql> insert into t1 (a) values (null), (null), (null); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from t1; +---+ | a | +---+ | 4 | | 5 | | 6 | +---+ 3 rows in set (0.00 sec) mysql> delete from t1; Query OK, 3 rows affected (0.00 sec) mysql> alter table t1 auto_increment=1; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> insert into t1 (a) values (null), (null), (null); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from t1; +---+ | a | +---+ | 1 | | 2 | | 3 | +---+ 3 rows in set (0.00 sec)
[15 Jun 2009 14:35]
Konstantin Osipov
This functionality is required for proper functioning of RESTORE in the online backup, see WL#4844.
[18 Jun 2009 14:31]
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/76579 2825 Ingo Struewing 2009-06-18 Bug#20667 - Truncate table fails for a write locked table TRUNCATE TABLE was not allowed under LOCK TABLES. The patch removes this restriction. mysql_truncate() does now handle that case. @ mysql-test/r/merge.result Bug#20667 - Truncate table fails for a write locked table Updated test result. @ mysql-test/r/truncate.result Bug#20667 - Truncate table fails for a write locked table Updated test result. @ mysql-test/t/merge.test Bug#20667 - Truncate table fails for a write locked table Updated test case due to now working TRUNCATE under LOCK TABLES. Added some SELECTs to show that child tables are truncated. @ mysql-test/t/truncate.test Bug#20667 - Truncate table fails for a write locked table Added test. @ sql/sql_class.cc Bug#20667 - Truncate table fails for a write locked table Added implementation for Locked_tables_list::get_table(). @ sql/sql_class.h Bug#20667 - Truncate table fails for a write locked table Added declaration for Locked_tables_list::get_table(). @ sql/sql_delete.cc Bug#20667 - Truncate table fails for a write locked table Added two branches for thd->locked_tables_mode. @ sql/sql_parse.cc Bug#20667 - Truncate table fails for a write locked table Deleted rejection of TRUNCATE in case of LOCK TABLES.
[18 Jun 2009 16:29]
Omry Yadan
ha, just bumped into this the same day it was patched. what are the odds? :)
[19 Jun 2009 14:48]
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/76717 2825 Ingo Struewing 2009-06-19 Bug#20667 - Truncate table fails for a write locked table TRUNCATE TABLE was not allowed under LOCK TABLES. The patch removes this restriction. mysql_truncate() does now handle that case. @ mysql-test/r/merge.result Bug#20667 - Truncate table fails for a write locked table Updated test result. @ mysql-test/r/truncate.result Bug#20667 - Truncate table fails for a write locked table Updated test result. @ mysql-test/t/merge.test Bug#20667 - Truncate table fails for a write locked table Updated test case due to now working TRUNCATE under LOCK TABLES. Added some SELECTs to show that child tables are truncated. @ mysql-test/t/truncate.test Bug#20667 - Truncate table fails for a write locked table Added test. @ sql/sql_class.cc Bug#20667 - Truncate table fails for a write locked table Added implementation for Locked_tables_list::get_table(). @ sql/sql_class.h Bug#20667 - Truncate table fails for a write locked table Added declaration for Locked_tables_list::get_table(). @ sql/sql_delete.cc Bug#20667 - Truncate table fails for a write locked table Added two branches for thd->locked_tables_mode. @ sql/sql_parse.cc Bug#20667 - Truncate table fails for a write locked table Deleted rejection of TRUNCATE in case of LOCK TABLES.
[20 Jul 2009 12:49]
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/79001 2847 Ingo Struewing 2009-07-20 Bug#20667 - Truncate table fails for a write locked table TRUNCATE TABLE was not allowed under LOCK TABLES. The patch removes this restriction. mysql_truncate() does now handle that case. @ mysql-test/r/merge.result Bug#20667 - Truncate table fails for a write locked table Updated test result. @ mysql-test/r/truncate.result Bug#20667 - Truncate table fails for a write locked table Updated test result. @ mysql-test/t/merge.test Bug#20667 - Truncate table fails for a write locked table Updated test case due to now working TRUNCATE under LOCK TABLES. Added some SELECTs to show that child tables are truncated. @ mysql-test/t/truncate.test Bug#20667 - Truncate table fails for a write locked table Added test. @ sql/sql_delete.cc Bug#20667 - Truncate table fails for a write locked table Added branches for thd->locked_tables_mode. @ sql/sql_parse.cc Bug#20667 - Truncate table fails for a write locked table Deleted rejection of TRUNCATE in case of LOCK TABLES.
[21 Jul 2009 16:54]
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/79116 2847 Ingo Struewing 2009-07-21 Bug#20667 - Truncate table fails for a write locked table TRUNCATE TABLE was not allowed under LOCK TABLES. The patch removes this restriction. mysql_truncate() does now handle that case. @ mysql-test/r/merge.result Bug#20667 - Truncate table fails for a write locked table Updated test result. @ mysql-test/r/truncate.result Bug#20667 - Truncate table fails for a write locked table Updated test result. @ mysql-test/r/truncate_coverage.result Bug#20667 - Truncate table fails for a write locked table New test result. @ mysql-test/t/merge.test Bug#20667 - Truncate table fails for a write locked table Updated test case due to now working TRUNCATE under LOCK TABLES. Added some SELECTs to show that child tables are truncated. @ mysql-test/t/truncate.test Bug#20667 - Truncate table fails for a write locked table Added test cases for TRUNCATE under LOCK TABLE. @ mysql-test/t/truncate_coverage.test Bug#20667 - Truncate table fails for a write locked table New test file. Coverage tests for TRUNCATE. @ sql/sql_delete.cc Bug#20667 - Truncate table fails for a write locked table Added branches for thd->locked_tables_mode. @ sql/sql_parse.cc Bug#20667 - Truncate table fails for a write locked table Deleted rejection of TRUNCATE in case of LOCK TABLES.
[21 Jul 2009 17:37]
Ingo Strüwing
Queued to mysql-6.0-backup.
[6 Aug 2009 8:27]
Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090806082225-qssc912qdv1mm6xv) (version source revid:ingo.struewing@sun.com-20090727131736-m4rgxw6hziux0d4g) (merge vers: 5.4.4-alpha) (pib:11)
[6 Aug 2009 20:00]
Paul DuBois
Noted in 5.4.4 changelog. TRUNCATE TABLE now is allowed for a table for which a WRITE lock has been acquired with LOCK TABLES.
[9 Dec 2009 18:42]
Ingo Strüwing
Backporting to mysql-next-mr-bugfixing.
[11 Dec 2009 12:14]
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/93678 3032 Konstantin Osipov 2009-12-11 Backport: ------------------------------------------------------------ 2599.161.3 Ingo Struewing 2009-07-21 Bug#20667 - Truncate table fails for a write locked table TRUNCATE TABLE was not allowed under LOCK TABLES. The patch removes this restriction. mysql_truncate() does now handle that case. @ mysql-test/r/merge.result Bug#20667 - Truncate table fails for a write locked table Updated test result. @ mysql-test/r/truncate.result Bug#20667 - Truncate table fails for a write locked table Updated test result. @ mysql-test/r/truncate_coverage.result Bug#20667 - Truncate table fails for a write locked table New test result. @ mysql-test/t/merge.test Bug#20667 - Truncate table fails for a write locked table Updated test case due to now working TRUNCATE under LOCK TABLES. Added some SELECTs to show that child tables are truncated. @ mysql-test/t/truncate.test Bug#20667 - Truncate table fails for a write locked table Added test cases for TRUNCATE under LOCK TABLE. @ mysql-test/t/truncate_coverage.test Bug#20667 - Truncate table fails for a write locked table New test file. Coverage tests for TRUNCATE. @ sql/sql_delete.cc Bug#20667 - Truncate table fails for a write locked table Added branches for thd->locked_tables_mode. @ sql/sql_parse.cc Bug#20667 - Truncate table fails for a write locked table Deleted rejection of TRUNCATE in case of LOCK TABLES.
[11 Dec 2009 12:32]
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/93679 3032 Konstantin Osipov 2009-12-11 Backport of: ------------------------------------------------------------ 2599.161.3 Ingo Struewing 2009-07-21 Bug#20667 - Truncate table fails for a write locked table TRUNCATE TABLE was not allowed under LOCK TABLES. The patch removes this restriction. mysql_truncate() does now handle that case. @ mysql-test/r/merge.result Bug#20667 - Truncate table fails for a write locked table Updated test result. @ mysql-test/r/truncate.result Bug#20667 - Truncate table fails for a write locked table Updated test result. @ mysql-test/r/truncate_coverage.result Bug#20667 - Truncate table fails for a write locked table New test result. @ mysql-test/t/merge.test Bug#20667 - Truncate table fails for a write locked table Updated test case due to now working TRUNCATE under LOCK TABLES. Added some SELECTs to show that child tables are truncated. @ mysql-test/t/truncate.test Bug#20667 - Truncate table fails for a write locked table Added test cases for TRUNCATE under LOCK TABLE. @ mysql-test/t/truncate_coverage.test Bug#20667 - Truncate table fails for a write locked table New test file. Coverage tests for TRUNCATE. @ sql/sql_delete.cc Bug#20667 - Truncate table fails for a write locked table Added branches for thd->locked_tables_mode. @ sql/sql_parse.cc Bug#20667 - Truncate table fails for a write locked table Deleted rejection of TRUNCATE in case of LOCK TABLES.
[16 Dec 2009 14:45]
Ingo Strüwing
Backport done and pushed by Konstantin to mysql-next-4284.
[11 Mar 2010 18:00]
Paul DuBois
This is in Celosia per Alik, so: Noted in 5.5.3 changelog.
[28 Apr 2010 12:47]
Konstantin Osipov
Paul, can this then be closed?
[28 Apr 2010 13:58]
Paul DuBois
Closing.
[20 Jun 2022 4:30]
Ramisha Farrukh
This is a drawback of using MyISAM storage engine. It does not support write concurrency. You should use MyISAM in case you want to have temporary tables. Since a temporary table only exists in the scope of a single session, there can never be any write concurrency, so this won’t matter. Another thing before truncating you should delete all row in the table. With zero row in the table it will be easily truncated. Also you said your tables are locked so, you cannot truncate a table which holds an active table lock. You can find more details of bugs in this article https://shatteredsilicon.net/blog/2021/11/27/mysql-drop-truncate-lock-bug/.
