EmergencyEMERGENCY? Get 24/7 Help Now!

Recovering from a bad UPDATE statement

 | October 19, 2012 |  Posted In: Insight for DBAs, Insight for Developers, MySQL

PREVIOUS POST
NEXT POST

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:

We run an accidental UPDATE statement that changes a row:

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

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

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:

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

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.

 

PREVIOUS POST
NEXT POST
Michael Coburn

Michael joined Percona as a Consultant in 2012 after having worked with high volume stock photography websites and email service provider platforms. With a foundation in Systems Administration, Michael enjoys working with SAN technologies and high availability solutions. A Canadian, Michael currently lives in Costa Rica with his wife, two children, and two dogs.

5 Comments

  • Hi, I add this feature for mysqlbinlog, you can see: http://bugs.mysql.com/bug.php?id=65178, or http://mysql.taobao.org/index.php/Patch_source_code#Add_flashback_feature_for_mysqlbinlog
    This patch is based Percona 5.5.18

    I add a option “-B/–flashback” for mysqlbinlog, it can do the same thing like you said.

    you can try my patch. 🙂

  • Thanks for the link P. Linux! Have you tried to submit this feature request to Percona Server as well?

    https://launchpad.net/percona-server

  • yes, this is a quite obvious exploitation of the fact that row based binlog stores the before image of a modified (deleted or updated) row in the binlog.

    However this will soon be history. MySQL 5.6 adds a new variable binlog_row_image:

    http://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#sysvar_binlog_row_image

    When this is set to “minimal”, the binlog will only contain the PK field of the before image (assuming each table has a PK, which is pretty reasonable).

    This new behavior is meant as an optimization, which it certainly is. Just imagine a mass delete from a wide table. Full before image would need much more I/O bandwidth for logging and log shipping than logging just the PK. One however must be aware that enabling this new feature has side effects.

    PS: for MySQL cluster the above trick will not work anyway. It defaults to log minimal before images (–ndb-log-updated-only defaults to TRUE) and what’s more: it defaults to log updates as inserts (–ndb-log-update-as-write defaults to TRUE). See http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-replication-conflict-resolution.html

Leave a Reply

 
 

Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.