New MySQL 8 Default Authentication Plugin Not Supported

[Palisand]

Describe the bug

MySQL 8.0.4 introduced a new default authentication plugin: caching_sha2_password. This replaced mysql_native_password. The mysql server team also stated the following:

Correspondingly, libmysqlclient will now use caching_sha2_password as the default authentication mechanism, too.

mysql_native_password relies on SHA1 algorithm and NIST has suggested to stop using it.

Further, if two user accounts use the same password, mysql_native_password transformation is the same in the mysql.user table. Although the hash does not expose information about the actual password, it still tells which two users use the same password.

Attempting to connect to mysql with this library in certain environments yields the following error:
Plugin caching_sha2_password could not be loaded: /usr/lib/x86_64-linux-gnu/mariadb19/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory

One workaround for this would be to set the default-authentication-plugin setting to mysql_native_password. You could also change the authentication for your user: ALTER USER 'user'@'host' IDENTIFIED WITH mysql_native_password BY 'password';.

I am not certain if this problem lies with mysqlclient-python, but this github issue may prove useful to others attempting to connect to mysql 8 from a Debian box. I am not asking you for help (I'm already using the aforementioned workaround) nor am I asking you to treat this as an IT ticket. I'm just requesting that you consider adding support for the new default authentication if mysqlclient-python is indeed responsible from this problem. Support for a newer, more secure authentication plugin would be greatly welcome.

To Reproduce

>>> MySQLdb.Connection(host="...", user="...", password="...", database="...", port=...)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python3.8/site-packages/MySQLdb/__init__.py", line 130, in Connect
    return Connection(*args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/MySQLdb/connections.py", line 185, in __init__
    super().__init__(*args, **kwargs2)
MySQLdb._exceptions.OperationalError: (1045, 'Plugin caching_sha2_password could not be loaded: /usr/lib/x86_64-linux-gnu/mariadb19/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory')

Environment

MySQL Server

• Server OS: Debian Buster
• Server Version: MySQL 8.0.20
  • Dockerfile (official) for reference

MySQL Client

• OS: Debian Buster
• Python 3.8.5
• Development Files: default-libmysqlclient-dev

Additional context

There are related issues in this repository, but they involve windows installs; I am experiencing this on Linux (Debian). I am not experiencing this issue on MacOS.

[methane]

Development Files: default-libmysqlclient-dev

See https://packages.debian.org/ja/buster/default-libmysqlclient-dev. It is libmariadb-dev-compat. Maybe, it doesn't support caching_sha2_password.
You should use libmysqlclient-dev instead.

[Palisand]

That's a good point. Unfortunately, it doesn't appear to be available on Buster 😞 . Thanks anyway.

[Palisand]

This is now resolved.

After some time, I attempted to use the caching_sha2_password plugin again. I encountered the following error:

RSA Encryption not supported - caching_sha2_password plugin was built with GnuTLS support

Which prompted me to install gnutls-bin, which did the trick.

[RobbieClarken]

I ran into this issue with the python:3.9.7-buster docker image. Installing gnutls-bin didn't help but installing libmysqlclient-dev from the MySQL APT repository solved the issue:

FROM python:3.9.7-buster
RUN apt-get update \
    && apt-get -y install lsb-release \
    && curl -sLo mysql.deb https://dev.mysql.com/get/mysql-apt-config_0.8.19-1_all.deb \
    && DEBIAN_FRONTEND=noninteractive dpkg -i mysql.deb \
    && rm mysql.deb \
    && apt-get update \
    && apt-get -y install libmysqlclient-dev \
    && pip install mysqlclient==2.0.3