有没有办法将 mysql-connector-python 中的“pool_reset_connection"与 MariaDB 10.4.7 一起使用? [英] Is there a way to use 'pool_reset_connection' from mysql-connector-python with MariaDB 10.4.7?
问题描述
我想将我的 python 程序从普通连接更改为连接池,以便在几个小时内没有发送查询时数据库连接不会丢失,并且数据库不会被一堆查询淹没使用高峰期.
I want to change my python program from a normal connection to a Connection Pool so that the database connection doesn't get lost when no queries are sent for some hours, as well as the database not being overwhelmed with a bunch of queries at once at peak usage.
我正在使用 Python 3.7.4、mysql-connector-python 8.0.17 和 MariaDB 10.4.7.当我使用普通连接时它工作正常,但 MariaDB 显然不支持 mysql.connector.pooling.MySQLConnectionPool
I'm using Python 3.7.4, mysql-connector-python 8.0.17, and MariaDB 10.4.7.
It works fine when I use a normal connection, but MariaDB apparently doesn't support the pool_reset_session
setting of mysql.connector.pooling.MySQLConnectionPool
在我的代码开始时,它会尝试创建尚不存在的数据库,这导致了我得到的错误.
At the start of my code, it tries to create the database if it doesn't yet exist, and that is causing the errors I get.
import mysql.connector as mariadb
from mysql.connector import errorcode
from mysql.connector import pooling
cnx = mariadb.pooling.MySQLConnectionPool(user='root', password='password', host='localhost',
pool_name='connectionpool', pool_size=10, pool_reset_session=True)
try:
db = cnx.get_connection()
cursor = db.cursor()
cursor.execute("CREATE DATABASE IF NOT EXISTS tests")
print("Created database")
except mariadb.Error as err:
print(f"Failed creating database: {err}")
finally:
print("Finally (create)")
db.close()
我希望这个片段只会创建数据库 tests
但我得到了以下两个错误:
I expected that this snippet would just create the database tests
but instead I got the following two errors:
mysql.connector.errors.NotSupportedError:MySQL 5.7.2 及更早版本不支持 COM_RESET_CONNECTION.
还有
mysql.connector.errors.OperationalError: 1047 (08S01): Unknown command
从追溯日志来看,这似乎是由于尝试在第 17 行执行 db.close()
造成的.
From the traceback logs, it looks like this gets caused by trying to execute db.close()
in line 17.
带有回溯的完整输出:https://pastebin.com/H3SAvA9N
我在问我能做些什么来解决这个问题,以及是否有可能在 MariaDB 10.4.7 中使用这种连接池(我很困惑,因为它说 MySQL <= 5.7.2 不支持即使我使用 MariaDB 10.4.7,使用后也会重置连接)
I am asking what I can do to fix this and if it's possible at all to use this sort of connection pooling with MariaDB 10.4.7 (I get confused because it says that MySQL <= 5.7.2 doesn't support this reset of connections after use even though I use MariaDB 10.4.7)
我还发现 MariaDB Connector/J 确实提供了这样一个选项,称为 useResetConnection
,但我不想仅仅为了这个功能而学习 Java.
I also found out that MariaDB Connector/J does provide such an option, called useResetConnection
but I don't want to learn Java just for this feature.
推荐答案
我在使用 mariaDB 的 mysql-connector-python 时遇到了同样的问题,我将 mysql-connector-python 版本降级到 8.0.12,它对我有用
I was facing the same issue with mysql-connector-python for mariaDB, and I downgraded mysql-connector-python version to 8.0.12 and it worked for me
这篇关于有没有办法将 mysql-connector-python 中的“pool_reset_connection"与 MariaDB 10.4.7 一起使用?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!