Engine=Buffer tables, Scan error on column total_bytes: value out of range

[ingria]

Logs:

~ # clickhouse-backup create_remote
2021/07/15 06:14:34  info SELECT name, engine FROM system.databases WHERE name != 'system'
2021/07/15 06:14:34  info SHOW CREATE DATABASE `default`
2021/07/15 06:14:34  info SELECT count() FROM system.settings WHERE name = 'show_table_uuid_in_table_create_query_if_not_nil'
2021/07/15 06:14:34  info SELECT * FROM system.tables WHERE is_temporary = 0 SETTINGS show_table_uuid_in_table_create_query_if_not_nil=1
2021/07/15 06:14:34 error cat't get tables from clickhouse: sql: Scan error on column index 18, name "total_bytes": converting driver.Value type uint64 ("18446744073709497760") to a int64: value out of range

After clickhouse-server restart or reload everything works fine for some time.

Clickhouse 21.6.6 revision 54448
clickhouse-backup built from sources, go1.16.2

[Slach]

wow, thanks a lot for reporting
do you really have table with 18446744073709497760 total_bytes 18.44 exabytes in a single table?

[ingria]

Of course not, it’s more like 18 megabytes.

Not sure if this is a bug with backup, clickhouse or my config

[Slach]

hmm, could you share
results of following query

SELECT name, total_bytes FROM system.tables WHERE is_temporary = 0 AND total_bytes IS NOT NULL AND total_bytes > 0 SETTINGS show_table_uuid_in_table_create_query_if_not_nil=1

[ingria]

┌─name───────────────────────────────────────────┬──────────total_bytes─┐
│ .inner_id.25774cbe-b422-44ca-a577-4cbeb42234ca │             31011214 │
│ some_table                                     │             24936023 │
│ some_table_buffer                              │ 18446744073709532992 │
│ asynchronous_metric_log                        │              1640961 │
│ metric_log                                     │             47093604 │
│ query_log                                      │             61387946 │
│ query_thread_log                               │             96867184 │
│ trace_log                                      │               157567 │
└────────────────────────────────────────────────┴──────────────────────┘

some_table_buffer is a buffer table (ENGINE = Buffer)

[Slach]

could you share result of SHOW CREATE TABLE some_table_buffer?

[ingria]

CREATE TABLE default.some_table_buffer
(
    `channel_id` Int32,
    `subscribers` UInt32,
    `datetime` DateTime DEFAULT now()
)
ENGINE = Buffer('default', 'some_table', 2, 30, 300, 1000, 2500, 20000, 200000)

[Slach]

SELECT version() ?

[ingria]

21.6.6.51

[Slach]

try to run
OPTIMIZE TABLE default.some_table_buffer;
and then
SELECT name, total_bytes FROM system.tables WHERE is_temporary = 0 AND total_bytes IS NOT NULL AND total_bytes > 0 SETTINGS show_table_uuid_in_table_create_query_if_not_nil=1

[ingria]

OPTIMIZE doesn't seem to change anything.

Also, like I said before some_table_buffer size resets after reload or restart. I've reloaded the service about an hour ago, and just after restart it looked like this:

┌─name───────────────────────────────────────────┬─total_bytes─┐
│ .inner_id.25774cbe-b422-44ca-a577-4cbeb42234ca │    31025064 │
│ some_table                                     │    24956371 │
│ some_table_buffer                              │         576 │
│ asynchronous_metric_log                        │     1654275 │
│ metric_log                                     │    47520414 │
│ query_log                                      │    62041984 │
│ query_thread_log                               │    97909407 │
│ trace_log                                      │      161997 │
└────────────────────────────────────────────────┴─────────────┘

8 rows in set. Elapsed: 0.004 sec.

After some time some_table_buffer disappeared completely from the table list. There were only some_table and .inner_id.uuid (I mean the output of this query).

┌─name───────────────────────────────────────────┬─total_bytes─┐
│ .inner_id.25774cbe-b422-44ca-a577-4cbeb42234ca │    31025420 │
│ some_table                                     │    24950421 │
│ asynchronous_metric_log                        │     1654964 │
│ metric_log                                     │    47559463 │
│ query_log                                      │    62071217 │
│ query_thread_log                               │    97966015 │
│ trace_log                                      │      161997 │
└────────────────────────────────────────────────┴─────────────┘

7 rows in set. Elapsed: 0.004 sec.

After some more time it appeared again with the large value, like this:

┌─name───────────────────────────────────────────┬──────────total_bytes─┐
│ .inner_id.25774cbe-b422-44ca-a577-4cbeb42234ca │             31027047 │
│ some_table                                     │             24944002 │
│ some_table_buffer                              │ 18446744073709549696 │
│ asynchronous_metric_log                        │              1659807 │
│ metric_log                                     │             47633370 │
│ query_log                                      │             62185644 │
│ query_thread_log                               │             98155413 │
│ trace_log                                      │               162631 │
└────────────────────────────────────────────────┴──────────────────────┘

8 rows in set. Elapsed: 0.003 sec. 

After that the value started changing from "long" to "short" randomly (it keeps the "long" or "short" state for some time (flush time maybe?), then switches to another state).

[ingria]

Turned out there was a bug in a Clickhouse: ClickHouse/ClickHouse#24450

Thanks for your time anyway!

[den-crane]

It does not matter, 18446744073709549696 is a valid number, anyway clickhouse-backup can cast total_bytes into Int64 in select for example

[Slach]

@den-crane thanks a lot for suggestion