TRUNCATE TABLE statement sometimes hangs

Why does the TRUNCATE TABLE statement hang sometimes? What are the reasons for this type of issue?

I am migrating from MySQL to MariaDB. This problem doesn't happen with MySQL, only with MariaDB.

The hanging statement is simply:

TRUNCATE TABLE sampledb.datatable;

What can cause this to happen, and how could I fix it?

Another one observation is if the table have some data, may be one or two rows, then the truncate query works successfully. Else the table have a lot of data, query becomes hang.

The reason why you experience performance degradation or stall while executing TRUNCATE TABLE is a known issue with this statement. Please refer to Bug #68184:Truncate table causes innodb stalls. There are other bug numbers opened for prior versions as well.

You can use:

CREATE TABLE log_table_new LIKE log_table;
RENAME TABLE log_table TO log_table_old, log_table_new TO log_table;
DROP TABLE log_table_old;

It gets tricky for tables with AUTO_INCREMENT values: new table is created with an AUTO_INCREMENT value which is immediately taken in the working table. If you do no want to use same values, you can:

ALTER TABLE log_table_new AUTO_INCREMENT=some high enough value;

StackExchange Database Administrators Q#28055, answer score: 17