5

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…

Toby Weed
  • 594
  • 2
  • 7
  • 20
  • Possible duplicate of [mysql\_exceptions.OperationalError: (1045, "Access denied for user 'root'@'localhost' (using password: YES)")](https://stackoverflow.com/questions/10181344/mysql-exceptions-operationalerror-1045-access-denied-for-user-rootlocalh) – paulsm4 May 20 '18 at 22:10
  • does your password contain any special characters (something other than letters and digits)? – jdigital May 20 '18 at 22:12
  • @jdigital it does not – Toby Weed May 20 '18 at 22:20
  • @paulsm4 edited to clarify why it is not – Toby Weed May 20 '18 at 22:20
  • 1
    right before db.init_app, print out the connection string and make sure you're sending what you think you're sending. it seems strange that mysql doesn't think you're sending a password. does sqlalchemy have an option that prohibits passwords via the connection string? – jdigital May 20 '18 at 22:51
  • @jdigital good thought, but it only prints the correct connection string. – Toby Weed May 21 '18 at 01:58
  • @Toby Weed: regardless if it happens to be the "exact same problem"... please *do* review the links (the one above, as well as the ones I cited below) for "troubleshooting tips". Please, too, consider alternatives like a) creating a new, different user, b) changing your connection string, c) reading the connection string elsewhere besides app.config. "Assume" nothing; try different alternatives, even if they "seem" equivalent. And keep us posted what you find! – paulsm4 May 21 '18 at 04:30
  • Q: Did you get this squared away? If not, consider posting an ["SSCCE"](https://meta.stackexchange.com/questions/22754/sscce-how-to-provide-examples-for-programming-questions). – paulsm4 May 23 '18 at 23:30
  • @paulsm4 to be honest, I kind of gave up on this. I'm currently using a SQLite instance for testing, and figure I'll probably just switch to a remote-hosted S3 MySQL instance shortly before production... woops! – Toby Weed May 29 '18 at 16:11
  • please see this link :http://www.daniloaz.com/en/how-to-create-a-user-in-mysql-mariadb-and-grant-permissions-on-a-specific-database/ – Amin Golmahalleh Mar 02 '20 at 11:53
  • Just need to add the PORT number. app.config['MYSQL_PORT'] = '3306' – Lakhwinder Singh Dhillon Apr 27 '20 at 03:44

3 Answers3

9

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

Braden Holt
  • 1,544
  • 1
  • 18
  • 32
2
mysql -u root -p

User is set to root Password is empty.

app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:@localhost:3306/ftm'

The colon : after the is the separator between user and password. If the password is empty, the @ immediately follows the :.

trembl
  • 3,873
  • 2
  • 20
  • 14
1

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:

  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:

paulsm4
  • 114,292
  • 17
  • 138
  • 190
  • Great suggestions, and I've narrowed it down to possibility #3 (I think). Any ideas on how to test this? – Toby Weed May 21 '18 at 21:39