Deleting all rows from locked heap table failing
am 10.05.2003 02:52:32 von Peter SpanglerHi,
I'm running MySQL 3.23.41 on Linux Intel and I'm
seeing some unexpected behavior with heap tables that
seems like a bug to me. I have also been able to
reproduce this behavior on an older 3.23.21
installation, also on Linux Intel.
The problem that I'm seeing is that if one mysql
client connects to a database and locks a heap table,
and a second mysql client locks that table immediately
thereafter, the first client can't clear out
the records in the table using either 'delete from'
or 'truncate'. Using 'delete from' with a where
clause succeeds though.
I can reproduce this behavior on my system this
way:
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)
If I do this with a MyISAM table then 'delete from'
and 'truncate' both successfully empty the table. It
seems like the behavior should be the same for heap
tables.
Thanks,
Peter Spangler
__________________________________
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.
http://search.yahoo.com
--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org