Tuesday, January 24, 2017

How to increase max connection in MySql

When you try to make a large number of connections to the MySQL server, sometimes you might get the following error.

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection,  message from server: "Too many connections"

There are two values governing the max number of connections one can create

  1. max_user_connections
  2. global.max_connections

1. max_user_connections

The max_user_connections is a user level parameter which you can set for each user. To let a user to create any number of connections set the above mentioned value to zero '0'.

First view the current  max_user_connections:

SELECT max_user_connections FROM mysql.user WHERE user='my_user' AND host='localhost';

Then set it to zero


2. global.max_connections

The global.max_connections is a global parameter and has the precedence over 
max_user_connections. Hence just increasing the max_user_connections is not enough. Hence you have to increase the max_user_connections as well.

set @@global.max_connections = 1500;


[1] http://dba.stackexchange.com/questions/47131/how-to-get-rid-of-maximum-user-connections-error

[2] https://www.netadmintools.com/art573.html

No comments:

Post a Comment