MySQL Bugs: #57708: EXCHANGE PARTITION misuses IGNORE

Bug #57708	EXCHANGE PARTITION misuses IGNORE
Submitted:	25 Oct 2010 10:56	Modified:	13 Dec 2010 7:42
Reporter:	Mattias Jonsson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S2 (Serious)
Version:	5.6	OS:	Any
Assigned to:	Mattias Jonsson	CPU Architecture:	Any

Description:
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.

Thank you for the bug report.

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:

  http://lists.mysql.com/commits/122291

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
      warning.
      
      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)

pushed to mysql-trunk-bugfixing

Marked bug#55944 as duplicate of this (not really a duplicate, but since IGNORE was disabled, that bug cannot occur any more).

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)

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.

Thanks!

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".

Closed.