List:General Discussion« Previous MessageNext Message »
From:Shawn Green (MySQL) Date:July 25 2011 2:56pm
Subject:Re: MySQL refusing to accept passwords
View as plain text  
On 7/22/2011 18:48, Tim Thorburn wrote:
> On 7/22/2011 5:02 PM, Shawn Green (MySQL) wrote:
>> On 7/21/2011 22:45, Tim Thorburn wrote:
>>> Hello,
>>> For those keeping score, this will be the second time in the past few
>>> months I've come upon this problem. To recap, this is happening on a
>>> development laptop running Win7 64-bit Ultimate and MySQL 5.5.13. This
>>> morning, all was working well. This evening, I launched MySQL Workbench
>>> 5.2.34 CE to work on a table. When I attempt to access the server from
>>> within Workbench, I'm now prompted with a window asking for my password.
>>> Of course, my password is not accepted when I enter it - I'm presented
>>> with error #2000.
>>> Just to confirm, mysql --version returns:
>>> mysql Ver 14.14 Distrib 5.5.13, for Win64 (x86)
>>> This is not an upgrade, after my last mishap, I once again formatted the
>>> laptop with a fresh install of Windows 7 Ultimate 64-bit as well as
>>> MySQL 5.5.13. I've begun digging through, but I'm not
>>> seeing any standing out. I should point out that this problem also
>>> breaks any sites running on this dev laptop as all passwords are no
>>> longer accepted.
>>> Any thoughts on what may be causing this? It seems to be happening every
>>> month or so at this point.
>>> Thanks in advance,
>>> -Tim Thorburn
>> * Check your binary logs, someone may actually be changing your
>> passwords.
>> * Be careful with what you backup/restore. You may accidentally revert
>> your tables to a condition before you set the password.
>> * mysql.exe is the command-line client. While it would be unusual to
>> have a client utility that is of a different version than your server,
>> the actual command to determine the version of the MySQL database
>> server would be
>> mysqld --version
>> * did you attempt to login using mysql to see if the passwords really
>> were different? Remember, the account 'root' for a new installation is
>> not created without a password. If you had restored a very old copy of
>> that table, that might have been your situation.
>> * are you aware of the "lost password reset" instructions in the manual?
> Hi Shawn,
> Thanks for the reply. As this has happened before, and because I'm on a
> deadline, I ended up stopping the MySQL service with NET STOP MYSQL,
> then started MySQL with the skip-grant-tables option so that I could log
> in as root and make a backup via mysqldump. After this, I uninstalled
> MySQL from Control Panel, then proceeded to delete C:\Program
> Files\MySQL and C:\ProgramData\MySQL directories before rebooting to do
> a clean install of MySQL 5.5.14.
> ...

Your approach was very heavy-handed. To me what you describe can be 
compared to building a whole new car just because you broke your key off 
in the lock.

Next time, query the `mysql`.`user` table and look at which users exist 
and what their password hashes are. To gain access you may still need to 
bypass the locks by using --skip-grant-tables. After you find (or don't 
find) the accounts you want to use, check their passwords. If you know 
the plaintext password of an account, compare its hash to the one on the 
table -

SELECT PASSWORD('passwordgoeshere');

If the two match then username/password may not be the problem. It may 
be the machine from which you are attempting to login. MySQL 
authentication requires three parts to align to provide access: user 
name, password, and a host value (or pattern).  You can be the correct 
user, using the correct password but you may not be allowed (by the host 
pattern) to login from the machine from which you are attempting to login.

Please do audit your old tables and see what you can discover.
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
MySQL refusing to accept passwordsTim Thorburn22 Jul
  • Re: MySQL refusing to accept passwordsJohan De Meersman22 Jul
  • Re: MySQL refusing to accept passwordsMySQL)22 Jul
    • Re: MySQL refusing to accept passwordsMySQL)22 Jul
      • Re: MySQL refusing to accept passwordsClaudio Nanni23 Jul
    • Re: MySQL refusing to accept passwordsTim Thorburn23 Jul
      • Re: MySQL refusing to accept passwordsJohan De Meersman23 Jul
      • Re: MySQL refusing to accept passwordsMySQL)25 Jul
        • Re: MySQL refusing to accept passwordsmisiaq26 Jul
  • rebuilding tableVelen Vydelingum23 Jul
    • Re: rebuilding tableshawn wilson23 Jul