1

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.

Abaddon
  • 36
  • 1
  • 8
  • So let me get this right -- you can connect to the local MySQL server from your PHP script using the root MySQL user and password, but if you change the user/password to another user you've create, it will not work, right? – Matt Runion Sep 28 '18 at 22:23
  • I can do the following: 1) login with root w/ no issue via PHP, CLI, MySQL Workbench 2) login with other usernames w/ no issue via CLI, MySQL Workbench 3) login with other usernames via PHP if I have opened a connection via CLI, MySQL Workbench since the last time I ran the script for credential creation (I'm learning so I am recreating things left and right on my local instance) – Abaddon Sep 29 '18 at 00:45
  • I have no idea then. If the CLI works and MySQL works, that should work. Are you sure your not using a firewall that's blocking port 3306 (or whatever port MySQL is listening on)? Are you using TCP/IP or sockets to connect? IS "skip_networking" commented out in your my.cnf config file? I'm really at a loss on this. – Matt Runion Sep 29 '18 at 19:09
  • I agree, it should work. My firewall is open on 3306. I'm... not sure if it's TCP/IP or sockets or how to tell? I'm using the built-in PHP mysqli OOP connection (see the above code). I haven't changed any major configs on it. I also can't find that config file anywhere on my computer (I failed to mention I'm working on a WAMP stack). It's a very weird problem - it will work, but only after logging in via the CLI/Workbench first. After that, I have no issues, it will connect, push updates, pull updates, everything. – Abaddon Sep 30 '18 at 04:03
  • 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`. I don't know how to fix this yet but I will be researching it. – Abaddon Sep 30 '18 at 16:35

1 Answers1

0

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.

Abaddon
  • 36
  • 1
  • 8