'Cannot write to ostream at offset' after 'insert ... select ...' (v19.5.2.6) #5230

lzy305 · 2019-05-09T15:44:40Z

When i run this sql like this

insert into table_a select col_a, col_b, col_c , sum(sale), sum(qty), .. from table_b group col_a, col_b, col_c

then clickhouse server crash.

the error message

0. /usr/bin/clickhouse-server(StackTrace::StackTrace()+0x16) [0x73fd046]
1. /usr/bin/clickhouse-server(DB::Exception::Exception(std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&, int)+0x22) [0x377e942]
2. /usr/bin/clickhouse-server(DB::WriteBufferFromOStream::nextImpl()+0x116) [0x7431a06]
3. /usr/bin/clickhouse-server(DB::WriteBufferFromHTTPServerResponse::nextImpl()+0xa1) [0x74311b1]
4. /usr/bin/clickhouse-server(DB::CompressedWriteBuffer::nextImpl()+0x258) [0x70030a8]
5. /usr/bin/clickhouse-server(DB::WriteBuffer::next()+0x29) [0x378d3c9]
6. /usr/bin/clickhouse-server(DB::WriteBuffer::write(char const*, unsigned long)+0x88) [0x378d498]
7. /usr/bin/clickhouse-server(DB::DataTypeDecimal<DB::Decimal<__int128> >::serializeText(DB::IColumn const&, unsigned long, DB::WriteBuffer&, DB::FormatSettings const&) const+0x380) [0x682d500]
8. /usr/bin/clickhouse-server(DB::IRowOutputStream::write(DB::Block const&, unsigned long)+0x77) [0x6f28c87]
9. /usr/bin/clickhouse-server(DB::BlockOutputStreamFromRowOutputStream::write(DB::Block const&)+0x5e) [0x6f181fe]
10. /usr/bin/clickhouse-server(DB::MaterializingBlockOutputStream::write(DB::Block const&)+0x3e) [0x6c4148e]
11. /usr/bin/clickhouse-server(DB::copyData(DB::IBlockInputStream&, DB::IBlockOutputStream&, std::atomic<bool>*)+0xf5) [0x6797b75]
12. /usr/bin/clickhouse-server(DB::executeQuery(DB::ReadBuffer&, DB::WriteBuffer&, bool, DB::Context&, std::function<void (std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&)>, std::function<void (std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&)>)+0x612) [0x69c70d2]
13. /usr/bin/clickhouse-server(DB::HTTPHandler::processQuery(Poco::Net::HTTPServerRequest&, HTMLForm&, Poco::Net::HTTPServerResponse&, DB::HTTPHandler::Output&)+0x3636) [0x3799576]
14. /usr/bin/clickhouse-server(DB::HTTPHandler::handleRequest(Poco::Net::HTTPServerRequest&, Poco::Net::HTTPServerResponse&)+0x3f1) [0x379b541]
15. /usr/bin/clickhouse-server(Poco::Net::HTTPServerConnection::run()+0x32c) [0x7506d6c]
16. /usr/bin/clickhouse-server(Poco::Net::TCPServerConnection::start()+0xf) [0x7500d6f]
17. /usr/bin/clickhouse-server(Poco::Net::TCPServerDispatcher::run()+0xe9) [0x75014a9]
18. /usr/bin/clickhouse-server(Poco::PooledThread::run()+0x81) [0x75b45e1]
19. /usr/bin/clickhouse-server(Poco::ThreadImpl::runnableEntry(void*)+0x38) [0x75b07a8]
20. /usr/bin/clickhouse-server() [0xb27522f]
21. /lib/x86_64-linux-gnu/libpthread.so.0(+0x76ba) [0x7fbd6b81c6ba]
22. /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d) [0x7fbd6ae3d41d]

2019.05.09 23:18:50.948551 [ 44 ] {e04dc9c8-589a-4671-a28f-cf1999d7a14f} <Error> HTTPHandler: Code: 24, e.displayText() = DB::Exception: Cannot write to ostream at offset 642777088, Stack trace:

0. /usr/bin/clickhouse-server(StackTrace::StackTrace()+0x16) [0x73fd046]
1. /usr/bin/clickhouse-server(DB::Exception::Exception(std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&, int)+0x22) [0x377e942]
2. /usr/bin/clickhouse-server(DB::WriteBufferFromOStream::nextImpl()+0x116) [0x7431a06]
3. /usr/bin/clickhouse-server(DB::WriteBufferFromHTTPServerResponse::nextImpl()+0xa1) [0x74311b1]
4. /usr/bin/clickhouse-server(DB::CompressedWriteBuffer::nextImpl()+0x258) [0x70030a8]
5. /usr/bin/clickhouse-server(DB::WriteBuffer::next()+0x29) [0x378d3c9]
6. /usr/bin/clickhouse-server(DB::WriteBuffer::write(char const*, unsigned long)+0x88) [0x378d498]
7. /usr/bin/clickhouse-server(DB::DataTypeDecimal<DB::Decimal<__int128> >::serializeText(DB::IColumn const&, unsigned long, DB::WriteBuffer&, DB::FormatSettings const&) const+0x380) [0x682d500]
8. /usr/bin/clickhouse-server(DB::IRowOutputStream::write(DB::Block const&, unsigned long)+0x77) [0x6f28c87]
9. /usr/bin/clickhouse-server(DB::BlockOutputStreamFromRowOutputStream::write(DB::Block const&)+0x5e) [0x6f181fe]
10. /usr/bin/clickhouse-server(DB::MaterializingBlockOutputStream::write(DB::Block const&)+0x3e) [0x6c4148e]
11. /usr/bin/clickhouse-server(DB::copyData(DB::IBlockInputStream&, DB::IBlockOutputStream&, std::atomic<bool>*)+0xf5) [0x6797b75]
12. /usr/bin/clickhouse-server(DB::executeQuery(DB::ReadBuffer&, DB::WriteBuffer&, bool, DB::Context&, std::function<void (std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&)>, std::function<void (std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&)>)+0x612) [0x69c70d2]
13. /usr/bin/clickhouse-server(DB::HTTPHandler::processQuery(Poco::Net::HTTPServerRequest&, HTMLForm&, Poco::Net::HTTPServerResponse&, DB::HTTPHandler::Output&)+0x3636) [0x3799576]
14. /usr/bin/clickhouse-server(DB::HTTPHandler::handleRequest(Poco::Net::HTTPServerRequest&, Poco::Net::HTTPServerResponse&)+0x3f1) [0x379b541]
15. /usr/bin/clickhouse-server(Poco::Net::HTTPServerConnection::run()+0x32c) [0x7506d6c]
16. /usr/bin/clickhouse-server(Poco::Net::TCPServerConnection::start()+0xf) [0x7500d6f]
17. /usr/bin/clickhouse-server(Poco::Net::TCPServerDispatcher::run()+0xe9) [0x75014a9]
18. /usr/bin/clickhouse-server(Poco::PooledThread::run()+0x81) [0x75b45e1]
19. /usr/bin/clickhouse-server(Poco::ThreadImpl::runnableEntry(void*)+0x38) [0x75b07a8]
20. /usr/bin/clickhouse-server() [0xb27522f]
21. /lib/x86_64-linux-gnu/libpthread.so.0(+0x76ba) [0x7fbd6b81c6ba]
22. /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d) [0x7fbd6ae3d41d]
 (version 19.5.2.6 (official build))
2019.05.09 23:18:51.002086 [ 44 ] {e04dc9c8-589a-4671-a28f-cf1999d7a14f} <Error> HTTPHandler: Cannot send exception to client: Code: 24, e.displayText() = DB::Exception: Cannot write to ostream at offset 641728812, Stack trace:

0. /usr/bin/clickhouse-server(StackTrace::StackTrace()+0x16) [0x73fd046]
1. /usr/bin/clickhouse-server(DB::Exception::Exception(std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&, int)+0x22) [0x377e942]
2. /usr/bin/clickhouse-server(DB::WriteBufferFromOStream::nextImpl()+0x116) [0x7431a06]
3. /usr/bin/clickhouse-server(DB::WriteBufferFromHTTPServerResponse::nextImpl()+0xa1) [0x74311b1]
4. /usr/bin/clickhouse-server(DB::WriteBuffer::next()+0x29) [0x378d3c9]
5. /usr/bin/clickhouse-server(DB::HTTPHandler::trySendExceptionToClient(std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&, int, Poco::Net::HTTPServerRequest&, Poco::Net::HTTPServerResponse&, DB::HTTPHandler::Output&)+0x1ba) [0x3794cca]
6. /usr/bin/clickhouse-server() [0x3421881]
7. /usr/bin/clickhouse-server(Poco::Net::HTTPServerConnection::run()+0x32c) [0x7506d6c]
8. /usr/bin/clickhouse-server(Poco::Net::TCPServerConnection::start()+0xf) [0x7500d6f]
9. /usr/bin/clickhouse-server(Poco::Net::TCPServerDispatcher::run()+0xe9) [0x75014a9]
10. /usr/bin/clickhouse-server(Poco::PooledThread::run()+0x81) [0x75b45e1]
11. /usr/bin/clickhouse-server(Poco::ThreadImpl::runnableEntry(void*)+0x38) [0x75b07a8]
12. /usr/bin/clickhouse-server() [0xb27522f]
13. /lib/x86_64-linux-gnu/libpthread.so.0(+0x76ba) [0x7fbd6b81c6ba]
14. /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d) [0x7fbd6ae3d41d]

The text was updated successfully, but these errors were encountered:

den-crane · 2019-05-09T15:51:17Z

DataTypeDecimal

Please provide DDL for table_a , table_b
show create table table_a;
show create table table_b;

lzy305 · 2019-05-09T16:33:47Z

table_b

create table dw_hr.fct_rt_dc_shop_sku_vender_day_ti
(
	stat_year UInt16,
	stat_month UInt32,
	stat_day Date,
	out_buid UInt8,
	categorytreeid UInt8,
	out_shop_id String,
	dctype UInt8,
	in_shop_id String,
	shopformid UInt8,
	goodsid String,
	category_id String,
	venderid String,
	logistics UInt8,
	buntype UInt8,
	datasource UInt8,
	rt_boxes Decimal(18,4),
	rt_qty Decimal(18,4),
	rt_cost Decimal(18,4),
	rt_taxcost Decimal(18,4)
)
engine =Log;

table_a:

create table dw_hr.fct_rt_dc_shop_sku_vender_day
(
	stat_year UInt16,
	stat_month UInt32,
	stat_day Date,
	out_buid UInt8,
	out_shop_id String,
	in_shop_id String,
	datasource UInt8,
	venderid String,
	categorytreeid UInt8,
	categoryid String,
	goodsid String,
	logistics UInt8,
	buntype UInt8,
	dctype UInt8,
	shopformid UInt8,
	rt_qty Decimal(18,4),
	rt_cost Decimal(18,4),
	rt_taxcost Decimal(18,4),
	rt_shops String,
	rt_drygood_qty Decimal(18,4),
	rt_drygood_cost Decimal(18,4),
	rt_drygood_boxes Decimal(18,4),
	rt_drygood_shops String,
	rt_fresh_qty Decimal(18,4),
	rt_fresh_cost Decimal(18,4),
	rt_fresh_shops String,
	rt_supshop_cost Decimal(18,4),
	rt_supshop_qty Decimal(18,4),
	rt_supshop_boxes Decimal(18,4),
	rt_supshop_shops String,
	rt_smallshop_cost Decimal(18,4),
	rt_smallshop_qty Decimal(18,4),
	rt_smallshop_boxes Decimal(18,4),
	rt_smallshop_shops String,
	rt_dc_cost Decimal(18,4),
	rt_dc_qty Decimal(18,4),
	rt_dc_boxes Decimal(18,4),
	rt_dc_shops String,
	rt_drygood_supshop_cost Decimal(18,4),
	rt_drygood_supshop_qty Decimal(18,4),
	rt_drygood_supshop_boxes Decimal(18,4),
	rt_drygood_supshop_shops String,
	rt_drygood_smallshop_cost Decimal(18,4),
	rt_drygood_smallshop_qty Decimal(18,4),
	rt_drygood_smallshop_boxes Decimal(18,4),
	rt_drygood_smallshop_shops String,
	rt_drygood_dc_cost Decimal(18,4),
	rt_drygood_dc_qty Decimal(18,4),
	rt_drygood_dc_boxes Decimal(18,4),
	rt_drygood_dc_shops String,
	rt_fresh_supshop_cost Decimal(18,4),
	rt_fresh_supshop_qty Decimal(18,4),
	rt_fresh_supshop_shops String,
	rt_fresh_smallshop_cost Decimal(18,4),
	rt_fresh_smallshop_qty Decimal(18,4),
	rt_fresh_smallshop_shops String,
	rt_fresh_dc_cost Decimal(18,4),
	rt_fresh_dc_qty Decimal(18,4),
	rt_fresh_dc_shops String
)
engine = MergeTree PARTITION BY toYYYYMM(stat_day) ORDER BY (stat_day, out_shop_id) SETTINGS index_granularity = 8192
;

den-crane · 2019-05-09T18:09:05Z

insert into table_a select col_a, col_b, col_c , sum(sale), sum(qty), ..
from table_b group col_a, col_b, col_c

I don't see col_a col_c ?

inolddays · 2019-08-12T06:47:45Z

hi, @filimonov is there any plan on this bug? we run into the same problem. have no idea when will it happen and how to resolve it.

filimonov · 2019-08-13T18:59:24Z

@Johnny-Three
What version do you have?
can you help to create well reproducable test case?

inolddays · 2019-08-14T04:08:35Z

@filimonov thanks for the reply. I use version 19.7.5.27 and it happens every day now in our production environment. here attached the SQL and the log. As we observed, it often happens when queries executed in concurrency scenario.I am not sure if the info is enough to make a judgment? if not, plz tell me how to help.
sqlexecute.txt
info.log

doom369 · 2019-08-16T10:29:04Z

@filimonov have the same issue. Scenario:

I have 2 servers that use the same clickhouse instance, but different DBs inside this instance. Every server have minute cron job that does batch inserts (simple inserts, no aggregation). Data size is small - few hundred of records.
Once a day I see this:

And in clickhouse error log:

2019.08.16 12:27:41.169481 [ 52 ] {48478327-c758-469d-a7d1-51149cd3df17} <Error> virtual DB::WriteBufferFromHTTPServerResponse::~WriteBufferFromHTTPServerResponse(): Code: 24, e.displayText() = DB::Exception: Cannot write to ostream at offset 72, Stack trace:

0. /usr/bin/clickhouse-server(StackTrace::StackTrace()+0x30) [0x7f58f30]
1. /usr/bin/clickhouse-server(DB::Exception::Exception(std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&, int)+0x25) [0x3c427b5]
2. /usr/bin/clickhouse-server() [0x3ab7759]
3. /usr/bin/clickhouse-server(DB::WriteBufferFromHTTPServerResponse::nextImpl()+0xa1) [0x78e0231]
4. /usr/bin/clickhouse-server(DB::WriteBufferFromHTTPServerResponse::~WriteBufferFromHTTPServerResponse()+0x4d) [0x78e057d]
5. /usr/bin/clickhouse-server(std::_Sp_counted_base<(__gnu_cxx::_Lock_policy)2>::_M_release()+0x48) [0x3c428c8]
6. /usr/bin/clickhouse-server(DB::HTTPHandler::handleRequest(Poco::Net::HTTPServerRequest&, Poco::Net::HTTPServerResponse&)+0x88f) [0x3c923ef]
7. /usr/bin/clickhouse-server(Poco::Net::HTTPServerConnection::run()+0x2a9) [0x7932009]
8. /usr/bin/clickhouse-server(Poco::Net::TCPServerConnection::start()+0x10) [0x792cde0]
9. /usr/bin/clickhouse-server(Poco::Net::TCPServerDispatcher::run()+0xed) [0x792d4fd]
10. /usr/bin/clickhouse-server(Poco::PooledThread::run()+0x81) [0x8057e21]
11. /usr/bin/clickhouse-server(Poco::ThreadImpl::runnableEntry(void*)+0x3c) [0x8055bcc]
12. /usr/bin/clickhouse-server() [0xba2baa0]
13. /lib/x86_64-linux-gnu/libpthread.so.0(+0x76db) [0x7f33f58bb6db]
14. /lib/x86_64-linux-gnu/libc.so.6(clone+0x3f) [0x7f33f504288f]
 (version 19.13.1.11 (official build))

So at the same second 2 servers report about dropped connection to clickhouse. Timing is the same. Seems like some concurrency issue inside request processing.

4ertus2 · 2019-08-16T19:43:40Z

Does the server crashes or this errors just exceptions and it's still running?
There was a crach in Decimal128 arrgeration fixed in recent versions #6378 It could be related if it craches.

doom369 · 2019-08-16T19:55:46Z

Server up and running. Just random connection is dropped.

4ertus2 · 2019-08-16T20:04:32Z

Does it occurs only with DB::DataTypeDecimal<DB::Decimal<__int128> >::serializeText or there're other data types that cannot be serialized?
Columns of what types are in group by, columns of what types are under other the aggregations? Do inserts wethout aggregations works? What mimimal query (with what aggregation) leads to the problem?

doom369 · 2019-08-17T11:01:13Z

If this is question to me - table is next:

CREATE TABLE reporting_raw_data (
  device_id UInt32,
  stream_id UInt32 DEFAULT 0,
  pin UInt8,
  pin_type UInt8,
  ts DateTime,
  value Float64
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(ts)
ORDER BY (device_id, stream_id, pin, pin_type, ts);

With many views like this:

CREATE MATERIALIZED VIEW reporting_minute
ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(ts)
ORDER BY (device_id, stream_id, pin, pin_type, ts)
AS SELECT
    device_id,
    stream_id,
    pin,
    pin_type,
    toStartOfMinute(ts) as ts,

    avgState(value) as avgValue,
    minState(value) as minValue,
    maxState(value) as maxValue,
    sumState(value) as sumValue,
    countState(value) as countValue
FROM reporting_raw_data
GROUP BY device_id, stream_id, pin, pin_type, ts;

Error is very random. I never looked into clickhouse logs, until I start seeing dropped connection every day. By the way, I have similar duplicated env. and no errors there. Different load but totally the same code and version of everything.

4ertus2 · 2019-08-17T15:14:48Z

Why do you think it’s the same issue? The source issue is about crash when calling insert select with aggregation. Is it your case?

filimonov · 2019-09-09T22:45:29Z

Let's continue in #3981

lzy305 added the question Question? label May 9, 2019

filimonov changed the title ~~crash in version 19.5.2.6 afer insert~~ 'Cannot write to ostream at offset' after 'insert ... select ...' (v19.5.2.6) May 10, 2019

filimonov added bug Confirmed user-visible misbehaviour in official release v19.5 and removed question Question? labels Jun 11, 2019

4ertus2 added crash Crash / segfault / abort st-need-info We need extra data to continue (waiting for response) labels Aug 16, 2019

4ertus2 self-assigned this Aug 16, 2019

4ertus2 removed the crash Crash / segfault / abort label Aug 16, 2019

filimonov closed this as completed Sep 9, 2019

filimonov added duplicate and removed st-need-info We need extra data to continue (waiting for response) v19.5 labels Sep 9, 2019

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

'Cannot write to ostream at offset' after 'insert ... select ...' (v19.5.2.6) #5230

'Cannot write to ostream at offset' after 'insert ... select ...' (v19.5.2.6) #5230

lzy305 commented May 9, 2019 •

edited by filimonov

den-crane commented May 9, 2019

lzy305 commented May 9, 2019 •

edited by filimonov

den-crane commented May 9, 2019 •

edited

inolddays commented Aug 12, 2019

filimonov commented Aug 13, 2019

inolddays commented Aug 14, 2019 •

edited

doom369 commented Aug 16, 2019

4ertus2 commented Aug 16, 2019 •

edited

doom369 commented Aug 16, 2019

4ertus2 commented Aug 16, 2019 •

edited

doom369 commented Aug 17, 2019

4ertus2 commented Aug 17, 2019

filimonov commented Sep 9, 2019

'Cannot write to ostream at offset' after 'insert ... select ...' (v19.5.2.6) #5230

'Cannot write to ostream at offset' after 'insert ... select ...' (v19.5.2.6) #5230

Comments

lzy305 commented May 9, 2019 • edited by filimonov

den-crane commented May 9, 2019

lzy305 commented May 9, 2019 • edited by filimonov

den-crane commented May 9, 2019 • edited

inolddays commented Aug 12, 2019

filimonov commented Aug 13, 2019

inolddays commented Aug 14, 2019 • edited

doom369 commented Aug 16, 2019

4ertus2 commented Aug 16, 2019 • edited

doom369 commented Aug 16, 2019

4ertus2 commented Aug 16, 2019 • edited

doom369 commented Aug 17, 2019

4ertus2 commented Aug 17, 2019

filimonov commented Sep 9, 2019

lzy305 commented May 9, 2019 •

edited by filimonov

lzy305 commented May 9, 2019 •

edited by filimonov

den-crane commented May 9, 2019 •

edited

inolddays commented Aug 14, 2019 •

edited

4ertus2 commented Aug 16, 2019 •

edited

4ertus2 commented Aug 16, 2019 •

edited