PHP 5.4 PDO could not connect to MySQL 4.1+ using the old insecure authentication

I know there are a tonne of similar questions, in fact I've read all (9) of them.

However, none of them solve my problem.

I have a shared-hosting package (the minimum). What's included in my package is the domain name, and a separate IP address where the MySQL server is hosted. For development, I'm using http://localhost/ with PHP 5.4's development server, and I'm using the MySQL server I get in my hosting package.

The problem arises only on my PC, because I have installed PHP 5.4, but my web host has installed PHP 5.2.17 and won't upgrade. The MySQL server is on MySQL 5.1.50.

Luckily, phpMyAdmin has a built-in "Change Password" feature.

There are two hashing options in phpMyAdmin for changing a password:

• MySQL 4.1+
• MySQL 4.0 compatible

I changed the password with the MySQL 4.1+ option, and it confirmed the update was successful.

The profile has been updated. SET PASSWORD = PASSWORD( '***' )

However, when I perform this query:

SELECT @@global.old_passwords, @@session.old_passwords, Length(PASSWORD('abc'));

It tells me the password length is still 16. Output:

1     1     16

And so the problem persists.

Could not connect to database. 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

I've also tried to do these queries, when logged in with the DBO user in phpMyAdmin:

SET SESSION old_passwords=0; 
[phpMyAdmin reloads to the home screen, but the value remains = 1]

SET GLOBAL old_passwords = 0;
#1227 - Access denied; you need the SUPER privilege for this operation

FLUSH PRIVILEGES;
#1227 - Access denied; you need the RELOAD privilege for this operation

This is contradictory to what is stated in the webhost's menu for setting the DBO user:

Database Owner
When you create a new database, you need to specify a Database Owner (DBO) User, which will have
full administrator access to the database.

Is this something I have to take up with my webhosts? Or can it be solved by my DBO user? Otherwise can this be bypassed in PHP? (since it works with PHP 5.2.17 but not PHP 5.4)

Solution 1:

SOLVED!

Although the SET SESSION old_passwords=0; wasn't working in phpMyAdmin.

I downloaded the MySQL GUI Tools and used the MySQL Query Browser to execute the same command on non-DBO user:

SET SESSION old_passwords = 0;

SELECT @@global.old_passwords, @@session.old_passwords, Length(PASSWORD('abc'));

now returned:

1      0      41

So I simply changed the password:

SET PASSWORD = PASSWORD('my_old_password')

And now PHP 5.4 PDO connects to the database with that user!

Solution 2:

In phpMyAdmin, setting the session variable will work, but the call to the password function must occur in the same session/execution.

For example, if I execute:

SET SESSION old_passwords = 0;
SET PASSWORD = PASSWORD('notagoodpassword');

It will correctly set it.