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

其他查询永远不会失败！查询是使用pythons mysql-connector (python version2.7)执行的。这个特定的查询是在第二个线程中执行的，但是，因为其他查询也是在不同的线程中执行的，所以我不认为问题是多线程造成的。

我总是在执行完查询后使用commit()。对于每个线程，都使用一个单独的mysql连接。

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配置

EDIT2://若要重新连接/提交/查询/I，请使用以下代码，它也应捕获异常/错误。它没有显示任何异常：

    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)

EDIT3://

我将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)作为主键。因为没有人知道何时执行不同线程中的函数，所以有时变量会被下一个条目覆盖，而不是首先执行查询。这导致我的程序更新了另一个错误主键的时间戳。谢谢你们的努力伙计们。