Search code examples
mysqlinnodbtruncatefreeze

MySQL truncate hangs

I'm trying to truncate a table with 300,000 rows. When no other queries are running, I run the truncate query, and it just hangs.

show processlist; says the state is "updating".

Server version: 5.1.41-3ubuntu12.8 (Ubuntu)

The table is InnoDB.

Any ideas why this is happening, or how I can investigate the problem further?

Thanks.

Solution

  • I think it is the foreign key checking problem.

    I've found that the following works as quickly as expected:

    SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE table_name;
SET FOREIGN_KEY_CHECKS = 1;