How to find changes in a table relative to an initial SQL fixture?

Question

I have a number of tests that run against a MySQL database which is pre-loaded with schemas and example data from a set of SQL files. Some of these tests, during their run, also create new data in the database.

Normally, tests are responsible for cleaning up after themselves (and thus not polluting the database environment for other tests). However, it appears that some of these tests aren't fully doing so, and thus leaving behind additional/modified records where they shouldn't.

Due to the complex set of code that is being tested, it isn't feasible to have a single transaction running for the entire test, so I can't just have MySQL roll everything back (there are both multiple cursors and multiple replicated DB servers involved, among other factors).

I'd like to have a way of more easily identifying these tests that are polluting the DB, but because it is allowable for tests to write to the DB (as long as they remove things afterwards), I can't just look at all alterations to the DB - I need only the effective changes, with canceling-out modifications removed.

One thought I had was that if there were a straightforward way to compare the contents of one table against another, I could do so after running each test, comparing the contents of a table initialized with the fixture to the contents of the table after the test.

Any suggestions on this?

Answer 1

Some various suggestions that I've gotten through other channels so far:

• CHECKSUM TABLE - this would be almost perfect for my needs, except that it only works for MyISAM tables (we use InnoDB).

• SHOW TABLE STATUS - this provides Data_length, which might work as a simplistic comparison. If I can't find anything better, this might suffice.

Answer 2

Before the test you could duplicate all of the tables (ie CREATE TABLE tmpTableA SELECT * FROM tableA) and then join against them after the tests to see what new rows there are using the statement

SELECT a.*
FROM tableA a
LEFT JOIN tmpTableA as tmp on tmp.id=a.id
WHERE tmp.id IS NULL

You could also do a dump of the table before the tests, then after the tests, and then do a diff on the two dumps.