Forums

I'm pretty new at working with databases and I have hit a small snag while attempting to setup my MySQL db.

I was successfully using a sqlite db but I wanted to make my flask site a little more robust so I have been converting everything over to a local mysql db. Everything is working fine locally but I am not quite understanding the role of users I'm afraid. Here's what's going on:

I have a mysql database created here (pythonanywhere) and I was trying to do a restore on it from my local db dump but when I tried it I got this error: ERROR 1044 (42000) at line 22: Access denied for user '<myusername>'@'%' to database '<my database>'

I had created a user with all privileges on my local database but the name was different from my user name here. I thought I might need to create an identical user in my local database so that they match but that didn't seem to help.

When I created the local user I did:

 sql CREATE USER '<my-pythonanywhere-username>'@'%' IDENTIFIED BY <password>;

I then granted that one all privileges.

I know I'm probably missing something small but I think it would help to know how users should be handled in my local database (assuming that is what is causing my error).

Personally I would have different usernames and passwords for local dev versus your live environment, just to reduce the risk that running a test locally would try to connect to the live database and cause trouble -- as you presumably already have code that allows you to change the database hostname when you're running in the two environments, changing the username should not be too much of a big step away from that.

However, that's kind of an aside, because if the local restore doesn't work with the same username on both sides, it's unlikely to work with different usernames!

What happens if you use the mysql command-line tool to connect to your local server as the user that you created, then try to manually create a database, use it, then create a table inside it?

Forgive my ignorance but I couldn't figure out how to switch users using the mysql command-line tool. I was able to log in with that user (and other users) using Powershell and everything worked as expected. I can create, drop and insert just fine. Whats really odd to me is that I can run the dump command on the pythonanywhere bash console, download and restore that backup to a local db just fine. It's only when I try to dump locally, upload that to the site and then run the command to restore it does it give me the user access error. Here is what I am using at the bash prompt (cd'd to home where the backup file is):

 mysql -u drewxcom -h drewxcom.mysql.pythonanywhere-services.com 'drewxcom$rh'  < rheaster.sql

and here is the full error message:

ERROR 1044 (42000) at line 22: Access denied for user 'drewxcom'@'%' to database 'rh'

I also tried added the -p flag and entering my password (even though the instruction say I don't need to). The result was the same.

On a separate but related note...I was playing around and created a new database called 'rh-new' and now I am unable to get rid of it. It seems the "-" I used in the name is causing problems. Here are all the things I've tried:

mysql> drop database drewxcom$rh-new;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-new' at line 1
mysql> drop database 'drewxcom$rh-new';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''drewxcom$rh-new'' at line 1
mysql> drop database "drewxcom$rh-new";
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"drewxcom$rh-new"' at line 1
mysql> drop database drewxcom$rh\-new;
ERROR: 
Unknown command '\-'.
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\-new' at line 1

I was able to create and drop a separate db that did not have the "-" in it so I am assuming that is the hold up there.

With further testing I have been successful in restoring a local database to the one I have here (pythonanywhere). I finally spun up a Linux mint distro in VirtualBox, installed mysql server with only the root account and everything work as I would expect. I'm still not sure why my windows backup wasn't able to restore to the db here. I'm thinking it might just be some weirdness between Windows, Powershell and Linux. Sadly, I do prefer to dev on WSL on Windows but I can't get mysql server to run on the WSL side. I might do some more testing locally and see if I can restore my Windows backup to my Linux mint db. Either way, I now have a path forward to get my next deployment done. :) Now I just need to find a way to remove my rh-new database (minor concern).

I think the trick for deleting the database is to use the weird specific quoting pattern that MySQL uses for schema names, which is the backtick character ` -- for example:

drop database `drewxcom$rh-new`;

I'm sure there's a good reason why they chose that kind of quote for that use case, though I don't know what it is...

Very strange about the Windows MySQL server not working while the Linux one does. Maybe you could keep the Virtualbox running just for that server, and then connect to it from your Python code running on Windows outside the VB? That would at least minimise the amount of your dev environment that was not Windows.

I know this post is pretty old now but I finally figured out what the issue was when dumping from my windows machine. I was originally trying to use this command in Powershell to dump the local db:

.\mysqldump -u root -p<password> --databases <dbname> --verbose --default-character-set=utf8 -r <backupname>.sql

It was the --databases that was causing the issue. Once I left that flag off, everything worked correctly and I was able to restore that dump to your site properly.

The correct command to use in Powershell:

.\mysqldump -u root -p<password> <dbname> --verbose --default-character-set=utf8 -r <backupname>.sql

Hope this helps someone out there :)

interesting! thanks for letting everyone know!