Mariadb-connector-c and mariadb-connector-python issue

Hello guys,

I am facing an issue that I can’t properly connect from Python to a MariaDB server.
I faced this issue while working with the frappe framework and there’s a follow-up topic at frappe’s forum but basically what happens is that the connection is always rejected despite everything is correct.

The error is:

python3.11 database-connect.py 
Traceback (most recent call last):
  File "/home/myuser/database-connect.py", line 29, in <module>
    display_user()
  File "/home/myuser/database-connect.py", line 26, in display_user
    result = execute_query(query)
             ^^^^^^^^^^^^^^^^^^^^
  File "/home/myuser/database-connect.py", line 9, in execute_query
    conn = mariadb.connect(
           ^^^^^^^^^^^^^^^^
  File "/home/myuser/.local/lib/python3.11/site-packages/mariadb/__init__.py", line 146, in connect
    connection = connectionclass(*args, **kwargs)
                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/myuser/.local/lib/python3.11/site-packages/mariadb/connections.py", line 85, in __init__
    super().__init__(*args, **kwargs)
mariadb.OperationalError: Access denied for user 'root'@'10.0.0.1' (using password: YES)

This is the script I used:

import mariadb

# MariaDB Server details
MARIADB_USER = 'root'
MARIADB_PSWD = 'root-password'
MARIADB_SERV = 'db-server.internal.net'

def execute_query(query):
    conn = mariadb.connect(
        user=MARIADB_USER,
        password=MARIADB_PSWD,
        host=MARIADB_SERV,
        port=3306
    )
    cursor = conn.cursor()
    cursor.execute(query)
    result = cursor.fetchall()
    cursor.close()
    conn.close()
    return result

# List user info
def display_user():
    query = "SELECT user()"
    result = execute_query(query)
    print(result)

display_user()

I did a similar shell script and connects successfully.
In the end this is the same error I am getting when using frappe bench, despite that bench uses PyMySQL (1.1.1) and my script uses mariadb (1.1.10).
On this system I had compiled and installed python3.11 from the source, so I thought of trying a different approach.
I created a clean VM and instead of going from the sources installed python3.11 supplied by appstream. I also got mariadb-connector-c from appstream v3.2.6 which is ok for python connector 1.0.11.
(btw dnf --showduplicates list mariadb-connector-c displays a disappointing variety of options)
After installing the mariadb-connector-c-3.2.6 I did pip3.11 install mariadb==1.0.10 for what I get:

$ pip3.11 install mariadb==1.0.11
Defaulting to user installation because normal site-packages is not writeable
Collecting mariadb==1.0.11
  Downloading mariadb-1.0.11.zip (85 kB)
     ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 85.9/85.9 kB 1.1 MB/s eta 0:00:00
  Preparing metadata (setup.py) ... done
Installing collected packages: mariadb
  DEPRECATION: mariadb is being installed using the legacy 'setup.py install' method, because it does not have a 'pyproject.toml' and the 'wheel' package is not installed. pip 23.1 will enforce this behaviour change. A possible replacement is to enable the '--use-pep517' option. Discussion can be found at https://github.com/pypa/pip/issues/8559
  Running setup.py install for mariadb ... error
  error: subprocess-exited-with-error
  
  × Running setup.py install for mariadb did not run successfully.
  │ exit code: 1
  ╰─> [41 lines of output]
      10.8.8
      running install
      /usr/lib/python3.11/site-packages/setuptools/command/install.py:34: SetuptoolsDeprecationWarning: setup.py install is deprecated. Use build and pip and other standards-based tools.
        warnings.warn(
      running build
      running build_py
      creating build
      creating build/lib.linux-x86_64-cpython-311
      creating build/lib.linux-x86_64-cpython-311/mariadb
      copying mariadb/__init__.py -> build/lib.linux-x86_64-cpython-311/mariadb
      creating build/lib.linux-x86_64-cpython-311/mariadb/constants
      copying mariadb/constants/__init__.py -> build/lib.linux-x86_64-cpython-311/mariadb/constants
      copying mariadb/constants/CLIENT.py -> build/lib.linux-x86_64-cpython-311/mariadb/constants
      copying mariadb/constants/INDICATOR.py -> build/lib.linux-x86_64-cpython-311/mariadb/constants
      copying mariadb/constants/CURSOR.py -> build/lib.linux-x86_64-cpython-311/mariadb/constants
      copying mariadb/constants/FIELD_TYPE.py -> build/lib.linux-x86_64-cpython-311/mariadb/constants
      running build_ext
      building 'mariadb._mariadb' extension
      creating build/temp.linux-x86_64-cpython-311
      creating build/temp.linux-x86_64-cpython-311/mariadb
      gcc -Wsign-compare -DDYNAMIC_ANNOTATIONS_ENABLED=1 -DNDEBUG -O2 -fexceptions -g -grecord-gcc-switches -pipe -Wall -Werror=format-security -Wp,-D_FORTIFY_SOURCE=2 -Wp,-D_GLIBCXX_ASSERTIONS -fstack-protector-strong -m64 -march=x86-64-v2 -mtune=generic -fasynchronous-unwind-tables -fstack-clash-protection -fcf-protection -D_GNU_SOURCE -fPIC -fwrapv -O2 -fexceptions -g -grecord-gcc-switches -pipe -Wall -Werror=format-security -Wp,-D_FORTIFY_SOURCE=2 -Wp,-D_GLIBCXX_ASSERTIONS -fstack-protector-strong -m64 -march=x86-64-v2 -mtune=generic -fasynchronous-unwind-tables -fstack-clash-protection -fcf-protection -D_GNU_SOURCE -fPIC -fwrapv -O2 -fexceptions -g -grecord-gcc-switches -pipe -Wall -Werror=format-security -Wp,-D_FORTIFY_SOURCE=2 -Wp,-D_GLIBCXX_ASSERTIONS -fstack-protector-strong -m64 -march=x86-64-v2 -mtune=generic -fasynchronous-unwind-tables -fstack-clash-protection -fcf-protection -D_GNU_SOURCE -fPIC -fwrapv -fPIC -DPY_MARIADB_MAJOR_VERSION=1 -DPY_MARIADB_MINOR_VERSION=0 -DPY_MARIADB_PATCH_VERSION=11 -I/usr/local/include/mariadb -I/usr/local/include/mariadb/mysql -I./include -I/usr/include/python3.11 -c mariadb/mariadb.c -o build/temp.linux-x86_64-cpython-311/mariadb/mariadb.o -DDEFAULT_PLUGINS_SUBDIR=\"/usr/local/lib/mariadb/plugin\"
      mariadb/mariadb.c: In function ‘PyInit__mariadb’:
      mariadb/mariadb.c:155:35: error: lvalue required as left operand of assignment
        155 |     Py_TYPE(&MrdbConnection_Type) = &PyType_Type;
            |                                   ^
      mariadb/mariadb.c:168:31: error: lvalue required as left operand of assignment
        168 |     Py_TYPE(&MrdbCursor_Type) = &PyType_Type;
            |                               ^
      mariadb/mariadb.c:174:29: error: lvalue required as left operand of assignment
        174 |     Py_TYPE(&MrdbPool_Type) = &PyType_Type;
            |                             ^
      mariadb/mariadb.c:180:34: error: lvalue required as left operand of assignment
        180 |     Py_TYPE(&MrdbIndicator_Type) = &PyType_Type;
            |                                  ^
      mariadb/mariadb.c:186:38: error: lvalue required as left operand of assignment
        186 |     Py_TYPE(&Mariadb_Fieldinfo_Type) = &PyType_Type;
            |                                      ^
      mariadb/mariadb.c:192:38: error: lvalue required as left operand of assignment
        192 |     Py_TYPE(&Mariadb_DBAPIType_Type) = &PyType_Type;
            |                                      ^
      error: command '/usr/bin/gcc' failed with exit code 1
      [end of output]
  
  note: This error originates from a subprocess, and is likely not a problem with pip.
error: legacy-install-failure

× Encountered error while trying to install package.
╰─> mariadb

note: This is an issue with the package mentioned above, not pip.
hint: See above for output from the failure.

So that library won’t go and if I just do pip3.11 install mariadb fails with

  │ exit code: 2
  ╰─> [1 lines of output]
      MariaDB Connector/Python requires MariaDB Connector/C >= 3.3.1, found version 3.2.6

So … here I go and remove 3.2.6 with dnf and compile and install from the sources v3.3.10
build with cmake and make make install now I have mariadb-connector-c-3.3.10
I do pip3.11 install mariadb and installs successfully.

I run my script to test if I can connect

$ python3.11 db-test.py 
Traceback (most recent call last):
  File "/opt/frappe/db-test.py", line 1, in <module>
    import mariadb
  File "/usr/local/lib64/python3.11/site-packages/mariadb/__init__.py", line 7, in <module>
    from ._mariadb import (
ImportError: libmariadb.so.3: cannot open shared object file: No such file or directory

but mariadb installed correctly with pip and

$ find /usr/local -iname libmariadb.so.3
/usr/local/lib/mariadb/libmariadb.so.3
/usr/local/src/mariadb-connector-c-3.3.10/build/libmariadb/libmariadb.so.3

Here I believe the difference is on the original machine, where the python script that imports mariadb not pymysql is that there I had installed mariadb-client from the mariadb-repo which also installs mariadb-shared etc. But nevertheless, libmariadb.so.3 exists and is there, and pip3.11 installed mariadb without complaints about the file.
Anyway, I went ahead and tried to test with bench

$ bench new-site ...
MySQL root password: 
Traceback (most recent call last):
  File "<frozen runpy>", line 198, in _run_module_as_main
  File "<frozen runpy>", line 88, in _run_code
  File "/opt/frappe/bench/apps/frappe/frappe/utils/bench_helper.py", line 114, in <module>
    main()
  File "/opt/frappe/bench/apps/frappe/frappe/utils/bench_helper.py", line 20, in main
    click.Group(commands=commands)(prog_name="bench")
  File "/opt/frappe/bench/env/lib64/python3.11/site-packages/click/core.py", line 1157, in __call__
    return self.main(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/frappe/bench/env/lib64/python3.11/site-packages/click/core.py", line 1078, in main
    rv = self.invoke(ctx)
         ^^^^^^^^^^^^^^^^
  File "/opt/frappe/bench/env/lib64/python3.11/site-packages/click/core.py", line 1688, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
                           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/frappe/bench/env/lib64/python3.11/site-packages/click/core.py", line 1688, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
                           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/frappe/bench/env/lib64/python3.11/site-packages/click/core.py", line 1434, in invoke
    return ctx.invoke(self.callback, **ctx.params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/frappe/bench/env/lib64/python3.11/site-packages/click/core.py", line 783, in invoke
    return __callback(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/frappe/bench/apps/frappe/frappe/commands/site.py", line 68, in new_site
    _new_site(
  File "/opt/frappe/bench/apps/frappe/frappe/installer.py", line 87, in _new_site
    install_db(
  File "/opt/frappe/bench/apps/frappe/frappe/installer.py", line 160, in install_db
    setup_database(force, source_sql, verbose, no_mariadb_socket)
  File "/opt/frappe/bench/apps/frappe/frappe/database/__init__.py", line 21, in setup_database
    return frappe.database.mariadb.setup_db.setup_database(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/frappe/bench/apps/frappe/frappe/database/mariadb/setup_db.py", line 31, in setup_database
    if force or (db_name not in dbman.get_database_list()):
                                ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/frappe/bench/apps/frappe/frappe/database/db_manager.py", line 50, in get_database_list
    return self.db.sql("SHOW DATABASES", pluck=True)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/frappe/bench/apps/frappe/frappe/database/database.py", line 211, in sql
    self.connect()
  File "/opt/frappe/bench/apps/frappe/frappe/database/database.py", line 117, in connect
    self._conn: "MariadbConnection" | "PostgresConnection" = self.get_connection()
                                                             ^^^^^^^^^^^^^^^^^^^^^
  File "/opt/frappe/bench/apps/frappe/frappe/database/mariadb/database.py", line 107, in get_connection
    conn = self._get_connection()
           ^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/frappe/bench/apps/frappe/frappe/database/mariadb/database.py", line 113, in _get_connection
    return self.create_connection()
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/frappe/bench/apps/frappe/frappe/database/mariadb/database.py", line 116, in create_connection
    return pymysql.connect(**self.get_connection_settings())
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/frappe/bench/env/lib64/python3.11/site-packages/pymysql/connections.py", line 361, in __init__
    self.connect()
  File "/opt/frappe/bench/env/lib64/python3.11/site-packages/pymysql/connections.py", line 669, in connect
    self._request_authentication()
  File "/opt/frappe/bench/env/lib64/python3.11/site-packages/pymysql/connections.py", line 957, in _request_authentication
    auth_packet = self._read_packet()
                  ^^^^^^^^^^^^^^^^^^^
  File "/opt/frappe/bench/env/lib64/python3.11/site-packages/pymysql/connections.py", line 775, in _read_packet
    packet.raise_for_error()
  File "/opt/frappe/bench/env/lib64/python3.11/site-packages/pymysql/protocol.py", line 219, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/opt/frappe/bench/env/lib64/python3.11/site-packages/pymysql/err.py", line 150, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.OperationalError: (1045, "Access denied for user 'root'@'10.0.0.1' (using password: YES)")

Back to the beginning.

So anyway I went ahead and to test with a shell script I need mariadb client anyway so I installed it.

================================================================================================================================================================================================
 Package                                             Architecture                         Version                                              Repository                                  Size
================================================================================================================================================================================================
Installing:
 MariaDB-client                                      x86_64                               11.4.2-1.el9                                         mariadb-main                               9.4 M
Installing dependencies:
 MariaDB-common                                      x86_64                               11.4.2-1.el9                                         mariadb-main                                88 k
 MariaDB-shared                                      x86_64                               11.4.2-1.el9                                         mariadb-main                               133 k
 perl-DBI                                            x86_64                               1.643-9.el9                                          appstream                                  700 k
 perl-Math-BigInt                                    noarch                               1:1.9998.18-460.el9                                  appstream                                  188 k
 perl-Math-Complex                                   noarch                               1.59-481.el9                                         appstream                                   45 k
 perl-Sys-Hostname                                   x86_64                               1.23-481.el9                                         appstream                                   16 k
Installing weak dependencies:
 MariaDB-client-compat                               noarch                               11.4.2-1.el9                                         mariadb-main                                11 k

Doing the test with a shell script:

#!/usr/bin/env sh

MARIADB_USER="root"
MARIADB_PSWD="db-root-password"
MARIADB_SERV="db-server-host"

execute_query() {
	local query=$1
	mariadb -h "$MARIADB_SERV" -u "$MARIADB_USER" -p"$MARIADB_PSWD" -e "$query"
}

display_user() {
	execute_query "SELECT user()"
}

display_user

returns:

+-----------------+
| user()          |
+-----------------+
| root@10.0.0.1   |
+-----------------+

and well trying the python script again:

$ python3.11 db-test.py 
Traceback (most recent call last):
  File "/opt/frappe/db-test.py", line 28, in <module>
    display_user()
  File "/opt/frappe/db-test.py", line 25, in display_user
    result = execute_query(query)
             ^^^^^^^^^^^^^^^^^^^^
  File "/opt/frappe/db-test.py", line 9, in execute_query
    conn = mariadb.connect(
           ^^^^^^^^^^^^^^^^
  File "/usr/local/lib64/python3.11/site-packages/mariadb/__init__.py", line 146, in connect
    connection = connectionclass(*args, **kwargs)
                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib64/python3.11/site-packages/mariadb/connections.py", line 85, in __init__
    super().__init__(*args, **kwargs)
mariadb.OperationalError: Access denied for user 'root'@'10.0.0.1' (using password: YES)

I’m stuck in this loop. Suggestions are immensely appreciated. Thank you

Just for kicks I removed MariaDB-client and all dependencies. Added a mariadb-connector-c.conf to /etc/ld.so.conf.d with /usr/local/lib/mariadb and tested the python script again:

$ python3.11 db-test.py 
Traceback (most recent call last):
  File "/opt/frappe/db-test.py", line 28, in <module>
    display_user()
  File "/opt/frappe/db-test.py", line 25, in display_user
    result = execute_query(query)
             ^^^^^^^^^^^^^^^^^^^^
  File "/opt/frappe/db-test.py", line 9, in execute_query
    conn = mariadb.connect(
           ^^^^^^^^^^^^^^^^
  File "/usr/local/lib64/python3.11/site-packages/mariadb/__init__.py", line 146, in connect
    connection = connectionclass(*args, **kwargs)
                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib64/python3.11/site-packages/mariadb/connections.py", line 85, in __init__
    super().__init__(*args, **kwargs)
mariadb.OperationalError: Access denied for user 'root'@'10.0.0.1' (using password: YES)

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.