Bug #60190 partition prune on insert/delete/update
Submitted: 21 Feb 2011 15:42 Modified: 29 Apr 2013 15:02
Reporter: Roberto Spadim (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S2 (Serious)
Version:5.1.32 OS:Linux
Assigned to: Jon Stephens CPU Architecture:Any
Tags: partition insert, qc

[21 Feb 2011 15:42] Roberto Spadim
Description:
hi guys, i was testing partitioning, the prune is only used by select?
i was reparing a partition and tried to insert data to another partition at the same table, but it didn´t returned, the tables was locked, not only the partition
could we port partition to update, delete, insert?
check how to...

How to repeat:
i will post the sql files....
but
create a table with two partitions
insert many rows to make repair slow
alter table table repair partition NOT_IN_USE
insert into table that will not use partition NOT_IN_USE

Suggested fix:
?
[21 Feb 2011 15:54] Valeriy Kravchuk
Our manual, http://dev.mysql.com/doc/refman/5.1/en/partitioning-pruning.html, clearly says:

"For a table that is partitioned by KEY, has a composite primary key, and uses a composite partitioning key, it is possible to perform pruning for queries meeting the following two criteria:

    - The query must have a WHERE clause of the form pkcol1 = c1 AND pkcol2 = c2 AND ... pkcolN = cN, where pkcol1 ... pkcolN are the partitioning key columns and c1 ... cN are constant values.
    - All columns of the partitioning key must be referenced in the WHERE clause."

Your INSERT does not (and can not) have WHERE clause, so I am not sure we can call this a bug...
[21 Feb 2011 17:25] Davi Arnaut
Hum, most DDLs take table level exclusive locks.
[21 Feb 2011 17:38] Roberto Spadim
i will change to feature request, since manual don´t cover INSERT, ALTER, DELETE, UPDATE, TRUNCATE, REPAIR, OPTIMIZE, CHECK
[21 Feb 2011 17:40] Roberto Spadim
yeah, the alter table take a table lock

maybe we could change alter table XXX YYYY partition, to partition lock (not table lock) = it´s a feature request =)
[3 Mar 2011 10:50] Mattias Jonsson
In 5.5 all partitions are locked, since pruning is done in optimizing phase, which require the tables to be locked (Bug#37252). This is somewhat fixed by WL#5217, which is just pushed to mysql-trunk. And will be fixed in a more general way by WL#4443.

Inserts are not currently pruned, since it requires to loop through all records before locking the table, by that may be included in WL#4443.

DELETE and UPDATE are pruned (scanning wise) just like SELECT.

So the real problem is ALTER TABLE, since it takes an exclusive table lock (which must be taken when altering the .frm file).

But ALTER TABLE t ANALYZE/CHECK/OPTIMIZE/REPAIR PARTITION p is actually executed the same way as ANALYZE/.../REPAIR TABLE but per partition. It would need exclusive lock on that partition though, which is not possible on the current meta data locking scheme.
[3 Mar 2011 15:15] Roberto Spadim
with all works that's it.

hum... .frm files, yeah, now i see was it is locked... since it don't use always frm to optimize/repair, we change the lock when running partition tables? not a 'exclusive' lock for .frm, but a 'read lock' (no writers) to .frm files and just run on partitions not at full tables, just one idea... you know better mysql/myisam/partition/innodb codes

another question.. why insert don't use partition prone? for example insert into table set field=value_for_partition_1, will insert execute on partitions 2,3,4,5,6... or only on partition 1?
[3 Mar 2011 15:27] Roberto Spadim
sorry bad text

hum... .frm files, yeah, now i see *why* it is locked... since it don't
use always *.frm* to optimize/repair, we *could* change the lock when running
partition tables? not a 'exclusive' lock for .frm, but a 'read lock'
(no writers) to .frm files and just run on partitions not at full
tables, just one idea... you know better mysql/myisam/partition/innodb
codes
[29 Apr 2013 14:57] Jon Stephens
This is not a feature request; it's a documentation issue.
[29 Apr 2013 15:02] Jon Stephens
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.
[29 Apr 2013 15:04] Jon Stephens
Fixed in mysqldoc rev 35030.

The issue was that we did not state explicitly what was already supported.

To be clear, in MySQL 5.5+, pruning is supported for SELECT, UPDATE, DELETE; it is not supported for INSERT.

Thanks.
[29 Jul 2014 10:12] Mattias Jonsson
Also INSERT are pruned in 5.6+ (see WL#4443).