Xaprb

Stay curious!

Warning: upgrade MySQL without testing at your own risk

with 4 comments

Do you test your application systematically when you upgrade or reconfigure your database server? You should! Here’s a real (anonymized) story of what happens if you don’t.

When we upgraded to 5.0.62 (from 5.0.27 and 5.0.45), our code broke for queries like this:

SELECT SUM(amt) FROM daily_amt WHERE day = FROM_UNIXTIME(1222889772);

The problem here was a wrong DATE/DATETIME comparison and other bug fixes in MySQL 5.0.62; it was stricter in enforcing the comparison.

This resulted in an outage and revenue loss to the company.

Daniel and I (mostly Daniel) continue to improve mk-upgrade to make it easy and inexpensive to find these kinds of scenarios before they bite you. Don’t get caught with your pants down — next time you make any major change to your database server (upgrade, configuration change, switching to InnoDB…) follow at least this bare-minimum process:

  • collect all queries run against the server for a sufficient time
  • get a snapshot of your data
  • use mk-upgrade to validate correctness and performance
  • submit bug reports — this is a non-trivial tool, and we need your feedback

If you need help with any of the above, I know someone who can help.

Written by Xaprb

August 8th, 2009 at 6:06 pm

Posted in Maatkit,SQL,Tools

«
»

4 Responses to 'Warning: upgrade MySQL without testing at your own risk'

Subscribe to comments with RSS

  1. You forgot to mention that you can collect all your queries with mk-query-digest. :)

    Sheeri K. Cabral

    10 Aug 09 at 1:00 pm

  2. Non deterministic queries have got to be hard to catch (order by rand() etc).

    Do you think fingerprinting explain commands will help catch non-optimizer plan regressions? I’m thinking of how bad the performance degradation of parser was in early 5.0 releases (it got better).

    Morgan Tocker

    10 Aug 09 at 7:36 pm

  3. We actually have a plan for non-deterministic queries! We’ll rewrite them to be deterministic.

    I don’t think fingerprinting explain will help with non-optimizer problems, no. But query timing will. That was the first thing we implemented.

    The 5.1 regressions I’ve seen in the real world so far are optimizer-related and will show up in EXPLAIN.

    Xaprb

    10 Aug 09 at 9:37 pm

  4. What Sheeri said: you can collect all your queries with mk-query-digest :-) There’s a code snippet in mk-upgrade’s SYNOPSIS that illustrates.

    Xaprb

    10 Aug 09 at 9:38 pm

Leave a Reply