- B.1.2 Common Errors When Using MySQL Programs
- B.1.2.1 Access denied
- B.1.2.2 Can't connect to [local] MySQL server
- B.1.2.3 Client does not support authentication protocol
- B.1.2.4 Password Fails When Entered Interactively
- B.1.2.5 Host 'host_name' is blocked
- B.1.2.6 Too many connections
- B.1.2.7 Out of memory
- B.1.2.8 MySQL server has gone away
- B.1.2.9 Packet too large
- B.1.2.10 Communication Errors and Aborted Connections
- B.1.2.11 The table is full
- B.1.2.12 Can't create/write to file
- B.1.2.13 Commands out of sync
- B.1.2.14 Ignoring user
- B.1.2.15 Table 'tbl_name' doesn't exist
- B.1.2.16 Can't initialize character set
- B.1.2.17 'File' Not Found and Similar Errors
If you get a Too many connections error
when you try to connect to the mysqld
server, this means that all available connections are in use
by other clients.
The number of connections allowed is controlled by the
max_connections system variable. Beginning
with MySQL 5.1.15, its default value is 151 to improve
performance when MySQL is used with the Apache Web server.
(Previously, the default was 100.) If you need to support more
connections, you should restart mysqld with
a larger value for this variable.
MySQL Enterprise.
Subscribers to the MySQL Network Monitoring and Advisory
Service receive advice on dynamically configuring the
max_connections variable — avoiding
failed connection attempts. For more information see,
http://www.mysql.com/products/enterprise/advisors.html.
mysqld actually allows
max_connections+1 clients to connect. The
extra connection is reserved for use by accounts that have the
SUPER privilege. By granting the
SUPER privilege to administrators and not
to normal users (who should not need it), an administrator can
connect to the server and use SHOW
PROCESSLIST to diagnose problems even if the maximum
number of unprivileged clients are connected. See
Section 13.5.4.24, “SHOW PROCESSLIST Syntax”.
The maximum number of connections MySQL can support depends on the quality of the thread library on a given platform. Linux or Solaris should be able to support 500-1000 simultaneous connections, depending on how much RAM you have and what your clients are doing. Static Linux binaries provided by MySQL AB can support up to 4000 connections.
User Comments
Detect "Too many connections" error and show alternate web page
<?php$link = mysql_connect("localhost", "mysql_user", "mysql_password");
if (mysql_errno() == 1203) {
// 1203 == ER_TOO_MANY_USER_CONNECTIONS (mysqld_error.h)
header("Location: http://your.site.com/alternate_page.php");
exit;
}
?>
<?ini_set('display_errors', '0');
$link = mysql_connect("localhost", "user", "pass");
if (mysql_errno() == 1040 OR mysql_errno() == 1203) {
define("DB_HOST", "some_ip:3306");//remote
define("DB_NAME", "db"); //database_name
define("DB_USER", "user"); //database user name
define("DB_PASSWORD","pass"); //database (user) password
}
else
{
define("DB_HOST", "localhost");
define("DB_NAME", "db_name2"); //database_name
define("DB_USER", "user"); //database user name
define("DB_PASSWORD","pass"); //database (user) password
}
ini_set('display_errors', '1');
//by feha at www.vision.to
?>
You can increase this value in main config file (e.g., /etc/my.cnf) using this syntax:
[mysqld]
set-variable=max_connections=250
A note por PHP developers. You can find this error if your scripts open persistent connections, wich aren't closed even if the script terminates. Use mysql_connect() instead of mysql_pconnect() unless you have a good reason. In particular, check this setting in third-party scripts (such as osCommerce).
Server administrators can disable persistent connections for PHP scripts in php.ini file:
[MySQL]
; Allow or prevent persistent links.
mysql.allow_persistent=Off
Scripts won't fail, they'll just use non-persistent connections silently.
Another symptom for PHP users, the "max_connections" error being returned to the browsers and the "show processlist" filling up with sleeping threads: This can sometimes be alleviated by using the PHP .htaccess option to lower the connect timeout from the default 60 seconds.
php_value mysql.connect_timeout 20
The osCommerce setting mentioned by ?varo is in the catalog/includes/configure.php file:
define('USE_PCONNECT', 'false'); // use persistent connections?
It defaults to true, so mysql_pconnect() is used, and you get the error message "Warning: mysql_pconnect(): Too many connections ..." Change to false for mysql_connect() to be used.
Please note that an instruction of the form
set-variable=max_connections=500
should be placed in the [mysqld] section. Otherwise MySQL will ignore it.
Add your own comment.