(MySQL) Need an “in-place” REPLACE for adjusting historical data

Question

I have a table with around 6,000,000 records. It is backed up every few hours. Recently a script ran that cascade updated about 2,000,000 of the older records to bad values. I have extracted those 2,000,000 records from the backup and have them ready for re-insert, in the form of "INSERT INTO table (col, col) values (val, val)"

Now, I need a mechanism to insert those 2,000,000 records, replacing the bad data with the historical data, while maintaining existing IDs (meaning REPLACE is not an option -- as it deletes on duplicate and increments the ID). Also, I need to do this without interrupting server access for too long (which means DROP TABLE, then --force trickery is not an option).

I'm sure I'm missing something, but I can't figure out for the life of me how to replace in-place within the current parameters. Thoughts?

Answer 1

Using the INSERT (SELECT)...ON DUPLICATE KEY UPDATE should permit you to visit these rows and update them with original values. Also you're not correct about REPLACE. This is the mechanism used by pt-table-sync to correct data drift. The documentation details a REPLACE == DELETE AND INSERT but that's not done within the scope of AUTO_INC.

mysql> create table replace_test (id int auto_increment primary key, c1 varchar(5)) engine=innodb;

mysql> insert into replace_test(c1) values ('aaaaa'),('bbbbb'),('ccccc');

mysql> select * from replace_test;
+----+-------+
| id | c1    |
+----+-------+
|  1 | aaaaa |
|  2 | bbbbb |
|  3 | ccccc |
+----+-------+
3 rows in set (0.00 sec)

mysql> replace into replace_test (id,c1) values (2,'ddddd');
Query OK, 2 rows affected (0.00 sec)

mysql> select * from replace_test;
+----+-------+
| id | c1    |
+----+-------+
|  1 | aaaaa |
|  2 | ddddd |
|  3 | ccccc |
+----+-------+
3 rows in set (0.00 sec)

I think you expected to see something like below but that is not the behaviour.

mysql> select * from replace_test;
+----+-------+
| id | c1    |
+----+-------+
|  1 | aaaaa |
|  3 | ccccc |
|  4 | ddddd |
+----+-------+
3 rows in set (0.00 sec)

But that does not happen as you can see from above.