pt-online-schema change eats data on adding a unique index
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona Toolkit moved to https://jira.percona.com/projects/PT |
Fix Released
|
Critical
|
Carlos Salguero |
Bug Description
Setup
=====
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
id tinyint unsigned auto_increment primary key,
notunique varchar(200) NOT NULL
);
INSERT INTO t1(notunique) VALUES(
SELECT * FROM t1;
Action
=====
pt-online-
Expected/Desired result
===================
The osc aborts because the data for the unique index is not unique
Actual result
==========
On MySQL 5.6 data with duplicate entries are dropped silently
On MySQL 5.7 data with duplicate entries are dropped with a warning 1062 in the stats (probably also for MariaDB 5.5+)
Related
======
https:/
Suggested fix
===========
- Abort on warning 1062 if possible and/or require a --force-unique flag when adding adding an unique index.
- Don't swap/drop if the original table has less rows than the original table (count inserts? stats?)
Versions
=======
- MySQL 5.7.11
- pt-online-
description: | updated |
Changed in percona-toolkit: | |
importance: | Undecided → Critical |
assignee: | nobody → Carlos Salguero (carlos-salguero) |
tags: | added: pt116 |
Changed in percona-toolkit: | |
status: | Fix Committed → Fix Released |
I can confirm that the issue still persists in the latest stable (2.2.20) and it caused us unexpected data loss.