Update primary key value, lost data

[rudy-gao]

Hello, I use gh-ost version 1.0.42 to do ddl, when I update primary key value, I may lost date.
For example my table t2 has 1000000 rows, table struct as following:

CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `password` varchar(20) DEFAULT NULL,
  `drc_check_time` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000 DEFAULT CHARSET=utf8mb4

1. First gh-ost use prmary key order to copy data to new table
2. When gh-ost copy to 100000, I execute a update sql
   update t2 set name='rudytest',id=-2 where id=888887;
3. Gh-ost read binlog event and do event on new table

2017-11-08 14:04:46 INFO dml event query args; query=
 			update /* gh-ost `test`.`_t2_gho` */
 					`test`.`_t2_gho`
				set
					`id`=?, `name`=?, `password`=?, `drc_check_time`=?
				where
 					((`id` = ?))
 		; args=[-2 rudytest <nil> 2017-11-08 14:04:24.948 888887],dmlEvent=[Update on test:t2]

4. Gh-ost update 0 row on new table, because id=888887 is not copy to new table, and old table lost id=888887 data, row copy can not copy this row to new table, so I lost this row data

[shlomi-noach]

Confirmed.

While updating a PRIMARY KEY is not generally considered good practice, it should be supported.

oak-online-alter-table originally addressed this very issue by converting any update into two queries:

• delete
• replace into

I'm not sure how it came to be I skipped this scenario in gh-ost. But I would be wary of reproducing delete+replace.

[shlomi-noach]

This bug is true not only for PRIMARY KEY but more specifically to the chosen migration's UNIQUE KEY. The migration typically uses PK for iteration, but not necessarily so. It can utilize another UNIQUE KEY in the event PK does not exist, or PK is being modified.

[shlomi-noach]

#520 follows up on this issue.

[shlomi-noach]

The fix was sent to testing. Will update in a few days.