[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]
Attached is a patch that resolves the issue on Bionic.