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

Bug #1246754 reported by Przemek on 2013-10-31
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Toolkit
Undecided
Unassigned

Bug Description

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) wrote :

tested with pt-online-schema-change 2.2.5

Changed in percona-toolkit:
status: New → Confirmed
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers