MySQL Connector / CharacterSet / two connections

Question

I am facing a problem using python, mysql.connector (8.1.0) and trying to open 2 connections on 2 different servers:

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

If I run only part B, it's Ok. It's as if something was set globally after the first connection.

any idea?

EDIT: Got it ! CharacterSet.desc is a class variable set at begining.

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

Answer 1

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:

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

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.

Answer 2

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.

Answer 3

Both server are 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).

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).

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...

Thanks