[MDEV-12968] ALTER IGNORE TABLE does not ignore duplicates as did in 10.1.23 Created: 2017-06-01  Updated: 2021-10-25  Resolved: 2021-10-25

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Storage Engine - InnoDB
Affects Version/s: 10.2.6
Fix Version/s: 10.2.23, 10.3.14, 10.4.4

Type: Bug Priority: Minor
Reporter: Frank Sagurna Assignee: Marko Mäkelä
Resolution: Fixed Votes: 0
Labels: None
Environment:

Ubuntu Linux 16.04 packages from mariadb repository


 Description   

In mariadb 10.1.23 for removing duplicates you can do a

ALTER IGNORE TABLE mytable ADD UNIQUE (bean_id, field, history_date, value, deleted);

Duplicates for this unique key will get removed.
In mariadb 10.2.6 you get an error:
ERROR 1062 (23000): Duplicate entry '123456-price-2016-0' for key 'bean_id'

So the "IGNORE" seems to be ... well ignored.

 Comments   
Comment by Frank Sagurna [ 2017-06-01 ]

Did a little fiddle for test. Works there in mysql 5.6, no mariadb to choose:
http://sqlfiddle.com/#!9/327ed3/1

Comment by Elena Stepanova [ 2017-06-05 ]

Thanks for the report.

create table t1 (i int, j int) engine=InnoDB;
 
insert into t1 values (1,1),(1,2);
 
alter ignore table t1 add unique index (i);
 
 
 
MariaDB [test]> alter ignore table t1 add unique index (i);
 
ERROR 1062 (23000): Duplicate entry '1' for key 'i'

Unsetting sql_mode does not make a difference.
Apparently it's something that came with InnoDB 5.7, because with MyISAM it still works all right. MySQL 5.7 removed IGNORE clause in ALTER TABLE, so it cannot be reproduced there; but in MariaDB 10.2 the clause still exists and not even deprecated.

marko, would you be able to shed some light on it?

Comment by Marko Mäkelä [ 2017-06-05 ]

In MySQL 5.6 and presumably MariaDB 10.0 and 10.1, InnoDB refuses ALGORITHM=INPLACE if the IGNORE keyword is present, so that the ALGORITHM=COPY will take care of the IGNORE. The following piece of code takes care of this in MariaDB 10.1:

	/* InnoDB cannot IGNORE when creating unique indexes. IGNORE
 
	should silently delete some duplicate rows. Our inplace_alter
 
	code will not delete anything from existing indexes. */
 
	if (ha_alter_info->ignore
 
	    && (ha_alter_info->handler_flags
 
		& (Alter_inplace_info::ADD_PK_INDEX
 
		   | Alter_inplace_info::ADD_UNIQUE_INDEX))) {
 
		ha_alter_info->unsupported_reason = innobase_get_err_msg(
 
			ER_ALTER_OPERATION_NOT_SUPPORTED_REASON_IGNORE);
 
		DBUG_RETURN(HA_ALTER_INPLACE_NOT_SUPPORTED);
 
	}

The above code was removed from MySQL 5.7 by WL#7395, which also deprecated the IGNORE keyword in MySQL 5.6.

Comment by Marko Mäkelä [ 2017-06-05 ]

Jan, can you take care of this? As part of this effort, I think that we should import all the InnoDB ALTER TABLE tests from MySQL 5.6 and 5.7 to MariaDB 10.2 (or even 10.0), because many of these tests are currently missing.

Comment by Denis Malinovskiy [ 2018-11-14 ]

As a temporary workaround, it's possible to run the original query adding "ALGORITHM=COPY":

ALTER IGNORE TABLE mytable 
  ADD UNIQUE (bean_id, field, history_date, value, deleted), 
  ALGORITHM=COPY;

Comment by Marko Mäkelä [ 2021-10-25 ]

It turns out that on a merge of MDEV-18732 from 10.1 to 10.2, I had reinstated the code that had inadvertently been removed in 10.2.2:

	/* InnoDB cannot IGNORE when creating unique indexes. IGNORE
 
	should silently delete some duplicate rows. Our inplace_alter
 
	code will not delete anything from existing indexes. */
 
	if (ha_alter_info->ignore
 
	    && (ha_alter_info->handler_flags
 
		& (Alter_inplace_info::ADD_PK_INDEX
 
		   | Alter_inplace_info::ADD_UNIQUE_INDEX))) {
 
		ha_alter_info->unsupported_reason = innobase_get_err_msg(
 
			ER_ALTER_OPERATION_NOT_SUPPORTED_REASON_IGNORE);
 
		DBUG_RETURN(HA_ALTER_INPLACE_NOT_SUPPORTED);
 
	}

Generated at Thu Feb 08 08:01:53 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.