pt-online-schema-change loosing data when making non unique column a new PK

[Przemek]

CREATE TABLE `mytable` (
  `userid` int(10) unsigned NOT NULL DEFAULT '0',
  `time` int(10) unsigned NOT NULL DEFAULT '0',
  `what` enum('paidaccount') DEFAULT NULL,
  `action` enum('new','renew','expire','return','ext') DEFAULT NULL,
  KEY `userid` (`userid`),
  KEY `time` (`time`)
) ENGINE=InnoDB

mysql> select * from mytable;
+--------+---------+-------------+--------+
| userid | time | what | action |
+--------+---------+-------------+--------+
| 32 | 2333232 | paidaccount | new |
| 32 | 111 | paidaccount | new |
| 32 | 222 | paidaccount | ext |
+--------+---------+-------------+--------+
3 rows in set (0.00 sec)

mysql> alter table mytable add primary key(userid);
ERROR 1062 (23000): Duplicate entry '32' for key 'PRIMARY'

mysql> select * from mytable;
+--------+---------+-------------+--------+
| userid | time | what | action |
+--------+---------+-------------+--------+
| 32 | 2333232 | paidaccount | new |
| 32 | 111 | paidaccount | new |
| 32 | 222 | paidaccount | ext |
+--------+---------+-------------+--------+
3 rows in set (0.00 sec)

[root@lap-prz ~]# pt-online-schema-change --execute p=xxx,D=test,t=mytable --alter "ADD PRIMARY KEY(userid)"
Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `test`.`mytable`...
Creating new table...
Created new table test._mytable_new OK.
Altering new table...
Altered `test`.`_mytable_new` OK.
2013-10-31T15:39:43 Creating triggers...
2013-10-31T15:39:43 Created triggers OK.
2013-10-31T15:39:43 Copying approximately 3 rows...
2013-10-31T15:39:43 Copied rows OK.
2013-10-31T15:39:43 Swapping tables...
2013-10-31T15:39:43 Swapped original and new tables OK.
2013-10-31T15:39:43 Dropping old table...
2013-10-31T15:39:43 Dropped old table `test`.`_mytable_old` OK.
2013-10-31T15:39:43 Dropping triggers...
2013-10-31T15:39:43 Dropped triggers OK.
Successfully altered `test`.`mytable`.

mysql> select * from mytable;
+--------+---------+-------------+--------+
| userid | time | what | action |
+--------+---------+-------------+--------+
| 32 | 2333232 | paidaccount | new |
+--------+---------+-------------+--------+
1 row in set (0.00 sec)

mysql> show create table mytable\G
*************************** 1. row ***************************
       Table: mytable
Create Table: CREATE TABLE `mytable` (
  `userid` int(10) unsigned NOT NULL DEFAULT '0',
  `time` int(10) unsigned NOT NULL DEFAULT '0',
  `what` enum('paidaccount') DEFAULT NULL,
  `action` enum('new','renew','expire','return','ext') DEFAULT NULL,
  PRIMARY KEY (`userid`),
  KEY `userid` (`userid`),
  KEY `time` (`time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

[Przemek (pmalkowski)]

tested with pt-online-schema-change 2.2.5

[Shahriyar Rzayev (rzayev-sehriyar)]

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