Not the answer you need?
Register and ask your own question!

alter table truncate partition

ysfinksysfinks EntrantCurrent User Role Beginner
We have a partitioned by day table. We use hash partitioning but generally it doesn't matter - range behaves the same way as described below. We are trying to organize rolling partition truncation for the old days.
Problem is "alter table truncate partition" requires "table metadata lock" which causes major locks, connection timeouts, dropped connections, etc. for heavily used table (there are no transactions against given partition for truncation). Anybody can have an experiment where you start one session with:
start transaction;
select * from a partition(p0);

And then run other session with
alter table a truncate partition p15;

Then open another one with
select * from a partition(p5);

Your second one will be blocked by first one with "Waiting for table metadata lock". Same is your third one.

In 5.6 documentation you have:
statements still take metadata locks on the table level.

Has anyone had experience with this? Any possible solution?
We use Percona 5.6.15

Thank you
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.