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