spine dies with Lost connection to MySQL server during query

[chilek]

Switched from mysql 5.5.39 to mariadb 5.5.39. All problems have been solved!

I've reviewed sql/net_serv.c from mysql and mariadb source.
mysql code checks THREAD_SAFE_CLIENT definition which apparently is not set, but
code ifdef-ed with THREAD_SAFE_CLIENT is responsible for retries after read operations
finished with EINTR status. There is no reasonable way to set THREAD_SAFE_CLIENT
for mysql compilation process. At the same time in mariadb we don't have such issue,
because they depend only on MYSQL_SERVER definition (more sane in my opinion).

[apfjunior]

I also realized the migration to MariaDB (plus some adjustments in parameters max_connections and wait_timeout), and the problems were resolved. Memory usage and CPU were also reduced. Thank you!

[gmx168]

I was just able to spend some more time working on this. The problem seems to be related to spine being built against MySQL-5.5 client libraries. I reverted mysql to 5.1.66 and recompiled spine against those libraries and the problem is solved. specifically libmysqlclient_r.so.16 instead of libmysqlclient_r.so.18 where this problem was occurring. I think that I can probably reinstall MySQL 5.5.x as long as I were to keep the .so.16 for my current spine binary and not rebuild spine against the so.18. I will try to test this next week.

Thanks MrRat. I work it out by using your solution.

8 CPU
32GB RAM
CentOS 6.5 2.6.32-431.23.3.el6.x86_64 #1 SMP Thu Jul 31 17:20:51 UTC 2014 x86_64 x86_64 x86_64 GNU/Linux

cacti-0.8.8b
cacti-spine-0.8.8b

MySQL-client-5.6.15-1.el6.x86_64
MySQL-shared-compat-5.6.15-1.el6.x86_64
MySQL-server-5.6.15-1.el6.x86_64
MySQL-devel-5.6.15-1.el6.i686
MySQL-shared-5.6.15-1.el6.x86_64

net-snmp-utils-5.5-49.el6_5.4.x86_64
net-snmp-devel-5.5-49.el6_5.4.x86_64
net-snmp-libs-5.5-49.el6_5.4.x86_64
net-snmp-5.5-49.el6_5.4.x86_64

php-cli-5.3.3-27.el6_5.1.x86_64
php-5.3.3-27.el6_5.1.x86_64
php-mysql-5.3.3-27.el6_5.1.x86_64

Maximum Concurrent Poller Processes 8
Maximum Threads per Process 8
Number of PHP Script Servers 8
Script and Script Server Timeout Value 40
The Maximum SNMP OID's Per SNMP Get Request 10

poller and cron are both set at 1 minute


Error: SPINE: Poller[0] FATAL: MySQL Error:'2013', Message:'Lost connection to MySQL server during query' (Spine thread)


ll /usr/lib64/libmysql*
lrwxrwxrwx 1 root root 17 Sep 1 16:29 /usr/lib64/libmysqlclient_r.so -> libmysqlclient.so
lrwxrwxrwx 1 root root 26 Sep 1 16:30 /usr/lib64/libmysqlclient_r.so.12 -> libmysqlclient_r.so.12.0.0
-rwxr-xr-x 1 root root 262312 Nov 18 2013 /usr/lib64/libmysqlclient_r.so.12.0.0
lrwxrwxrwx 1 root root 26 Sep 1 16:30 /usr/lib64/libmysqlclient_r.so.14 -> libmysqlclient_r.so.14.0.0
-rwxr-xr-x 1 root root 1253992 Nov 18 2013 /usr/lib64/libmysqlclient_r.so.14.0.0
lrwxrwxrwx 1 root root 26 Sep 1 16:30 /usr/lib64/libmysqlclient_r.so.15 -> libmysqlclient_r.so.15.0.0
-rwxr-xr-x 1 root root 2110184 Nov 18 2013 /usr/lib64/libmysqlclient_r.so.15.0.0
lrwxrwxrwx 1 root root 26 Sep 1 16:30 /usr/lib64/libmysqlclient_r.so.16 -> libmysqlclient_r.so.16.0.0
-rwxr-xr-x 1 root root 2141744 Nov 18 2013 /usr/lib64/libmysqlclient_r.so.16.0.0
lrwxrwxrwx 1 root root 20 Sep 1 16:29 /usr/lib64/libmysqlclient_r.so.18 -> libmysqlclient.so.18
lrwxrwxrwx 1 root root 24 Sep 1 16:29 /usr/lib64/libmysqlclient_r.so.18.1.0 -> libmysqlclient.so.18.1.0
lrwxrwxrwx 1 root root 20 Sep 1 16:29 /usr/lib64/libmysqlclient.so -> libmysqlclient.so.18
lrwxrwxrwx 1 root root 24 Sep 1 16:30 /usr/lib64/libmysqlclient.so.12 -> libmysqlclient.so.12.0.0
-rwxr-xr-x 1 root root 253888 Nov 18 2013 /usr/lib64/libmysqlclient.so.12.0.0
lrwxrwxrwx 1 root root 24 Sep 1 16:30 /usr/lib64/libmysqlclient.so.14 -> libmysqlclient.so.14.0.0
-rwxr-xr-x 1 root root 1242088 Nov 18 2013 /usr/lib64/libmysqlclient.so.14.0.0
lrwxrwxrwx 1 root root 24 Sep 1 16:30 /usr/lib64/libmysqlclient.so.15 -> libmysqlclient.so.15.0.0
-rwxr-xr-x 1 root root 2101416 Nov 18 2013 /usr/lib64/libmysqlclient.so.15.0.0
lrwxrwxrwx 1 root root 24 Sep 1 16:30 /usr/lib64/libmysqlclient.so.16 -> libmysqlclient.so.16.0.0
-rwxr-xr-x 1 root root 2131952 Nov 18 2013 /usr/lib64/libmysqlclient.so.16.0.0
lrwxrwxrwx 1 root root 24 Sep 1 16:29 /usr/lib64/libmysqlclient.so.18 -> libmysqlclient.so.18.1.0
-rwxr-xr-x 1 root root 8850238 Nov 18 2013 /usr/lib64/libmysqlclient.so.18.1.0


ldd /usr/local/spine/bin/spine
linux-vdso.so.1 => (0x00007fff121f4000)
libnetsnmp.so.20 => /usr/lib64/libnetsnmp.so.20 (0x00007fc65dd32000)
libmysqlclient.so.18 => /usr/lib64/libmysqlclient.so.18 (0x00007fc65d766000)
libcrypto.so.10 => /usr/lib64/libcrypto.so.10 (0x00007fc65d386000)
libz.so.1 => /lib64/libz.so.1 (0x00007fc65d170000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fc65cf52000)
libm.so.6 => /lib64/libm.so.6 (0x00007fc65ccce000)
libc.so.6 => /lib64/libc.so.6 (0x00007fc65c93a000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007fc65c735000)
librt.so.1 => /lib64/librt.so.1 (0x00007fc65c52d000)
libstdc++.so.6 => /usr/lib64/libstdc++.so.6 (0x00007fc65c227000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007fc65c010000)
/lib64/ld-linux-x86-64.so.2 (0x00007fc65e017000)

#### above shows the spine is compiled with libmysqlclient.so.18 ####


#### change the symbolic link to libmysqlclient_r.so.16 ####
cd /usr/lib64
rm -f libmysqlclient_r.so
ln -s libmysqlclient_r.so.16 libmysqlclient_r.so
rm -f libmysqlclient.so
ln -s libmysqlclient.so.16 libmysqlclient.so


#### here is the result ####
ll /usr/lib64/libmysqlclient*
lrwxrwxrwx 1 root root 33 Oct 14 14:32 /usr/lib64/libmysqlclient_r.so -> /usr/lib64/libmysqlclient_r.so.16
lrwxrwxrwx 1 root root 26 Sep 1 16:30 /usr/lib64/libmysqlclient_r.so.12 -> libmysqlclient_r.so.12.0.0
-rwxr-xr-x 1 root root 262312 Nov 18 2013 /usr/lib64/libmysqlclient_r.so.12.0.0
lrwxrwxrwx 1 root root 26 Sep 1 16:30 /usr/lib64/libmysqlclient_r.so.14 -> libmysqlclient_r.so.14.0.0
-rwxr-xr-x 1 root root 1253992 Nov 18 2013 /usr/lib64/libmysqlclient_r.so.14.0.0
lrwxrwxrwx 1 root root 26 Sep 1 16:30 /usr/lib64/libmysqlclient_r.so.15 -> libmysqlclient_r.so.15.0.0
-rwxr-xr-x 1 root root 2110184 Nov 18 2013 /usr/lib64/libmysqlclient_r.so.15.0.0
lrwxrwxrwx 1 root root 26 Sep 1 16:30 /usr/lib64/libmysqlclient_r.so.16 -> libmysqlclient_r.so.16.0.0
-rwxr-xr-x 1 root root 2141744 Nov 18 2013 /usr/lib64/libmysqlclient_r.so.16.0.0
lrwxrwxrwx 1 root root 20 Sep 1 16:29 /usr/lib64/libmysqlclient_r.so.18 -> libmysqlclient.so.18
lrwxrwxrwx 1 root root 24 Sep 1 16:29 /usr/lib64/libmysqlclient_r.so.18.1.0 -> libmysqlclient.so.18.1.0
lrwxrwxrwx 1 root root 31 Oct 14 14:33 /usr/lib64/libmysqlclient.so -> /usr/lib64/libmysqlclient.so.16
lrwxrwxrwx 1 root root 24 Sep 1 16:30 /usr/lib64/libmysqlclient.so.12 -> libmysqlclient.so.12.0.0
-rwxr-xr-x 1 root root 253888 Nov 18 2013 /usr/lib64/libmysqlclient.so.12.0.0
lrwxrwxrwx 1 root root 24 Sep 1 16:30 /usr/lib64/libmysqlclient.so.14 -> libmysqlclient.so.14.0.0
-rwxr-xr-x 1 root root 1242088 Nov 18 2013 /usr/lib64/libmysqlclient.so.14.0.0
lrwxrwxrwx 1 root root 24 Sep 1 16:30 /usr/lib64/libmysqlclient.so.15 -> libmysqlclient.so.15.0.0
-rwxr-xr-x 1 root root 2101416 Nov 18 2013 /usr/lib64/libmysqlclient.so.15.0.0
lrwxrwxrwx 1 root root 24 Sep 1 16:30 /usr/lib64/libmysqlclient.so.16 -> libmysqlclient.so.16.0.0
-rwxr-xr-x 1 root root 2131952 Nov 18 2013 /usr/lib64/libmysqlclient.so.16.0.0
lrwxrwxrwx 1 root root 24 Sep 1 16:29 /usr/lib64/libmysqlclient.so.18 -> libmysqlclient.so.18.1.0
-rwxr-xr-x 1 root root 8850238 Nov 18 2013 /usr/lib64/libmysqlclient.so.18.1.0


#### re-complie spine ####
cd cacti-spine-0.8.8b
./configure
make
make install

#### the spine is already compiled with libmysqlclient_r.so.16 ####
ldd /usr/local/spine/bin/spine
linux-vdso.so.1 => (0x00007fff4f586000)
libnetsnmp.so.20 => /usr/lib64/libnetsnmp.so.20 (0x00007fc555f7f000)
libmysqlclient_r.so.16 => /usr/lib64/libmysqlclient_r.so.16 (0x00007fc555b72000)
libcrypto.so.10 => /usr/lib64/libcrypto.so.10 (0x00007fc555792000)
libz.so.1 => /lib64/libz.so.1 (0x00007fc55557c000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fc55535e000)
libm.so.6 => /lib64/libm.so.6 (0x00007fc5550da000)
libc.so.6 => /lib64/libc.so.6 (0x00007fc554d46000)
libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007fc554b0e000)
libnsl.so.1 => /lib64/libnsl.so.1 (0x00007fc5548f5000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007fc5546f1000)
/lib64/ld-linux-x86-64.so.2 (0x00007fc556264000)
libfreebl3.so => /lib64/libfreebl3.so (0x00007fc554479000)

#### restore the symbolic link to libmysqlclient.so.18 ####
cd /usr/lib64
rm -f libmysqlclient_r.so
rm -f libmysqlclient.so
ln -s libmysqlclient.so.18 libmysqlclient.so
ln -s libmysqlclient.so libmysqlclient_r.so

#### related info ####
General Information
Date Tue, 14 Oct 2014 16:24:15 +0800
Cacti Version 0.8.8b
Cacti OS unix
SNMP Version NET-SNMP version: 5.5
RRDTool Version RRDTool 1.4.x
Hosts 30
Graphs 932
Data Sources Script/Command: 99
SNMP: 140
SNMP Query: 159
Script - Script Server (PHP): 63
Script Query - Script Server: 455
Total: 916
Poller Information
Interval 60
Type SPINE 0.8.8b Copyright 2002-2013 by The Cacti Group
Items Action[0]: 474
Action[1]: 99
Action[2]: 626
Total: 1199
Concurrent Processes 8
Max Threads 8
PHP Servers 8
Script Timeout 40
Max OID 20
Last Run Statistics Time:21.8612 Method:spine Processes:8 Threads:8 Hosts:31 HostsPerProcess:4 DataSources:1199 RRDsProcessed:916
PHP Information
PHP Version 5.3.3
PHP OS Linux
PHP uname Linux 2.6.32-431.23.3.el6.x86_64 #1 SMP Thu Jul 31 17:20:51 UTC 2014 x86_64
PHP SNMP Not Installed
max_execution_time 30
memory_limit 128M

[MrRat]

It appears that this is still broken in MySQL 5.6 as 5.6 still uses the broken libmysqlclient.so.18 ?

[lightenup]

Switched from mysql 5.5.39 to mariadb 5.5.39. All problems have been solved!

This past week I had to upgrade from Ubuntu 10.04 LTS to 12.04LTS to keep things supportable. I too ran into major issues with MySQL 5.5. Thanks for the tip on installing MariaDB! Everyone should know the following goodness regarding MariaDB (from Wikipedia):

MariaDB is a community-developed fork of the MySQL relational database management system intended to remain free under the GNU GPL. Being a fork of a leading open source software system, it is notable for being led by the original developers of MySQL, who forked it due to concerns over its acquisition by Oracle.[5] Contributors are required to share their copyright with the MariaDB Foundation.[6] The intent is also to maintain high compatibility with MySQL, ensuring a "drop-in" replacement capability with library binary equivalency and exact matching with MySQL APIs and commands.

You don't need to reconfigure a thing in Cacti! Just remove MySQL 5.5, add the MariaDB sources to apt, and install. At this point you are done! It uses the same config files as MySQL, format and all! I too also saw lower memory, and cpu usage, more consistent polling and boost times.

[martos]

Switched from mysql 5.5.39 to mariadb 5.5.39. All problems have been solved!

This past week I had to upgrade from Ubuntu 10.04 LTS to 12.04LTS to keep things supportable. I too ran into major issues with MySQL 5.5. Thanks for the tip on installing MariaDB! Everyone should know the following goodness regarding MariaDB (from Wikipedia):

MariaDB is a community-developed fork of the MySQL relational database management system intended to remain free under the GNU GPL. Being a fork of a leading open source software system, it is notable for being led by the original developers of MySQL, who forked it due to concerns over its acquisition by Oracle.[5] Contributors are required to share their copyright with the MariaDB Foundation.[6] The intent is also to maintain high compatibility with MySQL, ensuring a "drop-in" replacement capability with library binary equivalency and exact matching with MySQL APIs and commands.

You don't need to reconfigure a thing in Cacti! Just remove MySQL 5.5, add the MariaDB sources to apt, and install. At this point you are done! It uses the same config files as MySQL, format and all! I too also saw lower memory, and cpu usage, more consistent polling and boost times.

Works like a charm

[X505]

I was just able to spend some more time working on this. The problem seems to be related to spine being built against MySQL-5.5 client libraries. I reverted mysql to 5.1.66 and recompiled spine against those libraries and the problem is solved. specifically libmysqlclient_r.so.16 instead of libmysqlclient_r.so.18 where this problem was occurring. I think that I can probably reinstall MySQL 5.5.x as long as I were to keep the .so.16 for my current spine binary and not rebuild spine against the so.18. I will try to test this next week.

Thanks MrRat. I work it out by using your solution.
[...]
#### above shows the spine is compiled with libmysqlclient.so.18 ####
[...]
#### change the symbolic link to libmysqlclient_r.so.16 ####
[...]
#### re-complie spine ####
[...]
#### the spine is already compiled with libmysqlclient_r.so.16 ####
[...]
#### restore the symbolic link to libmysqlclient.so.18 ####

Thanks, it works for me but did you manage to get rid of those warnings?

Code: Select all

SPINE: Poller[0] WARNING: MySQL is NOT Thread Safe!