Finding source of PyMySQL error - err.InterfaceError("(0, '')")

Question

I am a Discord bot developer, and recently completed an order. The client upon setting the application up on their server initially had no issues, but according to them after running for "about three hours" the program begins spitting a specific stack trace error and no longer accepting commands.

The bot is built using Discord.py and uses Peewee as an ORM, using PyMySQL as the database driver. The server the client is running it on is hosted by DigitalOcean and if any information about the hardware, etc. is needed the client is able to give me that information on request. We have already attempted uninstalling and reinstalling all dependencies, as well as trying different distributions of them, but the errors persist.

This is the exact trace that the client is receiving:

  File "/usr/local/lib/python3.6/dist-packages/peewee.py", line 2666, in __exit__
    reraise(new_type, new_type(*exc_args), traceback)
  File "/usr/local/lib/python3.6/dist-packages/peewee.py", line 179, in reraise
    raise value.with_traceback(tb)
  File "/usr/local/lib/python3.6/dist-packages/peewee.py", line 2875, in execute_sql
    cursor.execute(sql, params or ())
  File "/usr/local/lib/python3.6/dist-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
  File "/usr/local/lib/python3.6/dist-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
  File "/usr/local/lib/python3.6/dist-packages/pymysql/connections.py", line 516, in query
    self._execute_command(COMMAND.COM_QUERY, sql)
  File "/usr/local/lib/python3.6/dist-packages/pymysql/connections.py", line 750, in _execute_command
    raise err.InterfaceError("(0, '')")
peewee.InterfaceError: (0, '')

The relevant portions from my database.py file, where the database connection is opened:

import discord
from peewee import *

from config import db_host, db_port, mysql_db_name, \
    mysql_db_username, mysql_db_password

db_connection = MySQLDatabase(
    mysql_db_name,
    user = mysql_db_username,
    password = mysql_db_password,
    host = db_host,
    port = db_port
)

def create_db_tables():
    # create_tables() does safe creation by default, and will simply not create
    # table if it already exists
    db_connection.create_tables([User])

The relevant portions from my bot.py file, specifically the bot startup function that runs when the bot is first opened, and the lines that create and start the bot client:

client = discord.Client()

async def bot_startup():
    # Check for config changes
    if client.user.name != config.bot_username:
        print("Username Updated To: {}".format(config.bot_username))
        await client.edit_profile(username=config.bot_username)

    # Start 'playing' message
    await client.change_presence(
        game=discord.Game( name=config.playing_message )
    )

    # Prepare database
    database.create_db_tables()
    print("Database Connected")

    print("Connected Successfully")

# ...

@client.event
async def on_ready():
    await bot_startup()

# ...

client.run(config.token)

According to the client, restarting the bot temporarily solves the problem and it runs fine for a few hours before the errors start up again. The bot no longer responds to any incoming commands once the errors start, and if enough errors are thrown, crashes completely.

What is typically the cause of this error, and what steps should be taken to fix whatever is causing it?

Sargassum

Does this answer your question? InterfaceError (0, '') – Visitor

Your connection management is messed up somehow. – Buddha

Answer 1

discord.py is asynchronous whilst PyMySQL is not - therefore it is blocking the discord.py runtime. Instead of PyMySQL use AIOMySQL which is non-blocking and might just solve your error.

Voltaic

Answer 2

The timing of when the error appears makes me think of a problem I also encountered when communicating to a database.

The problem could be that since the connection is opened when the bot starts up (or even when the program begins its execution) and is used sporadically, the database might close the connection, therefore any further execution will result in an error. To combat this, I create this decorator for all the methods of my Database Class

def ensures_connected(f):
    def wrapper(*args):
        args[0].connection.ping(reconnect=True, attempts=3, delay=2)
        return f(*args)

    return wrapper

to be placed above any method or function that has to communicate with the database.

The line args[0].connection.ping(reconnect=True, attempts=3, delay=2) means that we will call on self.connection (since it is the first argument passed to the method when called) the method ping, that allows to reconnect if the connection was dropped.

self.connection in my code is an object returned by the method call MySQLConnection.connect and should be equivalent to your obejct db_connection

This decorator should be placed above the method definition like the following example :

def ensures_connected(f):
    def wrapper(*args):
        args[0].connection.ping(reconnect=True, attempts=3, delay=2)
        return f(*args)

    return wrapper

class Database:
    def __init__(self):
        self.connection = mysql.connector.connect(
            user=self.username, password=password, host=self.host, database=self.database)

    # ...

    @ensures_connected
    def is_member_registered(self, guild: Guild, member: Member):
        # ...
        return

Hebetate

Answer 3

According to this comment you can use following code for reconnect automatically: ‍

from peewee import *
from playhouse.shortcuts import ReconnectMixin

class ReconnectMySQLDatabase(ReconnectMixin, MySQLDatabase):
    pass

db = ReconnectMySQLDatabase(...)

Insobriety