MySQL not recognizing login info through PHP, though I can login through the terminal

[davidai]

I can access my database with

mysql -u root -p

and then by entering my password.

But when I try to login through a php script

$link = mysqli_connect('localhost', 'root', 'mypassword');

I get the following error

Warning: mysqli_connect(): (HY000/1045): Access denied for user 'root'@'localhost' (using password: YES) in /Library/WebServer/Documents/connect/index.php on line 6

When I run

SELECT USER(),CURRENT_USER();

in MySQL, I get the following (reformatted)

root@localhost | root@localhost

So I know the three parameters in my login function are correct, but I am not able to sign in.

I would appreciate any help.

[s_molinari]

If you have a hosting package that allows you to create databases through an admin panel like cPanel or Plesk, then create a database that way, with a new user, and try to access that database over the new user’s credentials.

Scott

[davidai]

I asked on stackoverflow and was told to change ‘hostname’ to ‘127.0.0.1’ . This worked, though not sure why there was a problem as I’ve been viewing all my php documents with http://localhost/extension.

[Koobi]

The reason this happens is because your MySQL is configured to not use sockets, which is why ‘127.0.0.1’ works.
When you access your PHP documents via HTTP, localhost works fine because that’s just TCP/IP and, well, it’s configured properly.

Glad you sorted this out. The first time I ran into this issue, I spent a considerable amount of time till I figured it out.

Btw, are you by any chance using MariaDB?

[davidai]

I’m going to be using MariaDB in my actual site, yea. Not sure if I’m using it locally with the apache server.

[Koobi]

If you’re going to use MariaDB 10.0 and you need to log in via mysql CLI, do it this way:
sudo mysql -u root

The reason is, MariaDB 10.0 is using Linux sockets to authenticate.
I ran:
sudo mysql -uroot -e "SELECT plugin FROM mysql.user WHERE User='root';"
and I got:
+-------------+ | plugin | +-------------+ | unix_socket | | unix_socket | | unix_socket | | unix_socket | +-------------+

This is all good and secure until you want to install something like Drupal via the browser. By default, the installation will complain that it can’t connect.

You can either install the plugin: https://mariadb.com/kb/en/mariadb/unix_socket-authentication-plugin/
Or, you can create a new user with the appropriate privileges (secure and recommended).
You can also modify the plugin field to be an empty field but that’s not very secure or good so I won’t even go there