Python 与 MariaDB 服务器的连接大约每 24 小时关闭一次

Question

我正在为我的公司创建一个产品，用于跟踪带有 RFID 标签的元素，他们在信息亭刷卡，它会记录在数据库中，很简单。

数据库实际上托管在信息亭上(我知道这不是最佳实践)，并且信息亭上运行的主程序是带有 GuiZero 的 python 前端。该信息亭在 Raspberry Pi 上运行 Raspbian Desktop。

一切运行良好，但在程序启动后(大约)24 小时，它会像平常一样运行，直到数据库查询发送到服务器。 python-mariadb-connector 响应:2055: Lost connection to MySQL server at '127.0.0.1:3306', system error: 32 Broken pipeline，我已经对此进行了大约一个月的故障排除我现在知道什么可以解决这个问题。

每当发生这种情况时，我都尝试简单地从 python 前端内部更新连接，但这也不起作用。解决该问题的唯一方法是重新启动程序，但不能相信用户会知道该怎么做。

需要注意的一点是，一切都完美运行，直到到达大约 24 小时标记。

有什么帮助吗？

依赖关系:

Python 3.7
(pip) mysql-connector v2.2.9
MariaDB: 10.3.17-MariaDB-0+deb10ul Raspbian 10
Raspbian GNU/Linux v10

超时变量

MariaDB [locker]> show variables like "%timeout";
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| connect_timeout                       | 10    |
| delayed_insert_timeout                | 300   |
| idle_readonly_transaction_timeout     | 0     |
| idle_transaction_timeout              | 0     |
| idle_write_transaction_timeout        | 0     |
| innodb_flush_log_at_timeout           | 1     |
| innodb_lock_wait_timeout              | 50    |
| innodb_rollback_on_timeout            | OFF   |
| interactive_timeout                   | 28800 |
| lock_wait_timeout                     | 86400 |
| net_read_timeout                      | 30    |
| net_write_timeout                     | 60    |
| rpl_semi_sync_master_timeout          | 10000 |
| rpl_semi_sync_slave_kill_conn_timeout | 5     |
| slave_net_timeout                     | 60    |
| thread_pool_idle_timeout              | 60    |
| wait_timeout                          | 28800 |
+---------------------------------------+-------+

这是代码，第 166 行是处理错误的地方。 (并不是说我的处理有任何作用)Ctrl+F 表示“aghiulg”到达该行。

#!/usr/bin/python3.7
from datetime import datetime
from sys import exit

# Classes
class User:
    def __init__ (self, rfid, name):
        self.name = name
        self.rfid = rfid

class Key:
    def __init__ (self, rfid, kid, prop, loc):
        self.rfid = rfid
        self.kid = kid
        self.prop = prop.split("/")
        self.loc = loc

    def toString (self):
        return "[{}] {}".format(self.kid, "/".join(self.prop[:3]))

# Slack
import slack

slackBotToken = "OBFUSCATEDFORSTACKOVERFLOW"
slackClient = slack.WebClient(slackBotToken)
slackID = None

def getTimeString ():
    now = datetime.now()
    return now.strftime("%m/%d/%Y %H:%M:%S")

def log (s):
    time = getTimeString()

    # stdout
    print("[{}] {}".format(time, s))


    # slack
    slackErr = False
    try:
        slackClient.chat_postMessage(channel = "#keys", text = s)
    except concurrent.futures._base.TimeoutError:
        slackErr = True

    # file
    with open("/home/pi/key-locker/log.txt", "a+") as f:
        f.write("[{}] {}\n".format(time, s))
        if slackErr:
            f.write("Couldn't write that to Slack, oops.")

def xlog (s):
    try:
        slackClient.chat_postMessage(channel = "OBFUSCATED", text = s)
        return True
    except concurrent.futures._base.TimeoutError:
        return False

# guizero
from guizero import App, Text, TextBox, info

app = App(title = "Key Locker")
app.tk.attributes("-fullscreen", True)
REFOCUS_TIMER = 500
USER_TIMEOUT = 60 * 1000

# MariaDB
import mysql.connector as mariadb

def connectDB ():
    xlog("Reconnecting to database...")
    return mariadb.connect(user="OBFUSCATED", password="OBFUSCATED", database="OBFUSCATED")

# mdb = mariadb.connect(user="OBFUSCATED", password="OBFUSCATED", database="OBFUSCATED")
mdb = connectDB()
cursor = mdb.cursor(buffered = True)

def focusUIN (uin = None):
    if uin:
        uin.focus()

def onTextBoxKey (e, f = None):
    global uidBox, kidBox, bigText, underText, currentUser, currentKey, escapeKeys

    if len(e.key) == 0:
        return

    if f == uidBox and ord(e.key) == 13:
        uid = uidBox.value
        if uid.lower() in escapeKeys:
            exit(0)
        else:
            currentUser = codeToUser(uid)
            if currentUser:
                uidBox.cancel(focusUIN)
                uidBox.disable()

                kidBox.enable()
                kidBox.repeat(REFOCUS_TIMER, focusUIN, args = [kidBox])
                kidBox.when_key_pressed = lambda e: onTextBoxKey(e, f = kidBox)

                bigText.value = "Scan Key"
                underText.value = "Welcome, {}.".format(currentUser.name)

                app.after(USER_TIMEOUT, restart, args = ["User Timeout"])
            else:
                restart("That user doesn't exist.")
    elif f == kidBox and ord(e.key) == 13:
        kid = kidBox.value
        if kid.lower() in escapeKeys:
            exit(0)
        else:
            app.cancel(restart)

            currentKey = codeToKey(kid)
            if currentKey:
                kidBox.cancel(focusUIN)
                kidBox.disable()

                inLocker = (currentKey.loc.lower() == "locker")
                success = (checkout(currentUser, currentKey) if inLocker else checkin(currentUser, currentKey))
                if success:
                    restart("{} checked {} the {} keys.".format(currentUser.name, "out" if inLocker else "in", currentKey.toString()))
                else:
                    restart("System error, try again.")
            else:
                restart("That key doesn't exist.")

def restart (subText = ". . ."):
    global uidBox, kidBox, bigText, underText

    uidBox.value = ""
    uidBox.enable()
    uidBox.cancel(focusUIN)
    uidBox.repeat(REFOCUS_TIMER, focusUIN, args = [uidBox])
    uidBox.focus()

    kidBox.value = ""
    kidBox.cancel(focusUIN)
    kidBox.disable()

    bigText.value = "Scan Badge"
    underText.value = subText

# App
escapeKeys = "letmeout pleaseijustwanttoseemywifeandkidsagain".split(" ")
currentUser = None
currentKey = None

def codeToUser (uid = None):
    xlog("Trying to find user by RFID...")
    if uid:
        try:
            cursor.execute("select Name from user where RFID = '{}';".format(uid))
            names = []
            for n in cursor:
                names.append(n)
            if len(names) != 1:
                xlog("Ran the function, and literally got no matches for that user rfid.")
                xlog("Restarting...")
                exit(0)
                return None
            else:
                xlog("Found user: {}".format(names[0][0]))
                return User(uid, names[0][0])
        except mariadb.Error as e: # aghiulg
            xlog("Database error trying to find the user.\n{}".format(e))
            # fatalError(e)
            return None
    else:
        xlog("They didn't give me an RFID.")
        return None

def codeToKey (kid = None):
    if kid:
        try:
            cursor.execute("select KeyringID, Properties, Location from keyring where RFID = {};".format(kid))
            keys = []
            for k in cursor:
                keys.append(k)
            if len(keys) != 1:
                return None
            else:
                keys = keys[0]
                return Key(kid, keys[0], keys[1], keys[2])
        except mariadb.Error as e:
            # fatalError(e)
            return None
    else:
        return None

def checkout(user, key):
    global mdb

    log("Checking out k{} ({}) to {}.".format(key.kid, ", ".join(key.prop), user.name))
    try:
        cursor.execute("update keyring set Location = '{}' where KeyringID = {}".format(user.name, key.kid))
        mdb.commit()
        return True
    except mariadb.Error as e:
        # fatalError(e)
        mdb = connectDB()
        return False

def checkin (user, key):
    global mdb

    log("Checking in k{} ({}) from {}.".format(key.kid, ", ".join(key.prop), user.name))
    try:
        cursor.execute("update keyring set Location = 'locker' where KeyringID = {}".format(key.kid))
        mdb.commit()
        return True
    except mariadb.Error as e:
        # fatalError(e)
        mdb = connectDB()
        return False

def fatalError (e):
    log("Error: {}".format(e))
    log("Fatal error encountered, Key Locker turning off. Next user must open it by double clicking the desktop link.")
    exit()

# First Run
if __name__ == "__main__":
    bigText = Text(app, "Scan Badge", size = 40, color = "black")
    underText = Text(app, ". . .", size = 15, color = "black")

    uidBox = TextBox(app)
    uidBox.repeat(REFOCUS_TIMER, focusUIN, args = [uidBox])
    uidBox.when_key_pressed = lambda e: onTextBoxKey(e, f = uidBox)

    kidBox = TextBox(app, enabled = False)

    auditText = Text(app, size = 10, color = "black")

    app.display()

Answer 1

interactive_timeout 的默认值为 28800 秒 = 8 小时，这意味着 8 小时不活动后，服务器将自动关闭连接。

我会尝试增加此值并检查断开连接是否仍然发生。

根据 PEP-249，一旦连接句柄变得无效，所有游标对象都会变得无效。由于 MySQL Connector/Python 没有自动重新连接选项，并且不允许将连接对象重新分配给游标，因此您必须在重新使用游标之前重新创建游标。