Clickhouse: Lost connection to MySQL server at 'sending authentication information', system error: 2 ((nullptr):3306

[ssserj]

Describe the bug or unexpected behaviour
After upgrade to version 20.1.2.4 clickhouse can't update any MySQL external dictionaries by expiration lifetime (...<lifetime><min>3600</min><max>10800</max></lifetime>...).

Work around
Restarting the clickhouse resolving it problem and then i can update dictionary explicitly by command SYSTEM RELOAD DICTIONARY <any>;

How to reproduce

• ClickHouse server version 20.1.2.4 (official build)
• MySQL Server version: 5.7.22-22-log Percona Server (GPL), Release '22', Revision 'f62d93c'
• Linux 4.9.0-8-amd64 SMP Debian 4.9.110-3+deb9u6 (2018-10-08) x86_64 GNU/Linux

Note the error fragment ...system error: 2 ((nullptr):3306

2020.01.29 11:44:00.898570 [ 132 ] {} <Error> Application: mysqlxx::ConnectionFailed
2020.01.29 11:44:00.898658 [ 132 ] {} <Warning> Application: Connection to [email protected]:3306 as user asgro failed: mysqlxx::ConnectionFailed: Lost connection to MySQL server at 'sending authentication information', system error: 2 ((nullptr):3306)
2020.01.29 11:44:00.900291 [ 132 ] {} <Error> Application: mysqlxx::ConnectionFailed
2020.01.29 11:44:00.900354 [ 132 ] {} <Warning> Application: Connection to [email protected]:3306 as user asgro failed: mysqlxx::ConnectionFailed: Lost connection to MySQL server at 'sending authentication information', system error: 2 ((nullptr):3306)
2020.01.29 11:44:00.901810 [ 132 ] {} <Error> Application: mysqlxx::ConnectionFailed
2020.01.29 11:44:00.901878 [ 132 ] {} <Warning> Application: Connection to [email protected]:3306 as user asgro failed: mysqlxx::ConnectionFailed: Lost connection to MySQL server at 'sending authentication information', system error: 2 ((nullptr):3306)
2020.01.29 11:44:00.901936 [ 132 ] {} <Error> Application: Connection to all replicas failed 1 times
2020.01.29 11:44:00.903560 [ 132 ] {} <Error> Application: mysqlxx::ConnectionFailed
2020.01.29 11:44:00.903706 [ 132 ] {} <Warning> Application: Connection to [email protected]:3306 as user asgro failed: mysqlxx::ConnectionFailed: Lost connection to MySQL server at 'sending authentication information', system error: 2 ((nullptr):3306)
2020.01.29 11:44:00.905678 [ 132 ] {} <Error> Application: mysqlxx::ConnectionFailed
2020.01.29 11:44:00.905783 [ 132 ] {} <Warning> Application: Connection to [email protected]:3306 as user asgro failed: mysqlxx::ConnectionFailed: Lost connection to MySQL server at 'sending authentication information', system error: 2 ((nullptr):3306)
2020.01.29 11:44:00.907362 [ 132 ] {} <Error> Application: mysqlxx::ConnectionFailed
2020.01.29 11:44:00.907463 [ 132 ] {} <Warning> Application: Connection to [email protected]:3306 as user asgro failed: mysqlxx::ConnectionFailed: Lost connection to MySQL server at 'sending authentication information', system error: 2 ((nullptr):3306)
2020.01.29 11:44:00.907487 [ 132 ] {} <Error> Application: Connection to all replicas failed 2 times
2020.01.29 11:44:00.909089 [ 132 ] {} <Error> Application: mysqlxx::ConnectionFailed
2020.01.29 11:44:00.909197 [ 132 ] {} <Warning> Application: Connection to [email protected]:3306 as user asgro failed: mysqlxx::ConnectionFailed: Lost connection to MySQL server at 'sending authentication information', system error: 2 ((nullptr):3306)
2020.01.29 11:44:00.910788 [ 132 ] {} <Error> Application: mysqlxx::ConnectionFailed
2020.01.29 11:44:00.910890 [ 132 ] {} <Warning> Application: Connection to [email protected]:3306 as user asgro failed: mysqlxx::ConnectionFailed: Lost connection to MySQL server at 'sending authentication information', system error: 2 ((nullptr):3306)
2020.01.29 11:44:00.912705 [ 132 ] {} <Error> Application: mysqlxx::ConnectionFailed
2020.01.29 11:44:00.912829 [ 132 ] {} <Warning> Application: Connection to [email protected]:3306 as user asgro failed: mysqlxx::ConnectionFailed: Lost connection to MySQL server at 'sending authentication information', system error: 2 ((nullptr):3306)
2020.01.29 11:44:00.912852 [ 132 ] {} <Error> Application: Connection to all replicas failed 3 times
2020.01.29 11:44:00.913141 [ 132 ] {} <Error> ExternalDictionariesLoader: Could not update external dictionary 'campaign_price_range', leaving the previous version, next update is scheduled at 2020-01-29 11:47:48: Poco::Exception. Code: 1000, e.code() = 0, e.displayText() = Exception: Connections to all replicas failed: [email protected]:3306 as user asgro, [email protected]:3306 as user asgro, [email protected]:3306 as user asgro0. 0xbc31d9c Poco::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int)  in /usr/bin/clickhouse
1. 0x4e3b29a ?  in /usr/bin/clickhouse
2. 0x6e0c080 DB::MySQLDictionarySource::loadAll()  in /usr/bin/clickhouse
3. 0x6b9dc3d DB::RangeHashedDictionary::loadData()  in /usr/bin/clickhouse
4. 0x6ba05c7 DB::RangeHashedDictionary::RangeHashedDictionary(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, DB::DictionaryStructure const&, std::__1::unique_ptr<DB::IDictionarySource, std::__1::default_delete<DB::IDictionarySource> >, DB::ExternalLoadableLifetime, bool)  in /usr/bin/clickhouse
5. 0x6bd037a DB::RangeHashedDictionary::clone() const  in /usr/bin/clickhouse
6. 0x8bba95b DB::ExternalLoader::createObject(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, DB::ExternalLoader::ObjectConfig const&, std::__1::shared_ptr<DB::IExternalLoadable const> const&) const  in /usr/bin/clickhouse
7. 0x8bc4b0a DB::ExternalLoader::LoadingDispatcher::doLoading(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, unsigned long, bool, unsigned long, bool)  in /usr/bin/clickhouse
8. 0x8bbdf36 ThreadFromGlobalPool::ThreadFromGlobalPool<void (DB::ExternalLoader::LoadingDispatcher::*)(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, unsigned long, bool, unsigned long, bool), DB::ExternalLoader::LoadingDispatcher*, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >&, unsigned long&, bool&, unsigned long&, bool>(void (DB::ExternalLoader::LoadingDispatcher::*&&)(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, unsigned long, bool, unsigned long, bool), DB::ExternalLoader::LoadingDispatcher*&&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >&, unsigned long&, bool&, unsigned long&, bool&&)::'lambda'()::operator()() const  in /usr/bin/clickhouse
9. 0x4fa3b77 ThreadPoolImpl<std::__1::thread>::worker(std::__1::__list_iterator<std::__1::thread, void*>)  in /usr/bin/clickhouse
10. 0x4fa212f ?  in /usr/bin/clickhouse
11. 0x74a4 start_thread  in /lib/x86_64-linux-gnu/libpthread-2.24.so
12. 0xe8d0f clone  in /lib/x86_64-linux-gnu/libc-2.24.so
 (version 20.1.2.4 (official build))

[josepowera]

We got similar situation after upgrade of CH from official docker image: yandex/clickhouse-server:19.17.6.36 to image: yandex/clickhouse-server:20.1.2.4

Connections to all replicas failed: [email protected]:3306 as user dspconfig (version 20.1.2.4 (official build)

2020.01.30 11:33:12.735385 [ 31 ] {} <Warning> Application: Connection to [email protected]:3306 as user dspconfig failed: mysqlxx::ConnectionFailed: Lost connection to MySQL server at 'sending authe
ntication information', system error: 2 ((nullptr):3306)
2020.01.30 11:33:12.735414 [ 31 ] {} <Error> Application: Connection to all replicas failed 3 times
2020.01.30 11:33:12.735586 [ 31 ] {} <Error> MySQLDictionarySource: Poco::Exception. Code: 1000, e.code() = 0, e.displayText() = Exception: Connections to all replicas failed: [email protected]:3306
as user dspconfig0. 0xbc31d9c Poco::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int)  in /usr/bin/clickhouse
1. 0x4e3b29a ?  in /usr/bin/clickhouse
2. 0x6e0aba1 DB::MySQLDictionarySource::getLastModification() const  in /usr/bin/clickhouse
3. 0x6e0bfe2 DB::MySQLDictionarySource::loadAll()  in /usr/bin/clickhouse
4. 0x6b5ca2d DB::HashedDictionary::loadData()  in /usr/bin/clickhouse
5. 0x6b5f204 DB::HashedDictionary::HashedDictionary(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, std::__1::basic_string<char, std::__1::char_traits<char>,
std::__1::allocator<char> > const&, DB::DictionaryStructure const&, std::__1::unique_ptr<DB::IDictionarySource, std::__1::default_delete<DB::IDictionarySource> >, DB::ExternalLoadableLifetime, bool, bool,
 std::__1::shared_ptr<DB::Block>)  in /usr/bin/clickhouse
6. 0x6b5f6ae ?  in /usr/bin/clickhouse
7. 0x6b5f8bf ?  in /usr/bin/clickhouse
8. 0x89a2125 DB::DictionaryFactory::create(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, Poco::Util::AbstractConfiguration const&, std::__1::basic_string<ch
ar, std::__1::char_traits<char>, std::__1::allocator<char> > const&, DB::Context const&, bool) const  in /usr/bin/clickhouse
9. 0x8bba46a DB::ExternalDictionariesLoader::create(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, Poco::Util::AbstractConfiguration const&, std::__1::basic_
string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&) const  in /usr/bin/clickhouse
10. 0x8bba9a0 DB::ExternalLoader::createObject(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, DB::ExternalLoader::ObjectConfig const&, std::__1::shared_ptr<D
B::IExternalLoadable const> const&) const  in /usr/bin/clickhouse
11. 0x8bc4b0a DB::ExternalLoader::LoadingDispatcher::doLoading(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, unsigned long, bool, unsigned long, bool)  in /
usr/bin/clickhouse
12. 0x8bbdf36 ThreadFromGlobalPool::ThreadFromGlobalPool<void (DB::ExternalLoader::LoadingDispatcher::*)(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, unsig
ned long, bool, unsigned long, bool), DB::ExternalLoader::LoadingDispatcher*, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >&, unsigned long&, bool&, unsigned long&,
 bool>(void (DB::ExternalLoader::LoadingDispatcher::*&&)(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, unsigned long, bool, unsigned long, bool), DB::Extern
alLoader::LoadingDispatcher*&&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >&, unsigned long&, bool&, unsigned long&, bool&&)::'lambda'()::operator()() const  in /
usr/bin/clickhouse
13. 0x4fa3b77 ThreadPoolImpl<std::__1::thread>::worker(std::__1::__list_iterator<std::__1::thread, void*>)  in /usr/bin/clickhouse
14. 0x4fa212f ?  in /usr/bin/clickhouse
15. 0x76db start_thread  in /lib/x86_64-linux-gnu/libpthread-2.27.so
16. 0x12188f clone  in /lib/x86_64-linux-gnu/libc-2.27.so
 (version 20.1.2.4 (official build)
2020.01.30 11:33:12.736475 [ 31 ] {} <Error> Application: mysqlxx::ConnectionFailed
2020.01.30 11:33:12.736567 [ 31 ] {} <Warning> Application: Connection to [email protected]:3306 as user dspconfig failed: mysqlxx::ConnectionFailed: Lost connection to MySQL server at 'sending authe
ntication information', system error: 2 ((nullptr):3306)

[khamamet]

Same problem here after upgrading to 20.1.3.7

2020.02.05 17:15:24.358214 [ 86 ] {b72784a0-6a8f-44fe-9d0d-369aec6a214c} <Error> executeQuery: Code: 1000, e.displayText() = DB::Exception: Received from 127.0.0.1:9000. DB::Excep
tion: mysqlxx::ConnectionFailed: Lost connection to MySQL server at 'sending authentication information', system error: 2 ((nullptr):3366). Stack trace:
0. 0xbc3212c Poco::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int)  in /usr/bin/clickhouse
1. 0x4f6cbc9 DB::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int)  in /usr/bin/clickhouse
2. 0x4f8b1a0 DB::readException(DB::ReadBuffer&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&)  in /usr/bin/clickhouse
3. 0x914aa9b DB::Connection::receiveException()  in /usr/bin/clickhouse
4. 0x914f68d DB::Connection::receivePacket()  in /usr/bin/clickhouse
5. 0x9160e76 DB::MultiplexedConnections::receivePacket()  in /usr/bin/clickhouse
6. 0x8a93cb4 DB::RemoteBlockInputStream::readImpl()  in /usr/bin/clickhouse
7. 0x8a6d24f DB::IBlockInputStream::read()  in /usr/bin/clickhouse
8. 0x8f05876 DB::LazyBlockInputStream::readImpl()  in /usr/bin/clickhouse
9. 0x8a6d24f DB::IBlockInputStream::read()  in /usr/bin/clickhouse
10. 0x8c2a6ec DB::ParallelInputsProcessor<DB::UnionBlockInputStream::Handler>::thread(std::__1::shared_ptr<DB::ThreadGroupStatus>, unsigned long)  in /usr/bin/clickhouse
11. 0x8c2b465 ThreadFromGlobalPool::ThreadFromGlobalPool<void (DB::ParallelInputsProcessor<DB::UnionBlockInputStream::Handler>::*)(std::__1::shared_ptr<DB::ThreadGroupStatus>, unsig
ned long), DB::ParallelInputsProcessor<DB::UnionBlockInputStream::Handler>*, std::__1::shared_ptr<DB::ThreadGroupStatus>, unsigned long&>(void (DB::ParallelInputsProcessor<DB::Union
BlockInputStream::Handler>::*&&)(std::__1::shared_ptr<DB::ThreadGroupStatus>, unsigned long), DB::ParallelInputsProcessor<DB::UnionBlockInputStream::Handler>*&&, std::__1::shared_pt
r<DB::ThreadGroupStatus>&&, unsigned long&)::'lambda'()::operator()() const  in /usr/bin/clickhouse
12. 0x4fa3a77 ThreadPoolImpl<std::__1::thread>::worker(std::__1::__list_iterator<std::__1::thread, void*>)  in /usr/bin/clickhouse
13. 0x4fa202f ?  in /usr/bin/clickhouse
14. 0x76db start_thread  in /lib/x86_64-linux-gnu/libpthread-2.27.so
15. 0x12188f clone  in /lib/x86_64-linux-gnu/libc-2.27.so

[bezzil85]

same problem

SQL Error [1000]: ClickHouse exception, code: 1000, host: localhost, port: 8123; Poco::Exception. Code: 1000, e.code() = 2013, e.displayText() = mysqlxx::ConnectionFailed: Lost connection to MySQL server at 'sending authentication information', system error: 2 ((nullptr):3306) (version 20.1.3.7 (official build))

[SergeiSadov]

We have struggled the same problem since the update. Downgrading to 19.17.9.60 helped to solve this issue.

SQL Error [1000]: ClickHouse exception, code: 1000, host: localhost, port: 8123; Poco::Exception. >Code: 1000, e.code() = 2013, e.displayText() = mysqlxx::ConnectionFailed: Lost connection to >MySQL server at 'sending authentication information', system error: 2 ((nullptr):3306) (version >20.1.3.7 (official build))

[ssserj]

20.1.6.30 release fixed that issue.
@josepowera @khamamet @bezzil85 @SergeiSadov please check on your clusters.