为何已关闭异步MySQL连接仍报“流对象被垃圾回收需显式调用stream.close()”

错误根本原因 #

你调用的mydb.close()是异步方法，你没有加await关键字，导致关闭逻辑没有实际执行完成，函数就提前退出了。等到连接对象被垃圾回收时，底层的TCP流还处于打开状态，就触发了你看到的报错。你之前尝试更换多个MySQL驱动都没有解决问题，是因为错误本质是异步方法调用逻辑失误，和驱动本身无关。

修复方案 #

1. 快速修复 #

把代码中所有mydb.close()的调用都改成await mydb.close()，即可直接解决报错。

2. 更稳妥的优化方案 #

使用异步上下文管理器自动管理连接和游标生命周期，不需要在每个逻辑分支手动写关闭逻辑，完全避免漏加await或者遗漏分支的问题，改写后的示例代码如下：

@commands.Cog.listener("on_message")
async def on_message(self, message):
    # async with 会在逻辑结束或出现异常时自动关闭连接
    async with await getConnection() as mydb:
        # 自动管理游标，使用完成后自动关闭
        async with mydb.cursor() as mycursor:
            if len(message.mentions) >= 1 and message.author not in message.mentions and not message.author.bot:
                await mycursor.execute("SELECT * FROM afk WHERE memberid = %s", (int(message.mentions[0].id),))
                myresult = await mycursor.fetchone()
                if myresult and ".afk" not in message.content:
                    status = myresult[1]
                    time1 = datetime.fromtimestamp(int(myresult[2]))
                    diff = datetime.now() - time1

                    if (diff >= timedelta(minutes=2)) and (diff < timedelta(minutes=60)):
                        zeit = strfdelta(diff, "{minutes} Minuten")
                    elif (diff >= timedelta(minutes=60)) and (diff < timedelta(minutes=120)):
                        zeit = strfdelta(diff, "{hours} Stunde und {minutes} Minuten")
                    elif (diff >= timedelta(minutes=120)) and (diff < timedelta(minutes=1440)):
                        zeit = strfdelta(diff, "{hours} Stunden und {minutes} Minuten")
                    elif (diff >= timedelta(minutes=1440)) and (diff < timedelta(minutes=2880)):
                        zeit = strfdelta(diff, "{days} Tag und {hours} Stunden")
                    elif diff >= timedelta(minutes=2880):
                        zeit = strfdelta(diff, "{days} Tage und {hours} Stunden")
                    else:
                        zeit = "Eben gerade"
                    return

            await mycursor.execute("SELECT * FROM afk WHERE memberid = %s", (int(message.author.id),))
            myresult1 = await mycursor.fetchone()
            # 处理AFK用户发消息的逻辑
            if myresult1 and ".afk" not in message.content:
                await mycursor.execute("DELETE FROM afk WHERE memberid = %s", (int(message.author.id),))
                if message.author.nick is None:
                    updated = message.author.name.replace("AFK | ", "")
                else:
                    updated = message.author.nick.replace("AFK | ", "")
                if not int(message.author.id) == int(message.guild.owner.id):
                    await message.author.edit(nick=updated, reason="AFK-Status entfernt")
                await mydb.commit()
                return

内容的提问来源于stack exchange，提问作者Razzer