I have many tests running against a MySQL database that is pre-packed with schemas and example data from some SQL files. A few of these tests, throughout their run, also create new data within the database.

Normally, tests have the effect of cleaning after themselves (and therefore not polluting the database atmosphere for other tests). However, it seems that a few of these tests aren't fully doing this, and therefore abandoning additional/modified records where they should not.

Because of the complex group of code that's being examined, it is not achievable to possess a single transaction running for the whole test, and so i can't simply have MySQL roll everything back (you will find both multiple cursors and multiple duplicated DB servers involved, among additional factors).

Let me possess a method of easier determining these tests which are polluting the DB, but since it is allowable for tests to create towards the DB (as lengthy because they remove things later on), I can not just look whatsoever modifications towards the DB - I want just the effective changes, with eliminating-out modifications removed.

One thought I'd was when there have been an easy method to compare the items in one table against another, I possibly could achieve this after running each test, evaluating the items in a table initialized using the fixture towards the contents on the table following the test.

Any suggestions about this?

Some various suggestions that I have become through other channels to date:

  • [cde] - this is almost ideal for me, with the exception that it only works best for MyISAM tables (we use InnoDB).

  • [cde] - this gives CHECKSUM TABLE, that might act as a simplistic comparison. Basically aren't able to find anything better, this may suffice.

Prior to the test you can duplicate all the tables (ie CREATE TABLE tmpTableA Choose * FROM tableA) after which join against them following the tests to determine what new rows you will find while using statement

SHOW TABLE STATUS

You might perform a dump on the table prior to the tests, then following the tests, after which perform a diff around the two dumps.