# [SOLVED] mysql issue

## corey_s

I'm working with an older version of mysql (4.1.14) on an older box.

Recently some scripts on the box started failing with access denied message when connecting to the db.

Here's the problem:

For some reason, even though a password is set for the db user - I cannot log into the db with the '-p' flag to mysql....   it only allows access for this user when connecting _without_ a password.  I'm not understanding why this is happening.

mysql -h localhost -u myuser -p

Fails with a:

ERROR 1045: Access denied for user 'myuser'@'localhost' (using password: YES)

.... however:

mysql -h localhost -u myuser

(no -p flag, no password provided)

works just fine.

What's the deal? Any ideas on how to fix this, or where to look for what might be causing this behavior?Last edited by corey_s on Sun Aug 01, 2010 12:23 am; edited 1 time in total

----------

## mokia

Execute

```

use mysql

SELECT host,user,password from user WHERE user='myuser' AND host='localhost'

```

Do you have a password hash in the returned line?

----------

## corey_s

 *mokia wrote:*   

> Execute
> 
> ```
> 
> use mysql
> ...

 

Thanks a ton for the quick response - very much appreciated!

Host has '%', so the above query was null; here's output without querying on localhost:

```

mysql> SELECT host,user,password from user WHERE user='myuser';

+------+----------+-------------------------------------------+

| host | user     | password                                  |

+------+----------+-------------------------------------------+

| %    | myuser | *38ED700...ED |

+------+----------+-------------------------------------------+

```

Is '%' some sort of wildcard?

(I have another machine that has the same value for host, and it's working ok - so I don't _think_ that is the particular issue I'm experiencing)

----------

## mokia

And the hash was 16 or 41-byte long?

 *corey_s wrote:*   

> 
> 
> Is '%' some sort of wildcard?
> 
> 

 

Yes. its means any host.

```

SHOW GRANTS FOR 'myuser'@'localhost'; 

SHOW GRANTS FOR 'myuser'@'%';

```

GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'localhost' IDENTIFIED BY PASSWORD '*mypasshash' WITH GRANT OPTIONLast edited by mokia on Sat Jul 31, 2010 9:26 pm; edited 1 time in total

----------

## corey_s

 *mokia wrote:*   

> And the hash was 16 or 41-byte long?

 

It _was_ 16, originally - then I changed it with:

update user set password=PASSWORD("mypasswd") where User='myuser';

After which it was 41 bytes.

So I recently changed it back (again) to try it out; and things still aren't working.

I know at this point it sounds like things are all over the place, but it's not that bad:

#1 - I saw that there was an issue with our scripts

#2 - realized the issue was do to a sudden authentication issue  (I haven't touched the mysql server in months, so this is suspicious)

#3 - found that I could login to the mysql server as user 'myuser' _without_ a password

#4 - figured maybe the myuser password had for some reason been modified or something, so I issued:

update user set password=PASSWORD("mypasswd") where User='myuser';

#5 - noticed the hash went from 16 bytes to 41 bytes

#6 - restarted mysql service without the --skip-grant-tables option

#7 - tried logging into mysql as myuser (/usr/local/mysql/bin/mysql -h 127.0.0.1 -u myuser -p), but got the auth error

#8 - tried again, without the password (-p) option -- works fine...  (why is it allowing me to login _without_ a password, even though a password is set?)

#9 - went ahead and re-set the password again directly, using the original 16-byte hash string that was originally set

#10 - same exact problem:  I can login as user 'myuser' _without_ a password, even though a password is set

... totally confused.  (I'm more experienced with postgresql - so I'm finding myself at a loss here with this mysql issue)

Thanks!

----------

## corey_s

 *mokia wrote:*   

> 
> 
> ```
> 
> SHOW GRANTS FOR 'myuser'@'localhost'; 
> ...

 

Thanks - I'll try this right now!

----------

## corey_s

Brilliant - I can't login to mysql as root either.

And when I turn on the --skip-grant-tables switch, I'm unable to issue any GRANT commands:

"ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement"

----------

## corey_s

One other detail:

I can login using:

/usr/local/mysql/bin/mysql -h 127.0.0.1 -u myuser -p

... if I just hit enter at the password prompt.

But if I enter a password, I get the auth error.

----------

## mokia

So you cant login as root if you run the server wit grant tables?

----------

## corey_s

 *mokia wrote:*   

> So you cant login as root if you run the server wit grant tables?

 

Correct (assuming I read that right).

If running mysql without --skip-grant-tables, then I'm unable to login to mysql as root. (it could be that the root password has changed, along with whatever else was changed to tweak with the 'myuser' user)

I'm attempting to contact my client to verify whether he had been friggin' with the riggin', but haven't reached him yet.

What's really vexing - is I can login to mysql as user 'myuser' just fine _without_a_password_ (or using a blank password)...  That's no good!  I could apply a quick fix, and modify the (perl) scripts to connect w/o a pwd... but no way would I do that.  I really don't get why I can login without a password, even though a password is set for the user in question.  

Is there some mysql config file, or some other table or something that would cause such behavior?

----------

## corey_s

Also:

All the grant privs were enabled for 'myuser':

```

mysql> select * from user where user = 'myuser';

+------+----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+

| Host | User     | Password         | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections |

+------+----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+

| %    | myuser | 3a...12 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         |            | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                |          |            |             |              |             0 |           0 |               0 |

+------+----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+

```

I went ahead and turned them all off:

```

mysql> select * from user where user = 'myuser';

+------+----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+

| Host | User     | Password         | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections |

+------+----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+

| %    | myuser | 3a...12 | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                |          |            |             |              |             0 |           0 |               0 |

+------+----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+

1 row in set (0.00 sec)

```

Then issued a "FLUSH PRIVILEGES;"

Then restarted mysql with grant tables turned back on... but same problem:

User 'myuser' has a password in the user table, but I can only log into mysql with that user if I issue a blank password, or if I connect without the -p option.

----------

## corey_s

By the way - thanks so much for your time and help on this; I really appreciate it!

----------

## mokia

 *corey_s wrote:*   

> By the way - thanks so much for your time and help on this; I really appreciate it!

 

2010. aug.  1., vasárnap, 00.48.58 CEST   :Shocked: 

Im not shure, but did you try to run 

mysql_fix_privilege_tables

Source:

http://dev.mysql.com/doc/refman/4.1/en/set-password.html

http://dev.mysql.com/doc/refman/4.1/en/password-hashing.html

----------

## corey_s

ok - well, I re-set the root mysql password so that I could login into mysql without the --skip-grant-tables switch.

I then issued the GRANT commands you suggested.

I also tried the mysql_fix_privilege_tables script.

I also reset the 'myuser' password once again and flushed the privileges. 

... same issue:

Although I can now log into the mysql db as root with no issue (using the reset password), the 'myuser' issue still exists:  I can _only_ log into the 'myuser' db account _without_ a password...   freaking crazy.  

Thanks man for all your suggestions and advice...  guess at this point I'll try the mysql forums/mail-list.

Cheers

----------

## corey_s

Ok - just to follow and provide closure; got some help on the mysql mailling list:

 *Quote:*   

> 
> 
> after login to mysql issue:
> 
> select user();
> ...

 

 *Quote:*   

> 
> 
> login as root and remove 'empty' user ,issue:
> 
> delete from mysql.user where user='';
> ...

 

... so, recently for whatever reason, someone, somehow created an empty user on this server - which caused mysql to default to loging in as the empty user.

When I removed the empty user, the problem I was experiencing went away.

Cheers

----------

