Workaround for MySQL 1040 error too many connections

Source: Internet
Author: User

From the official documentation know that the Linux compiler installed MySQL default connection of 100, so for the needs of the site is not enough.
MySQL official tells us that we need to modify the value of max_connections, then how can we modify it? There are two ways

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 source code to/ETC/MY.CNF. I am using the MY-MEDIUM.CNF, medium server configuration. For example, my [mysqld] content is as follows

Copy CodeThe code is as follows:
[Mysqld]
Port = 3306
Socket =/tmp/mysql.sock
Skip-locking
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
max_connections=1000


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

2. Users who are not automatically started using the Mysqld script.

Modify the $mysql_home/bin/mysqld_safe file
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 for the corresponding line number
The above methods are written for reference on-line practices.

Method Two:

To find information about mysql_connect and mysql_pconnect in the PHP manual, here is a description of the two functions in the PHP manual:
mysql_connect function Prototypes:
Resource mysql_connect ([string server [, String username [, string password [, bool new_link [, int client_flags]]])
Return:
Returns a MySQL connection ID if successful, and false if it fails.
Describe:
Mysql_connect () establishes a connection to the MySQL server. Use the following default values when no optional parameters are available: Server = ' localhost:3306 ', username =
User name of the server process owner, password = null password.
If you call mysql_connect () for the second time with the same parameters, no new connection will be made and the connection ID that is already open will be returned. Parameter New_link change this behavior and make
Mysql_connect () always opens a new connection, even when mysql_connect () was previously called with the same parameters. The 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 previously called mysql_close () to close it.
Mysql_pconnect:
Function Prototypes:
Resource Mysql_pconnect ([string server [, String username [, string password [, int client_flags]]])
Return:
Returns a positive MySQL persistent connection identifier if successful, and False if the error occurs.
Describe:
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 ":p ORT" is added in version 3.0b4.
Support for ":/path/to/socket" is added in version 3.0.10. The difference between the two:
Mysql_pconnect () and mysql_connect () are very similar, but there are two main differences.
First, when connecting, this function will first attempt to find a (persistent) connection with the same user name and password on the same host, and if found, return this connection identity without opening a new connection.
Second, when the script finishes executing the connection to the SQL Server is not closed, the connection will remain open for later use (Mysql_close () will not close the connection established by Mysql_pconnect ().
Optional parameters client_flags available from PHP version 4.3.0. This type of connection is called "persistent".

Summary, to ensure that your system does not appear too many connections error, you need to pay attention to two points:
1. Ensure that your Apache's maximum number of processes does not exceed the maximum number of MySQL connections;
2. Do not connect to the same database server with too many mysql_pconnect in the program (one is enough). This requires good coding practices and specifications. In particular, the constant increase in the system
New features, if the system is not focused on the architecture and coding specifications, when the complexity of the systems to a certain degree, the entire system becomes impossible to maintain. It's going to be a lot of trouble to solve the problem when it comes up.
The workaround is to modify/ETC/MYSQL/MY.CNF and add the following line:
Set-variable = max_connections=500
or add parameter max_connections=500 to the start command
is to modify the maximum number of connections and then restart MySQL. The default number of connections is 100, too few, so prone to title errors

the above columns from the song Lee Hing are complemented by the following:

1. May be the problem with MySQL max connections setup
2. It is possible that multiple insert,update operations have not closed the session and need to configure transaction support in spring.

Solve:
1. Change the time-out time of the session in Tomcat to 20, (not required)
2. Transaction support for operations with large processing volumes for database insert or update.

=======================================
The following are the workarounds:
Com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:Data Source rejected establishment of Connection, message from server: "Too many connections"

Reason:

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


How to resolve:

Open MySQL install directory open My.ini find Max_connections (in about line 93rd) default is 1001 like set to 500~1000 more appropriate, restart MySQL, so 1040 error is solved.
max_connections=1000

Be sure to restart MySQL to take effect

Cmd->

net stop MySQL

net start MySQL

Questions about the inability to start MySQL after changing innodb_log_file_size

Innodb_buffer_pool_size=768m
innodb_log_file_size=256m
Innodb_log_buffer_size=8m
innodb_additional_mem_pool_size=4m
Innodb_flush_log_at_trx_commit=0
Innodb_thread_concurrency=20
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 will not start,

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

Delete Ib_logfile0, Ib_logfile1........ib_logfilen
Turn on the option again to start successfully.

Workaround for MySQL 1040 error too many connections

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: info-contact@alibabacloud.com 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.