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 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=3 #changed afterwards, see comments

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.

EDIT://
Changed MySQL configuration

EDIT2://
To reconnect/commit/query/ I use the following code, which also should catch exceptions/errors. It does not show any exception whatsoever:

    try:
        conn.ping(reconnect=True)      
        sql = '''INSERT INTO samples(timestamp_packaging_start, timestamp_udp_send) VALUES(%s,%s) ON DUPLICATE KEY UPDATE timestamp_udp_send=%s '''
        cur = conn.cursor()
        cur.execute(sql, (timestamp_packaging_start, timestamp_udp_send, timestamp_udp_send))
        conn.commit()
    except mysql.connector.Error as err:
        print("Something went wrong: {}".format(err))

As requested from the comment section here is the output of SHOW CREATE TABLE:

+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                            |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| samples | CREATE TABLE `samples` (
  `sender` text,
  `cpuusage` text,
  `tier` text,
  `active` text,
  `timestamp_packaging_start` varchar(256) NOT NULL,
  `timestamp_packaging_end` text,
  `timestamp_udp_send` text,
  `timestamp_udp_receive` text,
  `timestamp_receiver_start` text,
  `timestamp_receiver_end` text,
  PRIMARY KEY (`timestamp_packaging_start`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

EDIT3://

I changed the DBMS to MariaDB and the problem persists. I also changed the python-connector to pymysql, without resolving the problem.

Reasoning from experience, I think the problem is caused by my python code as nothing else makes sense.
Therefore, here is my code:

from pysnmp.hlapi import *
from pysnmp.proto.api import v2c
import socket
import psutil
import os.path
from os import path
from datetime import datetime
import thread
import io
import time
import subprocess
import sys
import mysql.connector
from mysql.connector import Error


def create_connection():

    lconn = None
    try:
        lconn = mysql.connector.connect(host='x',
                                         database='x',
                                         user='x',
                                         password='x',
                                         autocommit=True)
        return lconn
    except Error as e:
        print("Error while connecting to MySQL", e)
    return lconn

def insert_timestamps(conn, timestamp_packaging_start, timestamp_udp_send):
    try:
        conn.ping(reconnect=True)      
        sql = '''INSERT INTO samples(timestamp_packaging_start, timestamp_udp_send) VALUES(%s,%s) ON DUPLICATE KEY UPDATE timestamp_udp_send=%s '''
        cur = conn.cursor()
        cur.execute(sql,  (timestamp_packaging_start,timestamp_udp_send,timestamp_udp_send))
        conn.commit()
    except mysql.connector.Error as err:
        print("Something went wrong: {}".format(err))
        

def tcpdump():
    global writer
    global reader
    global socket 
    global hostname

    conn2 = create_connection()
    hostname = socket.gethostname()
    filename = 'test.log'
    with io.open(filename, 'wb') as writer, io.open(filename, 'rb', 1) as reader:
        process = subprocess.Popen(['tcpdump', '-Ul' ,'port 162 and host x'], stdout=writer)
        while process.poll() is None:
            packets = reader.read().split("n")
            if packets != ['']:
                for packet in packets:
                    if packet != '':
                        temp_split= packet.split(" ")
                        timestamp_udp_send = temp_split[0]
                        insert_timestamps(conn2, timestamp_packaging_start, timestamp_udp_send)
            time.sleep(0.25)

try:
    conn = create_connection()
    hostname = socket.gethostname()
    thread.start_new_thread(tcpdump,())
    while True:
        timestamp_packaging_start = datetime.now().strftime("%d:%m:%Y:%H:%M:%S:%f")
        
        if str(path.exists('/etc/nginx/sites-enabled/tier1'))=='True':
            tier='1'
        if str(path.exists('/etc/nginx/sites-enabled/tier2'))=='True':
            tier='2'
        if str(path.exists('/etc/nginx/sites-enabled/tier3'))=='True':
            tier='3'

        errorIndication, errorStatus, errorIndex, varBinds = next(
            sendNotification(
                SnmpEngine(),
                CommunityData('public'),
                UdpTransportTarget(('x', 162)),
                ContextData(),
                'trap',
                NotificationType(
                    ObjectIdentity('1.3.6.1.6.3.1.1.5.2')
                ).addVarBinds(
                ObjectType(ObjectIdentity('1.3.6.1.2.1.1.5.0'), hostname),
                ObjectType(ObjectIdentity('1.3.6.1.4.1.2021.10.1.3.1'), OctetString(psutil.cpu_percent(interval=1))),
                ObjectType(ObjectIdentity('1.3.6.1.2.1.1.5.1'), tier),
                ObjectType(ObjectIdentity('1.3.6.1.2.1.1.5.2'), timestamp_packaging_start)
                ),
            )
        )

        if errorIndication:
            print(errorIndication)
        elif errorStatus:
            print('%s at %s' % (errorStatus.prettyPrint(), errorIndex and varBinds[int(errorIndex) - 1][0] or '?'))
        else:
            for varBind in varBinds:
                print(' = '.join([x.prettyPrint() for x in varBind]))

        timestamp_packaging_end = datetime.now().strftime("%d:%m:%Y:%H:%M:%S:%f")


        try:
            sql = '''INSERT INTO samples(timestamp_packaging_end, timestamp_packaging_start) VALUES(%s,%s) ON DUPLICATE KEY UPDATE timestamp_packaging_end=%s '''
            cursor=conn.cursor()
            cursor.execute(sql, (timestamp_packaging_end, timestamp_packaging_start, timestamp_packaging_end))
            conn.commit()
        except mysql.connector.Error as error:
            print("Failed to update record to database: {}".format(error))
except KeyboardInterrupt:
    print('interrupted!')