Bug #398 After certain numer of connections mysql reports "Access denied"
Submitted: 8 May 2003 8:00 Modified: 12 Jun 2003 8:22
Reporter: Dziugas Baltrunas Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0.12 OS:FreeBSD (FreeBSD 5.0-RELEASE-p2)
Assigned to: CPU Architecture:Any

[8 May 2003 8:00] Dziugas Baltrunas
Description:
After certain numer of connections mysql reports an error:
ERROR 1045: Access denied for user: 'public@10.10.12.254' (Using
password: NO)

the same issue is mysql command line client as well as php, compiled with libmysqlclient from /usr/local/lib or with bundled mysql library. 

strange that this error occurs periodicaly and one after each other, then everything works fine for a while.

How to repeat:
while true; do mysql -hdb1 -upublic -psecret -e'quit'; done

when I execute the statment above, after 1 or 2 minute I get the following errors:

while true; do mysql -hdb1 -upublic -psecret
-e'quit'; done
ERROR 1045: Access denied for user: 'public@10.10.12.254' (Using
password: NO)
ERROR 1045: Access denied for user: 'public@10.10.12.254' (Using
password: NO)
ERROR 1045: Access denied for user: 'public@10.10.12.254' (Using
password: NO)
ERROR 1045: Access denied for user: 'public@10.10.12.254' (Using
password: NO)
ERROR 1045: Access denied for user: 'public@10.10.12.254' (Using
password: NO)
ERROR 1045: Access denied for user: 'public@10.10.12.254' (Using
password: NO)
ERROR 1045: Access denied for user: 'public@10.10.12.254' (Using
password: NO)
ERROR 1045: Access denied for user: 'public@10.10.12.254' (Using
password: NO)

mysql.log reports:
33578 Connect     Access denied for user: 'public@10.10.12.254' (Using password: YES)
33579 Connect     Access denied for user: 'public@10.10.12.254' (Using password: YES)
33580 Connect     Access denied for user: 'public@10.10.12.254' (Using password: YES)
33582 Connect     Access denied for user: 'public@10.10.12.254' (Using password: YES)
33583 Connect     Access denied for user: 'public@10.10.12.254' (Using password: YES)
33586 Connect     Access denied for user: 'public@10.10.12.254' (Using password: YES)
33587 Connect     Access denied for user: 'public@10.10.12.254' (Using password: YES)
33589 Connect     Access denied for user: 'public@10.10.12.254' (Using password: YES)

so there is strange behaviour about "Using password: YES" and "Using password" NO" messages. 

Suggested fix:
still missing
[13 May 2003 6:17] Michael Widenius
Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

I tried to repeat this on my Linux machine but was unable to get any Access denied errors by repeating the code in this bug report.

I did however find the problem with wrong value for 'Using password' in the log and this is now fixed in 4.0 development tree and will be in 4.0.13.
[14 May 2003 0:53] Dziugas Baltrunas
Thank you for your comments. I will try to give more details. We have highly loaded web server which uses PHP and MySQL. Here is an exapmle: php.log logs every error/warning. Logging started almost month ago, 17-Mar-2003 17:46:30. 

bash-2.05# grep -c "Using password: NO" php.log
28333

So, there are almost 30000 failed connections during the month. I'm absolutely sure, that every place in the code where mysql_connect() is used, there are both username and password provided. 

You said that you have fixed wrong value for 'Using password', so I would like to ask, what value it then should return instead of 'Access denied ... (Using password: NO)'?

As I wrote in my previous comment, this bug is not easy to repeat, but as you could see in php.log it's frequency is high enough. 

I though that I might have to chance some configuration options in my.cnf, like "max-connections" or others, but seems like this error is related with something different. I tried to repeat this "while" statment again and here is an output:

bash-2.05# while true; do mysql -hdb1 -upublic -psecret -e'quit'; done
ERROR 2003: Can't connect to MySQL server on 'db1' (49)
ERROR 2003: Can't connect to MySQL server on 'db1' (49)
ERROR 2003: Can't connect to MySQL server on 'db1' (49)
ERROR 2003: Can't connect to MySQL server on 'db1' (49)
ERROR 2003: Can't connect to MySQL server on 'db1' (61)
ERROR 2003: Can't connect to MySQL server on 'db1' (49)
ERROR 2003: Can't connect to MySQL server on 'db1' (49)
ERROR 2003: Can't connect to MySQL server on 'db1' (49)

This has happened after about 2 min since I started the command. So, behaviour is similar.

Any instructions how to reproduce more details about this issue are welcome, because currenly I see only solution - return back to 3.23.
[12 Jun 2003 8:22] Michael Widenius
This bug report forum is mainly for repeatable bug reports. For bug
reports like this one you should use the MySQL mailing list or buy
commercial support from MySQL AB.  

(The reason for this is that we have not been able to repeat this
problem here and to find out the bug we would need to find out much
more about your system, applications, exact MySQL usage etc..)

I can however give a couple of answers to you other problem:

The bug I fixed was just that MySQL logged a wrong value to the .err log.
It has no relevance for your real problem.

The loop in shell that you showed has probably nothing to do with your
problem.  What happens is that your TCP/IP stack on the server
overflows, as the TCP/IP connection is kept alive by the OS a short
term by (usually 30 seconds) after the connections is closed.  There
is noting we can do about it in MySQL but something that is normal for
TCP/IP connections.  The only solution for this is to use persistent
connections or somehow ensure that you don't disconnect/connect too
many times / second over TCP/IP.
[15 Jun 2003 23:29] Dziugas Baltrunas
Thank you very much for the answer. I'm just wondering to know, why mysql reports an "access denied with using password: no" error if the problem is related with TCP/IP stack and so on? Shouldn't it report something about "Too many connections"? 
In my global php.log, these errors appear at about every 5 minutes so I will really have to concentrate on finding right solution. Maybe there is another guy in this world having the same problem? 
By the way, I upgraded to 4.0.13 but situation is the same. 
Anyway, thanks again for the answers.
[30 Jun 2003 4:02] Dziugas Baltrunas
I would like to announce that this issue has been solved recompiling MySQL (4.0.13) together with LinuxThreads. Futhermore, this alteration has raised another problem - wait_timeout parameter is weakless (tried setting both in configuration file and manually using SET GLOBAL wait_timeout).
[30 Nov 2007 19:25] Patrick Shyu
I realize this ticket is 4 years old, but I'm getting this error.  I recently moved a mysql database from 'localhost' to a remote server, and am getting random "mysql can't connect" errors with error code 2003.  I'm running on Linux.

What's weird is that when I go to the interactive shell and invoke 'mysql', I also get intermittent "can't connect" messages.  I'm running MySql 5 on 64-bit.
[1 Dec 2007 7:21] Patrick Shyu
Solved it, for myself at least.  

I discovered that MySQL was crashing and restarting itself periodically after it got many connections.  That caused the "can't connect" messages.  I reinstalled a binary of MySQL and it's working great now.