Python MySQL Connector occasionally fails to insert/update
I have the problem that my queries occasionally fail to insert/update, however, no error is thrown in the python script or in the database log.
Here is the detailed explanation:
Changes I made to my.cnf of MySQL Server without resolving the problem.
[mysqld] max_allowed_packet=256M max_connections=4000 interactive_timeout=6000 connect_timeout=6000 wait_timeout=6000 log_error_verbosity=1
The following queries are executed within two python scripts (one sender, one receiver):
'''INSERT INTO samples(timestamp_packaging_start, timestamp_udp_send) VALUES(%s,%s) ON DUPLICATE KEY UPDATE timestamp_udp_send=%s ''' '''INSERT INTO samples(timestamp_packaging_end, timestamp_packaging_start) VALUES(%s,%s) ON DUPLICATE KEY UPDATE timestamp_packaging_end=%s ''' '''INSERT INTO samples(timestamp_packaging_start, timestamp_udp_receive) VALUES(%s,%s) ON DUPLICATE KEY UPDATE timestamp_udp_receive=%s ''' '''INSERT INTO samples(timestamp_receiver_start, timestamp_receiver_end, sender, cpuusage, tier, active, timestamp_packaging_start) VALUES(%s,%s,%s,%s,%s,%s,%s) ON DUPLICATE KEY UPDATE timestamp_receiver_start=%s, timestamp_receiver_end=%s, sender=%s, cpuusage=%s, tier=%s, active=%s '''
however, only the very first query occasionally fails without error, leaving this kind of entry in the database:
+------------+----------+------+--------+----------------------------+----------------------------+--------------------+-----------------------+----------------------------+----------------------------+ | sender | cpuusage | tier | active | timestamp_packaging_start | timestamp_packaging_end | timestamp_udp_send | timestamp_udp_receive | timestamp_receiver_start | timestamp_receiver_end | +------------+----------+------+--------+----------------------------+----------------------------+--------------------+-----------------------+----------------------------+----------------------------+ | webserver2 | 0.0 | 3 | 0 | 16:07:2020:13:10:11:371637 | 16:07:2020:13:10:12:490528 | NULL | 13:10:12.490810 | 16:07:2020:13:10:12:491818 | 16:07:2020:13:10:12:491897 | +------------+----------+------+--------+----------------------------+----------------------------+--------------------+-----------------------+----------------------------+----------------------------+
The other queries never fail! The queries are executed with pythons mysql-connector (python version 2.7). This specific query is executed in a second thread, however, as others are executed in different threads as well, I do not think the problem is a result of multithreading.
I always use commit()
after executing the queries. For every thread an individual mysql-connection is used.
The python scripts (one receiver and one sender) run as ubuntu systemctl services. The following service configuration is used:
[Unit] Description=Test Service After=multi-user.target [email protected] [Service] Type=simple ExecStart=/usr/bin/python /home/service/monitoring/sendTrapv1Multi.py StandardInput=tty-force [Install] WantedBy=multi-user.target
I can provide my full code if wanted, however, I tried to explain all the necessary information.