CODEWITHSUNDEEP
pythondiscord.py
ScreaMyCZE
ScreaMyCZE

Reputation: 65

mysql.connector issue with System Error : 32 Broken Pipe

I'm using library discord.py and i have issue with mysql.connector. I use mariadb database for server prefixes but when my bot runs 24/7, it starts throwing these errors

0|PyBot    |   File "/home/pi/.local/lib/python3.7/site-packages/discord/utils.py", line 329, in maybe_coroutine
0|PyBot    |     value = f(*args, **kwargs)
0|PyBot    |   File "/home/pi/PyBotV2/bot/main.py", line 11, in get_prefix
0|PyBot    |     return fetch_prefix(message.guild.id)
0|PyBot    |   File "/home/pi/PyBotV2/bot/cogs/database.py", line 15, in fetch_prefix
0|PyBot    |     cursor.execute(prefix_query, (gid,))
0|PyBot    |   File "/home/pi/.local/lib/python3.7/site-packages/mysql/connector/cursor.py", line 551, in execute
0|PyBot    |     self._handle_result(self._connection.cmd_query(stmt))
0|PyBot    |   File "/home/pi/.local/lib/python3.7/site-packages/mysql/connector/connection.py", line 490, in cmd_query
0|PyBot    |     result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
0|PyBot    |   File "/home/pi/.local/lib/python3.7/site-packages/mysql/connector/connection.py", line 261, in _send_cmd
0|PyBot    |     packet_number, compressed_packet_number)
0|PyBot    |   File "/home/pi/.local/lib/python3.7/site-packages/mysql/connector/network.py", line 143, in send_plain
0|PyBot    |     errno=2055, values=(self.get_address(), _strioerror(err)))
0|PyBot    | mysql.connector.errors.OperationalError: 2055: Lost connection to MySQL server at 'localhost:3306', system error: 32 Broken pipe

My code for DB connection:

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="USERNAME",
    password="PASSWORD",
    database="discord_bot"
)

cursor = mydb.cursor()


def fetch_prefix(gid):
    prefix_query = "SELECT prefix FROM servers WHERE id = %s"
    cursor.execute(prefix_query, (gid,))
    prefix = cursor.fetchone()
    return prefix[0]

def add_prefix(gid, prefix = "."):
    prefix_query = "INSERT INTO servers(id, prefix) VALUES (%s, %s)"
    cursor.execute(prefix_query, (gid, prefix,))
    mydb.commit()

def remove_prefix(gid):
    prefix_query = "DELETE FROM servers WHERE id = %s"
    cursor.execute(prefix_query, (gid,))
    mydb.commit()

def update_prefix(gid, prefix):
    prefix_query = "UPDATE servers SET prefix = %s WHERE id = %s"
    cursor.execute(prefix_query, (prefix, gid, ))

Upvotes: 1

Views: 4870

Answers (2)

Judev1
Judev1

Reputation: 436

A different solution using sqlite3

import sqlite3

path = "discord_bot.db"
conn = sqlite3.connect(path)
cursor = conn.cursor()

def database_interaction(data):
    cursor.execute("SQL Script", (data,))
    result = cursor.fetchone()
    return result[0]

Upvotes: -2

Judev1
Judev1

Reputation: 436

Old connections will time out eventually on the server side, and become unusable.

Try and connect to the server whenever you interact with the database. Or create a timer and reload the connection every so often

Maybe something like this:

import mysql.connector

def connect():
    mydb = mysql.connector.connect(
        host="localhost",
        user="USERNAME",
        password="PASSWORD",
        database="discord_bot"
    )

    return mydb.cursor()

def database_interaction(data):
    database = connect()
    database.execute("SQL Script", (data,))
    result = database.fetchone()
    database.close()
    return result[0]

Upvotes: 2

Related Questions