59

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

solomonbstoner
  • 74
  • 10
gc5
  • 8,206
  • 20
  • 76
  • 137
  • 1
    What version of PHP are you running? What version of MySQL? It sounds similar to http://www.bitshop.com/Blogs/tabid/95/EntryId/67/PHP-mysqlnd-cannot-connect-to-MySQL-4-1-using-old-authentication.aspx – Jack Murdoch Jun 22 '11 at 20:16

11 Answers11

87

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 :)

gc5
  • 8,206
  • 20
  • 76
  • 137
  • 1
    Well, I tried everything and it was still showing issue, figured out, I had other user with same name for different host value in DB, with different passwords and privileges, removed these users and it worked, – JulyOrdinary Jul 22 '16 at 13:56
  • 1
    where do you type in these commands? Im really new to this literally only started the other day. I'm using MAMP and trying to connect a database i set up in phpmyadmin. – kitchen800 Jul 04 '17 at 13:25
  • @pete800 I cannot recall exactly but you have to use the terminal, not phpmyadmin. I do not know if the latter has support for the terminal, however. You have to access mysql directly from the terminal. – gc5 Jul 06 '17 at 10:16
  • 1
    Dont understand where to type these command and exactly what and why? explain more please. – AY - 杨志强 Aug 07 '17 at 06:35
  • @Mr_Andrew you have to type this command through the terminal (the command line) of your OS. In Mac OS it may be Terminal, iTerm, in Windows Powershell, in Linux gnome-terminal, and so on. Once you get into the terminal you log in to the mysql client through the first command and then continue with subsequent commands. – gc5 Aug 17 '17 at 11:07
  • I had a similar problem. As you know, `root` user has all privileges. No need to define a new user. My problem was due to case sensitivity. I have a DB called `sampledb` in MySQL and its name was written `sampleDB` in PHP code. I modified my PHP code and the problem was resolved. – Abdollah Dec 23 '18 at 07:04
10

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 root@127.0.0.1 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 root@127.0.0.1.

queso
  • 1,110
  • 8
  • 6
  • When I log in from the command line I use `mysql -u root -p -h localhost` and typing `select user()` I get _root@localhost_ – gc5 Jun 22 '11 at 19:39
  • 1
    and........ I did `GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'; FLUSH PRIVILEGES;` so there should be no problems... yet mysql keeps on saying access denied... which obviously renders mysql quite useless. – user3338098 Nov 04 '15 at 17:56
  • 1
    ahhhh lied to by mysql again... there change password command was faulty I had to do `SET PASSWORD FOR 'root'@'%' = PASSWORD('MyNewPass');` mysql tricked me by making it look like there was only one password, when in fact mysql considers root@domain1 a totally different user than root@domain2.... – user3338098 Nov 04 '15 at 18:04
8

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
Community
  • 1
  • 1
Jack Murdoch
  • 2,671
  • 1
  • 15
  • 26
  • It seemed the right solution but it doesn't work :| as I edited before php and mysql shall be compatible by version.. I will google further.. Also, I set as user password (different hosts) the same password but with no results.. – gc5 Jun 22 '11 at 23:33
  • @Francesco What's the output of `echo mysqli_connect_error();`? – Jack Murdoch Jun 23 '11 at 08:22
  • the output is the one I used as title `Connect failed: Access denied for user 'root'@'localhost' (using password: YES)` – gc5 Jun 23 '11 at 11:31
  • Moreover: with `SELECT \`User\`, \`Host\`, \`Password\` FROM mysql.user` I got that all three root accounts have the same password hash.. – gc5 Jun 23 '11 at 11:39
5

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"

Pseudo Nym
  • 59
  • 1
  • 1
5

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.

LowLevel
  • 943
  • 1
  • 10
  • 33
  • 1
    Solved our issue with an OpenCart install on Cpanel. We were looking for issues with mysqli on the server, turned out to be an invalid character in the password! Thanks. – Jayden Meyer Mar 01 '16 at 01:23
  • @JaydenMeyer You're welcome; I'm so glad I could help! – LowLevel May 30 '17 at 11:25
4

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

Miku jessi
  • 89
  • 8
1

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
Dutch Glory
  • 14,795
  • 1
  • 12
  • 6
1

Most likely it is not identifying your user properly. root@localhost. 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

camdixon
  • 792
  • 2
  • 12
  • 32
1

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

Willey Hute
  • 407
  • 5
  • 5
  • hi @shrinivas-manjithaya, thanks for your answer. It would definitely help to include an example of the code that you are suggesting to the user. Also if you have the ability to reproduce the error they are experiencing and provide directions on how to resolve this for the user it is more likely your answer will be helpful for them. – TsTeaTime May 04 '19 at 03:44
0

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

Adesina Yomi
  • 1
0

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");
Greg
  • 1,943
  • 3
  • 18
  • 26
  • 2
    While that would make the code more readable, it doesn't solve the problem which has to do with incorrect authentication information and/or grants. – datasage Jun 22 '11 at 20:14
  • 13
    `$conn = new mysqli($host, $username, $password, $table);` No double quotes needed. – Rocket Hazmat Jun 22 '11 at 20:24