django mysql 2059,（2059，“身份验证插件'caching_sha2_password'”）在Django上运行与MYSQL数据库连接的服务器时...

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': '',

'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=

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")

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 '';

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.

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='',

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='',

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?

解决方案

I think I solved it.

By following this thread

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

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

with

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

and again, as specified here,

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

connect_to_mysql_2.py

I finally get

mysql.connector.connection_cext.CMySQLConnection object at 0x000001DFB7521550

So I really think this time the connection is on.