(2059,“Authentication Plugin 'caching_sha2_password'”) when running server connected with MYSQL database on Django

9
February 12, 2019, at 1:00 PM

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 pymysql.cursors
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?

Answer 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.

READ ALSO
Is there a way to use the values of a column as headers?

Is there a way to use the values of a column as headers?

I'm trying to make a table that shows all students and their grades by courseSo far I have something like this

22
Rails group also returns count of duplicate rows

Rails group also returns count of duplicate rows

TLDR - I am trying to do group on this returned collection of distinct rowsBut for some reason, it also counts rows with duplicate procurement_id

10
Query to update particular column field in SQL

Query to update particular column field in SQL

I tried the following queries to change the column field of the table 'role' from 'Admin' to 'Administrator'I am not informed about the values of id

24
How to query a date using strftime to format

How to query a date using strftime to format

I am trying to grab all the records for the monthThe string I have to query with is in this format 2019-01-12

19