Forums

Hi there,

I need some help with connecting to a mysql database. I am trying to run a python script called init_db.py that, as the name suggests, initializes database tables using sqlalchemy and needs to connect to the database. the lines of interest are:

 db_url = os.environ.get('DATABASE_URI') if db_url.startswith("mysql"): engine = create_engine(db_url, pool_recycle=280) else: engine = create_engine(db_url) # Test connection with engine.connect() as connection: print("Connection successful!") 

However, when I try to run the script using the following bash command:

DATABASE_URI="mysql://rmheu:<password>@rmheu.mysql.eu.pythonanywhere-services.com:3306/rmheu$dms_database" python scripts/init_db.py

I get the following error:

An error occurred: (MySQLdb.OperationalError) (1045, "Access denied for user 'rmheu'@'10.0.0.146' (using password: YES)")

The error suggests it is an authentication error, but having changed my password and trying again multiple times I am thinking it has more to do with my connection string. I have also tried adding '+mysqlclient' or '+PyMySQL' after 'mysql' (those give me different errors), and changed 'rmheu$dms_database' to 'dms_database'. What am I missing here? Any help is much appreciated.

To add to this, if I open a bash console and try to access mysql with:

mysql -u rmheu -h rmheu.mysql.eu.pythonanywhere-services.com -p 

and enter my password, I get:

ERROR 1045 (28000): Access denied for user 'rmheu'@'10.0.0.146' (using password: YES) 

I am not sure what I am doing wrong here. I have changed the password again to no avail. Any help would be appreciated.

Looking at the non-eu forums I found a post with a similar problem. Just like in that post, the password in the .my.cnf file was different than I'd expected. I am not sure why it doesn't change when I change it in the database settings tab? I can't recall any errors

What password do you use?

I have tried multiple, even reverting to password123. One of those it stored in the .my.cnf file. The one that is currently working is a complex one generated by bitwarden.

Are you able to reconstruct series of events that led to this state? E.g. were you changing the passord on the Databases page frequently? Did you have .my.cnf opened and possible overwrote it with old password? Is the password in .my.cnf one that you recall creating?

I can't reconstruct it precisely. I was having problems with setting the connection string into an environment variable via the bash console (slightly off topic, but it would be a really nice addition if the database tab would generate a connection string for use in bash commands or the wsgi file). I was indeed changing passwords to rule out a wrong password as one of the possible reasons my script wasn't getting access to the database. I did not have my .my.cnf file open when doing so. The password is indeed one of the passwords I entered.

Are you able to connect with the database now? Just in case you haven't seen it yet, here's our general help page on using MySQL.

Yes, thanks, I am able to connect to it now, and have also been able to set the environment variable correctly. <br><br> I see that the information about the structure of the connection string for the flask-sqlalchemy combo is present at the help page you reference, thanks for the reference. The addition I suggested above, is of making a filled out string present on the database page for use in the wsgi file or bash (which should include an additional '\' in front of the dollar sign I believe), just like you provide the username, host, and database information. Even though you are right in that all the necessary information bits are present, it still requires multiple (error prone) copy pastes combined with experience about escaping characters based on where you would like to execute the string. Combined with the password resetting behavior that was unpredictable, I experienced this part of setting up the website slightly frustrating. <br><br> I am seeing things only from my perspective and recent experience, of course please judge yourself whether the additional effort is worth putting in.

Glad you managed to connect to your db.

We have a healthy backlog but we will take your feedback onboard. ;)