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

它现在起作用了

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.

但我不能在图形用户界面应用程序中做到这一点。