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 Conflicts=getty@tty1.service  [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.

Add Comment
0 Answer(s)

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.