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.

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;

While not the most technical of answers, I struggled with this problem just today. I tried all of the above (locking tables, foreign key checks, checking the innodb engine status, et cetera) none of which were useful (though in the latter case, I simply didn't sufficiently understand what I was reading.)

I had a mail job table that refused to be write locked, truncated, renamed, or dropped (despite being completely empty.) I finally caved and restarted the mysql service at which point I could make the needed change, no problem.

One of our customers had a table with 450GB of size. And we tried all ways to empty this table, but no one has solved the problem.

We tried to truncate the table, but truncate operation took more than 10 hours and didn’t solve the problem. And when we used WHERE statement in any query on this table, SQL server executes the query without stopping.

The only way that I could to empty the table in 1 second was as the following:

set rowcount 1;

truncate table TABLE_NAME;

And of course after truncating the table, just go and shrink the database.

By this approach you just remove the pointer to this table and data rows of table will be removed by shrink

When you DROP or TRUNCATE a table in MySQL or MariaDB and you have large buffer pool, there can be server lockup or stall of multiple seconds.The is because when a table is dropped, MySQL will perform a full scan of buffer pool looking for pages that belong to dropped table. This will lock the buffer-pool and you have to wait until it's unlock. If your buffer pool is smaller this will not effect much but if you have larger one, it becomes devastating.

One possible solution can be delete all rows before dropping a table, this will not cause a memory stall because there will be no data in memory.

Another solution can be use of non-InnoDB storage engine for temporary tables. You can specify ENGINE=MyISAM in your CREATE TABLE statement. If you have a large code base and don’t explicitly specify the storage engine, you can change the default storage engine to MyISAM by adjusting the following configuration option:

default_tmp_storage_engine = MyISAM

Of course there are some drawbacks with these solutions. You can find complete details of this bug, solution and drawbacks MySQL troubleshooting article.

The good news is that the fix itself has been implemented MySQL 8.0.23 and later.