Inserts to MV failing

Skip to first unread message

sparsh sidana

Apr 19, 2021, 8:46:02 PMApr 19
to ClickHouse
One of our streaming ETLs is writing to a distributed table ('A') which is created on top of Replicated Merge Tree tables in Clickhouse. 
There's a MV created on top of that distributed table ('A') that inserts data to another distributed table ('B'). There's no aggregation being performed on the M.V., it just has a different partition key with a subset of columns.

Issue: The total count of rows in the Materialized view is lower than that in the base table which are expected to be same as there is no aggregation.

I see these exceptions in the logs:

2021.04.19 02:19:06.525826 [ 25473 ] {b3f32c37-3cc3-41eb-af8e-a1d72517f3be} <Error> executeQuery: Code: 242, e.displayText() = DB::Exception: Table is in readonly mode (version (official build)) (from [::ffff:]:40908) (in query: INSERT INTO mppayments_new.local_mp_transactions_processed_dashboard (wmt_order_no, wmt_order_line_no, event_name, wmt_po_no, wmt_po_line_no, fl_id, txn_time, invoice_id, return_order_no, run_day, source_sys, adjustment_code, partner_id, order_segment, is_manual_replay, is_auto_replay, insert_time, tenant_id, event_id, isGoodWill, comm_calc_date, ingestion_date, settlement_date, created_dtm) VALUES),

2021.04.19 02:18:56.929173 [ 23993 ] {} <Error> mppayments_new.local_mp_transactions_processed_dashboard: void DB::StorageReplicatedMergeTree::mutationsUpdatingTask(): Code: 999, e.displayText() = Coordination::Exception: Session expired (Session expired),

This issue could be because the connection was breaking with Zookeeper. However, if the connection to Zookeeper was failing for some reason, I think that even the inserts to the base table should be failing which shouldn't result in the discrepancy. Could you please let me know if this could  be the reason for the difference in the number of records or there's something else that we should be looking at?

Clickhouse server version:  20.5.3


Denis Zhuravlev

Apr 20, 2021, 8:02:12 PMApr 20
to ClickHouse
>should be failing which shouldn't result in the discrepancy.

It should not. It's not implemented. CH does not guarantee consistency of base_table <-> MV.
You can achieve consistency if you implement insertion_with_retry properly.

Reply all
Reply to author
0 new messages