Rant of the week: MySQL Old Passwords

Description

Time for the rant of the week and this week it relates to the setup of this very blog. It was far from WordPress’s famous “5-minute setup” and here’s why:

Connect Error (2000) mysqlnd cannot connect to MySQL 4.1+ using old authentication

The full error message can be found below. This error message occurred when setting up the latest version of WordPress using MySQL, PHP and phpMyAdmin.

As with any technical problem, my immediate instinct was to Google this (after having read the error message of course). Sadly, I had no idea how to execute the suggested command and being part of shared hosting made that no easier. After some time Googling, the issue was clearly unresolved and had no clear answer. This page has a few more details about MySQL password hashing. So, here goes at my attempt.

Explanation

Simply put, this is a version compatibility issue achat viagra ligne. Version X of PHP doesn’t like working with Version Y of MySQL. But there is a solution and it IS what is written in the error message. It’s just fiddly to get it to work. Lots of online comments talk about the “old_passwords” variable being set. In fact, this is largely irrelevant. So here’s how to solve the problem.

Solution

You do may need to switch off the old_passwords variable (this only affects the current session so you don’t need super-user privileges) using:

SET SESSION old_passwords=0;

I was using phpMyAdmin (as that’s what my host provides). So, log in to phpMyAdmin, and ignore the “Change password” link – it won’t work. It doesn’t matter how many times you select to target version 4.1+ of MySQL, it still won’t actually use the updated hashing algorithm. The solution is to by-pass the phpMyAdmin logic entirely. From the home panel of phpMyAdmin (not within a database) open the SQL panel (link at top of the page).And then issue the command within the error message:

SET PASSWORD = PASSWORD(‘your_existing_password’);

The phpMyAdmin change password link actually issues the command with PASSWORD = OLD_PASSWORD(…) and hence the issue.

Full Error Message

Database connection fialed: 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