No data written when using WriteTableAsync using Clickhouse clickhouse/clickhouse-server:latest

[aidanmorgan]

I have an interesting bug when trying to bulk insert data into a table using the WriteTableAsync function. I've tried to provide a simple reproduction here:

Database Structure:

CREATE DATABASE IF NOT EXISTS issue77_db;

CREATE TABLE IF NOT EXISTS issue77_db.issue77_table (
    words String,
    time DateTime64(9)
)
ENGINE=MergeTree()
ORDER BY time;

Using the clickhouse/clickhouse-server:latest docker image the following insert works:

using Octonica.ClickHouseClient;

await using var conn = new ClickHouseConnection("Host=localhost");
conn.Open();

var cmd = conn.CreateCommand("INSERT INTO issue77_db.issue77_table(words, time) VALUES({words}, {time})");
cmd.Parameters.AddWithValue("words", "direct_insert");
cmd.Parameters.AddWithValue("time", DateTimeOffset.UtcNow);

var res = cmd.ExecuteNonQuery();
Console.WriteLine(res);

Running a bulk insert for this however does not work, doing a SELECT * against the table returns zero rows.:

using Octonica.ClickHouseClient;

var cancelToken = new CancellationTokenSource();

await using var conn = new ClickHouseConnection("Host=localhost");
await conn.OpenAsync();

var writer = await conn.CreateColumnWriterAsync("INSERT INTO issue77_db.issue77_table(words, time) VALUES", cancelToken.Token);

await writer.WriteTableAsync(new object[]
{
    new List<string>() {"aa", "bb", "cc", "dd"},
    new List<DateTimeOffset>()
    {
        DateTimeOffset.UtcNow.Subtract(TimeSpan.FromDays(3)), 
        DateTimeOffset.UtcNow.Subtract(TimeSpan.FromDays(2)),
        DateTimeOffset.UtcNow.Subtract(TimeSpan.FromDays(1)), 
        DateTimeOffset.UtcNow
    }
}, 4, cancelToken.Token);

Checking the logs for the server, there seems to be a repeated error log printed:

<Information> TCPHandler: Client has dropped the connection, cancel the query.
<Information> executeQuery: Code: 394. DB::Exception: Query was cancelled or a client has unexpectedly dropped the connection. (QUERY_WAS_CANCELLED) (version 23.3.1.2823 (official build)) 

Switching to the yandex/clickhouse-server:latest image returns four rows.

[SergeyMirvoda]

@aidanmorgan
Hi!
Looks like another protocol change(

could you please submit the specific version numbers of both clickhouse versions.

select version()

21.3.2.5

[aidanmorgan]

Hi @SergeyMirvoda!

the yandex/clickhouse-server:latest version reports as 22.1.3.7
the clickhouse/clickhouse-server:latest version reports as: 23.3.1.2823

I did a bit of digging, and it looks like the issue is caused by a NullReferenceException that is getting swallowed internally:

System.NullReferenceException: Object reference not set to an instance of an object.
   at Octonica.ClickHouseClient.ClickHouseConnection.CreateColumnWriter(String insertFormatCommand, Boolean async, CancellationToken cancellationToken)
   at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.AsyncStateMachineBox`1.ExecutionContextCallback(Object s)
   at System.Threading.ExecutionContext.RunFromThreadPoolDispatchLoop(Thread threadPoolThread, ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.AsyncStateMachineBox`1.MoveNext(Thread threadPoolThread)
   at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.AsyncStateMachineBox`1.ExecuteFromThreadPool(Thread threadPoolThread)
   at System.Threading.ThreadPoolWorkQueue.Dispatch()
   at System.Threading.PortableThreadPool.WorkerThread.WorkerThreadStart()
   at System.Threading.Thread.StartCallback()

[kboniadi]

Hi,

I'm running into the same issue. No data is being written when using the bulk insert example in the readme. My Clickhouse server version is

select version()
23.5.1.3174

[victor-sushko]

The error seems to be caused by the lack of a backward compatibility in the ClickHouse server. Catching up with the current protocol revision should solve this problem.

[victor-sushko]

This error is caused by a really useful feature in the ClickHouse server: (limited) support of ACID transactions.

In the current version of ClickHouseClient the method ClickHouseColumnWriter.EndWrite commits a transaction but also closes the session. In the next version the method ClickHouseColumnWriter.Commit will be available. This method will allow to commit a transaction without closing the session.