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

Question

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!");
    }

}

Why do you think you can set this through a connection property? — Mark Rotteveel
– Mark Rotteveel, Commented Jul 30, 2018 at 14:50

O. Jones · Accepted Answer · 2018-07-30 12:53:03Z

1

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

answered Jul 30, 2018 at 12:53

O. Jones

109k17 gold badges134 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nayak0765 Over a year ago

But I read in offical documentation that we can pass mysql configuration parameters in jdbc string url. For example.. jdbc:mysql://localhost/test?user=root&password=root&autoReconnect=true works.But if I pass mysql config parameters like "maxConnections","connectTimeout" etc..in jdbc url it do not work.Like jdbc:mysql://localhost/test?maxCOnnections=3&connectTimeout=300 SO i dont understand which all mysql config parameters are allowed in jdbc con url. I have a requirment to manage this mysql config parameters programatically.Is it anything to do with global and session mysql variables?

Halko Karr-Sajtarevic · Accepted Answer · 2018-07-30 12:58:50Z

1

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);
}

answered Jul 30, 2018 at 12:58

Halko Karr-Sajtarevic

2,2781 gold badge17 silver badges14 bronze badges

Comments

Gabe Gates · Accepted Answer · 2018-07-30 12:52:15Z

0

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;

answered Jul 30, 2018 at 12:52

Gabe Gates

1,0501 gold badge16 silver badges23 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related