I'm having issues with intermittent connection issues from my localhost PHP site to my localhost MySQL DB. It gives me back Access Denied for 'user'@'localhost' (using password: YES) as is typical for a wrong password. I tested to make sure it wasn't anything particular, and I could get it with root, so I was confused. I even set up a 127.0.0.1, a localhost and a % account with the same username and password. None of them worked, but root worked just fine. That's been my biggest confusion - 'root' working with no issues, but this new user not working at all.
I've looked through so many of the problems (see below) and issues laid out on SE and other forums relating to Error 1045, Access Denied and all variants, and most boil down to a misspelling, or a typo, or something of that nature. I've copy and pasted every relevant string around all of my code, so that's not the issue. I don't have an anonymous user, so that's not the issue. There's no DNS issues, it's localhost and I've tried every iteration of that.
The issue comes from what solves the issue for me. If I open a connection to the DB using the new account via MySQL Workbench or CLI, suddenly the webpage will work with no issues, connecting via the new account with no problems. But if I run the script that changes the new account information whatsoever, even if I change nothing, and reload the page, it will deny the access again until I open that connection up again via MySQL Workbench or the CLI.
It works without fail, every single time. I have no idea what's going on - does the account need to be logged into via CLI or Workbench before they can be used via PHP? Or is there something more I am missing here?
PHP Code
$servername='localhost';
$username='website';
$password='password';
$database='db_1';
$conn=new mysqli($servername, $username, $password, $database);
if($conn->connect_error) die("connection failed");
MySQL Credentials Creation
create user website@'localhost' identified by 'password';
grant SELECT, INSERT, DELETE on db_1.* to 'website'@'localhost';
A few of the SO questions that didn't help:
MySQL - Access denied for user
access denied for user @ 'localhost' to database ''
"Connect failed: Access denied for user 'root'@'localhost' (using password: YES)" from php function
EDIT 1:
I am running a WAMP stack on a local computer. I have my firewall open for port 3306 (the port MySQL is listening on). I am not using CPanel (I think that's what it's called anyways). And I just tested and it doesn't work with root either until I go ahead and recreate a new connection to the DB via the CLI or Workbench.
The error it's giving back also includes this, but I've never had this come up as I've never tried to install OpenSSL on either before: mysqli::__construct(): PHP was built without openssl extension, can't send password encrypted in [file]. But this goes away as soon as I make the connection via the CLI (no fancy tags or anything, so I don't think I'm using SSL) or via Workbench.
Edit 2: The rabbit hole went deeper and then stopped but I'm still confused as to how to configure my set up properly to avoid it in the future/fix it to be more secure. The solution (which I can't put up as my own, so if one of you wants to explain and then claim this, go for it - I'll accept it) was to go back to my MySQL server and reconfigure the Authentication Method to not use the Strong Password Encryption and instead opt for the Legacy Authentication Method. If someone could explain how to make my PHP compatible with the newer authentication method (I haven't looked yet but will be after I post this) I would be grateful.
The solution was to go back to my MySQL server and reconfigure the Authentication Method to not use the Strong Password Encryption and instead opt for the Legacy Authentication Method.