Fix Error “Authentication plugin ‘caching_sha2_password’ is not supported”

Connecting to MySQL databases from Python often results in the frustrating error – “Authentication plugin ‘caching_sha2_password’ is not supported”. This occurs due to a mismatch between the authentication plugin used by the MySQL server and the plugin supported by the MySQL connector library in Python.

In this guide, we will understand the root cause of this error, explore solutions to fix it, and look at real-world examples of establishing database connections from Python applications.

Also read: How to Fix “Function is Not Defined” Error in Python

Why Authentication plugin ‘caching_sha2_password’ is not supported Error Occurs

To understand why this error occurs at all, let’s create a sample table so we can use that to guide our explanation.

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50)
);

INSERT INTO users (name) VALUES 
  ('John'),
  ('Sarah'), 
  ('Peter');

When trying to connect to this database from Python using the mysql-connector library, the following code results in an error:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="myuser",
  password="mypass"
)

Error:

mysql.connector.errors.NotSupportedError: Authentication plugin 'caching_sha2_password' is not supported  

This error occurs because MySQL 8.0 and above use a more secure password authentication plugin called caching_sha2_password for new user accounts.

However, the widely used mysql-connector Python library does not support this new plugin yet. It only supports the older mysql_native_password plugin used in old MySQL versions.

So there is a mismatch between the expected authentication plugin on the MySQL server and the one supported in mysql-connector causing connections to fail.

Also read: Firebase ImportError: Failed to import the Cloud Firestore Lib

How to Fix Authentication plugin ‘caching_sha2_password’ is not supported Error

There are a few ways to solve this problem:

1. Change User Authentication Plugin in MySQL

We can change the plugin used for the MySQL user account to mysql_native_password:

ALTER USER 'myuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mypass'; 

Now MySQL and python mysql-connector match in terms of plugin:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="myuser", 
  password="mypass"
)

print(mydb) # <mysql.connector.connection_cext.CMySQLConnection object>

However, this method compromises security since the mysql_native_password plugin is outdated and less secure than caching_sha2_password.

2. Use the mysql-connector-python Library

The mysql-connector-python library supports the newer caching_sha2_password authentication plugin.

First uninstall the mysql-connector package:

pip uninstall mysql-connector

Then install mysql-connector-python:

pip install mysql-connector-python

Now the connection code works with the newer plugin:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="myuser",
  password="mypass"  
)

print(mydb) # <mysql.connector.connection_cext.CMySQLConnection object>

This is the recommended approach as it keeps the security intact.

3. Specify Authentication Plugin in Connect Call

We can explicitly specify the mysql_native_password plugin when connecting from mysql-connector:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="myuser",
  password="mypass",
  auth_plugin='mysql_native_password'
) 

However, this method is not advised since the native password plugin has security issues.

Comparison of Solutions

Real-World Example: Connecting Flask App to MySQL

Let’s see examples of resolving this error when connecting Flask and Django apps to MySQL:

Error:

from flaskext.mysql import MySQL

mysql = MySQL()

app = Flask(__name__)
# MySQL configurations
app.config['MYSQL_DATABASE_USER'] = 'myuser'
app.config['MYSQL_DATABASE_PASSWORD'] = 'mypass'
app.config['MYSQL_DATABASE_DB'] = 'mydb'
app.config['MYSQL_DATABASE_HOST'] = 'localhost' 
mysql.init_app(app)

conn = mysql.connect() # ERROR !

Solution:

Simply change to use the mysql-connector-python library instead of flaskext.mysql. This connector supports the new authentication plugin:

import mysql.connector

mysql = MySQLconnector()

app = Flask(__name__) 
app.config['MYSQL_HOST'] = 'localhost'
app.config['MYSQL_USER'] = 'myuser'
app.config['MYSQL_PASSWORD'] = 'mypass'
app.config['MYSQL_DB'] = 'mydb'

mysql.init_app(app) 

conn = mysql.connect() # Works!

Summary

Some key points to remember:

• The error occurs due to a mismatch between the MySQL plugin and Python mysql connector library
• Use mysql-connector-python library to resolve it without downgrading security
• Changing the plugin in MySQL also fixes it but compromises the security
• Can specify auth plugin in connect call but has vulnerabilities.

The “Authentication plugin ‘caching_sha2_password’ is not supported” error occurs due to a mismatch between the MySQL authentication plugin and the one supported by the Python MySQL connector library.

We explored various solutions like changing the plugin in MySQL, using the mysql-connector-python library or explicitly specifying the auth plugin in connect call.

Using the mysql-connector-python library is the recommended approach as it works without downgrading MySQL security.

I hope this guide gave you a comprehensive understanding of why this error occurs and actionable solutions to fix it for establishing connections from Python to MySQL databases.

Reference: Reddit: