I am facing a problem using python, mysql.connector (8.1.0) and trying to open 2 connections on 2 different servers:
我在使用PYTHON,mysql.Connector(8.1.0)并尝试在2个不同的服务器上打开2个连接时遇到问题:
If I run :
如果我参选:
from mysql.connector import MySQLConnection
if __name__ in '__main__':
# A
try:
c1 = MySQLConnection(
host='host1',
user='*',
password='*',
database='A'
)
c1.close()
except Exception as e:
print(e)
finally:
print('c1')
# B
try:
c2 = MySQLConnection(
host='host2',
user='*',
password='*',
database='B'
)
c2.close()
except Exception as e:
print(e)
finally:
print('c2')
I got exception : Character set 'utf8' unsupported
for c2
我遇到异常:c2不支持字符集‘utf8’
If I run only part B, it's Ok. It's as if something was set globally after the first connection.
如果我只运行B部分,也没问题。这就像是在第一次连接之后在全球范围内设置了什么。
any idea?
有什么想法吗?
EDIT: Got it ! CharacterSet.desc
is a class variable set at begining.
编辑:知道了!CharacterSet.desc是在开始时设置的类变量。
from mysql.connector import MySQLConnection as MySQLConnection
from mysql.connector.constants import CharacterSet
if __name__ in '__main__':
desc = CharacterSet.desc.copy()
try:
c1 = MySQLConnection(
host='host1',
user='*',
password='*',
database='A'
)
c1.close()
except Exception as e:
print(e)
finally:
print('c1')
CharacterSet.desc = desc
try:
c2 = MySQLConnection(
host='host2',
user='*',
password='*',
database='B'
)
c2.close()
except Exception as e:
print(e)
finally:
print('c2')
It works now
它现在起作用了
更多回答
What happens if you run part B and then part A?
如果先运行B部分,然后运行A部分,会发生什么情况?
@StephenC It is ok if B then A
@斯蒂芬·C如果B然后A就可以了
OK ... so that means that it is also something to do with the actual servers and databases and (I guess) their selected character sets and collations. The next step would be to figure out what they are in each case.
好的..。因此,这意味着它还与实际的服务器和数据库以及(我猜)它们选择的字符集和排序规则有关。下一步将是弄清楚它们在每种情况下都是什么。
@StephenC Thanks. Still can't understand why I can use B, B and A but not A and B.
@斯蒂芬·C谢谢。我还是不明白为什么我可以用B、B和A,却不能用A和B。
If you can figure out what the database and server character sets are, that will be a start. Also database server versions. Basically, you need more information.
如果您能弄清楚数据库和服务器的字符集是什么,这将是一个开始。还有数据库服务器版本。基本上,你需要更多的信息。
If both connections (c1 and c2) work fine when run separately but produce the "Character set 'utf8' unsupported for c2" error when run together, it's likely due to a compatibility issue with the character set configurations between the two connections. you may try below steps:
如果两个连接(c1和c2)在单独运行时工作正常,但在一起运行时产生“字符集‘UTF8’不支持c2”错误,这可能是因为两个连接之间的字符集配置存在兼容性问题。您可以尝试以下步骤:
Specify Character Set for Both Connections: Explicitly specify the character set for both connections to ensure they use the same character set. For example, you can use 'utf8mb4' for both connections
为两个连接指定字符集:显式指定两个连接的字符集,以确保它们使用相同的字符集。例如,您可以对两个连接使用‘utf8mb4’
c1 = MySQLConnection(
host='host1',
user='*',
password='*',
database='A',
charset='utf8mb4'
)
c2 = MySQLConnection(
host='host2',
user='*',
password='*',
database='B',
charset='utf8mb4'
)
Use Separate Connection Instances: Ensure that you are not inadvertently sharing a connection instance between the two connections (c1 and c2). Each connection should have its own separate instance.
使用单独的连接实例:确保您不会无意中在两个连接(c1和c2)之间共享一个连接实例。每个连接都应该有自己的单独实例。
Both server are utf8mb4.
两台服务器都是utf8mb4。
No real explanation why it works if B is in first but mysql.connector
is totaly not safe for mariadb (I have read the opposite several times).
没有真正的解释为什么如果B是第一个,那么它为什么会起作用,但是mysql.Connector对于MariaDB来说是完全不安全的(我已经读了好几次相反的内容)。
During the handcheck, first says it is 11.1.2-MariaDB (arch linux package) and second says 5.5.5-10.4.18-MariaDB (ubuntu package).
在手检过程中,第一个说是11.1.2-MariaDB(ARCH Linux包),第二个说是5.5.5-10.4.18-MariaDB(ubuntu包)。
Connector try to get the version with:
连接器尝试使用以下命令获取版本:
regex_ver = re.compile(r"^(\d{1,2})\.(\d{1,2})\.(\d{1,3})(.*)")
And before exception, check is done and fail:
在异常之前,检查完成并失败:
if charset in ("utf8", "utf-8") and cls.mysql_version >= (8, 0):
charset = "utf8mb4"
So I am just moving to official MariaDB connector package...
所以我只是转到官方的MariaDB连接器包…
Thanks
谢谢
This way works :
这种方式工作:
import os
from mysql.connector import MySQLConnection as MySQLConnection
if __name__ in '__main__':
pid = os.fork()
if pid == 0:
try:
c1 = MySQLConnection(
host='host1',
user='*',
password='*',
database='A'
)
with c1.cursor() as cursor:
cursor.execute("SELECT 1, 'A'")
print(cursor.fetchone())
c1.close()
except Exception as e:
print(e)
finally:
print('c1')
else:
try:
c2 = MySQLConnection(
host='host2',
user='*',
password='*',
database='B'
)
with c2.cursor() as cursor:
cursor.execute("SELECT 1, 'B'")
print(cursor.fetchone())
c2.close()
except Exception as e:
print(e)
finally:
print('c2')
But I can't do that in a gui app.
但我不能在图形用户界面应用程序中做到这一点。
更多回答
"Use Separate Connection Instances" : That is not what I'm doing ?!
“使用单独的连接实例”:这不是我要做的吗?!
"Specify Character Set for Both Connections" : I'm not supposed to know them before connect
“为两个连接指定字符集”:我不应该在连接之前知道它们
you can use SHOW CHARACTER SET to get character set.
您可以使用show character set来获取字符集。
我是一名优秀的程序员,十分优秀!