13

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.

Machavity
  • 30,841
  • 27
  • 92
  • 100
user123
  • 1,060
  • 3
  • 13
  • 29
  • Possible a duplicate of http://stackoverflow.com/questions/1575807/cannot-connect-to-mysql-4-1-using-old-authentication – Stafox Nov 11 '15 at 08:21
  • Did you execute `FLUSH PRIVILEGES;` ? – Stafox Nov 11 '15 at 08:23
  • Please refer to http://stackoverflow.com/questions/1575807/cannot-connect-to-mysql-4-1-using-old-authentication – Ali Zia Nov 11 '15 at 08:38
  • have you tried.. whilst in the db admin SET PASSWORD = PASSWORD('your_existing_password') ?? – Tschallacka Nov 11 '15 at 08:51
  • @Stafox I only have limited access, I don't have such privilege. – user123 Nov 11 '15 at 08:55
  • @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. :( – user123 Nov 11 '15 at 08:58
  • Can you do a `SHOW GRANTS FOR 'user'@'your_hostmask_here';` ? – Machavity Nov 11 '15 at 16:22
  • Try `SET PASSWORD = UPPER(SHA1(UNHEX(SHA1('yourpassword'))));` THis is what the PASSWORD function does under the hood. – Mihai Nov 11 '15 at 16:23
  • @Machavity 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'@'%' ` – user123 Nov 12 '15 at 00:50
  • @Mihai whenever I run `SET PASSWORD = UPPER(SHA1(UNHEX(SHA1('yourpassword'))));` it shows an error in the SQL syntax. – user123 Nov 12 '15 at 01:24

2 Answers2

4

"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

marklong
  • 191
  • 8
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);
Hari Swaminathan
  • 616
  • 1
  • 13
  • 27
  • 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...` – user123 Nov 11 '15 at 00:19