Bug #58788 truncate error 'Cannot truncate a table referenced in a foreign key constraint'
Submitted: 7 Dec 2010 16:39 Modified: 7 Dec 2010 21:38
Reporter: Masahiro Tomita Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Documentation Severity:S2 (Serious)
Version:5.5.7, 5.5.9 OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Tags: regression

[7 Dec 2010 16:39] Masahiro Tomita
Description:
On mysqld 5.5.7 'truncate' command cause error 'Cannot truncate a table referenced in a foreign key constraint'.

On mysqld 5.5.6 it work fine.

How to repeat:
mysql> use test;
Database changed
mysql> create table t1 (id int, c char(10), primary key (id)) engine innodb;
Query OK, 0 rows affected (0.10 sec)

mysql> create table t2 (id int, t1 int, foreign key (t1) references t1(id) on delete cascade) engine innodb;
Query OK, 0 rows affected (0.24 sec)

mysql> truncate t1;
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1`) REFERENCES `test`.`t1` (`id`))
[7 Dec 2010 16:47] Peter Laursen
but

DELETE FROM t1;

.. works fine.
[7 Dec 2010 16:57] Valeriy Kravchuk
Indeed, we have this error message:

macbook-pro:5.5-sec openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.9-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table t1 (id int, c char(10), primary key (id)) engine innodb;Query OK, 0 rows affected (0.09 sec)

mysql> create table t2 (id int, t1 int, foreign key (t1) references t1(id) on delete cascade) engine innodb;
Query OK, 0 rows affected (0.07 sec)

mysql> truncate t1;ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1`) REFERENCES `test`.`t1` (`id`))

and this is not documented or explained at all in http://dev.mysql.com/doc/refman/5.5/en/truncate-table.html (we should expect deleting row by row, but not this). Thus this is a regression bug.
[7 Dec 2010 17:17] Davi Arnaut
Closed as not a bug. See Bug#54678 for reference.
[7 Dec 2010 17:25] Davi Arnaut
If you do not want foreign key checks to be run, disable them with SET foreign_key_checks=0 before TRUNCATE.
[7 Dec 2010 17:34] Peter Laursen
@Davi .. as this is changed behavior from 5.5.7 and not documented I believe it is a bug (documentation bug).

Don't be so eager to close as 'not a bug'. Docs are equally important as code is. And it may actually break applications that worked with earlier versions
[7 Dec 2010 17:34] Valeriy Kravchuk
As http://dev.mysql.com/doc/refman/5.5/en/truncate-table.html does NOT explain this (changed) behavior, it is a documentation bug at least.
[7 Dec 2010 17:36] Peter Laursen
Thanks Valeriy! And @Davi .. please learn from it.  It is not the first time!
[7 Dec 2010 17:36] Valeriy Kravchuk
Surely this can brake application/scripts that previously worked in 5.1.x. This is incompatible change in behavior (even if for a good reason) that must be explicitly documented in every possible place.
[7 Dec 2010 17:38] Davi Arnaut
Guys,

The other bug status is Documenting. It is in the process of being properly documented.
[7 Dec 2010 21:38] John Russell
Documented under bug#54678 (now closed).
[3 Nov 2011 11:01] MySQL Verification Team
http://bugs.mysql.com/bug.php?id=63086 marked as duplicate of this one.