Submitted: 25 Oct 2010 10:56 Modified: 13 Dec 2010 7:42
Reporter: Mattias Jonsson Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Partitions Severity:S2 (Serious)
Version:5.6 OS:Any
Assigned to: Mattias Jonsson CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[25 Oct 2010 10:56] Mattias Jonsson
Worklog 4445, EXCHANGE PARTITION WITH TABLE, misuses the IGNORE keyword defined in Worklog 4103.

IGNORE is for row-by-row checking
(when an error occurs, skip the row),
using either old or proposed definitions.
See WL#4103 Define IGNORE.

WL#4445's IGNORE skips the entire row validation step, i.e. no warnings or errors will be issued and it will allow rows which does not match the partitions definition into the partition. (Which also causes bugs like bug#55944).

How to repeat:
Read WL#4103 and compare with 'ALTER TABLE t EXCHANGE PARTITION p0 WITH TABLE t_non_partitioned IGNORE' as added in WL#4445.

Suggested fix:
Either remove the IGNORE option completely, or change it from [IGNORE] to [{WITH|WITHOUT} VALIDATION] as the syntax to skip the row-by-row validation step.

I would recommend to remove the support for IGNORE in EXCHANGE PARTITION as the fix for this bug, and create a new worklog for handling the case when there is rows in a partition that does not match the partitioning definition (bug#55944). And as a part of that worklog also add [{WITH|WITHOUT} VALIDATION] to EXCHANGE PARTITION to allow the DBA to EXCHANGE a non empty table with a partition without the need to do the row-by-row validation step.
[25 Oct 2010 13:18] Miguel Solorzano
Thank you for the bug report.
[29 Oct 2010 10:22] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:


3302 Mattias Jonsson	2010-10-29
      Bug#57708: EXCHANGE PARTITION misuses IGNORE
      First problem was that IGNORE was used for skipping the whole
      validation step (check of all rows in the table to fit
      into the partition) instead of changing every row error into a
      Second problem was that IGNORE opened up the possibility for
      bugs like 55944.
      Solution was to remove the IGNORE option from EXCHANGE PARTITION
      WITH TABLE (WL#4445).
     @ mysql-test/r/partition_exchange.result
        Updated the results
     @ mysql-test/t/partition_exchange.test
        Removed the IGNORE option from EXCHANGE PARTITION (wl#4445)
     @ sql/sql_partition_admin.cc
        Removed the IGNORE option from EXCHANGE PARTITION (wl#4445)
     @ sql/sql_yacc.yy
        Removed the IGNORE option from EXCHANGE PARTITION (wl#4445)
[16 Nov 2010 1:24] Mattias Jonsson
pushed to mysql-trunk-bugfixing
[16 Nov 2010 1:43] Mattias Jonsson
Marked bug#55944 as duplicate of this (not really a duplicate, but since IGNORE was disabled, that bug cannot occur any more).
[5 Dec 2010 12:44] Bugs System
Pushed into mysql-trunk 5.6.1 (revid:alexander.nozdrin@oracle.com-20101205122447-6x94l4fmslpbttxj) (version source revid:alexander.nozdrin@oracle.com-20101205122447-6x94l4fmslpbttxj) (merge vers: 5.6.1) (pib:23)
[13 Dec 2010 6:44] Jon Stephens
Please note that the correct syntax should be "ALTER IGNORE TABLE tbl_1_name EXCHANGE PARTITION partition_name WITH TABLE tbl_2_name" -- this is standard for all ALTER TABLE statements.

[13 Dec 2010 7:42] Jon Stephens
The issue doesn't appear in any release, so no changelog entry is required.

Noted that IGNORE is ignored by ALTER TABLE EXCHANGE PARTITION in the 5.6 versions of "Exchanging Partitions and Subpartitions with Tables" and "ALTER TABLE Syntax".