Database Reset Options for Test Automation

JUN

Database Reset Options for Test Automation

06 JUN 2012 | Posted in database test automation, testing | Author Brett Delle Grazie |

4 Comments

Some of our clients have legacy systems where the most effective test strategy is to use scenarios in the database. A key challenge when automating these tests is finding a mechanism to reset the database back to its pre-test state.

An additional consideration is the potential use of multiple reset points, for example:

Per Test
Per Test Suite or Group
Per Run
Per Phase (non-agile)

Here are a number of different approaches for resetting the database that I have used in different circumstances. Included are some interesting technical solutions specific to certain technologies, including legacy mainframe systems.

Inside the database:

Manual update / delete (i.e. reverse the test manually)
Transaction rollback
- Potentially with multiple rollback points to match the reset points above
Schema manipulation
- Database specific, similar to copy‑on‑write solution below but within the database itself. Likely to require intimate knowledge of database and schema
Database backup / restore mechanism
- Database specific

Outside the database:

The techniques below require database restarts to reset the data, usually a time and CPU expensive operation resulting in fewer reset points.

Copy: A physical copy of the database (space permitting)
Snapshotting (LVM, BTRFS, ZFS): With the database shutdown the file system is snapshotted. The snapshot is reset when the test run is finished. This is essentially the same as copy‑on‑write below but the OS handles the majority of the complexity.
Copy‑on‑write (Fuse or similar): The file system housing the database is made read only. A temporary read/write file system is mounted on top and then discarded when the test run is complete, resetting the database back to it is original state.

Comments

Aaron (not verified)
06 Jun 2012 18:42

For transactions, this can create a bad scenario, which I have experienced a number of times with MySQL. As the actual transaction commit is not being tested, this leaves a hole. The transaction itself will always suceed without a commit, giving a false positive.

I would also recommend :

A) VMWare snapshots at the specific points mentioned above. A simple call to the VMware command line can rollback to any snapshotted point. This option can also scale easily to multiple database versions and OSs.

B) Use vendor specific technology. oracle flashback is perfect for this, with only a couple of lines of scripting the database can be rolled backwards ( or forwards ) to any commit point.

If the application is greenfield, and a production scheme does not exist, then a full rebuild is recommended. This adds minimal time to a build compared to more technical solutions, and ensures a current schema build for the application.

If there is an existing schema, then two scenarios need to be handled, at least from the CI perspective. One is rolling out the schema changes and ensuring data integrity. The other is the migration of data between versions of the schema.

When it comes to testing the database, there are 4 requirements for data.

1) Clean database with no data
2) Clean database with static data
3) Clean database with functional data (ability to run the application beyond unit tests)
4) Production mirror, for upgrade and migration testing of real data.

A further consideration is software upgrade. Down the road scenarios may need to be considered where multiple database versions are being supported, or a new version is in need of testing side-by-side, for example oracle releases, which often fix bugs that people have worked around, or remove/replace some functionality.

Brett Delle Grazie
06 Jun 2012 19:14

Hi Aaron,

Thanks for the additional information, the specific context I was examining didn't have virtual machines as an option so I was looking purely within the OS.

I consider (B) a subset of database backup / restore which is normally specific to a database technology anyway.

I've come across MySQL connections defaulting to auto-commit or using a non-ACID compliant engine (no longer the default for many versions) resulting in transactions never rolling back. Can you elaborate a little bit more on the specific issue you described?

I've also come across a misconfigured stored procedure which didn't accept the transaction context offerred by the caller (iSeries specific issue with the stored procedure definition).

Thanks again,

Brett

Aaron (not verified)
06 Jun 2012 19:42

Apologies, my mind blotted out the word 'legacy' it seems.

One of the false positive scenarios I encountered was with MyIsam on MySQL. A java thick client with spring and hibernate.

When unit testing, the devs executed a spring/hibernate transaction, without calling commit, and then rolled back in the test tear down. Since the data was not committed, MySQL was lazy and did not validate constraints or all database rules, the transaction did not have the ability to throw as many errors as it could have done. This was confirmed using standard SQL without spring and hibernate also.

Which legacy architecture are we talking here? Im guessing that the base OS is Linux from your suggestions, which offers a few more opportunities.

The crux is normally the data size, and how it is constructed. The sickening avoidance of constraints by devs and dbas means that single tables or chunks of data can be copied around and used in isolation.

From the view of a legacy app for retrospective testing, then database auditing features to track DML and DDL statements can be used to harvest only specific chunks of data relevant to test/data scenario, and not importing anything else.

With regards to Flashback, I would not classify this within backup/restore for a few reasons.
1) it's realtime, and instantaneous - no need for copies, backups hanging around.
2) individual users can flashback parts of their instance.
3) individual tables can be flashback'd, a massive performance improvement
4) supports multiple rollback points within a single instance - no need for multiple copies

A further consideration would be linking.
If the number of tables being modified are small, compared to the verbally size of the database, then having a central database with 'static data' with a read only connection, and a local copy with the updateable tables and synonyms to all other tables on the central server means only the small local set needs to be rolled back.

The concept of editions, session scoped transactions ( as opposed to a standard set of crud operations in a transaction ), virtual tables, redirect able queries ( send a table request to another table ), updateable views and especially materialised views and their ilk create a large arsenal to decrease time and improve efficiency.

Aaron (not verified)
06 Jun 2012 19:52

Ah forgot to ask, if changing the FS ( to ZFS or whatever ) is possible, then is there a scope for installing virtual box or some other virtual environment? Even the embedded Linux kernel may offer advantages.

Post new comment

Brett Delle Grazie

Brett's profile and latest posts

WHAT WE'RE SAYING

MAY

Trust

While I was working with one of my clients a few years a go, I was given a book to read by the CEO. "The Speed of Trust". I read the book with a healthy dose of scepticism having read many management books in the past. But this book resonated with the core principles of Agile for me.

10 MAY

Skyfall - or falling from the cloud

29 APR

Building Eden - Or how to live or die based on your project planning approach

27 APR

DevOps on Windows - Fun Times installing .NET 4 over WinRM / WinRS on Windows Server 2008R2