I recently encountered this error trying to connect Achievo to an existing corporate project-tracking database:
Critical: Unknown error: 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).
To understand what’s going on, you need to understand a couple not-so-recent developments in PHP and MySQL:
- MySQL version 4.0 and earlier used a 16-byte password hash, but starting with version 4.1+ uses a more secure password algorithm and 41 byte hash (although still supporting the older less secure password hash if needed).
- The original external PHP module for accessing MySQL was mysql, but the current preferred module is mysqli (MySQL Improved).
- The original low-level c-library that mysql (and mysqli) used to actually interface to a MySQL server was libmysql (included with MySQL), but starting with PHP 5.3 they are typically compiled with mysqlnd (a seperate project). mysqlnd has many advantages compared to libmysql, but it doesn’t support the old 16-byte MySQL password hash.
The database server I needed to connect to was running MySQL v5.0, and was configured with “old_passwords” set to On, which sets password operations to use the older 16-byte password hash (possibly because the original client/server application had been developed with MySQL 4.0 or earlier).
The simplest solution would have been to turn old_passwords Off and reset my password as the error message said (assuming the password column in the mysql.usr table would hold a 41-byte hash, otherwise its width would have to be increased first). However, the dba was loath to change the server configuration in case it broke existing mission critical enterprise applications, so I needed a different solution.
Since mysql and mysqli are compiled seperately, they can use different underlying c-libraries. In other words, mysql can be compiled to use libmysql – which supports the old 16-byte password hash. That would allow using mysql to connect to the corporate project-tracking database, without any effect on using mysqli to connect to other databases.
On a FreeBSD server, it was as easy as:
# pkg_delete php5-mysql # cd /usr/ports/databases/php5-mysql # make config (uncheck support for mysqlnd) # make install clean
and then edited the Achievo config.inc.php file to use mysql for the corporate database instead of mysqli.
How do work old password db connection with php 5.3?
I use wamp
Assuming you are asking about PHP only (and not about MySQL server configuration), you should only need to use the “mysql” PHP module (instead of “mysqli”). However, I don’t know if the mysql module is included in WAMP, and if it is, whether it’s already enabled in php.ini.
i have 2 server in different php version (5.4 & 5.3)
5.3 should be no problem.
If i change this configure in mysql server is it 5.3 can not be connected?
If I understand you correctly, yes, you can have password problems when using current PHP versions with a MySQL server configured with “old_passwords”. My understanding is that PHP 5.3 will behave the same as PHP 5.4, because both mysqli and mysql will be compiled with mysqlnd and not libmysql. However, this *may* be platform specific (the blog post refers to an issue using a FreeBSD server, Windows and other Unix distributions may compile differently).