MySQL Bugs: #11816: Truncate table doesn't work with temporary innodb tables

Bug #11816	Truncate table doesn't work with temporary innodb tables
Submitted:	8 Jul 2005 13:57	Modified:	1 Sep 2005 0:25
Reporter:	[ name withheld ]	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0, 4.1, 5.0	OS:	Windows (Win XP PRO SP2/Linux)
Assigned to:	Ingo Strüwing	CPU Architecture:	Any

Description:
When trying to truncate a temporary table that's using the innodb table engine it doesn't work: all records are still intact.
Heap and myisam engines are fine.

How to repeat:
Following code repeats the problem:

drop table if exists test;
create temporary table test (a int) engine innodb;
insert into test values (4711);
truncate test;
insert into test values (42);
select * from test;

Should only return 42, but returns:
4711 
42 

The following, using myisam returns 42 as expected:

drop table if exists test;
create temporary table test (a int) engine myisam;
insert into test values (4711);
truncate test;
insert into test values (42);
select * from test;

Edit: I wrote client, should be server

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.12a-nt

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

mysql> drop table if exists test;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> create temporary table test (a int) engine innodb;
Query OK, 0 rows affected (0.09 sec)

mysql> insert into test values (4711);
Query OK, 1 row affected (0.05 sec)

mysql> truncate test;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test values (42);
Query OK, 1 row affected (0.02 sec)

mysql> select * from test;
+------+
| a    |
+------+
| 4711 |
|   42 |
+------+
2 rows in set (0.03 sec)

mysql> drop table if exists test;
Query OK, 0 rows affected (0.06 sec)

mysql> create table test (a int) engine innodb;
Query OK, 0 rows affected (0.08 sec)

mysql> insert into test values (4711);
Query OK, 1 row affected (0.02 sec)

mysql> truncate test;
Query OK, 1 row affected (0.02 sec)

mysql> insert into test values (42);
Query OK, 1 row affected (0.03 sec)

mysql> select * from test;
+------+
| a    |
+------+
|   42 |
+------+
1 row in set (0.00 sec)

miguel@hegel:/share/dbs/4.1$ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.13-debug-log

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

mysql> drop table if exists test;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> create temporary table test (a int) engine innodb;
Query OK, 0 rows affected (0.04 sec)

mysql> insert into test values (4711);
Query OK, 1 row affected (0.02 sec)

mysql> truncate test;
Query OK, 0 rows affected (0.02 sec)

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

mysql> select * from test;
+------+
| a    |
+------+
| 4711 |
|   42 |
+------+
2 rows in set (0.03 sec)

mysql>

Assigning this to Marko, as he has improved TRUNCATE in 5.0. This may be a bug on the MySQL side of the code, too.

Regards,

Heikki

This bug is in 5.0 as well, so it's unaffected by the fast TRUNCATE TABLE implemented in 5.0.

The problem is that mysql_truncate() in sql_delete.cc tries to re-generate temporary tables without checking ha_supports_generate(). This bug is not specific to InnoDB, but it should manifest itself also for BDB and NDB.

Unassigning from myself, as it's not an InnoDB bug.

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/internals/28079

Just to add a short comment, this bug seems to take place on Windows systems, but not on MacOs systems.

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/internals/28981

Pushed to 4.1.15 and 5.0.12.

Noted in 4.1.15, 5.0.12 changelogs.