MySQL 4.1 upwards uses an authentication protocol based on a password hashing algorithm that is incompatible with that used by older clients, including PHP 4 (and thus current versions of Xoops). If you upgrade your MySQL Server to 4.1, attempts to connect to it with an older PHP client may fail with the above error message.
To solve this problem, you should ideally tell MySQL to use it's old 16-bit plain text password protection rather than it's new 48-bit MD5 hash value encryption. The easiest way to do that is firstly by opening a MySQL DOS shell and type the following at the mysql> prompt....
mysql> SET PASSWORD FOR
-> 'some_user'@'some_host' = OLD_PASSWORD('newpwd');
So for example, 'some user' could be 'root'@'localhost'. Substitute the password you want to use for 'newpwd' in the preceding examples. MySQL cannot tell you what the original password was, so you'll need to pick a new one.
The mysqli extension (stands for "MySQL, Improved"; new in PHP 5) is compatible with the improved password hashing employed in MySQL 4.1 and higher, and no special configuration of MySQL need be done in order to use this newer MySQL client library for PHP.
For more information see the MySQL web site : here