Seeking input for a new tool to verify MySQL upgrades
I’ve had several customers in the last week or so who need a way to verify that their application will work well after an upgrade. I’m seeking input on a new tool to help with MySQL upgrades. Please add comments, either here or on the bug report, or on the mailing list topic.
If someone wants to sponsor this work, that would also be welcomed.
In my “SQL Antipatterns” talk, I had a subject that’s pretty close to this. I didn’t offer any solutions, but I suggested that database development habits should include regression-testing the database itself (not just application code that acts on the database).
For example:
Tables, Views, Columns: verify that they exist. Verify they have the columns you expect. Verify tables and columns you dropped during an upgrade are actually gone.
Constraints: verify that an update is aborted by a constraint when it should be. NOT NULL, check constraints, foreign key constraints. Cascading behaviors.
Triggers: verify that triggers work to implement constraints, provide cascading operations, logging, etc.
Stored Procedures: test parameter signatures, test code paths, etc. just as you would a non-SQL procedure.
Bootstrap Data: verify contents of lookup tables or other initial data that is present in your “empty” database.
Queries: test all queries that are executed from your application.
ORM classes: test all ORM classes, subclasses, aggregate classes, etc. just as you would any other code.
Bill Karwin
30 May 09 at 11:39 am
Your customers may not realize the value of this tool. It is _extremely_ valuable. We have an ugly version of this tool (it works, but the code is not in good shape to be published). It is used to validate a binary every time we change MySQL. It gives us much more faith in our changes.
It found many problems during our upgrade from MySQL 4 to 5. It is much better to find these problems before an upgrade.
Mark Callaghan
31 May 09 at 11:38 pm
[...] Like Baron Schwartz’s Maatkit, for example. Baron announced this week that he is writing a book about Maatkit, and also seeking input for a new tool to verify MySQL upgrades. [...]
Log Buffer #149: a Carnival of the Vanities for DBAs | Pythian Group Blog
5 Jun 09 at 1:02 pm