0

I'm trying to connect Python from a Mac to a MySql database that is running on a Raspberry Pi 4 with Ubuntu Server LTS 20.04 installed where both machines are on my LAN. Currently I have MySql Workbench on the Mac and am able to access the database via a Standard TCP/IP connection using SSH. No problem accessing the server via the Terminal over SSH or logging into the database in question.

I'm running Python 3.8.5 on the Mac and the latest version of MySQL Server on the Ubuntu Server installed on the RPi4.

The issue comes up when I write the connection code in a Python file, and attempt to run it via the Terminal. Here is the error I am getting (spaces are mine):

  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/mysql/connector/connection_cext.py", line 216, in _open_connection
    self._cmysql.connect(**cnx_kwargs)
_mysql_connector.MySQLInterfaceError: Access denied for user '***'@'localhost' (using password: YES)
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
  File "testdb1PYTHON.py", line 3, in <module>
    mydb = mysql.connector.connect(
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/mysql/connector/__init__.py", line 264, in connect
    return CMySQLConnection(*args, **kwargs)

  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/mysql/connector/connection_cext.py", line 80, in __init__
    self.connect(**kwargs)

  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/mysql/connector/abstracts.py", line 966, in connect
    self._open_connection()

  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/mysql/connector/connection_cext.py", line 218, in _open_connection
    raise errors.get_mysql_exception(msg=exc.msg, errno=exc.errno,

mysql.connector.errors.ProgrammingError: 1045 (28000): Access denied for user```
monkkey
  • 11
  • 2
  • 1
    it will be helpful to see the code you are using to try and connect to the pi, feel free to mock out any sensitive password/usernames – Aron Lawrence Aug 16 '20 at 01:41
  • is the RPi4 listening on all interfaces for mysql connection or it is listening only on the 127.0.0.1 IP? Also you should try to connect via terminal with `mysql` utility from the server where your code is, to your RPi IP with the credentials you are passing in your code, so that you figure out why the access is denied. Once you solve this, your code will also work. Also check [this](https://stackoverflow.com/questions/10299148/mysql-error-1045-28000-access-denied-for-user-billlocalhost-using-passw) answer. – Ron Aug 16 '20 at 01:59
  • Here is the code I'm using to attempt to connect: ```import mysql.connector mydb = mysql.connector.connect( host="localhost", user="user", passwd="xnxnxnxnx", database="testdb1" ) if mydb.cursor: print("done")``` – monkkey Aug 16 '20 at 02:08
  • Ron, so I'm able to SSH into the Raspberry Pi via Terminal on my mac, and then login to the MySql database. When I connect to it with the MySql Workbench, it looks like it is connecting via 127.0.0.1 When you say the 'mysql utility' - do you mean I need to have something like 'mysql client' installed on the Mac? – monkkey Aug 16 '20 at 02:49
  • So after some additional research, it looks like the "caching sha2 password" authentication method is not supported in Python 3. I went into the MySql server and changed the root user's authentication to "mysql_native_password" which is now giving me a "sha256_password requires SSL" error message. To date based on what I've read there is not any solution for this. I am able to SSH into the database on the server, so perhaps there is some Pythonic workaround, but I sure haven't found it yet. – monkkey Aug 17 '20 at 01:29

0 Answers0