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…

Solution 1:

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

Solution 2:

OK, there are several potential problems here. For example:

1. Maybe your "connect" syntax isn't sending a password (like you think it is)

2. Maybe your combination of "user" (e.g. "root") and "host" (e.g. "localhost") isn't defined in mySQL exactly the way you think it is.

3. Maybe the combination of user and host isn't being passed from app.config to mysql exactly the way you think it is.

SUGGESTIONS:

1. Look here:

   • Using MySQL in Flask and here:

   • mysql_exceptions.OperationalError: (1045, "Access denied for user 'root'@'localhost' (using password: YES)").

2. Consider changing "mysql+pymysql" and see what happens.

3. Consider defining a different user ("root" is probably a bad idea anyway) and see what happens.

4. Add "printf's" (or the Alchemy equivalent) and/or turn on tracing.

This tutorial might also help:

• The Flask Mega-Tutorial, Part XVI: Debugging, Testing and Profiling