pt-online-schema-change loosing data when making non unique column a new PK
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona Toolkit moved to https://jira.percona.com/projects/PT |
Confirmed
|
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'
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-
Operation, tries, wait:
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_
Altering `test`.`mytable`...
Creating new table...
Created new table test._mytable_new OK.
Altering new table...
Altered `test`.
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`.
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
*******
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'
PRIMARY KEY (`userid`),
KEY `userid` (`userid`),
KEY `time` (`time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
Changed in percona-toolkit: | |
status: | New → Confirmed |
tested with pt-online- schema- change 2.2.5