[Bug 1999711] Re: Snmptrapd cannot reconnect to MySQL server after hitting MySQL wait_timeout
Mauricio Faria de Oliveira
1999711 at bugs.launchpad.net
Thu Feb 23 16:33:28 UTC 2023
Thanks for the detailed steps to reproduce!
Just a copy/paste from pastebin (internal).
> Here are the steps to reproduce the issue.
> https://pastebin.canonical.com/p/MkZ9PR7kmh/
Steps to reproduce:
A. Launch 3 VMs (1 for MySQL Server, 1 Xenial snmptrapd client and 1
Focal snmptrapd client):
uvt-kvm create mysqlvm release=focal label=release --memory 2048 --cpu 2
--disk 10 --bridge virbr0 --ssh-public-key-file
/home/fabio/.ssh/id_rsa.pub
uvt-kvm create snmptrap-focal release=focal label=release --memory 2048
--cpu 2 --disk 10 --bridge virbr0 --ssh-public-key-file
/home/fabio/.ssh/id_rsa.pub
uvt-kvm create snmptrap-xenial release=xenial label=release --memory
2048 --cpu 2 --disk 10 --bridge virbr0 --ssh-public-key-file
/home/fabio/.ssh/id_rsa.pub
B. Prepare the MySQL server (mysqlvm):
1. Installed mysql-server:
sudo apt install mysql-server
2. Configured it to require a root password (not really relevant here):
systemctl stop mysql
sudo systemctl set-environment MYSQLD_OPTS="--skip-networking --skip-grant-tables"
sudo systemctl start mysql.service
sudo mysql -u root
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> USE mysql
Database changed
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'the-new-password';
Query OK, 0 rows affected (0.01 sec)
mysql> quit;
sudo systemctl unset-environment MYSQLD_OPTS
sudo systemctl revert mysql
sudo killall -u mysql
sudo systemctl restart mysql.service
4. Connected to mysql and created the necessary user / database / tables
for snmptrapd to work:
mysql -u root -p
# used the password defined previously (the-new-password)
mysql> create database net_snmp;
Query OK, 1 row affected (0.02 sec)
mysql> create user 'remotesnmp'@'%' identified by 'password';
Query OK, 0 rows affected (0.02 sec)
mysql> grant all privileges on net_snmp.* to 'remotesnmp'@'%';
Query OK, 0 rows affected (0.15 sec)
mysql> USE net_snmp;
Database changed
mysql> DROP TABLE IF EXISTS notifications;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> CREATE TABLE IF NOT EXISTS `notifications` (
-> `trap_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
-> `date_time` datetime NOT NULL,
-> `host` varchar(255) NOT NULL,
-> `auth` varchar(255) NOT NULL,
-> `type`
-> ENUM('get','getnext','response','set','trap','getbulk','inform','trap2','report') NOT NULL,
-> `version` ENUM('v1','v2c', 'unsupported(v2u)','v3') NOT NULL,
-> `request_id` int(11) unsigned NOT NULL,
-> `snmpTrapOID` varchar(1024) NOT NULL,
-> `transport` varchar(255) NOT NULL,
-> `security_model` ENUM('snmpV1','snmpV2c','USM') NOT NULL,
-> `v3msgid` int(11) unsigned,
-> `v3security_level` ENUM('noAuthNoPriv','authNoPriv','authPriv'),
-> `v3context_name` varchar(32),
-> `v3context_engine` varchar(64),
-> `v3security_name` varchar(32),
-> `v3security_engine` varchar(64),
-> PRIMARY KEY (`trap_id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected, 3 warnings (0.04 sec)
mysql> DROP TABLE IF EXISTS varbinds;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE TABLE IF NOT EXISTS `varbinds` (
-> `trap_id` int(11) unsigned NOT NULL default '0',
-> `oid` varchar(1024) NOT NULL,
-> `type` ENUM('boolean','integer','bit','octet','null','oid','ipaddress','counter','unsigned','timeticks','opaque','unused1','counter64','unused2') NOT NULL,
-> `value` blob NOT NULL,
-> KEY `trap_id` (`trap_id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected, 1 warning (0.05 sec)
mysql> exit
Bye
5. Edit the mysqld configuration file to let it bind to all network
interfaces and also set wait_timeout:
# vi /etc/mysql/mysql.conf.d/mysqld.cnf
...
[mysqld]
#
# * Basic Settings
#
user = mysql
# pid-file = /var/run/mysqld/mysqld.pid
# socket = /var/run/mysqld/mysqld.sock
# port = 3306
# datadir = /var/lib/mysql
wait_timeout = 660
# If MySQL is running as a replication slave, this should be
# changed. Ref https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmpdir
# tmpdir = /tmp
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 0.0.0.0
mysqlx-bind-address = 0.0.0.0
Restart the mysql database:
root at mysqlvm:/etc/mysql/mysql.conf.d# systemctl restart mysql.service
6. Connect to the database and set the wait_timeout and interactive_timeout:
root at mysqlvm:/etc/mysql/mysql.conf.d# mysql -u root -p
Enter password:
mysql> SET interactive_timeout=660;
Query OK, 0 rows affected (0.00 sec)
mysql> SET wait_timeout=660;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE "%wait%";
+---------------------------------------------------+----------+
| Variable_name | Value |
+---------------------------------------------------+----------+
| innodb_lock_wait_timeout | 50 |
| innodb_log_wait_for_flush_spin_hwm | 400 |
| innodb_spin_wait_delay | 6 |
| innodb_spin_wait_pause_multiplier | 50 |
| lock_wait_timeout | 31536000 |
| mysqlx_wait_timeout | 28800 |
| performance_schema_events_waits_history_long_size | 10000 |
| performance_schema_events_waits_history_size | 10 |
| wait_timeout | 660 |
+---------------------------------------------------+----------+
9 rows in set (0.01 sec)
mysql> quit
Bye
C. Prepare the snmptrapd clients:
1. On Focal, add the PPA that contains the patched snmptrapd:
https://launchpad.net/~canonical-support-eng-
att/+archive/ubuntu/sf339433-test
2. On both, install the snmptrapd packages:
sudo apt-get install snmp snmpd snmptrapd snmp-mibs-downloader
3. On both Xenial and Focal, install the mysql client:
Focal: sudo apt install mysql-client-core-8.0 -y
Xenial: sudo apt install mysql-client-core-5.7 -y
4. Edit the client configuration file:
# cat /etc/mysql/conf.d/mysql.cnf
[mysql]
user=remotesnmp
password=password
host=192.168.123.109
5. Test that you can connect to the database:
# mysql
mysql> use net_snmp;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+--------------------+
| Tables_in_net_snmp |
+--------------------+
| notifications |
| varbinds |
+--------------------+
2 rows in set (0.01 sec)
6. On both, setup the snmptrapd mysql credentials and add the wait_timeout:
# cat /etc/mysql/conf.d/snmptrapd.cnf
[snmptrapd]
user=remotesnmp
password=password
host=192.168.123.109
wait_timeout=660
7. Configure snmptrapd:
# cat /etc/snmp/snmptrapd.conf
disableAuthorization yes
authCommunity log mytrapcommunity
traphandle default /usr/bin/logger
sqlMaxQueue 1
sqlSaveInterval 9
On Xenial you also need to edit /etc/default/snmptrapd and set
TRAPDRUN=yes
sudo systemctl restart snmptrapd.service
8. On both, test that snmptrapd is working:
sudo snmptrap -v 2c -c mytrapcommunity $(ip -4 --brief address show enp1s0 | awk '{print $3}' | cut -d \/ -f 1) "" 1.2.3.4.0
snmptrap -v 2c -c public $(ip -4 --brief address show enp1s0 | awk '{print $3}' | cut -d \/ -f 1) '' 0 0 s "Hello, this is an SNMP trap."
Check that it was logged to /var/log/syslog on the client, and also that it was added to the mysql database:
mysql
use net_snmp;
select * from notifications;
select * from varbinds;
quit;
D. Effectively reproduce the problem:
1. Send a trap (I'm using the same long trap that the customer gave as
an example, but I believe any trap would be valid) + sleep 660 + try to
send a couple more traps:
/usr/bin/snmptrap -v 2c -c public $(ip -4 --brief address show enp1s0 |
awk '{print $3}' | cut -d \/ -f 1) '' '.1.3.6.1.4.1.9694.1.4.3.0.46'
.1.3.6.1.4.1.9694.1.4.1.18 u 723419 .1.3.6.1.4.1.9694.1.4.1.66 s "TCP
RST, SNMP Amplification" .1.3.6.1.4.1.9694.1.1.1.3 s incoming
.1.3.6.1.4.1.9694.1.1.1.11 s "2020-09-23 16:13:45 UTC"
.1.3.6.1.4.1.9694.1.1.1.12 t 30900 .1.3.6.1.4.1.9694.1.1.1.1 s
$(hostname) .1.3.6.1.4.1.9694.1.4.1.69 s "170.185.88.19"
.1.3.6.1.4.1.9694.1.4.1.68 i 1 .1.3.6.1.4.1.9694.1.4.1.70 u 15835539
.1.3.6.1.4.1.9694.1.4.1.71 u 44590 .1.3.6.1.4.1.9694.1.4.1.92 C 15835539
.1.3.6.1.4.1.9694.1.4.1.93 C 44590 .1.3.6.1.4.1.9694.1.1.1.16 s high
.1.3.6.1.4.1.9694.1.4.1.67 s "KENTUCKY_DEPT_EDU-MAIN_PIM"
sleep 660
/usr/bin/snmptrap -v 2c -c public $(ip -4 --brief address show enp1s0 |
awk '{print $3}' | cut -d \/ -f 1) '' '.1.3.6.1.4.1.9694.1.4.3.0.46'
.1.3.6.1.4.1.9694.1.4.1.18 u 723419 .1.3.6.1.4.1.9694.1.4.1.66 s "TCP
RST, SNMP Amplification" .1.3.6.1.4.1.9694.1.1.1.3 s incoming
.1.3.6.1.4.1.9694.1.1.1.11 s "2020-09-23 16:13:45 UTC"
.1.3.6.1.4.1.9694.1.1.1.12 t 30900 .1.3.6.1.4.1.9694.1.1.1.1 s
$(hostname) .1.3.6.1.4.1.9694.1.4.1.69 s "170.185.88.19"
.1.3.6.1.4.1.9694.1.4.1.68 i 1 .1.3.6.1.4.1.9694.1.4.1.70 u 15835539
.1.3.6.1.4.1.9694.1.4.1.71 u 44590 .1.3.6.1.4.1.9694.1.4.1.92 C 15835539
.1.3.6.1.4.1.9694.1.4.1.93 C 44590 .1.3.6.1.4.1.9694.1.1.1.16 s high
.1.3.6.1.4.1.9694.1.4.1.67 s "KENTUCKY_DEPT_EDU-MAIN_PIM"
/usr/bin/snmptrap -v 2c -c public $(ip -4 --brief address show enp1s0 |
awk '{print $3}' | cut -d \/ -f 1) '' '.1.3.6.1.4.1.9694.1.4.3.0.46'
.1.3.6.1.4.1.9694.1.4.1.18 u 723419 .1.3.6.1.4.1.9694.1.4.1.66 s "TCP
RST, SNMP Amplification" .1.3.6.1.4.1.9694.1.1.1.3 s incoming
.1.3.6.1.4.1.9694.1.1.1.11 s "2020-09-23 16:13:45 UTC"
.1.3.6.1.4.1.9694.1.1.1.12 t 30900 .1.3.6.1.4.1.9694.1.1.1.1 s
$(hostname) .1.3.6.1.4.1.9694.1.4.1.69 s "170.185.88.19"
.1.3.6.1.4.1.9694.1.4.1.68 i 1 .1.3.6.1.4.1.9694.1.4.1.70 u 15835539
.1.3.6.1.4.1.9694.1.4.1.71 u 44590 .1.3.6.1.4.1.9694.1.4.1.92 C 15835539
.1.3.6.1.4.1.9694.1.4.1.93 C 44590 .1.3.6.1.4.1.9694.1.1.1.16 s high
.1.3.6.1.4.1.9694.1.4.1.67 s "KENTUCKY_DEPT_EDU-MAIN_PIM"
- On Xenial we'll observe that trap 1 works, trap 2 fails with "SQL Error 2013 (HY000): Lost connection to MySQL server during query" / Error 2006 (HY000): MySQL server has gone away, trap 3 works
- On Focal we'll observe that trap 1 works, trap 2 and 3 fails with SQL
Error 4031 (HY000): The client was disconnected by the server because of
inactivity. The problem will persist until we restart snmptrapd.
So, it seems that Focal will not re-establish the connection to the MySQL database after getting disconnected due to inactivity, while Xenial does.
--
You received this bug notification because you are a member of Ubuntu
Sponsors Team, which is subscribed to the bug report.
https://bugs.launchpad.net/bugs/1999711
Title:
Snmptrapd cannot reconnect to MySQL server after hitting MySQL
wait_timeout
Status in net-snmp package in Ubuntu:
In Progress
Status in net-snmp source package in Bionic:
In Progress
Status in net-snmp source package in Focal:
In Progress
Status in net-snmp source package in Jammy:
In Progress
Status in net-snmp source package in Kinetic:
In Progress
Status in net-snmp source package in Lunar:
In Progress
Bug description:
[Impact]
wait_timeout is the number of seconds the MySQL server waits for activity before closing the connection.
MySQL v8.0.24 writes the reason for the connection before closing it, and the client receives a more informative error message (ER_CLIENT_INTERACTION_TIMEOUT).
Snmptrapd does not handle this error code, so the connection will not reconnect to the MySQL server afterward.
[Test Plan]
1. Setup MySQL server and modify wait_timeout to 15 in /etc/MySQL/mysql.conf.d/mysqld.cnf
2. Setup snmptrapd and execute snmptrap command
3. Sleep 15 seconds and re-execute snmptrap command, then the connection will fail until the snmptrapd service restarted
[Where problems could occur]
Once the error happens, snmptrapd will keep the sql commands and resend it after reconnecting to the MySQL server.
The regression can be considered as low.
[Other Info]
To manage notifications about this bug go to:
https://bugs.launchpad.net/ubuntu/+source/net-snmp/+bug/1999711/+subscriptions
More information about the Ubuntu-sponsors
mailing list