Drupal/MySQL Authentication Error

Yesterday I noticed that a number of Drupal websites I look after were down, and it was a pretty interesting error message:

MYSQL ERROR 2049 (HY000): Connection using old (pre-4.1.1) authentication protocol ref used (client option 'secure_auth' enabled)

Like most error messages it does give a tantalising hint about the problem, and unsurprisingly other people have had similar issues as StackOverflow explains. But it all looks somewhat annoying, especially when running on a hosted service (honestly, I have other things to do these days).

I was about to rue the day, and not for the first time, when I decided to move from plain HTML/CSS/Javascript sites to something with a database-driven system (Drupal, WordPress, etc). But fortunately, there is a stunningly easy fix; simply change the password. Specifically, change the password of the MySQL user that accesses the database that is being used by Drupal, and the authentication problem will solve itself. Actually this is probably timely because it's a good indication that (ahem) the password hadn't been changed "for quite a while".

The following are command-line options, but it's equally simple enough if one is using CPanel or similar.

1. Change password for database user. Get on MySQL the usual manner (e.g., mysql -u root -h localhost -p) and run
ALTER USER '$username'@'localhost' IDENTIFIED BY '$newpassword';

2. Change permissions on the $drupalroot/sites/default folder. The default permissions are set to 0555, set them to 0755 for editing.
chmod 0755 $drupalroot/sites/default

3. Change permissions for settings.php to 640 for editing
cd default; chmod 0640 settings.php

4. Change password for database user in settings.php ; this can be found in two locations, depending on the varsion of Drupal being used:

$db_url = 'mysql://username:password@localhost/databasename';


$databases = array (
'database' => '$database_name',
'username' => '$username',
'password' => '$newpassword',

5. Change permissions for settings.php and sites/default to read only
chmod 0440 settings.php; cd ..; chmod 0555 $drupalroot/sites/default

6. Reload website. Everything will be back to working order.