CODEWITHSUNDEEP
javamysqljdbc
nayak0765
nayak0765

Reputation: 193

change in Mysql system variable in java jdbc connection url do not take effect

In mysql the system variable "max_connections" has value 100. But i am trying to override this value programatically by pass key-value pair in jdbc connection string as "jdbc:mysql://localhost/test?maxConnections=3". But it seems like it is not reflecting any change. Here below I made maxConnection to 3 but still in below example I am able to create 40 connections. Note I have not closed connection in each iteration purposely.Just to see if the key value added in jdbc connection string is taking effect.If it takes effect then it should give exception of "Too many Connections"

See below code for reference:

public class JDBCOne {

    public static void main(String[] args) throws ClassNotFoundException, SQLException {


        for(int i=1;i<=35;i++)
            {
            Connection conn = null;  
            Statement stmt = null;

            Class.forName("com.mysql.jdbc.Driver");

            System.out.println("Connecting to database...:"+i);
            conn = DriverManager.getConnection("jdbc:mysql://localhost/test?maxConnections=3", "root", "root");
            stmt = conn.createStatement();
            Integer id = null;
            String name = null;
            Double amount = null;
            String sql = "SELECT * FROM emp where id=1";

            ResultSet rs = stmt.executeQuery(sql);

            while (rs.next()) {
                id = rs.getInt("id");
                name = rs.getString("name");
                amount = rs.getDouble("amount");
            }
            System.out.println("id-" + id);
        }
        System.out.println("Goodbye!");
    }

}

Upvotes: 0

Views: 1808

Answers (3)

Halko Karr-Sajtarevic
Halko Karr-Sajtarevic

Reputation: 2268

You can set it using set global max_connections = 200; if you have the SUPER privilege.

e.g.:

try (PreparedStatement pstmt = conn.prepareStatement("SET GLOBAL max_connections = ?")) {
    pstmt.setInt(1, 200);
    pstmt.execute();
} catch (SQLException ex) {
    throw new RuntimeException(ex);
}

Upvotes: 1

O. Jones
O. Jones

Reputation: 108651

max_connections is a global MySQL server variable governing the number of connections the server accepts from all programs connecting to it.

It's not a configuration string parameter, though. So, if you try to change it with a connection string, You Can't Do That™.

See this. https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-configuration-properties.html

Upvotes: 1

Gabe Gates
Gabe Gates

Reputation: 1000

I can't say for sure, but it may be possible that com.mysql.jdbc.Driver is closing some of the connections when conn and/or stmt go out of scope.

You could try putting them outside the for loop:

        Connection conn = null;  
        Statement stmt = null; 
        for(int i=1;i<=35;i++)
        {

You could verify if connections are staying open by running the following MySQL command:

SHOW PROCESSLIST;

Upvotes: 0

Related Questions