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

[8 Jul 2005 13:57] [ name withheld ]
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;
[8 Jul 2005 13:58] [ name withheld ]
Edit: I wrote client, should be server
[8 Jul 2005 14:46] MySQL Verification Team
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>
[11 Jul 2005 11:24] Heikki Tuuri
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
[11 Jul 2005 13:50] Marko Mäkelä
This bug is in 5.0 as well, so it's unaffected by the fast TRUNCATE TABLE implemented in 5.0.
[12 Jul 2005 8:54] Marko Mäkelä
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.
[12 Jul 2005 8:59] Marko Mäkelä
Unassigning from myself, as it's not an InnoDB bug.
[9 Aug 2005 17: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/internals/28079
[15 Aug 2005 13:57] Frederick Aubert
Just to add a short comment, this bug seems to take place on Windows systems, but not on MacOs systems.
[29 Aug 2005 15:01] 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/internals/28981
[30 Aug 2005 12:38] Ingo Strüwing
Pushed to 4.1.15 and 5.0.12.
[1 Sep 2005 0:25] Paul DuBois
Noted in 4.1.15, 5.0.12 changelogs.