mysqli doesn't like old passwords

By John Swindells 13 Mar 2013 | Comment
| Share:

mysqli, the recommended PHP interface to mysql, will not authenticate with mysql servers using old (16-bit hash) passwords. This is the message you see:

PHP Warning: mysqli::mysqli(): (HY000/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

Here's the solution, as long as you have MySQL DB admin rights:

  1. Check which passwords are old-style (16-bit); 41-bit passwords don't need changing:
    SELECT `User`, `Host`, Length(`Password`) FROM mysql.user;
  2. Edit /etc/my.cnf: remove old_passwords=1
  3. Run this command: service mysqld restart
  4. Run these mysql commands:
    SET PASSWORD FOR 'the_user'@'the_host'=PASSWORD('the_password');
    FLUSH PRIVILEGES;
    (Obviously, replace the_user, the_host the_password with your own values
  5. Check that the passwords you changed are now 41-bit:
    SELECT `User`, `Host`, Length(`Password`) FROM mysql.user;

Caveat emptor: What you are doing here runs the risk of denying access to your database, to the point of not being able to access the mysql at all. What I've explained above did the job for me, but don't blame me if it doesn't work for you! If you have the option, do a test run on a non-production server first.

Share:

Comments

It's quiet in here...Add your comment