Error: mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication

Question

I am getting the following error:

Database connection failed: mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication. Please use an administration tool to reset your password with the command SET PASSWORD = PASSWORD('your_existing_password').

This will store a new, and more secure, hash value in mysql.user. If this user is used in other scripts executed by PHP 5.2 or earlier you might need to remove the old-passwords flag from your my.cnf file

I using PHP 5.3.8 and MySQL 5.5.16 on my local machine and my host (media temple) is running PHP 5.3 MySQL 5.0.51a. The version on the live server is older than the one on my machine.

How do I fix this error and connect to MySQL from my local machine?

I know there are similar posts to this one but I have tried them all and none are working.

This is duplicate of http://stackoverflow.com/questions/1575807/cannot-connect-to-mysql-4-1-using-old-authentication — rkosegi, Jan 12 '12 at 06:55
As stated in my post, i have tried those suggestions and none are working for me. That post was also back in 09 so i reposted to see if there were other/more recent solutions. — user982853, Jan 12 '12 at 06:57

score 43 · Accepted Answer · edited Mar 26 '17 at 14:54

43

Remove or comment old_passwords = 1 in my.cnf

Restart MySQL. If you don’t, MySQL will keep using the old password format, which will mean that you cannot upgrade the passwords using the builtin PASSWORD() hashing function.

The old password hashes are 16 characters, the new ones are 41 characters.

Connect to the database, and run the following query:

SELECT user, Length(`Password`) FROM  `mysql`.`user`;

This will show you which passwords are in the old format, e.g.:

+----------+--------------------+
| user     | Length(`Password`) |
+----------+--------------------+
| root     |                 41 |
| root     |                 16 |
| user2    |                 16 |
| user2    |                 16 |
+----------+--------------------+

Notice here that each user can have multiple rows (one for each different host specification).

To update the password for each user, run the following:

UPDATE mysql.user SET Password = PASSWORD('password') WHERE user = 'username';

Finally, flush privileges:

FLUSH PRIVILEGES;

Source: How to fix "mysqlnd cannot connect to MySQL 4.1+ using old authentication" on PHP5.3

edited Mar 26 '17 at 14:54

kenorb

155,785
88
678
743

answered Jan 12 '12 at 07:16

Ramil Amerzyanov

1,301
12
26

Im a little confused, should i be doing this int he command line of my own comp or do should i be using myadminphp to connect to the external db. My db is on a remote server not on my comp. – user982853 Jan 12 '12 at 07:27
Everything i run inside phpmyadmin tells me access denied. I can do normal queries but any admin commands i getting denied. – user982853 Jan 12 '12 at 07:36
When i run the above code on my local machine i get root | 0 three times. Three rows of root|0. Like there is no passord or the password is 0 in lenght. When i run it in phpmyadmin (external db) i get access denied. – user982853 Jan 12 '12 at 07:42
@user982853 you should connect to remote mysql host by root user. At your local machine you get root | 0 because you have not password for root – Ramil Amerzyanov Jan 12 '12 at 09:15
@user982853 my be you have not access from other host. Only from localhost. Ask your administrator – Ramil Amerzyanov Jan 12 '12 at 09:17
Worked like a charm, thanks. To answer the first two comments, this is done through the command line. – jel May 31 '13 at 08:27
Hey I am getting the same problem, I am using SQLYog GUI for mysql and I am not able to find my.cnf file. Please help me to out of it. – Anil Kumar Pandey Dec 23 '13 at 07:07
When removing old_password=1, does all the current password still work? Can I update only the password for the account that cause problem and keep all others untouched? – Byscripts Oct 02 '17 at 08:52

score 4 · Answer 2 · answered Apr 18 '14 at 08:49

I had a issue where the old passwords had been enable by the server by default, so a simple SET PASSWORD FOR 'some-user'@'%' = PASSWORD ('XXXX'); wouldn't work(for reason due to old software and legacy which I won't go into....)

Solution :

SET old_passwords = 0;
SET PASSWORD FOR 'some-user'@'%' = PASSWORD ('XXXX');
FLUSH PRIVILEGES;

Details :

Doing this as the logged in user

SET Password = PASSWORD('password')

Simply didn't work

Eg testable here

SHOW CREATE TABLE `mysql`.`user`;

The password wouldn't shift to from

| HOST | USER | PASS |

OLD PASSWORD

| % | some-user | 7fa559aa33d844b4 |

WHAT I WANTED, EG NEW PASSWORD

| % | some-user | *CF04AECA892176E6C0C8206F965C03374D12F93E |

So I looked up the variables for old passwords

SHOW VARIABLES;

...
old_passwords = ON
...

So basically I had to set the mysql server var first to old_password=OFF, eg this worked for me

SET old_passwords = 0;
SET PASSWORD FOR 'some-user'@'%' = PASSWORD ('XXXX');
FLUSH PRIVILEGES;

score 2 · Answer 3 · answered May 30 '13 at 05:41

I had the same issue trying to connect to mediatemple external-db through IIS on my local Windows machine. Updating my password for the specific db user solved the problem connecting to the database.

Within (mt) Account center, simply update the password. I used the same password and it solved all my problems.

score 2 · Answer 4 · edited Jun 13 '13 at 00:12

2

Configure target Mysql server to allow old insecure auth.

http://dev.mysql.com/doc/refman/5.0/en/server-options.html#option_mysqld_old-passwords

Simply in the my.cnf file on target Mysqld server comment out the old_passwords.

Maybe there is way to obtain PHP build (or build it yourself) which uses compatible (old) auth mode.

edited Jun 13 '13 at 00:12

Aristos

66,005
16
114
150

answered Jan 12 '12 at 07:02

rkosegi

14,165
5
50
83

I do have access to my my.cnf file. I made the changes of commenting that line out and now i get the error: Database selection failed: Access denied for user 'db95658_ewr'@'%' to database 'mvc'. I have checking my username and password and they are correct. – user982853 Jan 12 '12 at 07:11
Try to reset your password o same as you had.When mysqld changed auth mode, it needs to change password hashes in users table. – rkosegi Jan 12 '12 at 07:37
How can we configure php to use old compatible auth mode before building? – nurp Jan 08 '19 at 09:14

score 0 · Answer 5 · edited Mar 26 '17 at 16:05

0

I had this issue when I imported a database from an older version of MySQL. The biggest problem - it was preventing me from connecting to MySQL with my root user, so I had to reset the root password by following the instructions below:

How to Reset the Root Password

After this I was able to apply the fix given above.

E.g. by:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');

edited Mar 26 '17 at 16:05

kenorb

155,785
88
678
743

answered Oct 25 '13 at 11:43

littledynamo

406
3
12

score 0 · Answer 6 · answered Jul 29 '16 at 18:51

I ran into this error for the first time with a Media Temple db on a grid server.

The issue was caused because I used a password for a database user that was still in their old password format. The new format calls for 10+ characters, special characters, a number etc...

I created a new database user and password in the new format and it worked fine.

score 0 · Answer 7 · answered Jun 10 '19 at 11:03

i have the same issue and this is all steps i've done to solve this porbleme :

Check if you use the correct credential
use this command to be sure that you change your pass : use characters ,numbers and letters (more than 10)

UPDATE mysql.user SET Password = PASSWORD('NewPassword') WHERE user = 'username';
be sure that you comment oldpassword value in /etc/my.cnf
restart mysql service to avoid any other issues

Error: mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication

7 Answers7

Linked

Related