有没有办法在MariaDB 10.4.7中从mysql-connector-python使用'pool_reset_connection'? [英] 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 version 5.7.2 and earlier does not support 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

Full output with traceback: 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

这篇关于有没有办法在MariaDB 10.4.7中从mysql-connector-python使用'pool_reset_connection'?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！