"Connect failed: Access denied for user 'root'@'localhost' (using password: YES)" from php function [duplicate]

Solution 1:

I solved in this way: I logged in with root username

mysql -u root -p -h localhost

I created a new user with

CREATE USER 'francesco'@'localhost' IDENTIFIED BY 'some_pass';

then I created the database

CREATE DATABASE shop;

I granted privileges for new user for this database

GRANT ALL PRIVILEGES ON shop.* TO 'francesco'@'localhost';

Then I logged out root and logged in new user

quit;
mysql -u francesco -p -h localhost

I rebuilt my database using a script

source shop.sql;

And that's it.. Now from php works without problems with the call

 $conn = new mysqli("localhost", "francesco", "some_pass", "shop");

Thanks to all for your time :)

Solution 2:

Is there a user account entry in the DB for root@localhost? In MySQL you can set different user account permissions by host. There could be several different accounts with the same name combined with the host they are connecting from. The most common are [email protected] and root@localhost. These can have different passwords and permissions. Make sure root@localhost exist and has the settings you expect.

I am willing to bet, based on your explanation, that this is the problem. Connecting from another PC uses a different account than root@localhost and the command line I think connects using [email protected].

Solution 3:

From what you've said so far, it sounds like a problem where the MySQL driver in PHP5.3 has trouble connecting to the older MySQL version 4.1. Have a look on http://www.bitshop.com/Blogs/tabid/95/EntryId/67/PHP-mysqlnd-cannot-connect-to-MySQL-4-1-using-old-authentication.aspx

There's a similar question here with some useful answers, Cannot connect to MySQL 4.1+ using old authentication

SELECT `User`, `Host`, Length(`Password`) FROM mysql.user

This will return 16 for accounts with old passwords and 41 for accounts with new passwords (and 0 for accounts with no password at all, you might want to take care of those as well). Either use the user managements tools of the MySQL front end (if there are any) or

SET PASSWORD FOR 'User'@'Host'=PASSWORD('yourpassword');
FLUSH Privileges