DuckDBOpen failed: IO Error: File is already open

[kannenberg]

When we try to open the connection after closing it, the database file remains locked.

Version: 0.10.2

DuckDB.NET.Data.DuckDBException HResult=0x00000001 Message=DuckDBOpen failed: IO Error: File is already open in C:\Workspace\platform-v4\src\Api\bin\Debug\net7.0\Api.exe (PID 6876) Source=DuckDB.NET.Data StackTrace: at DuckDB.NET.Data.Internal.ConnectionManager.GetConnectionReference(DuckDBConnectionString connectionString) at DuckDB.NET.Data.DuckDBConnection.Open() at Data.Sql.CustomConnection.Open() in C:\Workspace\platform-v4\src\Data\Sql\CustomConnection.cs:line 47 at Data.Sql.CustomConnection.ExecuteCommand(String query) in C:\Workspace\platform-v4\src\Data\Sql\CustomConnection.cs:line 167

This exception was originally thrown at this call stack: [External Code] Data.Sql.CustomConnection.Open() in CustomConnection.cs Data.Sql.CustomConnection.ExecuteCommand(string) in CustomConnection.cs

[kannenberg]

This code worked perfectly in release 0.9.2, but in release 0.10.2 it returns the error mentioned in this ticket.

using DuckDB.NET.Data;

var connectionStringReadWrite = $"Data Source=C:\\Temp\\mydatabase.db;access_mode=read_write";
var connectionStringReadOnly = $"Data Source=C:\\Temp\\mydatabase.db;access_mode=read_only";

var duckDBConnectionReadWrite = new DuckDBConnection(connectionStringReadWrite);
var duckDBConnectionReadOnly = new DuckDBConnection(connectionStringReadOnly);

// Open a read/write connection
duckDBConnectionReadWrite.Open();

// Open a read/only connection
duckDBConnectionReadOnly.Open();

// Execute a DDL command
var command1 = duckDBConnectionReadWrite.CreateCommand();
command1.CommandText = $"CREATE TABLE mytable_{DateTime.Now.Ticks}(foo INTEGER, bar INTEGER);";
_ = command1.ExecuteNonQuery();

// Close read/write connection
duckDBConnectionReadWrite.Close();
duckDBConnectionReadWrite.Dispose();

// Execute a SQL command
var command3 = duckDBConnectionReadOnly.CreateCommand();
command3.CommandText = $"SELECT 1;";
_ = command3.ExecuteReader();

// Close read only connection
duckDBConnectionReadOnly.Close();
duckDBConnectionReadOnly.Dispose();

// Reopen a Read/Write Connection, but here returned a IO Exception
duckDBConnectionReadWrite.Open();

var command2 = duckDBConnectionReadWrite.CreateCommand();
command2.CommandText = $"CREATE TABLE mytable_{DateTime.Now.Ticks}(foo INTEGER, bar INTEGER);";
_ = command2.ExecuteNonQuery();

duckDBConnectionReadWrite.Close();
duckDBConnectionReadWrite.Dispose();

Exception:

DuckDB.NET.Data.DuckDBException
  HResult=0x00000001
  Message=DuckDBOpen failed: IO Error: File is already open in 
C:\Temp\DuckDBFileLock\bin\Debug\net7.0\DuckDBFileLock.exe (PID 79256)
  Source=DuckDB.NET.Data
  StackTrace:
   at DuckDB.NET.Data.Internal.ConnectionManager.GetConnectionReference(DuckDBConnectionString connectionString)
   at DuckDB.NET.Data.DuckDBConnection.Open()
   at Program.<Main>$(String[] args) in C:\Temp\DuckDBFileLock\Program.cs:line 35

[Giorgi]

I don't know why it worked in 0.9.2 but you need to Dispose command and reader objects, or even better, put them in using. In such case, it works as expected.