| Bug #398 | After certain numer of connections mysql reports "Access denied" | ||
|---|---|---|---|
| Submitted: | 8 May 2003 10:00 | Modified: | 12 Jun 2003 10:22 |
| Reporter: | Dziugas Baltrunas | ||
| Status: | Not a Bug | ||
| Category: | Server | Severity: | S3 (Non-critical) |
| Version: | 4.0.12 | OS: | FreeBSD (FreeBSD 5.0-RELEASE-p2) |
| Assigned to: | Target Version: | ||
[8 May 2003 10:00]
Dziugas Baltrunas
[13 May 2003 8: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 2: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 10: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.
[16 Jun 2003 1: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 6: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 20: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 8: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.