(2059,“Authentication Plugin 'caching_sha2_pas

I want to configure my django project in order to connect it with database in MYSQL I created with workbench 8.0,
and then I want to run the server by running

python manage.py runserver

from anaconda command prompt,
so that I can use the Django interface to visualize and alter data.
Please note that I don’t want to downgrade workbench 8.0.

These are the steps I have made:

From anaconda prompt:

pip install mysqlclient

In my project folder, in settings.py

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'schema_meta',
        'USER': 'root',
        'PASSWORD': '<mypassword>',
        'HOST': '127.0.0.1',
        'PORT': '3306',
    },
}

Inside the directory of mysql server, I open cnf.ini and insert a the [client] section:

[client]
database=schema_meta
host=127.0.0.1
user=root
password=<mypassword>
port=3306
default-character-set = utf8

Then from anaconda prompt I run

Python manage.py runserver

And I obtain error

django.db.utils.OperationalError: (2059, "Authentication plugin 'caching_sha2_password' cannot be loaded: Impossibile trovare il modulo specificato.\r\n")

So I try to solve it by following this thread:
django.db.utils.operationalError: (2059,"Authentication Plugin 'caching_sha2_password'")

I open mysql workbench and I run this query:

delete from mysql.user
where user='root'
and host = '127.0.0.1';
flush privileges;
CREATE  USER 'root'@'127.0.0.1' IDENTIFIED WITH mysql_native_password BY '<mypassword>';

And then, in the my.ini file I change

default-authentication-plugin= caching_sha2_password

With

default-authentication-plugin=mysql_native_password

Finally from anaconda prompt:

python manage.py runserver

But again I get

django.db.utils.OperationalError: (2059, "Authentication plugin 'caching_sha2_password' cannot be loaded: Impossibile trovare il modulo specificato.\r\n")

Now, what’s wrong? Why it did not get the changes into the authentication method?

In order to check that there are no other errors, from mysql workbench, from first “home” view, I right click on my database, I open “edit connection”, I click on “test connection”, and the software says that the connection is successfull.

mysql workbench saying connection successfully established

Moreover, I wanted to check if the problem was in my Django settings. So from anaconda prompt I run

pip install pymysql

Then in the project folders I created a “connect_to_mysql.py” script, with the following code inside:

import pymysql.cursors
mydb = pymysql.connect(host='127.0.0.1',
                             user='root',
                             password='<mypassword>',
                             db='schema_meta',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)
print(mydb)

and this seems to work fine, since when I run

connect_to_mysql.py

from anaconda, I get

pymysql.connections.Connection object at 0x000002013F2851D0

That I guess it means “connection successfully established”.
And just to be sure that the problem is into mysql (mysql connector I guess), I create a file “connect_to_mysql_2.py” with this code inside:

import mysql.connector
mydb = mysql.connector.connect(user='root', password='<mypassword>',
                             host='127.0.0.1', database='meta_schema')
print(mydb)

And when I run it from anaconda, again I get

"Authentication plugin '{0}' is not supported".format(plugin_name)) mysql.connector.errors.NotSupportedError: Authentication plugin 'caching_sha2_password' is not supported

That means that I fixed nothing by working on mysql workbench and in my.ini file.

How can I get my Django connected with my mysql database and my server running?

Is there a way to establish the server connector using pymysql connector instead that mysql connector?

回答1:

This is probably not a problem in your python code, but in the python connector. The caching_sha2_password plugin is now the default auth plugin and clients have to support it in order to connect. So, the best solution is to update your python connector. An alternative way is to disable this plugin, but that's something I don't recommend as it lowers your server's security.

回答2:

I think I solved it.

By following this thread

https://stackoverflow.com/a/21740692/7658051

In settings.py, at the DATABASES entry, I substituted

'ENGINE': 'django.db.backends.mysql'

with

'ENGINE': 'mysql.connector.django',

and again, as specified here,

https://django-mysql.readthedocs.io/en/latest/checks.html#django-mysql-w003-utf8mb4

i added also

'OPTIONS': {
            # Tell MySQLdb to connect with 'utf8mb4' character set
            'charset': 'utf8mb4',
        },
        # Tell Django to build the test database with the 'utf8mb4' character set
        'TEST': {
            'CHARSET': 'utf8mb4',
            'COLLATION': 'utf8mb4_unicode_ci',
        }

and then if I run

python manage.py runserver

It seems to work, even if I cannot access http://127.0.0.1:8000/admin , because I am connected to a legacy database, so I still have to inspect db and stuff I still have to learn.

As second proof that the connection is now working, when I run