I have mysql 4.1.12 installed on SX 10.4, and have run into the
curious problem that mysql forgets my user password (but not my root
password) when I restart the server. When I attempt to log in, I get:
ERRR 1045 (28000): Access denied for user 'chris'@'localhost' (using
password: YES)
Yet, when I go in as root and re-grant permissions with the user
password, access is restored. When I reboot, I get the error again.
What could possibly be causing this?
Thanks,
Chris Fonnesbeck

Chris Fonnesbeck wrote:
I have mysql 4.1.12 installed on SX 10.4, and have run into the
curious problem that mysql forgets my user password (but not my root
password) when I restart the server. When I attempt to log in, I get:

ERRR 1045 (28000): Access denied for user 'chris'@'localhost' (using
password: YES)

Yet, when I go in as root and re-grant permissions with the user
password, access is restored. When I reboot, I get the error again.
What could possibly be causing this?

Thanks,
Chris Fonnesbeck

Sorry again forgot the mailing group

"Did you 'flush privileges'?"

Danny Stolle
Netherlands

Danny Stolle wrote:

Chris Fonnesbeck wrote:

>I have mysql 4.1.12 installed on SX 10.4, and have run into the
>curious problem that mysql forgets my user password (but not my root
>password) when I restart the server. When I attempt to log in, I get:
>>
>ERRR 1045 (28000): Access denied for user 'chris'@'localhost' (using
>password: YES)
>>
>Yet, when I go in as root and re-grant permissions with the user
>password, access is restored. When I reboot, I get the error again.
>What could possibly be causing this?
>>
>Thanks,
>Chris Fonnesbeck
>>

Sorry again forgot the mailing group

"Did you 'flush privileges'?"

Danny Stolle
Netherlands

Danny,
First, FLUSH PRIVILEGES is not needed with GRANT. Second, if he were editing
the user table instead of using GRANT and failing to FLUSH PRIVILEGES, he
would get the opposite behavior -- the login would not work before the
restart, but would work after.

Chris,
possibility is a startup script which is altering the user table. Another
possibility is some error in granting permissions or restarting the server, or
logging in. It is difficult to say without knowing more. Please show us

* the GRANT command you use to create 'chris'@'localhost' (but don't show us
the real password)
* the output of "SHW GRANTS FR 'chris'@'localhost'" when it is working
(before a restart).
* the method you use to restart the server
* the output of "SHW GRANTS FR 'chris'@'localhost'" when it isn't working
(after the restart).

Michael

Hello.

What does

show grants for 'chris'@'localhost';

reports when you're logged as root?

Chris Fonnesbeck <fonnesbeck (AT) gmail (DOT) comwrote:
I have mysql 4.1.12 installed on SX 10.4, and have run into the
curious problem that mysql forgets my user password (but not my root
password) when I restart the server. When I attempt to log in, I get:

ERRR 1045 (28000): Access denied for user 'chris'@'localhost' (using
password: YES)

Yet, when I go in as root and re-grant permissions with the user
password, access is restored. When I reboot, I get the error again.
What could possibly be causing this?

Thanks,
Chris Fonnesbeck

7/14/05, Gleb Paharenko <gleb.paharenko (AT) ensita (DOT) netwrote:
Hello.

What does

show grants for 'chris'@'localhost';

reports when you're logged as root?

I get the following:

| GRANT ALL PRIVILEGES N T 'chris'@'localhost' IDENTIFIED BY
PASSWRD '3446cb892d3dffdd' WITH GRANT PTIN |

Tried that. I get the following:

:~/Research/Right Whale chris$ mysql mysql -u root -p
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12 to server version: 4.1.12-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysqlflush privileges;
Query K, 0 rows affected (0.11 sec)

mysqlBye
:~/Research/Right Whale chris$ mysql mysql -p
Enter password:
ERRR 1045 (28000): Access denied for user 'chris'@'localhost' (using
password: YES)

7/14/05, Danny Stolle <d.stolle (AT) chello (DOT) nlwrote:
Chris Fonnesbeck wrote:
I have mysql 4.1.12 installed on SX 10.4, and have run into the
curious problem that mysql forgets my user password (but not my root
password) when I restart the server. When I attempt to log in, I get:

ERRR 1045 (28000): Access denied for user 'chris'@'localhost' (using
password: YES)

Yet, when I go in as root and re-grant permissions with the user
password, access is restored. When I reboot, I get the error again.
What could possibly be causing this?

Thanks,
Chris Fonnesbeck

Sorry again forgot the mailing group

"Did you 'flush privileges'?"

Danny Stolle
Netherlands

Michael Stassen wrote:
Danny Stolle wrote:

>Chris Fonnesbeck wrote:
>>
I have mysql 4.1.12 installed on SX 10.4, and have run into the
curious problem that mysql forgets my user password (but not my root
password) when I restart the server. When I attempt to log in, I get:

ERRR 1045 (28000): Access denied for user 'chris'@'localhost' (using
password: YES)

Yet, when I go in as root and re-grant permissions with the user
password, access is restored. When I reboot, I get the error again.
What could possibly be causing this?

Thanks,
Chris Fonnesbeck

>>
>Sorry again forgot the mailing group
>>
>"Did you 'flush privileges'?"
>>
>Danny Stolle
>Netherlands

Danny,
First, FLUSH PRIVILEGES is not needed with GRANT. Second, if he were
editing the user table instead of using GRANT and failing to FLUSH
PRIVILEGES, he would get the opposite behavior -- the login would not
work before the restart, but would work after.

Chris,
possibility is a startup script which is altering the user table.
Another possibility is some error in granting permissions or restarting
the server, or logging in. It is difficult to say without knowing
more. Please show us

* the GRANT command you use to create 'chris'@'localhost' (but don't
show us
the real password)
* the output of "SHW GRANTS FR 'chris'@'localhost'" when it is working
(before a restart).
* the method you use to restart the server
* the output of "SHW GRANTS FR 'chris'@'localhost'" when it isn't working
(after the restart).

Michael

Aah I get the picture, thanx Michael.

Danny

Here is the startup script:

#!/bin/sh
#
# /
#
# A script to automatically start up MySQL on system bootup
# for Mac S X. This is actually just a wrapper script around
# the standard mysql.server init script, which is included in
# the binary distribution.
#
# (c) 2003 MySQL AB
# Written by Lenz Grimmer <lenz (AT) mysql (DOT) com>
#

# Suppress the annoying "$1: unbound variable" error when no option
# was given
if [ -z $1 ] ; then
echo "Usage: $0 [start|stop|restart] "
exit 1
fi

# Source the common setup functions for startup scripts
test -r /etc/rc.common || exit 1
/etc/rc.common

# The path to the mysql.server init script. The official MySQL
# Mac S X packages are being installed into /usr/local/mysql.
SCRIPT="/"

StartService ()
{
if [ "${MYSQLCM:=-N}" = "-YES-" ] ; then
ConsoleMessage "Starting MySQL database server"
$SCRIPT start /dev/null 2>&1
fi
}

StopService ()
{
ConsoleMessage "Stopping MySQL database server"
$SCRIPT stop /dev/null 2>&1
}

RestartService ()
{
ConsoleMessage "Restarting MySQL database server"
$SCRIPT restart /dev/null 2>&1
}

if test -x $SCRIPT ; then
RunService "$1"
else
ConsoleMessage "Could not find MySQL startup script!"
fi

The grant command was:

grant all on to chris@localhost identified by 'my_password';

Thanks for the help,
C.

7/14/05, Danny Stolle <d.stolle (AT) chello (DOT) nlwrote:
Michael Stassen wrote:
Danny Stolle wrote:
>
>Chris Fonnesbeck wrote:
>>
I have mysql 4.1.12 installed on SX 10.4, and have run into the
curious problem that mysql forgets my user password (but not my root
password) when I restart the server. When I attempt to log in, I get:

ERRR 1045 (28000): Access denied for user 'chris'@'localhost' (using
password: YES)

Yet, when I go in as root and re-grant permissions with the user
password, access is restored. When I reboot, I get the error again.
What could possibly be causing this?

Thanks,
Chris Fonnesbeck

>>
>Sorry again forgot the mailing group
>>
>"Did you 'flush privileges'?"
>>
>Danny Stolle
>Netherlands
>
>
Danny,
First, FLUSH PRIVILEGES is not needed with GRANT. Second, if he were
editing the user table instead of using GRANT and failing to FLUSH
PRIVILEGES, he would get the opposite behavior -- the login would not
work before the restart, but would work after.

Chris,
possibility is a startup script which is altering the user table.
Another possibility is some error in granting permissions or restarting
the server, or logging in. It is difficult to say without knowing
more. Please show us

* the GRANT command you use to create 'chris'@'localhost' (but don't
show us
the real password)
* the output of "SHW GRANTS FR 'chris'@'localhost'" when it is working
(before a restart).
* the method you use to restart the server
* the output of "SHW GRANTS FR 'chris'@'localhost'" when it isn't working
(after the restart).

Michael
--

Aah I get the picture, thanx Michael.

Danny

Hello.

PASSWRD '3446cb892d3dffdd' WITH GRANT PTIN |

You're using passwords in old format. Is it possible that problem
somehow related to this. Are you connecting using mysql command line
client? What version it is?

Chris Fonnesbeck <fonnesbeck (AT) gmail (DOT) comwrote:
7/14/05, Gleb Paharenko <gleb.paharenko (AT) ensita (DOT) netwrote:
>Hello.
20
20
20
20
>What does
20
>show grants for 'chris'@'localhost';
20
20
>reports when you're logged as root?
20

I get the following:

| GRANT ALL PRIVILEGES N T 'chris'@'localhost' IDENTIFIED BY
PASSWRD '3446cb892d3dffdd' WITH GRANT PTIN |

7/14/05, Gleb Paharenko <gleb.paharenko (AT) ensita (DOT) netwrote:
Hello.

PASSWRD '3446cb892d3dffdd' WITH GRANT PTIN |

You're using passwords in old format. Is it possible that problem

somehow related to this. Are you connecting using mysql command line

client? What version it is?

I was trying from the command line and through python using MySQLdb.

It is version 4.1.12

Thanks for the help,
C.

Chris Fonnesbeck wrote:
Here is the startup script:
<standard script snipped>
The grant command was:

grant all on to chris@localhost identified by 'my_password';

Thanks for the help,
C.

Chris,

You only answered one of my questions. I'm hoping to narrow the problem by
determining whether the problem is on the server or in the client.
Unfortunately, you haven't given enough information to clear that up.

I'll try again.

1) Right after you've issued the GRANT, while you can log in as chris, what does

SHW GRANTS FR 'chris'@'localhost'

give you?

2) When you say "restart the server", what, exactly, do you mean? Are you
restarting mysqld, or the whole machine?

3) After restarting, when you cannot log in as chris, what does

SHW GRANTS FR 'chris'@'localhost'

give you then?

Michael

Sorry. While I am able to log in, I get the following:

| GRANT ALL PRIVILEGES N T 'chris'@'localhost' IDENTIFIED BY
PASSWRD '*' WITH GRANT PTIN
|

after a restart, I get

| GRANT ALL PRIVILEGES N T 'chris'@'localhost' IDENTIFIED BY
PASSWRD '3446cb892d3dffdd' WITH GRANT PTIN |

Does this mean the password has somehow changed?

C.

7/15/05, Michael Stassen <Michael.Stassen (AT) verizon (DOT) netwrote:
Chris Fonnesbeck wrote:
Here is the startup script:
<standard script snipped>
The grant command was:

grant all on to chris@localhost identified by 'my_password';

Thanks for the help,
C.

Chris,

You only answered one of my questions. I'm hoping to narrow the problem by
determining whether the problem is on the server or in the client.
Unfortunately, you haven't given enough information to clear that up.

I'll try again.

1) Right after you've issued the GRANT, while you can log in as chris, what does

SHW GRANTS FR 'chris'@'localhost'

give you?

2) When you say "restart the server", what, exactly, do you mean? Are you
restarting mysqld, or the whole machine?

3) After restarting, when you cannot log in as chris, what does

SHW GRANTS FR 'chris'@'localhost'

give you then?

Michael

Chris Fonnesbeck wrote:

Sorry. While I am able to log in, I get the following:

| GRANT ALL PRIVILEGES N T 'chris'@'localhost' IDENTIFIED BY
PASSWRD '*' WITH GRANT PTIN
|

This is a new, 4.1+, 41 byte password hash.

after a restart, I get

| GRANT ALL PRIVILEGES N T 'chris'@'localhost' IDENTIFIED BY
PASSWRD '3446cb892d3dffdd' WITH GRANT PTIN |

This is an old, pre 4.1, 16 byte password hash.

Does this mean the password has somehow changed?

Yes. The question is, how? Something is not just changing the password, it
is changing the way it is stored.

You said you have 4.1.12 on S X 10.4. Was this an upgrade from a previous,
4.0.x version? Is it possible you have a script that fires at startup which
is copying your old mysql db over your new one? Perhaps something left over
from the migration process which you forgot to turn off?

Here's a test. Create a new user:

GRANT ALL N test.* T joe@localhost IDENTIFIED BY PASSWRD 'testing';

Verify this worked with

SHW GRANTS FR joe@localhost;

then restart the way you usually do. Log into mysql as root and

SHW GRANTS FR joe@localhost;

I'm betting joe@localhost will have disappeared completely. Either way, we'll
know more.

C.

Michael

Issuing the grant command yields an error:

mysqlGRANT ALL N test.* T joe@localhost IDENTIFIED BY PASSWRD 'testing';
ERRR 1105 (HY000): Password hash should be a 41-digit hexadecimal number

Also, I tried deleting and re-creating the 'chris' user, and the same
problems arose. However, I just used the IDENTIFIED BY 'foobar'
syntax.

Thanks,
C.

7/16/05, Michael Stassen <Michael.Stassen (AT) verizon (DOT) netwrote:
Chris Fonnesbeck wrote:

Sorry. While I am able to log in, I get the following:

| GRANT ALL PRIVILEGES N T 'chris'@'localhost' IDENTIFIED BY
PASSWRD '*' WITH GRANT PTIN
|

This is a new, 4.1+, 41 byte password hash.

after a restart, I get

| GRANT ALL PRIVILEGES N T 'chris'@'localhost' IDENTIFIED BY
PASSWRD '3446cb892d3dffdd' WITH GRANT PTIN |

This is an old, pre 4.1, 16 byte password hash.

Does this mean the password has somehow changed?

Yes. The question is, how? Something is not just changing the password, it
is changing the way it is stored.

You said you have 4.1.12 on S X 10.4. Was this an upgrade from a previous,
4.0.x version? Is it possible you have a script that fires at startup which
is copying your old mysql db over your new one? Perhaps something left over
from the migration process which you forgot to turn off?

Here's a test. Create a new user:

GRANT ALL N test.* T joe@localhost IDENTIFIED BY PASSWRD 'testing';

Verify this worked with

SHW GRANTS FR joe@localhost;

then restart the way you usually do. Log into mysql as root and

SHW GRANTS FR joe@localhost;

I'm betting joe@localhost will have disappeared completely. Either way, we'll
know more.

C.

Michael

Chris Fonnesbeck wrote:

Issuing the grant command yields an error:

mysqlGRANT ALL N test.* T joe@localhost IDENTIFIED BY PASSWRD 'testing';
ERRR 1105 (HY000): Password hash should be a 41-digit hexadecimal number

That's my fault. That should have been

GRANT ALL N test.* T joe@localhost IDENTIFIED BY 'testing';

Don't know why I threw "PASSWRD" in there. Sorry.

Also, I tried deleting and re-creating the 'chris' user, and the same
problems arose. However, I just used the IDENTIFIED BY 'foobar'
syntax.

Are you saying that

GRANT ALL N T chris@localhost IDENTIFIED BY 'foobar';

gave the same error? did it give a different error? If so, what error did
it give?

Thanks,
C.

Michael

It's always a good idea to keep the thread on the list. That way, more people
can help solve the problem, and others may benefit from the answers.

Chris Fonnesbeck wrote:
Here is what happens:

mysqlGRANT ALL N test.* T joe@localhost IDENTIFIED BY 'testing';
Query K, 0 rows affected (0.09 sec)

mysqlBye
:~ chris$ mysql mysql -u joe -p
Enter password:
ERRR 1045 (28000): Access denied for user 'joe'@'localhost' (using
password: YES)

That doesn't tell us anything. You only granted joe access to the test db, so
he cannot access the mysql db. Hence, this error is expected. In any case,
logging in is not the test.

I need you to do all the steps I asked, in order, to help us narrow this down.
Could you:

1) Log in as root and

SHW GRANTS FR joe@localhost;

and send the output.

2) Restart the server in your usual way (you still haven't specified what that
is).

3) Log in as root once more, enter

SHW GRANTS FR joe@localhost;

and send that output.

If my suspicion is correct, joe won't exist after the restart. Even if I'm
wrong, however, that will tell us something. I can't make any progress,
though, until you do all thre steps in order and report the results.

Michael

Please keep this on the list.

Chris Fonnesbeck wrote:
Something seems rather screwed up; I was starting from scratch by
revoking all privileges from joe and deleting him. I got the
following:

mysqlREVKE ALL PRIVILEGES N FRM 'joe'@'localhost';
Query K, 0 rows affected (0.00 sec)

mysqlSHW GRANTS FR joe@localhost;
++
| Grants for joe@localhost
|
++
| GRANT USAGE N T 'joe'@'localhost' IDENTIFIED BY PASSWRD
'*' |
++
1 row in set (0.00 sec)

Why would he still have privileges at this point?

He doesn't. USAGE means no privileges. It's counterintuitive, I know.
REVKE removes privileges, it doesn't drop users. Use

DRP USER 'joe'@'localhost';

to get rid of him. <>

Starting with another user, I ran the test that you suggested:

mysqlGRANT ALL N T jane@localhost IDENTIFIED BY 'testing';
Query K, 0 rows affected (0.00 sec)

mysqlSHW GRANTS FR jane@localhost;
++
| Grants for jane@localhost
|
++
| GRANT ALL PRIVILEGES N T 'jane'@'localhost' IDENTIFIED BY
PASSWRD '*' |
++
1 row in set (0.00 sec)

Then,

:~ chris$ sudo mysqladmin -p shutdown
Password:
Enter password:
:~ chris$ Starting mysqld daemon with databases from /usr/local/mysql/data

:~ chris$ mysql mysql -u root -p
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.12-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysqlSHW GRANTS FR jane@localhost;
++
| Grants for jane@localhost
|
++
| GRANT ALL PRIVILEGES N T 'jane'@'localhost' IDENTIFIED BY
PASSWRD '3ac57754462b6d4c' |
++
1 row in set (0.00 sec)

So, Jane still exists.

Hmmm In that case, I have more questions. Log in as root and run

SHW VARIABLES LIKE '%pass%';
DESC user password;

and post the results.

Also, was this a brand new installation, or an upgrade? If an upgrade, from
what version?

Thanks,
C.

Michael

7/18/05, Michael Stassen <Michael.Stassen (AT) verizon (DOT) netwrote:

Hmmm In that case, I have more questions. Log in as root and run

SHW VARIABLES LIKE '%pass%';
DESC user password;

and post the results.

Also, was this a brand new installation, or an upgrade? If an upgrade, from
what version?

This is a brand new installation, following a clean install of my S.
Here are the commands you requested:

mysqlSHW VARIABLES LIKE '%pass%';

| Variable_name | Value |

| old_passwords | FF |

1 row in set (0.03 sec)

mysqlDESC user password;

| Field | Type | Null | Key | Default | Extra |

| Password | varchar(16) | | | | |

1 row in set (0.00 sec)

Chris Fonnesbeck wrote:

7/18/05, Michael Stassen <Michael.Stassen (AT) verizon (DOT) netwrote:


>>Hmmm In that case, I have more questions. Log in as root and run
>>
>SHW VARIABLES LIKE '%pass%';
>DESC user password;
>>
>>and post the results.
>>
>>Also, was this a brand new installation, or an upgrade? If an upgrade, from
>>what version?

This is a brand new installation, following a clean install of my S.

Sorry, my question was imprecise. I meant to ask if you were using an earlier
version before this install. I expect the answer is yes, and you are using
the new 4.1 server with the old data.

Here are the commands you requested:

mysqlSHW VARIABLES LIKE '%pass%';

| Variable_name | Value |

| old_passwords | FF |

1 row in set (0.03 sec)

mysqlDESC user password;

| Field | Type | Null | Key | Default | Extra |

| Password | varchar(16) | | | | |

1 row in set (0.00 sec)

Well, there's the problem. The Password column needs to be a varchar(41) to
hold the new 41 character password hash used by 4.1. Your Password column is
only a varchar(16), which is the old size. I'm guessing you copied in your
old data, but haven't run the mysql_fix_privilege_tables script. That should
update your Password column to fix this (among other things).

Still, the behavior you describe is not what the manual says will happen
<>. It says, "If the
column has not been updated and still has the pre-4.1 width of 16 bytes, the
server notices that long hashes cannot fit into it and generates only short
hashes when a client performs password-changing operations using PASSWRD(),
GRANT, or SET PASSWRD. This is the behavior that occurs if you have
upgraded to 4.1 but have not yet run the mysql_fix_privilege_tables script to
widen the Password column." You may have stumbled onto a bug, in which case
you should report it <http://bugs.mysql.com/>.

Michael