pt-online-schema-change makes duplicate rows in _t_new for UPDATE t set pk=0 where pk=1
Bug #1646713 reported by
Nickolay Ihalainen
This bug affects 1 person
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona Toolkit moved to https://jira.percona.com/projects/PT |
Fix Released
|
High
|
Carlos Salguero |
Bug Description
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 |
Changed in percona-toolkit: | |
status: | New → Confirmed |
Changed in percona-toolkit: | |
importance: | Undecided → High |
assignee: | nobody → Carlos Salguero (carlos-salguero) |
milestone: | none → 2.2.21 |
Changed in percona-toolkit: | |
milestone: | 2.2.21 → 3.0.2 |
tags: | added: pt94 |
Changed in percona-toolkit: | |
status: | Fix Committed → Fix Released |
To post a comment you must log in.
Hi~ ------- ------- ------- ------- ------- ------- ------- - for{$new_ col} || $new_col; $new_col) ; $old_col) ; struct- >{keys} ->{$del_ index}- >{cols} } ); ------- ------- ------- ------- ------- ------- ------- -
-------
+
+ my $upd_index_cols = join(" AND ", map {
+ my $new_col = $_;
+ my $old_col = $old_col_
+ my $new_qcol = $q->quote(
+ my $old_qcol = $q->quote(
+ "OLD.$old_qcol <=> NEW.$new_qcol"
+ } @{$tbl_
+
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. :-) /github. com/gywndi/ percona- toolkit/ commit/ 7c3c5c9a8e7bdc9 fadf047757182f4 4d6aef4f53
>> https:/
Thanks.
Chan. (Kakaobank)