I'm creating a product for my company to keep track of items with RFID tags attached, they swipe at the kiosk, it's logged in the database, simple.
The database is actually hosted on the kiosk (not best practice, I know), and the main program running on the kiosk is a python frontend with GuiZero. The kiosk runs Raspbian Desktop off of a Raspberry Pi.
Everything works well, but (approximately) 24 hours after the program launches, it acts like normal, until a database query is sent to the server. The python-mariadb-connector responds with: 2055: Lost connection to MySQL server at '127.0.0.1:3306', system error: 32 Broken pipe, I've been troubleshooting this for about a month now and I'm now sure what could fix it.
I've tried simply renewing the connection from within the python frontend whenever this happens, but that doesn't work either. The only thing that fixes the issue, is restarting the program, which users can't be trusted with figuring out how to do.
One thing to note is that everything works perfectly until that ~24 hour mark hits.
Any help?
Dependencies:
Python 3.7
(pip) mysql-connector v2.2.9
MariaDB: 10.3.17-MariaDB-0+deb10ul Raspbian 10
Raspbian GNU/Linux v10
Timeout Variables
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 |
+---------------------------------------+-------+
Here's the code, line 166 is where the error is handled. (Not that my handling does anything) Ctrl+F for "aghiulg" to get to that line.
#!/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()
