method to resolve Mysql 1040 error too many connections _mysql

Source: Internet
Author: User
Tags function prototype port number
From the official documentation that Linux compiled installed on the MySQL default connection of 100, so the site is not enough for the needs.
MySQL official told us that we need to modify the value of max_connections, so how do we modify it? There are two ways of

1, modify the profile file

Modify/ETC/MY.CNF This file, add Max_connections=n in [mysqld], if you do not have this file, please copy the *.cnf file you need from the Support-files folder in the compiled source code to/ETC/MY.CNF. I am using the MY-MEDIUM.CNF, medium server configuration. For example, the contents of my [mysqld] are as follows
Copy Code code as follows:

Port = 3306
Socket =/tmp/mysql.sock
Key_buffer = 160M
Max_allowed_packet = 1M
Table_cache = 64
Sort_buffer_size = 512K
Net_buffer_length = 8K
Read_buffer_size = 256K
Read_rnd_buffer_size = 512K
Myisam_sort_buffer_size = 8M

Because MySQL is not very familiar with, so many parameters have not been modified. Ha ha..

2, not using the Mysqld script to automatically start the user.

modifying $mysql_home/bin/mysqld_safe files
For example:/usr/local/mysql/bin/mysqld_safe this file
Grep-n ' max_connection ' $MYSQL _home/bin/mysqld_safe
Modify the Max_connections parameter value of the corresponding line number
The above methods are written for reference to online practices.

Method Two:

Find information about mysql_connect and mysql_pconnect in the PHP manual, and here's a description of the two functions in the PHP manual:
mysql_connect function Prototype:
Resource mysql_connect ([string server [, String username [, string password [, bool new_link [, int client_flags]]]
Returns a MySQL connection identity if successful, and returns False if it fails.
Mysql_connect () establishes a connection to the MySQL server. The following default values are used when no optional arguments are supplied: Server = ' localhost:3306 ', username =
User name of the server process owner, password = null password.
If mysql_connect () is invoked the second time with the same argument, no new connection will be established, and the connection ID that is already open will be returned. Parameter New_link changes this behavior and makes the
Mysql_connect () always opens a new connection, even when mysql_connect () was previously invoked with the same parameters. Parameter client_flags can be a combination of the following constants
: Mysql_client_compress,mysql_client_ignore_space or Mysql_client_interactive.
Note: The New_link parameter is available from PHP 4.2.0.
The Client_flags parameter is available from PHP 4.3.0.
Once the script is finished, the connection to the server is closed. Unless you have already called mysql_close () to close it.
Function Prototypes:
Resource Mysql_pconnect ([string server [, String username [, string password [, int client_flags]]]
Returns a positive MySQL persistent connection identifier If successful, and returns false for error.
Mysql_pconnect () establishes a connection to the MySQL server. If an optional parameter is not provided, the following default value is used: Server = ' localhost:3306 ',
Username = user name of the server process owner, password = null password. The Client_flags parameter can be a combination of the following constants: Mysql_client_compress,
Mysql_client_ignore_space or mysql_client_interactive.
The server parameter can also include a port number, such as "Hostname:port", or a path to a native socket, such as ":/path/to/socket".
Note: Support for the ":p ort" is added by the 3.0B4 version.
The support for ":/path/to/socket" was added by the 3.0.10 version. The difference between the two:
Mysql_pconnect () and mysql_connect () are very similar, but there are two major differences.
First, when connected, this function will first attempt to find a (persistent) connection that has been opened with the same username and password on the same host, and if found, returns this connection identity without opening a new connection.
Second, when the script completes, the connection to the SQL Server is not closed, and the connection remains open for later use (Mysql_close () does not close the connection established by Mysql_pconnect ()).
Optional parameter client_flags is available from the PHP 4.3.0 version. This type of connection is called "persistent."

To ensure that your system does not appear too many connections errors, you need to be aware of two points:
1. Ensure that your Apache maximum number of processes does not exceed the maximum number of MySQL connections;
2. Do not use too many mysql_pconnect in the program to connect to the same database server (one is enough). This requires good coding habits and specifications. Especially the continuous increase of the system
New features, if not the system architecture and coding norms, when the complexity of the system to a certain extent, the entire system becomes impossible to maintain. When problems arise, it is troublesome to solve them.
The workaround is to modify the/ETC/MYSQL/MY.CNF and add the following line:
Set-variable = max_connections=500
Or add parameters to the startup command max_connections=500
is to modify the maximum number of connections, and then restart MySQL. The default number of connections is 100, too few, so it is easy to appear as the title error

the above excerpt from the song Lee Xing's column is supplemented by the following:

1. Probably the problem of MySQL max connections setup
2. It is possible that multiple insert,update operations do not close session, and need to configure transaction support in spring.

1. Reduce the time-out time of the session in Tomcat to 20, (not required)
2. Provide transaction support for the operation of database insert or update operations with large amount of processing.

Here's the solution:
Com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:Data Source rejected establishment of Connection, message from server: "Too many connections"


Because the number of concurrent connections in the My.ini in your MySQL installation directory is too low or the system is busy causing the number of connections to be filled up

Solution Method:

Open the MySQL installation directory to open My.ini find Max_connections (on the 93rd line) by default is 1001-like settings to 500~1000 more appropriate, restart MySQL, so 1040 errors to solve it.

Be sure to restart MySQL to take effect


net stop MySQL

net start MySQL

About the problem of not starting MySQL after changing innodb_log_file_size

The above is the initial optimization of the InnoDB engine, found that the update innodb_log_file_size=256m when there is a problem, as long as the addition of this can not start,

Later know that the original to stop service first, and then delete the original file ...
Open/mysql Server 5.5/data

Delete Ib_logfile0, Ib_logfile1........ib_logfilen
Open the option again and start successfully.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.