Could not connect to MySQL 4.1+ using old insecure authentication

Question

I know that there have been a lot of questions raised with the same concern as mine, but I hope you can help me find out another solution for this.

We are using Symfony 2.5.10, PHP 5.4.44 and MySQL 4.1.20 for our project.

Whenever I attempt to log in to the system or any act that concerns connecting to the database (e.g adding new user using FOS commands), I receive this error:

SQLSTATE[HY000] [2000] 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

We already tried setting old_passwords in my.cnf to 0, restart mysql, set a new password for the mysql user and restart mysql again but still the same. And whenever we run this in the query window:

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

it still shows 16 as the length of the password of the user we are using. And also when checking the mysql server variables and settings, old passwords is ON (even though we already set old_password to 0 in my.cnf).

What could be wrong with this? I would really appreciate your help.

Update: For the meantime, we remove the db user's password for us to be able to access the database. But I'm still looking for other solutions since those solutions I found on the net (like removing or commenting out old_passwords=1 in my.cnf, setting old_passwords to 0 and setting new password then restart) didn't work for me.

Another Update: Commonly, the solution for this problem would be setting old_passwords off in mysql but then, as mentioned in the PHP documentation,

the new mysqlnd library does not read mysql configuration files (my.cnf/my.ini).

So does this mean that setting old_passwords to in 0 in my.cnf has no use? I still haven't figure out solution to this problem.

Do we need an upgrade in any of the platforms used? Thanks in advance for the help.

Answer 1

"SET PASSWORD" is deprecated in MySQL server 5.7.6.

The syntax to be followed for servers below 5.7.6 is,

SET PASSWORD [FOR user] = password_option
password_option: {
    PASSWORD('auth_string')
  | OLD_PASSWORD('auth_string')
  | 'hash_string'
}

The syntax to be followed for servers 5.7.6 and above is,

SET PASSWORD [FOR user] = password_option
password_option: {
    PASSWORD('auth_string')
  | 'auth_string'
}

Also refer-> http://dev.mysql.com/doc/refman/5.7/en/set-password.html

Answer 2

Please try this solution

http://laravel.io/forum/04-16-2014-solving-mysqlnd-cannot-connect-to-mysql-41

As per the solution you just need to set the password again and specify that it be saved in the new format.

mysql> SET @@session.old_passwords = 0;
Query OK, 0 rows affected (0.00 sec)
SET PASSWORD FOR 'existinguser'@'localhost' = PASSWORD('existingpass');
Query OK, 1 rows affected (0.00 sec);