Clickhouse driver: could not prepare Tx

[igrpon]

From time-to-time I get error on Db-to-Db replication (Postgres to Clickhouse): could not prepare Tx.
Strangely this happens only from time-to-time, on the SAME source dataset.

And usually sling starts processing rows, and fails in some time afterwards (e.g. 5sec)

Is it possible to somehow debug that in details?

PS. Sometimes Clickhouse requires FQN table name, with database name included, could it be that ? or _tmp table was not created before this Prepare call ? Could it be caused by incorrect Data Types / data type mismatch ?

Screens:

[igrpon]

I suppose this may be connected with this: ClickHouse/clickhouse-go#336

[flarco]

About the link, if it was a bad query it should fail every time (I think).

[flarco]

this?

[igrpon]

I connect to the cloud-based Clickhouse, single node cluster, so HAPproxy is not the case I guess.
It looks like due some errors the connection becomes unusable for further requests...

[flarco]

Indeed. Not sure. The error code 400 is undescriptive, so not helpful, especially since it's not deterministic. Are you connecting via HTTP? If so, could you try using the native port (9000?)

[igrpon]

In my case I can only use HTTP, as for native connection I need encryption, which is not supported by sling for now

[flarco]

You can try running with --trace or env var DEBUG=TRACE.

[igrpon]

Today was lucky and got same error with --trace

For ~10sec replication was going smoothly, and on the next chunk I got this:

...
10s 235,373 13094 r/s 143 MB | 17% MEM | 68% CPU 2025-08-20 14:29:26 TRC closed ds.1755689352928.3Km-2
2025-08-20 14:29:26 TRC new batch ds.1755689352928.3Km-3
2025-08-20 14:29:26 TRC commiting
2025-08-20 14:29:26 TRC begin
2025-08-20 14:29:26 TRC insert target fields: waylist_id, waylist_bid, wl_date, wl_related_zone_name, wl_region_name, wl_operation_type_name, wl_related_party_name, wl_org_unit_name, wl_org_unit_object_name, wl_dispatcher_id, wl_dispatcher_bid, wl_dispatcher_name, wl_car_id, wl_car_bid, wl_car_idcode, wl_car_category_name, wl_car_type_name, wl_car_loadtype_name, wl_car_is_3rd_party_asset, wl_car_status, wl_car_owner_business_party_name, sdf_totalcount, sdf_max_severity
2025-08-20 14:29:26 TRC insert statement: insert into `bi_waylist_sdf_summary_tmp` (`waylist_id`, `waylist_bid`, `wl_date`, `wl_related_zone_name`, `wl_region_name`, `wl_operation_type_name`, `wl_related_party_name`, `wl_org_unit_name`, `wl_org_unit_object_name`, `wl_dispatcher_id`, `wl_dispatcher_bid`, `wl_dispatcher_name`, `wl_car_id`, `wl_car_bid`, `wl_car_idcode`, `wl_car_category_name`, `wl_car_type_name`, `wl_car_loadtype_name`, `wl_car_is_3rd_party_asset`, `wl_car_status`, `wl_car_owner_business_party_name`, `sdf_totalcount`, `sdf_max_severity`) x 1
2025-08-20 14:29:26 TRC {bi_waylist_sdf_summary_tmp prod-01  %!s(bool=false)   clickhouse [] map[] `prod-01`.`bi_waylist_sdf_summary_tmp` %!s(int=0) %!s(int=0)}: []string{"waylist_id", "waylist_bid", "wl_date", "wl_related_zone_name", "wl_region_name", "wl_operation_type_name", "wl_related_party_name", "wl_org_unit_name", "wl_org_unit_object_name", "wl_dispatcher_id", "wl_dispatcher_bid", "wl_dispatcher_name", "wl_car_id", "wl_car_bid", "wl_car_idcode", "wl_car_category_name", "wl_car_type_name", "wl_car_loadtype_name", "wl_car_is_3rd_party_asset", "wl_car_status", "wl_car_owner_business_party_name", "sdf_totalcount", "sdf_max_severity"}
2025-08-20 14:29:26 TRC rolling back
2025-08-20 14:29:26 TRC Context.CaptureErr => &g.ErrType{Err:"clickhouse [execute]:: 400 code: ", MsgStack:[]string{"could not prepare Tx: insert into `bi_waylist_sdf_summary_tmp` (`waylist_id`, `waylist_bid`, `wl_date`, `wl_related_zone_name`, `wl_region_name`, `wl_operation_type_name`, `wl_related_party_name`, `wl_org_unit_name`, `wl_org_unit_object_name`, `wl_dispatcher_id`, `wl_dispatcher_bid`, `wl_dispatcher_name`, `wl_car_id`, `wl_car_bid`, `wl_car_idcode`, `wl_car_category_name`, `wl_car_type_name`, `wl_car_loadtype_name`, `wl_car_is_3rd_party_asset`, `wl_car_status`, `wl_car_owner_business_party_name`, `sdf_totalcount`, `sdf_max_severity`) values  ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23)", "could not prepare statement", "could not prepare statement", "could not copy data", "could not bulk import"}, CallerStack:[]string{"transaction.go:95 Prepare", "database.go:1117 Prepare", "database_clickhouse.go:172 func2", "database_clickhouse.go:276 BulkImportStream", "database.go:2450 func1", "database.go:2463 BulkImportFlow", "task_run_write.go:301 WriteToDb", "task_run.go:820 runDbToDb", "task_run.go:140 func2"}, Position:4, OrigErr:(*errors.errorString)(0xc00542ab00), OrigErrVal:""}
2025-08-20 14:29:26 ERR clickhouse [execute]:: 400 code:  error="--- task_run.go:140 func2 ---\n--- task_run.go:820 runDbToDb ---\n--- task_run_write.go:301 WriteToDb ---\n--- database.go:2463 BulkImportFlow ---\n~ could not bulk import\n--- database.go:2450 func1 ---\n~ could not copy data\n--- database_clickhouse.go:276 BulkImportStream ---\n~ could not prepare statement\n--- database_clickhouse.go:172 func2 ---\n~ could not prepare statement\n--- database.go:1117 Prepare ---\n~ could not prepare Tx: insert into `bi_waylist_sdf_summary_tmp` (`waylist_id`, `waylist_bid`, `wl_date`, `wl_related_zone_name`, `wl_region_name`, `wl_operation_type_name`, `wl_related_party_name`, `wl_org_unit_name`, `wl_org_unit_object_name`, `wl_dispatcher_id`, `wl_dispatcher_bid`, `wl_dispatcher_name`, `wl_car_id`, `wl_car_bid`, `wl_car_idcode`, `wl_car_category_name`, `wl_car_type_name`, `wl_car_loadtype_name`, `wl_car_is_3rd_party_asset`, `wl_car_status`, `wl_car_owner_business_party_name`, `sdf_totalcount`, `sdf_max_severity`) values  ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23)\n--- transaction.go:95 Prepare ---\nclickhouse [execute]:: 400 code: "
2025-08-20 14:29:26 TRC executing defer functions
11s 279,114 28065 r/s 169 MB | 17% MEM | 40% CPU
2025-08-20 14:29:26 DBG drop table if exists `prod-01`.`bi_waylist_sdf_summary_tmp`
2025-08-20 14:29:26 DBG table `prod-01`.`bi_waylist_sdf_summary_tmp` dropped
2025-08-20 14:29:26 DBG closed "clickhouse" connection (conn-clickhouse-bm2)
2025-08-20 14:29:26 DBG closed "postgres" connection (conn-postgres-rVj)
2025-08-20 14:29:26 TRC executing defer functions
2025-08-20 14:29:26 TRC executing defer functions
2025-08-20 14:29:26 INF execution failed
...

basically nothing new :-( - smth failed inside of Prepare in clickhouse go driver

"could not prepare statement", "could not prepare statement", "could not copy data", "could not bulk import"

on subsequent run this table was migrated, but on the next one I got another error:

--- task_run.go:140 func2 ---
--- task_run.go:820 runDbToDb ---
--- task_run_write.go:320 WriteToDb ---
~ Could not WriteToDb
--- database.go:1309 GetCount ---
~ could not get count for temp table `prod-01`.`bi_waylist_sdf_details_tmp`
--- database.go:1230 Query ---
~ Error with StreamRows
--- database.go:1243 QueryContext ---
~ SQL Error for:
select count(*) cnt from `prod-01`.`bi_waylist_sdf_details_tmp`
--- database.go:863 StreamRowsContext ---
clickhouse [execute]:: 400 code:

seems like a bug in clickhouse go driver :-(

[igrpon]

Finally I've reconfigured my clickhouse-clould and switched to native clickhouse connection, instead of http
So far I didn't see such errors anymore.

Also found a note in clickhouse-go driver release notes:

https://github.com/ClickHouse/clickhouse-go/blob/main/CHANGELOG.md

HTTP still has some limitations to be aware of for things like batch flushing and session context, so be cautious when switching over code to this protocol.

@flarco - I suggest put a quote \ reference somewhere in sling docs w.r.t. clickhouse HTTP driver possible issues

[flarco]

Great, will do. How did you handle the encryption part?

[igrpon]

played with different settings on cloud side to enable dedicated port for native (TCP) clickhouse clients connection + set secure: true and skip_verify: true in sling env file