This bug was fixed in the package net-snmp - 5.9.3+dfsg-1ubuntu1.4
---------------
net-snmp (5.9.3+dfsg-1ubuntu1.4) kinetic; urgency=medium
* Fix snmptrapd reconnection issue after hitting MySQL wait_timeout
(LP: #1999711)
- d/p/snmptrapd-mysql-reconnection-after-hitting-wait_timeout.patch
- d/p/snmptrapd-mysql-fix-build-error.patch
-- Chengen Du <chengen...@canonical.com> Fri, 17 Feb 2023 03:41:21
+0000
** Changed in: net-snmp (Ubuntu Kinetic)
Status: Fix Committed => Fix Released
--
You received this bug notification because you are a member of SE
("STS") Sponsors, 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:
Fix Released
Status in net-snmp source package in Bionic:
Incomplete
Status in net-snmp source package in Focal:
Fix Committed
Status in net-snmp source package in Jammy:
Fix Committed
Status in net-snmp source package in Kinetic:
Fix Released
Status in net-snmp source package in Lunar:
Fix Released
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. Launch 2 VMs (1 for MySQL Server, 1 for snmptrapd client in focal)
2. Prepare the MySQL server
2-1. Installed mysql-server
# sudo apt install mysql-server
2-2. Configured it to require a root password
# 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
3. Connected to mysql and created the necessary user / database / tables for
snmptrapd to work
# mysql -u root -p
# 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
4. 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
...
# systemctl restart mysql.service
5. Connect to the database and set the wait_timeout and interactive_timeout:
# mysql -u root -p
# 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
6. Prepare the snmptrapd clients:
6-1. Install the snmptrapd, mysql client:
# sudo apt-get install snmp snmpd snmptrapd snmp-mibs-downloader
mysql-client-core-8.0
6-2.. Edit the client configuration file
# cat /etc/mysql/conf.d/mysql.cnf
[mysql]
user=remotesnmp
password=password
host=XXX.XX.XX.XXX
6-3. Test that you can connect to the database:
# 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-4. Setup the snmptrapd mysql credentials and add the wait_timeout:
# cat /etc/mysql/conf.d/snmptrapd.cnf
[snmptrapd]
user=remotesnmp
password=password
host=XXX.XX.XX.XXX
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
# sudo systemctl restart snmptrapd.service
8. Send a trap / sleep 660 / try to send a couple more traps:
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.
[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
--
Mailing list: https://launchpad.net/~sts-sponsors
Post to : sts-sponsors@lists.launchpad.net
Unsubscribe : https://launchpad.net/~sts-sponsors
More help : https://help.launchpad.net/ListHelp
