Bug #117700 Thread safety issue when calling mysql.connector.connect on multiple threads

Description:
While using mysql-connector-python, with the C extension, some calls to mysqlconnect fail with the following stack trace:

Traceback (most recent call last):
  File "/usr/local/lib64/python3.12/site-packages/mysql/connector/connection_cext.py", line 365, in _open_connection
    self._cmysql.connect(**cnx_kwargs)
_mysql_connector.MySQLInterfaceError: Authentication plugin 'mysql_native_password' cannot be loaded: it is already loaded

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/app/lib/sql/mysql_store.py", line 41, in __init__
    self._connection = connect(
                       ^^^^^^^^
  File "/usr/local/lib64/python3.12/site-packages/mysql/connector/pooling.py", line 322, in connect
    return CMySQLConnection(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib64/python3.12/site-packages/mysql/connector/connection_cext.py", line 153, in __init__
    self.connect(**kwargs)
  File "/usr/local/lib64/python3.12/site-packages/mysql/connector/abstracts.py", line 1529, in connect
    self._open_connection()
  File "/usr/local/lib64/python3.12/site-packages/mysql/connector/connection_cext.py", line 370, in _open_connection
    raise get_mysql_exception(
mysql.connector.errors.DatabaseError: 2059 (HY000): Authentication plugin 'mysql_native_password' cannot be loaded: it is already loaded

How to repeat:
This happens in the following context:

 * In a multi-threaded web application;
 * When concurrent calls to mysql.connector.connect are being made;
 * When there was previously no activity (and thus, no previously successful calls to mysql.connector.connect) in the same process;

Suggested fix:
After reviewing the source code for mysql-connector-python as published on github, I have a working theory for where the issue comes from:

https://github.com/mysql/mysql-connector-python/blob/9.2.0/mysql-connector-python/src/mysq...

mysql.connector.connect eventually calls into the MySQL_connect C function, and this function calls the mysql_init function in a GIL-unlocked block:

```
    Py_BEGIN_ALLOW_THREADS
    if (self->connected) {
        self->connected = 0;
        mysql_close(&self->session);
    }

    mysql_init(&self->session);
    Py_END_ALLOW_THREADS
```

This means that the mysql_init method can be called concurrently in a multi-threaded python context. However, when the library has never been initialized in the process, mysql_init ends up calling mysql_library_init, which is documented in https://dev.mysql.com/doc/c-api/9.2/en/c-api-threaded-clients.html as being not thread-safe.

I can think of two big ways of solving that:

 * Either adding a call to mysql_library_init in the module initialization function https://github.com/mysql/mysql-connector-python/blob/9.2.0/mysql-connector-python/src/mysq... ;
 * Or adding a boolean in MySQL_connect that checks whether mysql_library_init has already been called in the current process, and to call that function only if it has not been called (this boolean and check must be made in a thread-safe way)

Right now, I am going to work around this bug by managing a lock around calls to connect.

Hello Jean Hominal,

Thank you for the report and feedback.
I'm not able to reproduce this issue at my end but it would be really good if you can share a python script/test case which manifest the issue so that it can be verified at our end. Thank you.

regards,
Umesh

I have attached my python code reproduction, it has the following caveats:

 - This requires the user to be using the deprecated "mysql_native_password" plugin;
 - I am using the mysql-connector-python 9.1.0 version;
 - The database I am connecting to is using 8.0.41-commercial;
 - For unknown reasons, when using a localhost database I was unable to trigger the bug. Maybe due to thread ordering?
 - The reproduction does not trigger every time, I sometimes need to re-run the program around 10-20 times before triggering the error;