Cryptic MySQL error

  • 2 min read

    • Today as I was attempting to test one of my PHP applications, I received this error after attempting to connect to a MySQL database:

    Warning:  mysql_connect() [function.mysql-connect]: OK packet 6 bytes shorter
    than expected in index.php on line 29

    Warning:  mysql_connect() [function.mysql-connect]: mysqlnd cannot connect to
    MySQL 4.1+ using old authentication in index.php on line 29

    The script giving the error was running on OS X 10.6.4 with the stock PHP 5.3.1. After doing a bit of searching and reading the MySQL documentation on the old password format, I was a bit confused because I ran this on the server:

    [user@host ~]# rpm -q mysql mysql-server
    mysql-5.0.77-4.el5_5.3
    mysql-server-5.0.77-4.el5_5.3

    Both the server and client should support the new authentication version, which was introduced all the way back in MySQL 4.1. So why wouldn't it connect?

    It turns out that CentOS 5 disables the new password hashes by default in favour of remaining compatible with 3.x (and earlier) MySQL clients. All you have to do is edit /etc/my.cnf and comment the old_passwords=1 line. After restarting the server, you should notice that running SELECT PASSWORD('foobar'); in a MySQL prompt will return 41-character hashes, not the old-style 16 character hashes. Reset the user passwords to start using the new hashes and you'll be good to go.