MySQL Bugs: #423: DELETE does not work for locked HEAP table

Bug #423	DELETE does not work for locked HEAP table
Submitted:	13 May 2003 6:05	Modified:	13 May 2003 9:30
Reporter:	Peter Zaitsev (Basic Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	3.23	OS:	Any (all)
Assigned to:		CPU Architecture:	Any

Description:
MySQL 3.23 fails to delete all rows from HEAP table than "truncate" optimization is used - no where clause.

MySQL 4.0 seems to work fine. 

How to repeat:
1. Create a heap table Foo and insert some records
into
   it.

   mysql> show tables;
   Empty set (0.00 sec)

   mysql>         create table Foo(
       ->                          id int primary key,
       ->                          someinfo int
       ->                         ) type=heap;
   Query OK, 0 rows affected (0.00 sec)

   mysql> insert into Foo values(1, 12);
   Query OK, 1 row affected (0.00 sec)

   mysql> insert into Foo values(2, 34);
   Query OK, 1 row affected (0.00 sec)

   mysql> insert into Foo values(3, 67);
   Query OK, 1 row affected (0.00 sec)

   mysql> select * from Foo;
   +----+----------+
   | id | someinfo |
   +----+----------+
   |  1 |       12 |
   |  2 |       34 |
   |  3 |       67 |
   +----+----------+
   3 rows in set (0.00 sec)

2. Put a lock on the table...
 
   mysql> lock tables Foo write;
   Query OK, 0 rows affected (0.00 sec)

3. Connect to the database with another client and
lock
   Foo.  This waits in the queue as it should.

4. Now, back in the first client, I try to delete all
   the records in the table, using 'delete from' 
   and 'truncate' but neither works.

   mysql> delete from Foo;
   Query OK, 0 rows affected (0.00 sec)

   mysql> select * from Foo;
   +----+----------+
   | id | someinfo |
   +----+----------+
   |  1 |       12 |
   |  2 |       34 |
   |  3 |       67 |
   +----+----------+
   3 rows in set (0.00 sec)

   mysql> truncate Foo;
   Query OK, 0 rows affected (0.01 sec)

   mysql> select * from Foo;
   +----+----------+
   | id | someinfo |
   +----+----------+
   |  1 |       12 |
   |  2 |       34 |
   |  3 |       67 |
   +----+----------+
   3 rows in set (0.00 sec)

5. If, however, I add a where clause that is always
   true, then this works.

   mysql> delete from Foo where 1=1;
   Query OK, 3 rows affected (0.00 sec)

   mysql> select * from Foo;
   Empty set (0.00 sec)

Thank you for taking the time to report a problem.  Unfortunately
you are not using a current version of the product your reported a
problem with -- the problem might already be fixed. Please download
a new version from http://www.mysql.com/downloads/

If you are able to reproduce the bug with one of the latest versions,
please change the version on this bug report to the version you
tested and change the status back to "Open".  Again, thank you for
your continued support of MySQL.

This is a bug that is not going to be fixed in 3.23 as it's not a critical bug and we are concentrating our efforts on 4.0 and 4.1