Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled)
I have a problem when trying to connect to MySQL database using Windows OBDC driver. There are plenty of search hits regarding the obvious... people are using old versions, however, I'm not.
mysqld is on CentOS 6.4 32bit
./usr/libexec/mysqld Ver 5.1.69 for redhat-linux-gnu on i386 (Source distribution)
So I'm at a loss to understand where any pre 4.1.1 protocol is coming from. Any ideas?
Answer
I guess that if you ask the right question its easier to find the answer.
In this case "my" problem relates to how the passwords are hashed and stored in the database. Legacy passwords were stored with a shorter hash that's now deprecated.
A few important points:
mysql_upgrade cannot and does not upgrade passwords, nor does it warn about it in some versions, see: http://bugs.mysql.com/bug.php?id=65461.
Even it you have mostly the latest server and clients, all it takes is one legacy client somewhere to create a legacy password and then you'll have trouble with that account no matter what client tries to use it.
Different versions have treated the situation differently so you can be sitting on some legacy passwords in your database and then suddenly, for no apparent reason, some accounts stop working... this is because of how different versions chose to handle the situation.
You cannot upgrade passwords. You must know what they are and you must change them.
EDIT: To be more clear, you must change the password that is stored with the shorter hash using a new client that uses longer hashes. By doing so you will be writing that accounts password with the longer hash, at which point nothing should be flagging attempts to access the account any more. If the problem is recurrent you should be looking for the older clients at your site which are still writing passwords with the deprecated hash length.