I have a .net test application which creates table, uses them, then drops them, many times.
When I run this application against a Firebird 3.0.2 database with Firebird ADO.Net Data Provider 5.9.1, it fails at soon as it has to create a table with the same name than a previously dropped one: table does already exist!
Restarting the application dodge the trouble, but I do not want to restart it after each test.
This question is very similar but is hanging at the drop instead, and use directly the Firebird isql
tool instead of a .Net application.
Is there a way to actually drop the tables in Firebird without restarting the application?
This application tests many other databases, without this issue: SQL-Server, SQL-Server Compact Edition, MySql, SQLite, Oracle, PostgreSQL.
Here is a MCVE failing for Firebird. Replace the two first lines with suitable code for having a connection string. All other code is just Firebird ADO.Net data provider and NUnit. It does not fail exactly as my actual application, but I think it is the same underlying trouble.
[Test]
public void CreateSelectDrop()
{
var cfg = TestConfigurationHelper.GetDefaultConfiguration();
var cnxStr = cfg.Properties[Environment.ConnectionString];
using (var cnx = new FbConnection(cnxStr))
{
cnx.Open();
using (var tran = cnx.BeginTransaction())
{
using (var cmd = cnx.CreateCommand())
{
cmd.Transaction = tran;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "create table test (id int not null primary key)";
cmd.ExecuteNonQuery();
}
tran.Commit();
}
}
using (var cnx = new FbConnection(cnxStr))
{
cnx.Open();
using (var tran = cnx.BeginTransaction())
{
using (var cmd = cnx.CreateCommand())
{
cmd.Transaction = tran;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "insert into test (id) values (1)";
cmd.ExecuteNonQuery();
}
tran.Commit();
}
}
using (var cnx = new FbConnection(cnxStr))
{
cnx.Open();
using (var tran = cnx.BeginTransaction())
{
using (var cmd = cnx.CreateCommand())
{
cmd.Transaction = tran;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select id from test";
using (var reader = cmd.ExecuteReader())
{
Assert.IsTrue(reader.Read());
Assert.AreEqual(1, reader.GetInt32(0));
Assert.IsFalse(reader.Read());
}
}
tran.Commit();
}
}
using (var cnx = new FbConnection(cnxStr))
{
cnx.Open();
using (var tran = cnx.BeginTransaction())
{
using (var cmd = cnx.CreateCommand())
{
cmd.Transaction = tran;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "delete from test";
cmd.ExecuteNonQuery();
}
tran.Commit();
}
}
using (var cnx = new FbConnection(cnxStr))
{
cnx.Open();
using (var tran = cnx.BeginTransaction())
{
using (var cmd = cnx.CreateCommand())
{
cmd.Transaction = tran;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "drop table test";
cmd.ExecuteNonQuery();
}
tran.Commit();
}
}
}
Only selecting from the table was not enough. The trouble appeared only after I have added the delete to the test. It fails at the last transaction commit, the one of the drop, with message:
FirebirdSql.Data.FirebirdClient.FbException : lock conflict on no wait transaction
unsuccessful metadata update
object TABLE "TEST" is in use
----> FirebirdSql.Data.Common.IscException : lock conflict on no wait transaction
unsuccessful metadata update
object TABLE "TEST" is in use
at FirebirdSql.Data.FirebirdClient.FbTransaction.Commit()
at NHibernate.Test.DialectTest.FirebirdDialectFixture.CreateSelectDrop()
According to Nathan Brown in a Github discussion, this trouble seems bounded to Firebird ADO.Net Data provider only. He has narrowed it to switching from its 2.7.7 version to 3.0.0.