Re: Drop partition while running a query

[Mikael Ronström]

Hi,

Eric Her Wrote:
-------------------------------------------------------
> Hi
>
> Imagine that you have a table partitioned by range
> and each partition have millions of rows. What
> happen to an ongoing query that "uses" a partition
> and this partition is dropped by another process?
> In which state this query is going to fall? Is it
> canceled automatically or immediately returns an
> empty result set or it falls in a black hole?
>
> Thanks
>

MySQL employs locking in those cases. So the query that
reads the data will ensure that drop partition is blocked
until it's completed. Any queries starting after drop
partition has started will be blocked waiting for the
drop partition to complete. So in both cases no problems
will occur.

At one point queries that are queued waiting for drop partition
could be aborted due to that the meta-data changed so the query
plan would be faulty if they do not abort to reread the table
meta-data.

Rgrds Mikael

> Eric