MySQL refusing to accept passwords

--------------090307080509080303020802
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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 bugs.mysql.com, 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

--------------090307080509080303020802--

Re: MySQL refusing to accept passwords

----- Original Message -----
> From: "Tim Thorburn"
>
> For those keeping score, this will be the second time in the past few

Yes, I remember that. You wouldn't happen to have a known-good backup around, to verify if the password has indeed changed in the authentication tables?

--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: MySQL refusing to accept passwords

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 bugs.mysql.com, 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.
http://dev.mysql.com/doc/refman/5.5/en/mysqlbinlog.html

* 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?
http://dev.mysql.com/doc/refman/5.5/en/resetting-permissions .html


--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: MySQL refusing to accept passwords

On 7/22/2011 17:02, Shawn Green (MySQL) wrote:
> ... quick correction ...
>
> * ...the account 'root' for a new installation is*
> created without a password. ...

I originally said 'is not'. Sorry for the confusion


--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: MySQL refusing to accept passwords

--0015176f131263514704a8b011af
Content-Type: text/plain; charset=ISO-8859-1

Hi,

I dont remember the details of the past 'experience' but,

did you try a simple:

mysql -uroot -p -h127.0.0.1 -P3306?

and also an anomymous login:
mysql

also make sure you remove the anonymous account if present, sometimes it
introduces strange behaviours as the one you described.

and as suggested already you should see if the grant tables were actually
changed.

Claudio


2011/7/22 Shawn Green (MySQL)

> On 7/22/2011 17:02, Shawn Green (MySQL) wrote:
>
>> ... quick correction ...
>>
>> * ...the account 'root' for a new installation is*
>> created without a password. ...
>>
>
> I originally said 'is not'. Sorry for the confusion
>
>
>
> --
> Shawn Green
> MySQL Principal Technical Support Engineer
> Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
> Office: Blountville, TN
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?**
> unsub=claudio.nanni@gmail.com
>
>


--
Claudio

--0015176f131263514704a8b011af--

Re: MySQL refusing to accept passwords

--------------040700020003080207070805
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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 bugs.mysql.com, 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.
> http://dev.mysql.com/doc/refman/5.5/en/mysqlbinlog.html
>
> * 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?
> http://dev.mysql.com/doc/refman/5.5/en/resetting-permissions .html
>
>
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.

Before this, I did try to simply reset the root password by once again
stopping the MySQL service, starting it with --skip-grant-tables;
however regardless of what I changed the password to, I received the
same error. This error was present for each login I had on the MySQL
server.

Unfortunately, now that I've uninstalled and deleted the previous MySQL
directories, it seems unlikely that I'll have access to the previous set
of logs. I do, however, have a complete backup of --all-databases from
mysqldump. Would there be anything in here that might shed some light
on the issue?

I'm at a loss here. The only "different" things I had done between when
MySQL was working and when it decided to no longer accept my passwords
was to launch Chrome instead of Firefox to load a specific Google page
and to allow Bonjour to update iTunes and Safari on this laptop. Though
I can't see how either of those would have any impact on MySQL.

Thanks again,
-Tim Thorburn

--------------040700020003080207070805--

rebuilding table

Hi,

I have 2 tables and need to create a 3rd one as follows:

Table Sales:
Code Price sQty sDate
123 12.00 2 2011-03-12
190 10.00 1 2011-03-14
123 13.00 2 2011-03-29
123 13.00 3 2011-03-31
190 11.00 2 2011-03-31


Table Costs
Code Cost hDate
123 8.00 2011-03-01
123 9.50 2011-03-09
190 7.00 2011-03-09
123 9.00 2011-03-13
190 8.00 2011-03-13
190 9.00 2011-03-30
123 9.50 2011-03-30


I want to have the results for Table Profit as follows:

Table Profit
Code Cost Sales Profit
123 65.50 159.50 94.00
190 26.00 44.00 18.00

the cost should be a sum of sQty * cost for each item using the cost which
is prevailing at sDate, that is where hDate<=sDate
the sales is the sum of sQty * Price for each item Code
profit is the difference between cost and sales

Can anyone help me to have 1 query to build up this 3rd table (Profit)?

Thanks.

Regards,


Velen




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: rebuilding table

--0016e649895851430e04a8c00b4d
Content-Type: text/plain; charset=ISO-8859-1

Look into select into or just insert. The mysql doc covering this might even
have a suitable example.
On Jul 23, 2011 8:42 AM, "Velen Vydelingum" wrote:
> Hi,
>
> I have 2 tables and need to create a 3rd one as follows:
>
> Table Sales:
> Code Price sQty sDate
> 123 12.00 2 2011-03-12
> 190 10.00 1 2011-03-14
> 123 13.00 2 2011-03-29
> 123 13.00 3 2011-03-31
> 190 11.00 2 2011-03-31
>
>
> Table Costs
> Code Cost hDate
> 123 8.00 2011-03-01
> 123 9.50 2011-03-09
> 190 7.00 2011-03-09
> 123 9.00 2011-03-13
> 190 8.00 2011-03-13
> 190 9.00 2011-03-30
> 123 9.50 2011-03-30
>
>
> I want to have the results for Table Profit as follows:
>
> Table Profit
> Code Cost Sales Profit
> 123 65.50 159.50 94.00
> 190 26.00 44.00 18.00
>
> the cost should be a sum of sQty * cost for each item using the cost which

> is prevailing at sDate, that is where hDate<=sDate
> the sales is the sum of sQty * Price for each item Code
> profit is the difference between cost and sales
>
> Can anyone help me to have 1 query to build up this 3rd table (Profit)?
>
> Thanks.
>
> Regards,
>
>
> Velen
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=ag4ve.us@gmail.com
>

--0016e649895851430e04a8c00b4d--

Re: MySQL refusing to accept passwords

----- Original Message -----
> From: "Tim Thorburn"
>
> Before this, I did try to simply reset the root password by once
> again stopping the MySQL service, starting it with --skip-grant-tables;
> however regardless of what I changed the password to, I received the
> same error. This error was present for each login I had on the MySQL
> server.

Hmmmmm. That sounds like there's a record in the mysql.user table that gets precedence, somehow... Normally, if multiple entries match a user/host combination, the most specific one gets used, which makes it kind of unlikely; but would you mind posting the full set of user/host combinations in mysql.user (don't need the password hashes, of course) and a few of the errors you get (those show the exact user@host the server tries to match) ?

It's a bit of a long shot, but anything is worth checking out at this point.


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: MySQL refusing to accept passwords

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 bugs.mysql.com, 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.
>> http://dev.mysql.com/doc/refman/5.5/en/mysqlbinlog.html
>>
>> * 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?
>> http://dev.mysql.com/doc/refman/5.5/en/resetting-permissions .html
>>
>>
> 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 General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: MySQL refusing to accept passwords

"Shawn Green (MySQL)" pisze:
(...)
> 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.
>

It could be the case you got your account setup as username@localhost and it works fine, as long as you're using 'localhost' in your Workench connection window.
Once changed to an IP address or a host name and from MySQL server perspective - as Shawn said - you're a different user.

Regards,
m

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org