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.

Louralourdes

Possible a duplicate of #1576307 – Madeira

Did you execute FLUSH PRIVILEGES; ? – Madeira

Please refer to #1576307 – Volney

have you tried.. whilst in the db admin SET PASSWORD = PASSWORD('your_existing_password') ?? – Fritter

@Madeira I only have limited access, I don't have such privilege. – Louralourdes

@MichaelDibbets I already tried resetting the password couple of times yet still the same. And whenever I check the password length, it's always 16 even though I tried running SET SESSION old_passwords=FALSE; before setting another password and even modifying my.cnf as I've mentioned above. :( – Louralourdes

Can you do a SHOW GRANTS FOR 'user'@'your_hostmask_here'; ? – Cervical

Try SET PASSWORD = UPPER(SHA1(UNHEX(SHA1('yourpassword')))); THis is what the PASSWORD function does under the hood. – Halfcaste

@Cervical After running SHOW GRANTS FOR 'user'@'%'; here's the result: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES ON *.* TO 'user'@'%' – Louralourdes

@Halfcaste whenever I run SET PASSWORD = UPPER(SHA1(UNHEX(SHA1('yourpassword')))); it shows an error in the SQL syntax. – Louralourdes

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

Kyne

They are using MySQL 4.1.20 not MySQL 5.7.6 so it should work ;) – Echoechoic

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

Martinmartina

I tried this but it has still the same output.. SQLSTATE[HY000] [2000] mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication... – Louralourdes