Resolve mysql 1040 error Too many connections method
- 2020-05-14 05:04:16
- OfStack
The mysql official tells us that we need to modify the value of max_connections, so how do we modify it? There are two ways
1. Modify the configuration file
Modify /etc/ my.cnf by adding 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 used my-medium. cnf, medium server configuration. For example, my [mysqld] reads 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
Since I am not familiar with mysql, many parameters have not been changed. Ha ha..
2. Users who do not use mysqld script to start automatically.
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 value of the max_connections parameter for the corresponding line number
The above methods are written for reference on the Internet.
Method 2:
Find information about mysql_connect and mysql_pconnect in the PHP manual. The following is the description of these 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:
An MySQL connection id is returned on success and FALSE on failure.
Description:
mysql_connect() establishes a connection to the MySQL server. The following default values are used when no optional parameters are provided: server = 'localhost:3306', username =
The user name of the server process owner, password = empty password.
If you call mysql_connect() the second time with the same parameter, you will not make a new connection, but will return the connection id that is already open. The parameter new_link changes this behavior and makes
mysql_connect() always opens a new connection, even when mysql_connect() was previously called with the same argument. 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 ends, the connection to the server is closed. Unless you've already called mysql_close() to close it.
mysql_pconnect:
Function prototype:
resource mysql_pconnect ( [string server [, string username [, string password [, int client_flags]]]])
Returns:
Returns a positive MySQL persistent connection identifier on success, and FALSE on error.
Description:
mysql_pconnect() establishes a connection to the MySQL server. If no optional parameters are provided, the following default values are used: server = 'localhost:3306',
username = user name of server process owner, password = empty password. The client_flags parameter can be a combination of the following constants: MYSQL_CLIENT_COMPRESS,
MYSQL_CLIENT_IGNORE_SPACE or MYSQL_CLIENT_INTERACTIVE.
server parameters can also include the port number, such as "hostname: port", or the path of the native socket, such as ": / path to/socket".
Note: support for ":port" was added in version 3.0B4.
For ": / path to/socket" support is 3.0.10 version added. The difference between the two:
mysql_pconnect() and mysql_connect() are very similar, but with two major differences.
First, when connecting, this function will first try to find a (persistent) connection that has been opened with the same username and password on the same host. If found, it will return the connection id without opening a new connection.
Second, the connection to the SQL server will not be closed when the script is executed, but will remain open for later use (mysql_close() will not close the connection established by mysql_pconnect()).
The optional parameter client_flags is available from version 4.3.0 of PHP. Such connections are called "persistent".
To make sure your system doesn't have Too many connections errors, there are two things to note:
1. Ensure that the maximum number of processes in your apache does not exceed the maximum number of connections in mysql;
2. Don't use too many mysql_pconnect in your application to connect to the same database server (one is enough). This requires good coding habits and specifications
If the new functions do not pay attention to the system architecture and coding specifications, when the complexity of the system reaches a certain level, the whole system will become unmaintainable, which will be very troublesome to solve when problems arise.
The solution is to modify/etc/mysql/my cnf, add the following line 1:
set-variable = max_connections=500
Or add the parameter max_connections=500 to the startup command
This is to change the maximum number of connections, and then restart mysql. The default number of connections is 100, which is too small, so it is easy to make mistakes
The above column from song lixing is supplemented by the following:
1. It may be a problem with mysql's max connections Settings
2. It may be multiple times of insert,update operation did not turn off session, so transaction support needs to be configured in spring.
Solution:
1. Modify the tomcat session time-out time reduced to 20 (not required)
2. Provide transaction support for database insert or update operations with large processing capacity.
=======================================
Here's the solution:
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection, message from server: "Too many connections"
The reason:
Because your mysql installation directory, my.ini, has set the number of concurrent connections too low or the system is busy, the number of connections is full
Solution:
Open the MYSQL installation directory open MY.INI find max_connections (about line 93) the default is 100 1 and it is appropriate to set it to 500 ~ 1000. Restart mysql and the 1040 error will be resolved.
max_connections=1000
1 you must restart MYSQL to take effect
CMD- >
net stop mysql
net start mysql
About the problem that innodb_log_file_size cannot be started after changing innodb_log_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 preliminary optimization of innodb engine. It is found that there is a problem when updating innodb_log_file_size=256M. As long as adding this, it cannot be started.
Only later did I learn that I wanted the STOP 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 and launch successfully.