Search code examples
pythonmysqlmysql-connector-python

Getting an error of "Client does not support authentication protocol requested by server" using MySQL Connector in Python

Hello Dear StackOverflow friends,

I'm receiving an strange error when connecting to a managed MySQL instance (DigitalOcean). The connection works on my Dev computer (Windows 8.1 machine), but not on the Prod server (CentOS 8, SELinux in permissive mode). The connection also works with MySQL Workbench.

I've done pip freeze on both mentioned environments and both results are mysql-connector-python==8.0.19 which I find very strange. I've made sure to run my tests with the venv activated.

The managed MySQL 8.x instance is set up to allow connections from both my droplet and my Dev IP address. I've also tried this without the firewall enabled. The managed instance requires the usage of an SSL enabled connection, so a CA Certificate is provided (I've applied chmod 777 over it for now to make sure that's not the cause of the problem).

I've checked the documentation of the library I'm using and it's compatible with MySQL 8.

It is also worth noting I've also tried the solution in this question about it.

The code is the following. Works as expected in Windows.

import datetime

import mysql.connector
from mysql.connector.constants import ClientFlag

dbconn_host = '<sanitized>'
dbconn_port = '<sanitized>'
dbconn_user = '<sanitized>'
dbconn_passwd = '<sanitized>'
dbconn_database = '<sanitized>'

cnx = mysql.connector.connect(
    host=dbconn_host,
    port=dbconn_port,
    user=dbconn_user,
    passwd=dbconn_passwd,
    database=dbconn_database,
    client_flags=ClientFlag.SSL,
    ssl_ca='.\\ca_certificate.crt', # When running on prod server I change it to a proper Linux path
    # auth_plugin='caching_sha2_password' # Trying another solution I had it changed to mysql_native_password
)

cur_a = cnx.cursor(buffered=True)

query_sel = (
    "SELECT * FROM datasources"
)

cur_a.execute(query_sel)

for w in cur_a:
    print(w[0])

This is the stack trace I receive in Linux.

(venv) [root@<sanitized> <sanitized>]# python -i conn-test.py
Traceback (most recent call last):
  File "conn-test.py", line 12, in <module>
    cnx = mysql.connector.connect(
  File "/var/<sanitized>/venv/lib/python3.8/site-packages/mysql/connector/__init__.py", line 219, in connect
    return MySQLConnection(*args, **kwargs)
  File "/var/<sanitized>/venv/lib/python3.8/site-packages/mysql/connector/connection.py", line 104, in __init__
    self.connect(**kwargs)
  File "/var/<sanitized>/venv/lib/python3.8/site-packages/mysql/connector/abstracts.py", line 960, in connect
    self._open_connection()
  File "/var/<sanitized>/venv/lib/python3.8/site-packages/mysql/connector/connection.py", line 290, in _open_connection
    self._do_auth(self._user, self._password,
  File "/var/<sanitized>/venv/lib/python3.8/site-packages/mysql/connector/connection.py", line 212, in _do_auth
    self._auth_switch_request(username, password)
  File "/var/<sanitized>/venv/lib/python3.8/site-packages/mysql/connector/connection.py", line 256, in _auth_switch_request
    raise errors.get_exception(packet)
mysql.connector.errors.DatabaseError: 1251: Client does not support authentication protocol requested by server; consider upgrading MySQL client
>>>

What do you think could be the issue here?

Solution

  • The magic of StackOverflow, is when you post a question that you find the solution in a few minutes. Two things happened:

    • Half the time I didn't have network connectivity to the MySQL database.
    • So I ran all kinds of tests before I could even ping the server, then I realized I should run all tests again, but I didn't start with the basics (I did all tests with patches applied, instead of trying a "vanilla" connection first, so to speak).

    The solution is I commented out client_flags=ClientFlag.SSL, but left the CA Certificate enabled and the connection worked as expected in the Prod server.