pt-online-schema-change makes duplicate rows in _t_new for UPDATE t set pk=0 where pk=1

[Nickolay Ihalainen]

Update trigger is not able to cover primary/unique key change.

CREATE TABLE `t` (
  `id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

mysql> select * from t;
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)

mysql> update t set id=0 where id=1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from t;
+----+
| id |
+----+
| 0 |
+----+
1 row in set (0.00 sec)

mysql> select * from _t_new;
+----+------+
| id | c1 |
+----+------+
| 0 | NULL |
| 1 | NULL |

Expected just
mysql> select * from _t_new;
+----+------+
| id | c1 |
+----+------+
| 0 | NULL |

[DONGCHAN SUNG (sdchan1)]

Hi~
---------------------------------------------------------
 +
 + my $upd_index_cols = join(" AND ", map {
 + my $new_col = $_;
 + my $old_col = $old_col_for{$new_col} || $new_col;
 + my $new_qcol = $q->quote($new_col);
 + my $old_qcol = $q->quote($old_col);
 + "OLD.$old_qcol <=> NEW.$new_qcol"
 + } @{$tbl_struct->{keys}->{$del_index}->{cols}} );
 +
     my $update_trigger
        = "CREATE TRIGGER `${prefix}_upd` AFTER UPDATE ON $orig_tbl->{name} "
        . "FOR EACH ROW "
 - . "REPLACE INTO $new_tbl->{name} ($qcols) VALUES ($new_vals)";
 + . "BEGIN "
 + . "DELETE IGNORE FROM $new_tbl->{name} WHERE !($upd_index_cols) AND $del_index_cols;"
 + . "REPLACE INTO $new_tbl->{name} ($qcols) VALUES ($new_vals);"
 + . "END ";
---------------------------------------------------------

Fixed version, you can check here. :-)
>> https://github.com/gywndi/percona-toolkit/commit/7c3c5c9a8e7bdc9fadf047757182f44d6aef4f53

Thanks.
Chan. (Kakaobank)

[Carlos Salguero (carlos-salguero)]

PR: https://github.com/percona/percona-toolkit/pull/181

[Shahriyar Rzayev (rzayev-sehriyar)]

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