CODEWITHSUNDEEP
unit-testingsqlitenunitioexception
Stewart Johnson
Stewart Johnson

Reputation: 14449

NUnit [TearDown] fails -- what process is accessing my files?

Final Edit: I found a solution to the problem (at the bottom of the question).

I've got an Nunit problem that's causing me grief. Edit: actually it looks more like a SQLite problem, but I'm not 100% certain yet.

My TestFixture has a setup that generates a random filename that's used as a SQLite database in each of my tests.

[Setup]
public void Setup()
{
    // "filename" is a private field in my TestFixture class
    filename = ...; // generate random filename
}

Each of my tests use this construct in each method that accesses the database:

[Test]
public void TestMethod()
{
    using (var connection = Connect())
    {
        // do database activity using connection

        // I've tried including this line but it doesn't help
        // and is strictly unnecessary:
        connection.Close();
    }
}

private DbConnection Connect()
{
    var connection = DbProviderFactories.GetFactory("System.Data.SQLite").CreateConnection();
    connection.ConnectionString = "Data Source=" + filename;
    connection.Open();
    return connection;
}

So that one helper method Connect() is used by all the methods. I'm assuming that the using() { } construct is calling Dispose() on the connection at the end of TestMethod() and freeing up the connection to the SQLite database file.

The problem I have is in my [TearDown] method:

    [TearDown]
    public void Cleanup()
    {
        File.Delete(filename); // throws an IOException!
    }

With every test I get an exception:

System.IO.IOException: The process cannot access the file 'testdatabase2008-12-17_1030-04.614065.sqlite' because it is being used by another process.

All of the tests fail when they get to the [TearDown], so I end up with a directory full of temporary databse files (one per test, each with a different name) and a whole bunch of failed tests.

What process is accessing the file? I don't get how a second process could be accessing the file. The connection has completely gone out of scope and been Dispose()d by the time I'm trying to delete the file, so it can't be something SQLite related. Can it?

Note that I get the same result if I run all the tests or just a single test.

Update: So I tried Dispose()ing of my DbCommand objects as well, since I wasn't doing that (I assumed that every other ADO.NET provider that Dispose()ing the DbConnection also Dispose()s any commands on that connection.) So now they look like:

[Test]
public void TestMethod()
{
    using (var connection = Connect())
    {
        using (var command = connection.CreateCommand())
        {
        // do database activity using connection

        }
    }
}

It didn't make any difference -- the File.Delete() line still throws an IOException. :-(

If I remove that one line in [TearDown] then all my tests pass, but I'm left with a whole bunch of temporary database files.

Another Update: This works just fine:

var filename = "testfile.sqlite";
using (var connection = BbProviderFactories.GetFactory("System.Data.SQLite").CreateConnection())
{
    connection.ConnectionString = "Data Source=" + filename;
    connection.Open();
    var createCommand = connection.CreateCommand();
    createCommand.CommandText =
        "CREATE TABLE foo (id integer not null primary key autoincrement, bar text not null);";
    createCommand.ExecuteNonQuery();
    var insertCommand = connection.CreateCommand();
    insertCommand.CommandText = "INSERT INTO foo (bar) VALUES (@bar)";
    insertCommand.Parameters.Add(insertCommand.CreateParameter());
    insertCommand.Parameters[0].ParameterName = "@bar";
    insertCommand.Parameters[0].Value = "quux";
    insertCommand.ExecuteNonQuery();
}
File.Delete(filename);

I don't understand!

Update: Solution found:

    [TearDown]
    public void Cleanup()
    {
        GC.Collect();
        File.Delete(filename);
    }

I ran the unit tests through the debugger, and when the [TearDown] method starts there are definitely no references to the SQLite DbConnection around any more. Forcing a GC must clean them up though. There must be a bug in SQLite.

Upvotes: 19

Views: 5050

Answers (11)

flipm0de
flipm0de

Reputation: 183

Thanks for the posted answer at the bottom. I was digging for hours for exactly the same case and 

GC.Collect ();
GC.WaitForPendingFinalizers ();

did the trick.

Upvotes: 9

TTT
TTT

Reputation: 2375

Call static method

SqliteConnection.ClearAllPools()

After this call the database file is unlocked and you can delete the file in the [TearDown].

Upvotes: 1

jumpalongjim
jumpalongjim

Reputation: 353

I know this answer is over a year late, but for anyone reading in the future...

I had a similar problem to yours - attempting to delete test databases in between tests failed because of the SQLite database file remaining open. I traced the problem in my code to a SQLiteDataReader object not being explicitly closed.

SQLiteDataReader dr = cmd_.ExecuteReader();
while (dr.Read())
{
    // use the DataReader results
}
dr.Close();  //  <-- necessary for database resources to be released

Upvotes: 1

Sebastian Edelmeier
Sebastian Edelmeier

Reputation:

Thanks for pointing this out!

The problem is not related to SQLite, but rather to memory management in general. After your test has run, the objects pointing to files have no scope anymore, but they still exist in memory.

Hence, there's still references to the files and you can't delete / move them.

Upvotes: 0

Patrick Desjardins
Patrick Desjardins

Reputation: 140903

Tear down is executed after each test.

This attribute is used inside a TestFixture to provide a common set of functions that are performed after each test method is run.

You should try to delete them all with TestFixtureTearDown:

    [TestFixtureTearDown]
    public void finish()
    {
        //Delete all file
    }

Maybe one test is using the file that you try to delete in an other test. <-- [Stewart] As I stated in the question, it happens when I run only one test, so this isn't possible.

Update You do not give enough information about what you are doing. Have you try to clean up all the Test file with only 1 test in your test file and try it? [Stewart] Yes. If it works [Stewart] (it doesn't) then it's that you have multiple test problem (they access each other). You need to cut down the problem to find the source. Then, come back here we will help you. for the moment it's only guessing that we can give you. [Stewart] I have already done these cut-downs of the problem that you suggest, you'll find that they're in my original question!

Upvotes: 0

Steven A. Lowe
Steven A. Lowe

Reputation: 61242

try calling Close on the dbconnection

make sure the sqllite process is terminated

you can see what process has your file locked with the Unlocker (free) utility

this may be a 'known' issue with SqlLite; the forum banter suggests closing the connection and disposing the command, and suggests that this will be fixed in a future version (since this behavior is not consistent with other ADO providers)

Upvotes: 1

Patrick Desjardins
Patrick Desjardins

Reputation: 140903

What do you use to open your database ? Do you use the ADO 2.0 connector from here. If have an application that use it and I can do multiple connection with it (close/open). If you do not use this connector, you might give a try. What does your method Connect() return?

Upvotes: 0

Andrew Rollings
Andrew Rollings

Reputation: 14571

Is it possible that the file is in the process of being closed (I.e. SQLLite doesn't release it immediately)?

You could try putting the db close in a delay loop, (maybe one second between each attempt), and only throwing the exception after a few (5 or so) iterations through the loop.

Upvotes: 0

Gishu
Gishu

Reputation: 136633

First step would be to find who is holding a handle to the file in question.

Get a copy of Process Explorer Run it. Press Ctrl+F and enter the name of the file. It would show you a list of processes accessing it.

Upvotes: 0

Roger Lipscombe
Roger Lipscombe

Reputation: 91885

Do you have Antivirus running? Some AV products scan the file when they see them being closed. If the AV software still has the file open when you come to delete it, you'll see this problem. You could retry the delete after a small delay.

I've also seen this happen with search indexers.

Upvotes: 0

tvanfosson
tvanfosson

Reputation: 532575

I'd need to see your filename creation logic, but it is possible that you are opening the file to create it but not closing it once it is created. I think if you use System.IO.Path.GetTempFileName() it will just create the file and return you the name of the file with the file closed. If you are doing your own random name generation and using File.Open, you'll need to make sure it's closed afterwards.

On another note, I heartily recommend pursuing a mocking strategy to abstract out the database rather than reading/writing from an actual database in your unit tests. The point of unit tests is that they should be very fast and file I/O is going to really slow these down.

Upvotes: 0

Related Questions