how can I get a fresh ubuntu system connecting python to mysql?
What is the problem here? This is everything I did to create and provision the server:
Create a new high memory system using Ubuntu 22.04 LTS
ssh root@mynewserver
% apt update -y
% apt upgrade -y
% python3 --version
Python 3.10.6
% apt install mysql-server -y
% reboot
ssh root@mynewserver
% apt install libmysqlclient-dev -y
% apt install python3-pip
% cat requirements.txt
cffi==1.15.1
click==8.1.3
cryptography==38.0.1
Flask==2.2.2
greenlet==1.1.3
itsdangerous==2.1.2
Jinja2==3.1.2
MarkupSafe==2.1.1
numpy==1.23.4
pandas==1.5.1
pycparser==2.21
PyMySQL==1.0.2
python-dateutil==2.8.2
python-dotenv==0.21.0
pytz==2022.6
six==1.16.0
SQLAlchemy==1.4.41
Werkzeug==2.2.2
%
% pip install -r requirements.txt
% mysqladmin -u root password zekretword
% mysql -u root --password=zekretword -h localhost
(NOTE THAT I CAN CONNECT HERE!)
mysql> create database mydb;
mysql> quit
% mysql -u root --password=zekretword -h localhost mydb
(NOTE THAT I CAN CONNECT HERE!)
mysql> quit
% python3
Python 3.10.6 (main, Nov 2 2022, 18:53:38) [GCC 11.3.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>>
>>> from sqlalchemy import create_engine
>>>
>>> engine = create_engine("mysql+pymysql://root:zekretword@localhost/mydb")
>>> engine.connect()
Traceback (most recent call last):
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/base.py", line 3361, in _wrap_pool_connect
return fn()
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/pool/base.py", line 320, in connect
return _ConnectionFairy._checkout(self)
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/pool/base.py", line 884, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/pool/base.py", line 486, in checkout
rec = pool._do_get()
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/pool/impl.py", line 145, in _do_get
with util.safe_reraise():
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
compat.raise_(
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/util/compat.py", line 208, in raise_
raise exception
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/pool/impl.py", line 143, in _do_get
return self._create_connection()
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/pool/base.py", line 266, in _create_connection
return _ConnectionRecord(self)
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/pool/base.py", line 381, in __init__
self.__connect()
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/pool/base.py", line 677, in __connect
with util.safe_reraise():
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
compat.raise_(
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/util/compat.py", line 208, in raise_
raise exception
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/pool/base.py", line 673, in __connect
self.dbapi_connection = connection = pool._invoke_creator(self)
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/create.py", line 578, in connect
return dialect.connect(*cargs, **cparams)
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/default.py", line 598, in connect
return self.dbapi.connect(*cargs, **cparams)
File "/usr/local/lib/python3.10/dist-packages/pymysql/connections.py", line 353, in __init__
self.connect()
File "/usr/local/lib/python3.10/dist-packages/pymysql/connections.py", line 633, in connect
self._request_authentication()
File "/usr/local/lib/python3.10/dist-packages/pymysql/connections.py", line 907, in _request_authentication
auth_packet = self._read_packet()
File "/usr/local/lib/python3.10/dist-packages/pymysql/connections.py", line 725, in _read_packet
packet.raise_for_error()
File "/usr/local/lib/python3.10/dist-packages/pymysql/protocol.py", line 221, in raise_for_error
err.raise_mysql_exception(self._data)
File "/usr/local/lib/python3.10/dist-packages/pymysql/err.py", line 143, in raise_mysql_exception
raise errorclass(errno, errval)
pymysql.err.OperationalError: (1698, "Access denied for user 'root'@'localhost'")
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/base.py", line 3315, in connect
return self._connection_cls(self, close_with_result=close_with_result)
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/base.py", line 96, in __init__
else engine.raw_connection()
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/base.py", line 3394, in raw_connection
return self._wrap_pool_connect(self.pool.connect, _connection)
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/base.py", line 3364, in _wrap_pool_connect
Connection._handle_dbapi_exception_noconnection(
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/base.py", line 2198, in _handle_dbapi_exception_noconnection
util.raise_(
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/util/compat.py", line 208, in raise_
raise exception
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/base.py", line 3361, in _wrap_pool_connect
return fn()
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/pool/base.py", line 320, in connect
return _ConnectionFairy._checkout(self)
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/pool/base.py", line 884, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/pool/base.py", line 486, in checkout
rec = pool._do_get()
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/pool/impl.py", line 145, in _do_get
with util.safe_reraise():
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
compat.raise_(
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/util/compat.py", line 208, in raise_
raise exception
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/pool/impl.py", line 143, in _do_get
return self._create_connection()
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/pool/base.py", line 266, in _create_connection
return _ConnectionRecord(self)
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/pool/base.py", line 381, in __init__
self.__connect()
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/pool/base.py", line 677, in __connect
with util.safe_reraise():
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
compat.raise_(
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/util/compat.py", line 208, in raise_
raise exception
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/pool/base.py", line 673, in __connect
self.dbapi_connection = connection = pool._invoke_creator(self)
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/create.py", line 578, in connect
return dialect.connect(*cargs, **cparams)
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/default.py", line 598, in connect
return self.dbapi.connect(*cargs, **cparams)
File "/usr/local/lib/python3.10/dist-packages/pymysql/connections.py", line 353, in __init__
self.connect()
File "/usr/local/lib/python3.10/dist-packages/pymysql/connections.py", line 633, in connect
self._request_authentication()
File "/usr/local/lib/python3.10/dist-packages/pymysql/connections.py", line 907, in _request_authentication
auth_packet = self._read_packet()
File "/usr/local/lib/python3.10/dist-packages/pymysql/connections.py", line 725, in _read_packet
packet.raise_for_error()
File "/usr/local/lib/python3.10/dist-packages/pymysql/protocol.py", line 221, in raise_for_error
err.raise_mysql_exception(self._data)
File "/usr/local/lib/python3.10/dist-packages/pymysql/err.py", line 143, in raise_mysql_exception
raise errorclass(errno, errval)
sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1698, "Access denied for user 'root'@'localhost'")
(Background on this error at: https://sqlalche.me/e/14/e3q8)
>>>
What the heck?
3 Replies
What is the problem here? This is everything I did to create and provision the server:
Create a new high memory system using Ubuntu 22.04 LTS
. . .
sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1698, "Access denied for user 'root'@'localhost'")
(Background on this error at: https://sqlalche.me/e/14/e3q8)
This is your problem…nothing at all having to do with provisioning your server. IMHO, your login credentials to the database are incorrect here:
>>> engine = create_engine("mysql+pymysql://root:zekretword@localhost/mydb")
>>> engine.connect()
I don't know squat about python so this is about as far as I can go…
-- sw
I understand what you are saying. The question is, why does sqlalchemy give me an error connecting when I can connect directly, as is shown above the error output.
I know this is not, strictly speaking, a provisioning issue.
But. If I can create an AWS container and this works and I create a linode container and it does not, you all would want to know about that, yes?
I have used AWS for this stuff many times in the past and can re-verify what I am saying. And it is completely possible that I am doing something wrong. I will let you know if I see that.
By the way, it would be great if you offered another flask app in the marketplace that is not based on Debian. And is that using nginx? It would be great to have an example using apache. Ubuntu/apache is not an uncommon setup.
If I get this working, is there a way that I can contribute to the marketplace? I like using it, when it has what I want.
The question is, why does sqlalchemy give me an error connecting when I can connect directly, as is shown above the error output.
Because connecting with sqlalchemy (a python framework) is not the same as connecting with mysql (the DB vendor supplied CLI). Did you consider that sqlalchemy might have a bug…or there might be a version mismatch between the one you installed and the one you used before (either in sqlalchemy or in something it calls -- like libmysqlclient)?
But. If I can create an AWS container and this works and I create a linode container and it does not
You haven't yet demonstrated that the two scenarios are identical. Linode and AWS are not the same. Your development environments may not be the same.
you all would want to know about that, yes?
I don't care at all…it's not my project. I don't work for Linode…never have. I'm a volunteer here. FWIW, I doubt Linode would care either (unless you can demonstrate the problem is in the Linode infrastructure or networking).
By the way, it would be great if you offered another flask app in the marketplace that is not based on Debian. And is that using nginx? It would be great to have an example using apache. Ubuntu/apache is not an uncommon setup.
I don't work for Linode. I don't contribute to the Marketplace. I don't do python.
-- sw