There are some constraints when giving passwords for mysql databases when using with drupal 6. Some special characters will make the site unavailable with the error "Access denied for user 'user'@'localhost' (using password: YES)" even if the password is correct in Drupal 6.
This issue may also be caused by some other reasons too, however the following problem is a major one causing the issue.
All the problems started after changing the MySQL user password associated with the mysql database.After that the drupal 6 site is unavailable showing the error "Access denied for user 'user'@'localhost' (using password: YES)" and it is clear that the password provided is correct.
After changing the password, i have explicitly changed the password in the settings.php file.By analysing it is found that some special characters in the password may be the culprit. By replacing the special charecters with some others found the actual culprit. It is the "+" sign in the password that is making the problem.
The actual problem is with the way drupal 6 parses the db url which is in the format "mysql://username:password@localhost/databasename". Drupal 6 uses the default php function parse_url(). The problem with parse url is that any of the special characters :, /, @, +, (, ), ?, =, and & will result in an unexpected behaviour of the function, which is causing the error even if the password was correct.
Inorder to avoid the problem we can use URI HEX encodings corresponding to each of these special characters.
- : = %3a
- / = %2f
- @ = %40
- + = %2b
- ( = %28
- ) = %29
- ? = %3f
- = = %3d
- & = %26
However the problem is not there in Drupal 7, since in Drupal 7 we can not use the db URL syntax in settings.php file, in fact we have to give the result of the parse_url() function, i.e as an array. The default.settings.php in Drupal 7 gives the general format.
array( 'driver' => 'mysql', 'database' => 'databasename', 'username' => 'username', 'password' => 'password', 'host' => 'localhost', 'port' => 3306, 'prefix' => 'myprefix_', 'collation' => 'utf8_general_ci', );
As you can see here the credentials and other details have given directly in the array like structure, so even if the password contains any of these special characters it will not create any problems. So in this case the problem with the parse_url() function will not occur. The solution is to use the URI HEX encoding corresponding to the special characters explained above.