I wrote some function used by a php webpage, in order to interact with a mysql database. When I test them on my server I get this error:

"Connect failed: Access denied for user 'root'@'localhost' (using password: YES)" 

I am able to use them on my pc (using XAMPP) and I can navigate through the tables of the database using the command line in the server. However, the webpage fails to connect. I've checked the password but with no results. It's correct (otherwise I could not log in to mysql from the command line).

The call of the function is the following:

$conn = new mysqli("localhost", "root", "password", "shop");

Do I have to set something in my server? Thanks

Edit: PHP version 5.3.3-7+squeeze1 mysql version: 5.1.49-3 both on debian

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 [email protected]? 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 [email protected] These can have different passwords and permissions. Make sure [email protected] 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 [email protected] 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

Solution 4

I had this problem too. But this was because of another reason. My password began with character $... e.g. $MyPassword I've changed it to #MyPassword and the problem is solved.

Solution 5

Easy.

open xampp control panel -> Config -> my.ini edit with notepad. now add this below [mysqld]

skip-grant-tables

Save. Start apache and mysql.

I hope help you

Solution 6

Here maybe?

I believe that the code should be:


$connect = new mysqli("host", "root", "", "dbname");

because root does not have a password. the (using password: YES) is saying "you're using a password with this user"

Solution 7

TIP:

comment out pid and socket file, if you can't connect to server.. mysql could be connecting with incorrect pid and/or socket file, so when you try to connect to server trough command-line it "looks" at the incorrect pid/socket file...

### comment out pid and socket file, if you can't connect to server..
#pid-file=/var/run/mysql/mysqld.pid #socket=/var/lib/mysql/mysql.sock

Solution 8

Most likely it is not identifying your user properly. [email protected] Select the Mysql version your MAMP is using.

/Applications/MAMP/Library/bin/mysqldump -uroot -p db_name > test_db_dump.sql

I actually wrote an alias on my computer so I don't have to remember how to get to the bin directory.

alias mysqldumpMAMP='/Applications/MAMP/Library/bin/mysqldump'

Which allows me to run this:

mysqldumpMAMP -uroot -p db_name > test_db_dump.sql

You can save an alias in your bash profile ~/.bash_profile or ~/.bash_rc

Solution 9

May be there password is not set and you are passing password as argument. Try ommitting password argument during connection..

Solution 10

Edit your privileges on PHPmyAdmin (WAMP), Note that your password and user name has not been created. So do create or edit it, that it might work with your sql connection in your php. Hope it works

Solution 11

Try initializing your variables and use them in your connection object:

$username ="root";
$password = "password";
$host = "localhost";
$table = "shop";
$conn = new mysqli("$host", "$username", "$password", "$table");