Adding new column causes duplicate PK error

[monty solomon]

When trying to add a column to an InnoDB table the server reports an error about a duplicate PK.

[monty solomon (monty+launchpad)]

mysql> alter table campaignLastTouched add column negativeLastSendOrDropTimestamp int default null, lock=none;
ERROR 1062 (23000): Duplicate entry '72502503' for key 'PRIMARY'

mysql> select * from campaignLastTouched where db_id=72502503;
Empty set (0.00 sec)

mysql> show create table campaignLastTouched\G
*************************** 1. row ***************************
       Table: campaignLastTouched
Create Table: CREATE TABLE `campaignLastTouched` (
  `db_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `portalId` int(10) unsigned NOT NULL,
  `campaignId` bigint(20) NOT NULL,
  `negativeTimestamp` bigint(20) NOT NULL,
  `appId` int(11) NOT NULL,
  PRIMARY KEY (`db_id`),
  UNIQUE KEY `portalId-campaignId-appid` (`portalId`,`campaignId`,`appId`),
  KEY `covering` (`portalId`,`negativeTimestamp`,`campaignId`,`appId`)
) ENGINE=InnoDB AUTO_INCREMENT=72525403 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql Ver 14.14 Distrib 5.6.22-71.0, for Linux (x86_64) using EditLine wrapper
Server version: 5.6.22-71.0-log Percona Server (GPL), Release 71.0, Revision 726

Linux 2.6.32-431.el6.x86_64 #1 SMP Fri Nov 22 03:15:09 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux
CentOS release 6.5 (Final)

[monty solomon (monty+launchpad)]

I suspect that many inserts took place while the online DDL was running and confusion about the PK resulted.

The table contains a UNIQUE KEY and inserts are of the form INSERT ... ON DUPLICATE KEY UPDATE

The db_id PK column is updated by the server, it is not updated (or inserted) by any statements.

[Valerii Kravchuk (valerii-kravchuk)]

I wonder if some of INSERTs are of INSERT ... SELECT type and what is the value of innodb_autoinc_lock_mode server variable?

[Peiran Song (peiran-song)]

Another customer encountered this bug.

The table schema originally has a UNIQUE KEY that duplicates the PK and another UK. After removing the duplicate UK, the problem remained.

I am able to generate the duplicate key error with single thread, single row simple insert statements while running ALTER TABLE. And the duplicate key error is observed for both ADD column and DROP column.

To reproduce: before acknowledgment from the customer to share the data on launchpad, I put the table schema and insert query on the sftp server, at /srv/ftp/53111.newr/

- table.sql for table structure
- insert.sql for DML
- the alter statements:
 ALTER TABLE remote_archives ADD insert_worker integer, ALGORITHM=INPLACE, LOCK=NONE;
 ALTER TABLE remote_archives DROP insert_worker, ALGORITHM=INPLACE, LOCK=NONE;

Characteristics in summary:
1) table characteristics: PK + UK

2) concurrent DML : insert

3) observed for both ADD column and DROP column

4) error happens on newly added columns

5) in the test, started to step on errors when rows over 78K
ERROR 1062 (23000): Duplicate entry '78151' for key 'PRIMARY'

[Nilnandan Joshi (nilnandan-joshi)]

Confirmed. Able to reproduce the same on sftp server as Peiran mentioned in comment #4.

mysql> ALTER TABLE remote_archives ADD insert_worker integer, ALGORITHM=INPLACE, LOCK=NONE;
ERROR 1062 (23000): Duplicate entry '2957' for key 'PRIMARY'
mysql>

[Laurynas Biveinis (laurynas-biveinis)]

Upstream?

[Nilnandan Joshi (nilnandan-joshi)]

Verified with upstream: https://bugs.mysql.com/bug.php?id=76895

[Laurynas Biveinis (laurynas-biveinis)]

Upstream resolution is "not a bug" because

https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-limitations.html

"When running an online ALTER TABLE operation, the thread that runs the ALTER TABLE operation will apply an “online log” of DML operations that were run concurrently on the same table from other connection threads. When the DML operations are applied, it is possible to encounter a duplicate key entry error (ERROR 1062 (23000): Duplicate entry), even if the duplicate entry is only temporary and would be reverted by a later entry in the “online log”. This is similar to the idea of a foreign key constraint check in InnoDB in which constraints must hold during a transaction."

Do you agree?

[Valerii Kravchuk (valerii-kravchuk)]

I definitely do not agree to just live with this limitation, even documented. So, I think this is confirmed for the wishlist.

[Laurynas Biveinis (laurynas-biveinis)]

Values aside, how do the insert statements the comments #4 and #5 look like? Are they ODKU?

[Nilnandan Joshi (nilnandan-joshi)]

Hi Laurynas,

No, there are not with "ON DUPLICATE KEY UPDATE". They are simple INSERT INTO ...VALUES statements.

[gechong (gechong0513)]

"Upstream resolution is "not a bug" because

https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-limitations.html

"When running an online ALTER TABLE operation, the thread that runs the ALTER TABLE operation will apply an “online log” of DML operations that were run concurrently on the same table from other connection threads. When the DML operations are applied, it is possible to encounter a duplicate key entry error (ERROR 1062 (23000): Duplicate entry), even if the duplicate entry is only temporary and would be reverted by a later entry in the “online log”. This is similar to the idea of a foreign key constraint check in InnoDB in which constraints must hold during a transaction."

Do you agree?"
yes

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-2467

[Ricardo Cescon (icemanx+)]

I have the same problem, can't add column because I get an error 1062. Any idea how to fix in productive system (MySQL RDS) with very big table? I think it is definitely a bug.

[Jean-François Gagné (jfg956)]

This comment is to make sure a work-around is out there, and this workaround is to use LOCK=EXCLUSIVE.