Recovering from a bad UPDATE statement

Did you just run an UPDATE against your 10 million row users table without a WHERE clause?  Did you know that in MySQL 5.5 that sometimes you can recover from a bad UPDATE statement?  This is possible if you are running in binlog_format=ROW !

Imagine this scenario:

CREATE TABLE `t1` (
 `c1` int(11) NOT NULL AUTO_INCREMENT,
 `c2` varchar(10) NOT NULL,
 PRIMARY KEY (`c1`)
) ENGINE=InnoDB;
INSERT INTO `t1` (`c2`) VALUES ('michael'), ('peter'), ('aamina');

We run an accidental UPDATE statement that changes a row:

UPDATE `t1` SET `c2` = 'tom' WHERE `c1` = 2;

If we examine this UPDATE using the Binary Logging format of STATEMENT the entry would look like:

# at 464
#121019 16:10:42 server id 1 end_log_pos 532 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1350677442/*!*/;
BEGIN
/*!*/;
# at 532
#121019 16:10:42 server id 1 end_log_pos 638 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1350677442/*!*/;
UPDATE `t1` SET `c2` = 'tom' WHERE `c1` = 2
/*!*/;
# at 638
#121019 16:10:42 server id 1 end_log_pos 665 Xid = 19
COMMIT/*!*/;

Now let’s see what it looks like if you were using the Binary Logging format of ROW:

# at 665
#121019 16:12:34 server id 1 end_log_pos 733 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1350677554/*!*/;
BEGIN
/*!*/;
# at 733
# at 777
#121019 16:12:34 server id 1 end_log_pos 777 Table_map: `test`.`t1` mapped to number 39
#121019 16:12:34 server id 1 end_log_pos 827 Update_rows: table id 39 flags: STMT_END_F

BINLOG '
MrSBUBMBAAAALAAAAAkDAAAAACcAAAAAAAEABHRlc3QAAnQxAAIDDwIKAAA=
MrSBUBgBAAAAMgAAADsDAAAAACcAAAAAAAEAAv///AIAAAADdG9t/AIAAAAFdmFkaW0=
'/*!*/;
### UPDATE test.t1
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='tom' /* VARSTRING(10) meta=10 nullable=0 is_null=0 */
### SET
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='vadim' /* VARSTRING(10) meta=10 nullable=0 is_null=0 */
# at 827
#121019 16:12:34 server id 1 end_log_pos 854 Xid = 21
COMMIT/*!*/;

Of interest is that the old and new versions of the changed column is stored in the binary log — in this example in @2.

This example looks trivial to fix when it is just one row changed — you don’t need anything fancy to recover from this failure case and can just run another UPDATE reversing the old and new values and be done.  But like I started out saying, what if this UPDATE statement affected all rows in your very important big table? It might look more like this:

UPDATE `t1` SET `c2` = 'matt';

And when we examine the binary log (still in binlog_format=ROW):

# at 854
#121019 16:13:38 server id 1 end_log_pos 922 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1350677618/*!*/;
BEGIN
/*!*/;
# at 922
# at 966
#121019 16:13:38 server id 1 end_log_pos 966 Table_map: `test`.`t1` mapped to number 39
#121019 16:13:38 server id 1 end_log_pos 1062 Update_rows: table id 39 flags: STMT_END_F

BINLOG '
crSBUBMBAAAALAAAAMYDAAAAACcAAAAAAAEABHRlc3QAAnQxAAIDDwIKAAA=
crSBUBgBAAAAYAAAACYEAAAAACcAAAAAAAEAAv///AEAAAAHbWljaGFlbPwBAAAABG1hdHT8AgAA
AAV2YWRpbfwCAAAABG1hdHT8AwAAAAZhYW1pbmH8AwAAAARtYXR0
'/*!*/;
### UPDATE test.t1
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='michael' /* VARSTRING(10) meta=10 nullable=0 is_null=0 */
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='matt' /* VARSTRING(10) meta=10 nullable=0 is_null=0 */
### UPDATE test.t1
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='vadim' /* VARSTRING(10) meta=10 nullable=0 is_null=0 */
### SET
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='matt' /* VARSTRING(10) meta=10 nullable=0 is_null=0 */
### UPDATE test.t1
### WHERE
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='aamina' /* VARSTRING(10) meta=10 nullable=0 is_null=0 */
### SET
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='matt' /* VARSTRING(10) meta=10 nullable=0 is_null=0 */
# at 1062
#121019 16:13:38 server id 1 end_log_pos 1089 Xid = 22
COMMIT/*!*/;

As an exercise to the reader you would then need to:

• Stop database activity (set read_only or put up a holding page on your web server, etc) so that changes are no longer hitting your database. You need time to think.
• Take a database backup if you can.
• Identify which binary log has the offending UPDATE statement.
• Parse the binary log using mysqlbinlog -vv  and match on the old version of @1 to the new version of @1, old version of @2 to the new version of @2, etc.
• Reverse the entries row entries and create new UPDATE statements that filter on the new version and SET the old version.

I hope this helps someone out there!

Note: this recovery example ONLY works when binlog_format=ROW — MIXED doesn’t help as in my testing as MySQL chooses to binlog UPDATEs in STATEMENT format when in MIXED mode.