MySQL Bugs: #35392: Delete all statement does not execute properly after few delete statements

Bug #35392	Delete all statement does not execute properly after few delete statements
Submitted:	18 Mar 2008 12:03	Modified:	7 Apr 2008 17:26
Reporter:	Rizwan Maredia	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.1.22/5.1/6.0	OS:	Windows
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any
Tags:	delete, temporary table

Description:
Execution of a DELETE FROM <tablename>; statement causes an error 

'DELETE FROM t1' failed with wrong errno 14: 'Can't change size of file (Errcode: 1224)', instead of 1224...

which neither is understandable not appropriate. This condition occurs if the delete all command is issued on a temporary table on which some delete statements with where clause have been executed previously.

How to repeat:
Here is how to reproduce it

CREATE TEMPORARY TABLE t1(a int, b varchar(20));

INSERT INTO t1 VALUES(1, 'val1');
INSERT INTO t1 VALUES(2, 'val2');
INSERT INTO t1 VALUES(3, 'val3');

DELETE FROM t1 WHERE a=1;
SELECT count(*) FROM t1;

--Error 14
DELETE FROM t1;

Suggested fix:
The statement should execute without an error.

Thank you for the bug report. This bug is only repeatable on Windows for
server versions 5.1 and 6.0 (verified with latest source):

Windows:

c:\dbs>5.1\bin\mysql -uroot db8
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.24-rc-nt-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TEMPORARY TABLE t1(a int, b varchar(20));
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO t1 VALUES(1, 'val1');
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO t1 VALUES(2, 'val2');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES(3, 'val3');
Query OK, 1 row affected (0.00 sec)

mysql> DELETE FROM t1 WHERE a=1;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT count(*) FROM t1;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

mysql> DELETE FROM t1;
ERROR 14 (HY000): Can't change size of file (Errcode: 1224)
mysql>

Linux:

[miguel@mira dbs]$ 5.1/bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.24-rc-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database dby;
Query OK, 1 row affected (0.00 sec)

mysql> use dby
Database changed
mysql> CREATE TEMPORARY TABLE t1(a int, b varchar(20));
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> INSERT INTO t1 VALUES(1, 'val1');
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO t1 VALUES(2, 'val2');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES(3, 'val3');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> DELETE FROM t1 WHERE a=1;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT count(*) FROM t1;
+----------+
| count(*) |
+----------+
|        2 | 
+----------+
1 row in set (0.00 sec)

mysql> DELETE FROM t1;
Query OK, 2 rows affected (0.00 sec)

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/44569

ChangeSet@1.2575, 2008-03-28 14:34:49+04:00, ramil@mysql.com +3 -0
  Fix for bug #35392: Delete all statement does not execute properly 
  after few delete statements
  
  Problem: changing a file size might require that the it must be 
  unmapped beforehand.
  
  Fix: unmap the file before changing its size.

Ok to push with one change:
mmap_lock is intended to use with concurrent inserts. As no other thread is allowed to perform concurrent insert when delete_all is called, please remove locking of mmap_lock mutex.

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/44595

ChangeSet@1.2586, 2008-03-28 19:16:52+04:00, ramil@mysql.com +3 -0
  Fix for bug #35392: Delete all statement does not execute properly 
  after few delete statements
  
  Problem: changing a file size might require that it must be 
  unmapped beforehand.
    
  Fix: unmap the file before changing its size.

Pushed into 5.1.24-rc

Noted in 5.1.24 changelog.

For a TERMPORARY table, DELETE with no WHERE clause could fail when
preceded by DELETE statements with a WHERE clause. 

Resetting report to Patch queued waiting for push into 6.0.x.

Pushed into 6.0.5-alpha

Noted in 6.0.5 changelog.