`The connection is closed.` exception on next command after failed command

[MaceWindu]

Octonica.ClickHouseClient.Exceptions.ClickHouseException
  Message=The connection is closed.
  Source=Octonica.ClickHouseClient
  StackTrace:
   at Octonica.ClickHouseClient.ClickHouseConnection.ConnectionSession..ctor(ClickHouseConnection connection, IClickHouseSessionExternalResources externalResources)
   at Octonica.ClickHouseClient.ClickHouseCommand.<OpenSession>d__85.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Octonica.ClickHouseClient.ClickHouseCommand.<OpenSession>d__85.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Octonica.ClickHouseClient.ClickHouseCommand.<ExecuteNonQuery>d__57.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Octonica.ClickHouseClient.ClickHouseCommand.<ExecuteNonQuery>d__57.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Octonica.ClickHouseClient.ClickHouseCommand.<ExecuteNonQuery>d__57.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Octonica.ClickHouseClient.Utils.TaskHelper.WaitNonAsyncTask[T](ValueTask`1 task)
   at Octonica.ClickHouseClient.ClickHouseCommand.ExecuteNonQuery()

  This exception was originally thrown at this call stack:
    Octonica.ClickHouseClient.ClickHouseConnection.ConnectionSession.ConnectionSession(Octonica.ClickHouseClient.ClickHouseConnection, Octonica.ClickHouseClient.IClickHouseSessionExternalResources)
    Octonica.ClickHouseClient.ClickHouseCommand.OpenSession(bool, bool, System.Threading.CancellationToken)
    System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
    Octonica.ClickHouseClient.ClickHouseCommand.OpenSession(bool, bool, System.Threading.CancellationToken)
    System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
    System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(System.Threading.Tasks.Task)
    System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(System.Threading.Tasks.Task)
    Octonica.ClickHouseClient.ClickHouseCommand.ExecuteNonQuery(bool, System.Threading.CancellationToken)
    System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
    Octonica.ClickHouseClient.ClickHouseCommand.ExecuteNonQuery(bool, System.Threading.CancellationToken)
    ...
    [Call Stack Truncated]

using var cn = new ClickHouseConnection(cs);
cn.Open();
using var cmd = cn.CreateCommand();
cmd.CommandText = @"DROP TABLE unknown_table";
try
{
    cmd.ExecuteNonQuery(); // this commad throws error (table not found)
}
catch
{
}

// this command should succeed but instead "The connection is closed." exception thrown
cmd.CommandText = @"CREATE TABLE unknown_table(field Int8) ENGINE = Memory()";
cmd.ExecuteNonQuery();

[SergeyMirvoda]

@MaceWindu

Can you please rewrite your code with old school using pattern?

Looks like it's too short code block issue: your cn variable never used after line 3 and successfully closed before the second execute.

[MaceWindu]

No, this is not related to using styles.

using (var cn = new ClickHouseConnection(cs))
{
    cn.Open();
    using (var cmd = cn.CreateCommand())
    {
        cmd.CommandText = @"DROP TABLE unknown_table";
        try
        {
            cmd.ExecuteNonQuery(); // this commad throws error (table not found)
        }
        catch
        {
        }

        // this command should succeed but instead "The connection is closed." exception thrown
        cmd.CommandText = @"CREATE TABLE unknown_table(field Int8) ENGINE = Memory()";
        cmd.ExecuteNonQuery();
    }
}

[MaceWindu]

CH version: 22.6.1.1985 (same error with 22.5)

[MaceWindu]

BTW, I think it could be an issue with server. There were a lot of similar issues with your provider in 22.5, but after migration to 22.6 only this one left.

[SergeyMirvoda]

oh, I see it -- missed the unknown_table query...

@victor-sushko

here is the test case against our local server.

using Octonica.ClickHouseClient;

var cs = "Host=click;";
using var cn = new ClickHouseConnection(cs);
cn.Open();
using var cmd = cn.CreateCommand();
cmd.CommandText = @"DROP TABLE unknown_table";
try
{
    cmd.ExecuteNonQuery(); // this commad throws error (table not found)
}
catch
{
}

cmd.CommandText = @"CREATE TABLE unknown_table(field Int8) ENGINE = Memory()";
cmd.ExecuteNonQuery();

@victor-sushko can we separate "simple error" like this from "server errors" when it is mandatory to close connection to clear some state?

[SergeyMirvoda]

@MaceWindu

I think it could be an issue with server

FYI Checked against 21.3.2.5 and 21.12.3.32.

I believe it is issue with error handling routine: simple strategy -- just close connection when receive any error from server.