Python MySQL 连接器偶尔无法插入/更新

Question

我的问题是我的查询偶尔无法插入/更新，但是，python 脚本或数据库日志中没有抛出错误。
以下是详细解释:
我对 MySQL 服务器的 my.cnf 所做的更改没有解决问题。

[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

以下查询在两个 Python 脚本(一个发送者，一个接收者)中执行:

'''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 '''

然而，只有第一个查询偶尔会失败而没有错误，在数据库中留下这种条目:

+------------+----------+------+--------+----------------------------+----------------------------+--------------------+-----------------------+----------------------------+----------------------------+
| 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 |
+------------+----------+------+--------+----------------------------+----------------------------+--------------------+-----------------------+----------------------------+----------------------------+

其他查询永远不会失败!
使用 python mysql-connector(python 2.7 版)执行查询。这个特定的查询在第二个线程中执行，但是，由于其他查询也在不同的线程中执行，我认为问题不是多线程的结果。
我总是用 commit()执行查询后。对于每个线程，使用单独的 mysql-connection。
python 脚本(一个接收器和一个发送器)作为 ubuntu systemctl 服务运行。使用以下服务配置:

[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

如果需要，我可以提供我的完整代码，但是，我试图解释所有必要的信息。
编辑://
更改了 MySQL 配置
编辑2://
要重新连接/提交/查询/我使用以下代码，它也应该捕获异常/错误。它没有显示任何异常:

    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))

根据评论部分的要求，这里是 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)

编辑3://
我将 DBMS 更改为 MariaDB，但问题仍然存在。我也把python-connector改成了pymysql，问题没有解决。
从经验推理，我认为问题是由我的 python 代码引起的，因为没有其他意义。
因此，这是我的代码:

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!')

Answer 1

我发现了错误:
我在多个查询中使用全局变量 (timestamp_packaging_start) 作为主键。由于没有人知道何时执行了不同线程中的函数，有时变量会被下一个条目覆盖，而没有首先执行查询。这导致我的程序更新 ANOTHER 和 WRONG 主键的时间戳。谢谢你们的努力。