Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mysql Access denied for user root@localhost (using password: NO) despite having set a password and entered it in connection uri

I'm trying to configure a mysql db with pymysql and Flask-sqlalchemy in a flask app.

db = SQLAlchemy()
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:password@localhost:3306/ftm'
db.init_app(app)

However, when I run my app, I get:

OperationalError: (pymysql.err.OperationalError) (1045, u"Access denied for user 'root'@'localhost' (using password: NO)")

I tried resetting the password, and verified that it works: from command line, I can enter mysql by writing:

mysql -u root -p

And then entering my password at the prompt. Does anybody have any insight as to why mysql seems to think I'm trying to connect without a password, even though I have a (valid) password in the URI?

Note that this is a different problem from mysql_exceptions.OperationalError: (1045, "Access denied for user 'root'@'localhost' (using password: YES)") and other potential duplicates because those error messages at least register that the connection attempt has a password attached.

Edit: In no particular order, I have: tried a different user, checked the state of the connect string immediately before db.init_app, created a different instance of MySQL and tried connecting to it, removed pymysql from the connection string, and tried back tracing it with pdb (useless because the only method call which I recognized as my own was init_app()). I have also scoured the internet, and found no mention of anybody getting this error message when trying to connect to the database using a password. The only remaining possibility that I can think of is that while being passed from app.config to mysql (when I call db.init_app) something is changing my string to remove the password. Any ideas? If nobody’s got any, I’ll just have to go use Postgre or something…

like image 722
Toby Weed Avatar asked Jan 02 '23 08:01

Toby Weed


1 Answers

I ran into this problem and solved it by entering the db shell and telling mysql to use the mysql_native_password authentication plugin to authenticate your database. This is an older plugin and it's probably worth looking for a newer one that's also compatible with Python's mysql connect driver, but this is the only way I could get it to work.

ALTER USER 'user_name'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password123'

In the example provided by the op you would substitute root for user_name.

More about the plugin: https://dev.mysql.com/doc/refman/8.0/en/native-pluggable-authentication.html

like image 103
Braden Holt Avatar answered Jan 05 '23 14:01

Braden Holt