I followed the tutorial in the docs to create an app running MySQL. The app is running, and I am able to connect to the database, but only if I use the WireGuard tunnel to connect from my local machine with the SQLPro for MySQL app (presumably other MySQL client apps might work, this just happens to be the first one I grabbed).
With the proxy running (a simple fly proxy 3306 invocation), SQLPro is able to connect to the database at localhost:3306 using the username and password that I set, and I am able to make changes to the database.
However, if I attempt to connect from the terminal with mysql -h 127.0.0.1 -u <username> -p using the same username and password as with SQLPro, I get an access denied error message: ERROR 1045 (28000): Access denied for user '<username>'@'localhost' (using password: YES).
If I ssh into the instance running the MySQL app, I get the same error when trying to connect with mysql -u <username> -p, and I get the same when I ssh into the instance running my web app with mysql -h <app_name>.internal -u <username> -p.
The grant statement that I used to create my user and apply permissions looks like this:
CREATE USER IF NOT EXISTS '<username>'@'%';
ALTER USER '<username>'@'%' IDENTIFIED WITH mysql_native_password BY '<password>';
GRANT ALL ON <database>.* TO '<username>'@'%';
FLUSH PRIVILEGES;
Obviously SQLPro is doing something different to connect, but I am much more familiar with Postgres, and haven’t used MySQL much for several years, and am just at a loss for where to go next with this.
Just in case I had made some mistake while setting up the app, I just deleted it and re-created it from scratch, making sure to follow the tutorial closely. I’m getting the exact same behavior this time…
Do you happen to have a mysql running locally (directly or in a container) that’s listening on port 3306 already?
Note that mysql treats localhost and 127.0.0.1 differently (localhost always looks for a local socket file, while the IP address looks for a network). I’m not totally sure if that matters here, unless you THOUGHT you were connecting to the remote host and secretly were not (when using the wireguard connection method and connecting to localhost:3306).
I do not have another MySQL instance running locally, and just to be safe, I tried proxying local port 8000 to 3306 on the mysql machine, and mysql could not connect to the local 3306, but did connect to 8000 with the exact same behavior as before.
I had the same thought about localhost vs 127.0.0.1. It seems like SQLPro must be intelligently using the network and not socket for localhost; but nothing changes if I switch SQLPro to use the IP. from the terminal, obviously it doesn’t work to use localhost since there’s no socket open.
All I can think is that there must be some setting or permission that isn’t getting set, but I can’t think of what it would be. Everything I’ve tried results in the exact same behavior.
I get the same access denied message I do when I try to connect through the terminal locally or from the app server. In all cases it’s clearly talking to MySQL, because I’m getting MySQL errors, but it rejects all login attempts from the terminal, even though it accepts them from the GUI client.
My next best guess is that it’s a character encoding issue, or something. The passwords were set through Fly.io secrets, which perhaps encoded them in such a way that the password pasted into the terminal doesn’t match, but the GUI client does for some reason. I guess I’ll try resetting the passwords to something very simple so I can experiment with that.
It looks like my theory that it had to do with character encodings was correct!
If I do mysql -u root -p, and copy/paste the password into the prompt, it fails. However, if I do mysql -u root -p$MYSQL_ROOT_PASSWORD, it succeeds! The same is true if I use mysql -u $MYSQL_USER -p$MYSQL_PASSWORD.
I’m guessing that if I change my passwords to not include any characters that need escaping, everything will work fine.
Well, maybe that wasn’t quite it. I can now reliably connect to the database with the correct user from my app server with mysql -h my-mysql-app.internal -u $MYSQL_USER -p$MYSQL_PASSWORD, however even with a new password that doesn’t need escaping, it still doesn’t work if I copy/paste the password in, and my app is also still not connecting, suggesting that whatever the issue is with the copy/paste password is probably also happening with the app…
So I guess I still need to figure out why I’m getting access denied when I paste the password in; which is true even if I use --default-auth=mysql_native_password.
Got it! It was very simple… The password was too long…
I guess when passing it in as an environment variable, and through the SQLPro, it was getting truncated in a particular way that wasn’t happening when copy/pasting the password in. Reducing the password to 32 characters has solved the problem.
