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

Question

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…

Answer 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