Bug #56785 InnoDB: Cannot truncate table … because there is a foreign key check running
Submitted: 15 Sep 2010 7:46 Modified: 10 Sep 2018 12:13
Reporter: Marko Mäkelä Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Locking Severity:S5 (Performance)
Version:5.0, 5.1, 5.5 OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Tags: foreign key, innodb, mdl

[15 Sep 2010 7:46] Marko Mäkelä
Description:
InnoDB supports fast TRUNCATE TABLE t in certain conditions. If those conditions fail, the operation will be mapped to DELETE FROM t, which is much slower, because every index page will have to be fetched to the buffer pool and a large amount of redo and undo log will have to be written.

When there are foreign key constraints defined on a table, InnoDB can be forced to do a fast TRUNCATE (and bypass the foreign key checks) by the following command:

SET foreign_key_checks=0;

Being a session variable, this does not prevent foreign key checks in other connections. If some other connection is modifying a table when we are about to truncate a referenced table, then table->n_foreign_key_checks_running > 0 and we will have to resort to row-by-row DELETE, in spite of the foreign_key_checks=0.

How to repeat:
Create three InnoDB tables, grandparent-parent-child that are connected by foreign key constraints. Try to truncate the parent table while several other clients are executing DML on the child or grandparent table. Observe the message “Cannot truncate table” in the error log.

Suggested fix:
Acquire appropriate meta-data locks on the referencing and referenced tables for the duration of DML statements.
[13 Oct 2010 16:13] Konstantin Osipov
Marko,
for the bug to get properly verified you need to provide an SQL test case.
Thanks,
-- 
kostja
[14 Nov 2010 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[10 Sep 2018 12:13] Dyre Tjeldvoll
Posted by developer:
 
dlenev: after wl#6049 there are MDLs on all tables involved in FKs
bin.su: After new DD, InnoDB will not truncate table by row-be-row deletion. Only re-create an empty table.

Closing.