DbUp / dbup-sqlite

SQLite provider for DbUp

MIT License

0 stars 1 forks source link

File locked when running SQLite.Helpers.TemporarySQLiteDatabase.Dispose() #1

Closed flatrick closed 1 month ago

flatrick commented 2 years ago

Issue

When Dispose() attempts to delete the temporary file, the user recieves an I/O error stating that the file is locked.

This issue seems to be isolated to Windows (10); when I ran the same code under Linux, the file is able to be deleted without any issues.

Cause

Starting with dotNet 6.0, all SQLite connections are by default pooled for Microsoft.Data.Sqlite

Possible solutions

Run SqliteConnection.ClearAllPools() before disposing to ensure no locks on the file
Set the option Pooling=false for the connection string for the class SQLite.Helpers.TemporarySQLiteDatabase to disable pooling entirely for the file

links regarding the issue/change

https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-6.0/breaking-changes#connection-pool https://github.com/dotnet/efcore/issues/27139

SoorajKothari commented 2 years ago

Use Read Only=True; in your connection string to open the file in read-only mode. This will allow you to open file even while SQLite has it opened/Connection opened. Works perfectly for me on .NET6

flatrick commented 2 years ago

Use Read Only=True; in your connection string to open the file in read-only mode. This will allow you to open file even while SQLite has it opened/Connection opened. Works perfectly for me on .NET6

How is opening the database in a read only mode a fix? The provided suggestions retain the expected behavior, but it should be added to the helper-class

SoorajKothari commented 2 years ago

Use Read Only=True; in your connection string to open the file in read-only mode. This will allow you to open file even while SQLite has it opened/Connection opened. Works perfectly for me on .NET6

How is opening the database in a read only mode a fix? The provided suggestions retain the expected behavior, but it should be added to the helper-class

In the cases when you want to only read from the db and perform no write operation you can open it in read-only mode. That would allow you to access the targeted file. By using any of above suggestion until you don't close the connection you cannot open the file. Also Pooling=true suggestion doesn't seems to be working at least for me on .NET6. Cheers its just another solution that I have purposed.

droyad commented 1 month ago

This seems to have been fixed at some stage, SqliteConnection.ClearAllPools() is now called on dispose