1

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?

Improve this question
4
  • Please the show create table of the following: 1) the table with the current historical data whose values need fixing, 2) the table who has the correct historical values. – RolandoMySQLDBA Nov 14 '14 at 17:47
  • They both have the exact same schema. The relevant issue is the data contained in the rows with the same ID. For example, 2,000,000 "bank_account_id" (not the primary key for the table) were changed to "0". I need to re-insert the recovered 2,000,000 "bank_account_id" without changing their auto-incremented primary key. FYI, I don't want a table-specific solution. I need a generic solution in the event that any dev runs a poorly constructed script on any of the tables in the future. Thanks in advance. – humble_coder Nov 14 '14 at 21:16
  • 1
    How about loading your 2 million into a temp table and then INSERT ... SELECT {} FROM {} ... ON DUPLICATE KEY UPDATE? Would that suffice? – eroomydna Nov 14 '14 at 23:15
  • @eroomydna: Ha! That's actually what ended up deciding on. It would obviously be nice to have a simple import mechanism (similar to --force) that forced replacement rather than ignoring. But this is working for now. Feel free to post it as an official answer and I'll accept it. And thanks again. – humble_coder Nov 14 '14 at 23:27
0

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.

Improve this answer
2
  • I'm not sure what you mean. From my own experience, REPLACE deletes the duplicate row, and reinserts. This is a problem for auto-incremented tables. Also, per the documentation: "REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted." – humble_coder Nov 15 '14 at 16:05
  • 1
    Added some examples for REPLACE to illustrate. – eroomydna Nov 16 '14 at 13:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.