Error driver: bad connection

[y761350477]

1. I'll be prompted when the table I'm querying doesn't exist: Table xxx doesn't exist.
2. When the table I'm requesting again exists, I prompt driver: Bad connection problem
   Use in a go web environment

[pablote]

I’m getting the same problem. In general, whenever a query fails, for whatever reason, that seems to leave the connection in a bad state, and the next one will always fail with “bad connection”.

After that, everything starts working ok.

[johnatannvmd]

Got this error on "columnar" type of insertion if I try to use the same connection with OpenDirect. Something like that(not real code, just to show the idea):

c := clickhouse.OpenDirect("tcp://127.0.0.1:9000?debug=true&database=databse&compress=1")
for i := 0; i < 2; i++ {
    c.Begin()
    c.Prepare(stmt)
    block, _ := c.Block()
    block.NumRows(100)
    block.Reverse()
    for _, row := range rows {
        block.Writes...
    }
    c.WriteBlock(block)
    c.Rollback()
}

On the second iteration I got error on c.Begin() line. If I "catch" the error and reopen connection with opendirect function, everything is continue to work.

2019.12.11 20:55:29.409365 [ 49 ] {460e6c16-c4f6-4296-8ab8-98395f3997c5} <Error> executeQuery: Code: 32, e.displayText() = DB::Exception: Attempt to read after eof (version 19.17.4.11 (official build)) (from 192.168.38.105:59844) (in query: INSERT INTO samples (`date`,`time`,`device`,`src`,`dst`,`srcASN`,`dstASN`,`proto`) VALUES ), Stack trace:

0. 0x55fa1af89f60 StackTrace::StackTrace() /usr/bin/clickhouse
1. 0x55fa1af89d35 DB::Exception::Exception(std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&, int) /usr/bin/clickhouse
2. 0x55fa1aad043f DB::throwReadAfterEOF() /usr/bin/clickhouse
3. 0x55fa1e1eab1b ? /usr/bin/clickhouse
4. 0x55fa1e1ed6a1 DB::NativeBlockInputStream::readImpl() /usr/bin/clickhouse
5. 0x55fa1e1e001a DB::IBlockInputStream::read() /usr/bin/clickhouse
6. 0x55fa1b010590 DB::TCPHandler::receiveData(bool) /usr/bin/clickhouse
7. 0x55fa1b011029 DB::TCPHandler::receivePacket() /usr/bin/clickhouse
8. 0x55fa1b01120e DB::TCPHandler::readDataNext(unsigned long const&, int const&) /usr/bin/clickhouse
9. 0x55fa1b01179e DB::TCPHandler::readData(DB::Settings const&) /usr/bin/clickhouse
10. 0x55fa1b011a11 DB::TCPHandler::processInsertQuery(DB::Settings const&) /usr/bin/clickhouse
11. 0x55fa1b013045 DB::TCPHandler::runImpl() /usr/bin/clickhouse
12. 0x55fa1b0132cb DB::TCPHandler::run() /usr/bin/clickhouse
13. 0x55fa1ef0f660 Poco::Net::TCPServerConnection::start() /usr/bin/clickhouse
14. 0x55fa1ef0fd7d Poco::Net::TCPServerDispatcher::run() /usr/bin/clickhouse
15. 0x55fa205e5ef1 Poco::PooledThread::run() /usr/bin/clickhouse
16. 0x55fa205e3c9c Poco::ThreadImpl::runnableEntry(void*) /usr/bin/clickhouse
17. 0x55fa20d59e00 ? /usr/bin/clickhouse
18. 0x7f69ede74e65 start_thread /usr/lib64/libpthread-2.17.so
19. 0x7f69ed79188d __clone /usr/lib64/libc-2.17.so

Long story, but on 19.14.6.12-2 same application works perfectly.

[johnatannvmd]

Test case for clickhouse_columnar_test.go

func Test_ColumnarInsertTwicely(t *testing.T) {
	const (
		ddl = `
			CREATE TABLE clickhouse_test_columnar_insert (
				uint64 UInt64,
				string  String,
				date    Date,
				datetime   DateTime
			) Engine=Memory
		`
		dml = `
			INSERT INTO clickhouse_test_columnar_insert (
				uint64,
				string,
				date,
				datetime
			) VALUES (
				?,
				?,
				?,
				?
			)
		`
	)
	if connect, err := clickhouse.OpenDirect("tcp://127.0.0.1:9000?debug=true"); assert.NoError(t, err) {
		{
			connect.Begin()
			stmt, _ := connect.Prepare("DROP TABLE IF EXISTS clickhouse_test_columnar_insert")
			stmt.Exec([]driver.Value{})
			connect.Commit()
		}
		{
			if _, err := connect.Begin(); assert.NoError(t, err) {
				if stmt, err := connect.Prepare(ddl); assert.NoError(t, err) {
					if _, err := stmt.Exec([]driver.Value{}); assert.NoError(t, err) {
						assert.NoError(t, connect.Commit())
					}
				}
			}
		}
		{
			if _, err := connect.Begin(); assert.NoError(t, err) {
				if _, err := connect.Prepare(dml); assert.NoError(t, err) {
					block, err := connect.Block()
					assert.NoError(t, err)
					block.Reserve()
					block.NumRows = 100

					for i := 0; i < 100; i++ {
						block.WriteUInt64(0, uint64(i))
						block.WriteString(1, "string")
						block.WriteDate(2, time.Now())
						block.WriteDateTime(3, time.Now())
						if !assert.NoError(t, err) {
							return
						}
					}
					assert.NoError(t, connect.WriteBlock(block))
					assert.NoError(t, connect.Rollback())
				}
			}
		}
		{
			if _, err := connect.Begin(); assert.NoError(t, err) {
				if _, err := connect.Prepare(dml); assert.NoError(t, err) {
					block, err := connect.Block()
					assert.NoError(t, err)
					block.Reserve()
					block.NumRows = 100

					for i := 0; i < 100; i++ {
						block.WriteUInt64(0, uint64(i))
						block.WriteString(1, "string")
						block.WriteDate(2, time.Now())
						block.WriteDateTime(3, time.Now())
						if !assert.NoError(t, err) {
							return
						}
					}
					assert.NoError(t, connect.WriteBlock(block))
					assert.NoError(t, connect.Commit())
				}
			}
		}
	}
}

With same error in CH:

2019.12.11 21:27:28.230137 [ 80 ] {4eb8cb2c-9e63-45db-9b19-b6b0d1b4afac} <Error> executeQuery: Code: 32, e.displayText() = DB::Exception: Attempt to read after eof (version 19.17.2.4 (official build)) (from 172.18.0.1:58282) (in query: SELECT 1, sleep(2)), Stack trace:

0. 0x557ae5b8bc40 StackTrace::StackTrace() /usr/bin/clickhouse
1. 0x557ae5b8ba15 DB::Exception::Exception(std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&, int) /usr/bin/clickhouse
2. 0x557ae56d2dbf DB::throwReadAfterEOF() /usr/bin/clickhouse
3. 0x557ae5c15cfb DB::readVarUInt(unsigned long&, DB::ReadBuffer&) /usr/bin/clickhouse
4. 0x557ae5c0f0ce DB::TCPHandler::isQueryCancelled() /usr/bin/clickhouse
5. 0x557ae5c114d7 DB::TCPHandler::processOrdinaryQuery() /usr/bin/clickhouse
6. 0x557ae5c14a8e DB::TCPHandler::runImpl() /usr/bin/clickhouse
7. 0x557ae5c14c7b DB::TCPHandler::run() /usr/bin/clickhouse
8. 0x557ae9b0d790 Poco::Net::TCPServerConnection::start() /usr/bin/clickhouse
9. 0x557ae9b0dead Poco::Net::TCPServerDispatcher::run() /usr/bin/clickhouse
10. 0x557aeb1e4021 Poco::PooledThread::run() /usr/bin/clickhouse
11. 0x557aeb1e1dcc Poco::ThreadImpl::runnableEntry(void*) /usr/bin/clickhouse
12. 0x557aeb957f40 ? /usr/bin/clickhouse
13. 0x7f3c6e7df6db start_thread /lib/x86_64-linux-gnu/libpthread-2.27.so
14. 0x7f3c6e0fc88f __clone /lib/x86_64-linux-gnu/libc-2.27.so

[johnatannvmd]

Holly molly, it's here 204d131#diff-17703cbd24e279392c28017efd531a45R163

Cleanup blocks and buffers are okay, but why close connection? And why I'm able to send data after connection is closed?

[kshvakov]

@johnatannvmd ClickHouse doesn't support the real transactions, begin/commit/rollback - is a transaction emulation. The connection is closed to prevent ClickHouse corruption (after error driver can send incorrect blocks).
It's normal, but connection must be removed from the connection pool

[qJkee]

Same here.
Getting strange error, which occurs on some writes with driver: bad connection.
@kshvakov according to your answer above, the problem is that sql package in golang storing this connection in the pool after usage, instead of closing it(but its a pool =)).
So, should I force to close the connection after usage manually, and open a new connection for each request?

[cameronbraid]

I'm having similar issues

2020/02/10 03:02:55 Unable to prepare insert statement : driver: bad connection

Is there a known workaround ?

[qJkee]

@cameronbraid
use sql.DB.Acquire() and Release() methods to work with connection directly.
Acquire will open a new connection if there are no live connections(or with bad connection) available

[pablote]

@qJkee I think that working with the driver directly is supposed to work like that already. Requesting a connection makes the code more verbose, more error handling, and only really needed if you need to do something with the connection object itself.

[sudhakar]

FWIW if anybody is working on a fix for driver.ErrBadConn, here's how mysql guys fixed this issue

[lelvisl]

any updates?

[goku321]

@kshvakov would like to work on this issue.

[kshvakov]

@goku321 , It's Open Source. Of course you can do it!)