strange authentication issue

am 01.08.2010 01:27:29 von Corey

Hello - I'm only moderately familiar with mysql; the following issue has me
stumped.

I just noticed that some scripts on one of my client's servers suddenly
started failing due to being no longer able to log into the mysql db.

I have not touched the mysql config on that machine in months.

The problem is as follows:

The scripts need to login to mysql as user 'scripts'.

Suddenly, user 'scripts' can only log into the mysql console _without_
a password (or using an empty/null password):

/usr/local/mysql/bin/mysql -h localhost -u scripts -p
Enter password:
ERROR 1045 (28000): Access denied for user 'scripts'@'localhost' (using
password: YES)

However, the following works just fine (without the '-p' option):

/usr/local/mysql/bin/mysql -u scripts
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 22 to server version: 4.1.14-standard

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

mysql>

I've checked the mysql.user table, and ensured that user 'scripts'
indeed still has a password. I also went ahead and reset it:

mysql> update user set password=PASSWORD("mypwd") where User='scripts';
Query OK, 1 row affected (0.03 sec)

Then I flushed the privileges, restarted mysql and tried again - same issue:

I can _only_ log into mysql with user 'scripts' _without_ a password.

I have no clue what might be causing this behavior (or why in the world this
suddenly started occurring, in the first place).

I could really use some advice and help - because I've tried everything I
can think of, and the problem seems unshakable.

Thankyou!

--
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: strange authentication issue

am 01.08.2010 01:30:00 von chaim rieger

Just a stab but what's the max connect set to ?

--
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: strange authentication issue

am 01.08.2010 01:32:25 von Corey

On Saturday 31 July 2010 4:30:00 chaim rieger wrote:
> Just a stab but what's the max connect set to ?
>

Thanks a ton for the quick response - I'm at wits end, been working on
this for hours!

max_connections is set to 0.

(but it is also set to 0 on another server, which largely follows the
same setup/config as on the server I'm currently having problems
with)

Oh , yeah - this is mysql 4.1.14.

--
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: strange authentication issue

am 01.08.2010 01:40:14 von chaim rieger

Another thing I just noticed

In your first example you are using localhost, which probably means you are connecting via network

The second option you don't define a host, which means you're prolly using socket connection

--
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: strange authentication issue

am 01.08.2010 01:47:56 von Corey

On Saturday 31 July 2010 4:40:14 chaim rieger wrote:
> Another thing I just noticed
>
> In your first example you are using localhost, which probably means you are
> connecting via network
>
> The second option you don't define a host, which means you're prolly using
> socket connection

Oh, whoops - my bad for not being consistent with the examples.

/usr/local/mysql/bin/mysql -h localhost -u scripts
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 32 to server version: 4.1.14-standard

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

mysql> \q

.... it also works if I supply the '-p' option, but just hit at
the password prompt:

/usr/local/mysql/bin/mysql -h localhost -u scripts -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 33 to server version: 4.1.14-standard

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

mysql>

I'm completely baffled as to why I can _only_ login _without_ a
password... but only for that one user, 'scripts'.

Everything else works as expected.

Is there some other auth table, or a config file or something that
could cause such behavior.

(I also don't understand why this would suddenly start occurring -
seems suspicious)

--
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: strange authentication issue

am 01.08.2010 01:58:36 von Claudio Nanni - TomTom

after login to mysql issue:

select user();
and
select current_user();
and post output
you will see they dont match.

Claudio

On 8/1/2010 1:47 AM, Corey wrote:
> On Saturday 31 July 2010 4:40:14 chaim rieger wrote:
>
>> Another thing I just noticed
>>
>> In your first example you are using localhost, which probably means you are
>> connecting via network
>>
>> The second option you don't define a host, which means you're prolly using
>> socket connection
>>
> Oh, whoops - my bad for not being consistent with the examples.
>
> /usr/local/mysql/bin/mysql -h localhost -u scripts
> Welcome to the MySQL monitor. Commands end with ; or \g.
> Your MySQL connection id is 32 to server version: 4.1.14-standard
>
> Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>
> mysql> \q
>
>
> ... it also works if I supply the '-p' option, but just hit at
> the password prompt:
>
> /usr/local/mysql/bin/mysql -h localhost -u scripts -p
> Enter password:
> Welcome to the MySQL monitor. Commands end with ; or \g.
> Your MySQL connection id is 33 to server version: 4.1.14-standard
>
> Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>
> mysql>
>
>
>
> I'm completely baffled as to why I can _only_ login _without_ a
> password... but only for that one user, 'scripts'.
>
> Everything else works as expected.
>
> Is there some other auth table, or a config file or something that
> could cause such behavior.
>
> (I also don't understand why this would suddenly start occurring -
> seems suspicious)
>
>
>

--
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: strange authentication issue

am 01.08.2010 02:02:49 von Corey

On Saturday 31 July 2010 4:58:36 Claudio Nanni wrote:
> after login to mysql issue:
>
> select user();
> and
> select current_user();
> and post output
> you will see they dont match.
>

/usr/local/mysql/bin/mysql -h localhost -u scripts -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 4.1.14-standard

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

mysql> select user();
+--------------------+
| user() |
+--------------------+
| scripts@localhost |
+--------------------+
1 row in set (0.00 sec)

mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| @localhost |
+----------------+
1 row in set (0.01 sec)

.... ok - there's the output - and it's like you said; does this
pinpoint the issue? I'm not sure what to do with the info.

Thanks!

--
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: strange authentication issue

am 01.08.2010 02:11:33 von Claudio Nanni - TomTom

login as root and remove 'empty' user ,issue:

delete from mysql.user where user='';
flush privileges;

mysql authentication system is logging you on as 'empty' user.

you will be ok after that ;)

Claudio

On 8/1/2010 2:02 AM, Corey wrote:
> On Saturday 31 July 2010 4:58:36 Claudio Nanni wrote:
>
>> after login to mysql issue:
>>
>> select user();
>> and
>> select current_user();
>> and post output
>> you will see they dont match.
>>
>>
>
> /usr/local/mysql/bin/mysql -h localhost -u scripts -p
> Enter password:
> Welcome to the MySQL monitor. Commands end with ; or \g.
> Your MySQL connection id is 6 to server version: 4.1.14-standard
>
> Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>
> mysql> select user();
> +--------------------+
> | user() |
> +--------------------+
> | scripts@localhost |
> +--------------------+
> 1 row in set (0.00 sec)
>
> mysql> select current_user();
> +----------------+
> | current_user() |
> +----------------+
> | @localhost |
> +----------------+
> 1 row in set (0.01 sec)
>
>
>
> ... ok - there's the output - and it's like you said; does this
> pinpoint the issue? I'm not sure what to do with the info.
>
> Thanks!
>
>

--
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: strange authentication issue

am 01.08.2010 02:15:43 von Corey

On Saturday 31 July 2010 5:11:33 Claudio Nanni wrote:
> login as root and remove 'empty' user ,issue:
>
> delete from mysql.user where user='';
> flush privileges;
>
> mysql authentication system is logging you on as 'empty' user.
>
> you will be ok after that ;)
>

Man, thanks a ton! That was it!

I'll have to look into why/how an empty user was created... but
at least it's fixed now.

Beers!

--
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: strange authentication issue

am 01.08.2010 10:53:47 von Claudio Nanni - TomTom

sorry I went to sleep CEST here,

its created by default by MySQL

Cheers!

Claudio

On 8/1/2010 2:15 AM, Corey wrote:
> On Saturday 31 July 2010 5:11:33 Claudio Nanni wrote:
>
>> login as root and remove 'empty' user ,issue:
>>
>> delete from mysql.user where user='';
>> flush privileges;
>>
>> mysql authentication system is logging you on as 'empty' user.
>>
>> you will be ok after that ;)
>>
>>
> Man, thanks a ton! That was it!
>
> I'll have to look into why/how an empty user was created... but
> at least it's fixed now.
>
>
> Beers!
>
>

--
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: strange authentication issue

am 01.08.2010 10:54:59 von Claudio Nanni - TomTom