Issue connecting to MySQL with mysqli in PHP

Question

Been going through tons of answers but none have exactly matched my issue.

I have a MySQL database running and am attempting to connect in PHP with mysqli. The connection code is as follows:

<?php
    if(!isset($_COOKIE["uid"])) {

            $servername = "localhost";
            $username = "study";
            $password = "somepassword";
            $dbname = "user_study";
            $conn = new mysqli($servername, $username, $password, $dbname);
            if($conn->connect_error){
                    die("aww");
            }
}

However every time I get the error

mysqli::mysqli(): (28000/1045): Access denied for user 'study'@'localhost' (using password: YES)

I know that the user 'study'@'localhost' has permissions to access this database, because I am able to access it through the MySQL command line just fine. I have tried other accounts such as roots with the same result.

Is there anything else I should be checking?

New information (2/23) It seems that even when I shut the database down I'm getting an access denied result, meaning it seems to be trying to connect to some other database on the server. How would I ensure it is connecting to the correct one?

Try this : $con = `mysqli_connect("localhost","my_user","my_password","my_db");` — prakash tank, Feb 23 '17 at 04:52
Did you tried without the if condition? if not remove the if statement and then try to connect. — mapmalith, Feb 23 '17 at 05:04

spencer7593 · Answer 1 · 2017-02-23T05:48:37.897

I think the error message indicates that PHP was able to contact the MySQL Server using the socket file. (It would have been a different error otherwise.)

There's a couple of reasons you could get this error. If we can successfully connect to MySQL Server with a the mysql command line client like this:

> mysql --no-defaults -h localhost -u study -psomepassword user_study

That's going to rule out a lot of the possible reasons for the failure.

The most reasonable explanation for the error message from PHP is that the password being provided in the connection attempt from PHP does not match the password MySQL is expecting.

Some ideas we should be able to rule out. Privileges on the user_study database have been granted to 'study'@'localhost', e.g.

 GRANT SELECT ON user_study.* TO 'user'@'localhost'

On a totally different tack, given the assignment statement:

 $password = "somepassword";

And assuming that you wouldn't be supplying the actual password in the question... we're left wondering if the actual password contains characters that are subject to PHP string interpretation, such as backslash character, or a dollar sign.

For debugging, I suggest doing an echo $password; following the assignment, and verify that the string emitted is what is expected.

Another possibility is that there isn't an exact match in the mysql.user table, and the user 'study' is actually matching to a different mysql.user... an entry with an empty user ''@'localhost'.

I'd be taking a look at all of the entries in the mysql.user table where user='study' and user=''.

I also want to rule out the possibility that the mysql command line client using a .mylogin.cnf file.

I'm also tempted to suggest that changes were applied to the mysql.user table and a FLUSH PRIVILEGES statement wasn't executed... but that doesn't jive with the behavior (successful connection) observed in the mysql command line client.

We're assuming obviously that the MySQL Server is running local, on the same machine that PHP is executing on. And we're expecting to connect via the local socket file.

As a test, I'd suggest connecting via TCP. Specifying host as 127.0.0.1. That would require a different entry in the mysql.user table. We'd test connection from the mysql command line client:

> mysql --no-defaults -h 127.0.0.1 -u study -psomepassword user_study

But this gets into a whole host of other configuration issues with the MySQL Server, networking enabled, bind address, DNS name resolution, listening port, iptables, firewall, et al.

--

If the problem was an unsupported authentication protocol, I'd expect a different error. If the problem was the inability to connect to the socket file, I'd also expect a different error.

All of the usual causes for this error seem to be ruled out by a successful connection from the command line client, running on the local machine, connecting to using the same credentials.

You are exactly correct that mysql --no-defaults -h localhost -u study -psomepassword user_study fails as well. Currently privileges on study shows: Grants for study@localhost GRANT ALL PRIVILEGES ON *.* TO 'study'@'localhost' IDENTIFIED BY PASSWORD '*hash' I have already checked and cleared all ''@'localhost' users. However I AM using a .cnf file, due to the restrictions im currently under. (The server hosts multiple sites). Could there be something there causing this? — Firebarrage, Feb 23 '17 at 06:30
When you "cleared" the ''@'localhost' user, if you used DML operations, then you need to issue a `FLUSH PRIVILEGES` statement to make those change effective. I'd compare the password hash values. Get the hash of the password you are using, `SELECT PASSWORD('somepassword')` and compare that to the contents of the password column in mysql.user to make sure they are the same. — spencer7593, Feb 23 '17 at 16:33
Flushed privileges to no effect. I have compared the password hashes and they match. — Firebarrage, Feb 23 '17 at 17:55
New info - the same error happens even if I shut the database down, meaning it is trying to connect to some other database. Any ideas on how to force it to connect to the right one? — Firebarrage, Feb 23 '17 at 18:14
We don't run more than one MySQL instance on a VM, Most of our connections are routed through default TCP port (`mysql -P 3306`), not the special `localhost` unix socket file. https://dev.mysql.com/doc/refman/5.7/en/problems-with-mysql-sock.html I'm at a loss to understand your environment, where the socket file is, where the mysql client and PHP are looking for the socket file, and how many instances of MySQL Server you have configured. And I don't have any visibility to how the networking is configured (--bind-address, --skip-name-resolve, etc.) on the database you are trying to connect to. — spencer7593, Feb 23 '17 at 21:07

Issue connecting to MySQL with mysqli in PHP

1 Answers1