I’ve just run into an interesting bug.
Our application is dynamically creating databases based on some rules. When creating a new database, the code should verify the database already exists and, if it exists, validate the physical structure and, partially, the logical schema.
A recent change request from our customer was to allow the user to decide what the application should do when a database is to be created, but it already exists (that is usually a sign of a previous failed attempt to create the database). Our design simply used the existing database, however the customer wanted the user to be allowed to choose to delete the existing database.
The following sequence triggers the bug:
- decide a new database should be created and it should be named DB_ARCHIVE_A
- detect the fact the DB_ARCHIVE_A already exists
- open a connection to it to read some data from a table
- dispose the connection (at this point, the connection pool manager still keeps it open!)
- the user decides to delete the existing database
- drop the database
- create it
- connect to the newly created database
At this point, the connection pool will return the old connection from the pool. The moment the code tries to execute an SQL statement, an exception is raised, because the connection is invalid (it points to a database that no longer exists).
Unfortunately the exception is not always raised; we’ve managed to reproduce it only on some virtual machines, but never on the development workstations.
We’ve had two choices to fix this bug:
- use a special connection string when validating the existing database (add the pooling=false keyword, see http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx)
- clear the connections pool after deleting the database (use SqlConnection.ClearAllPools() )
We’ve implemented the second choice (it’s easier to implement, and the code sequence will seldom execute – I think only 5 of our users are using this databases creation feature).