Setup and Teardown issues with PHPUnit Tests, mysql query logging

I have a PHPUnit test at work which does the prototypical setup and tear down to setup the data that the test needs. In this particular case, I need to create a user during setup, and remove it to restore state during the teardown phase.

The test case has a handful of tests so the setup and tear down functions happen, say, a dozen times during the course of the test. Academically, I should just leave the test as is, but during some builds, I get a failure. Digging deeper into the problem, I noticed that the user creation and user deletion was calling an API which invoked a service. For some reason, the server (which is multi-threaded) is slower in handling the delete than the insertion of the user. To avoid build failures, I’ve had to insert a 1 second delay after the delete request during the tear down. That eliminated the race condition which caused the subsequent insert to fail because of a duplicate key constraint during setup of the next test. The sporadic failure of the unit tests decreases the confidence that my developers have in the unit tests, and makes them more likely to ignore the build failures. This is unacceptable since I want them to jump at red-light build failures IMMEDIATELY.

I consider this hack more like “flailing” versus a “surgical” fix. The real problem is that the server seems to be delaying the execution of the DELETE sql until the row is no longer being read. Since we’re using InnoDB, we do not have table level locking, but a LOW PRIORITY modifier on the delete statement could still be causing the problem.

I’m using mysql 5.1.31 at the moment. To turn on query logging, I simply bounce mysqld with the –general_log=1 and –general_log_file=/var/log/mysqd_queries.log options as per: http://dev.mysql.com/doc/refman/5.1/en/query-log.html .

Now that the culprit has been identified, we need to do a couple of things.

One: do I really need to delete the user? – I might be able to use the test-chaining features of PHPUnit 4 to avoid having to delete a user which will just be recreated by the same test (as long as the test does not modify the user record). I need to ensure though that when the test case is finished, that the DB is in the same state as when I entered it.

Two: I think that using services in the setup and teardown sections of a PHPUnit test case is asking for trouble. I know what state the DB has to be in. It behooves me to do a brute force insertion of the data that I need for the test to work. The problem with this solution lies in its brittleness. As the data changes, I need to make sure the setup and tear down also change. In time, the maintenance of setup and tear down functions may be time consuming.

I’m still trying to find a good balance between flexibility, ease of implementation and reliability of running tests. What are your thoughts on handling build failures that are caused by nuances of the system rather than by actual bugs in the code?