Mysql too many connections

Question

I do not like to raise a question that is widely asked on the Internet, but I cannot solve it.

I started the project a while ago, and after a month of testing, I hit the "Too many connections" error. I looked at it and “decided” by increasing max_connections. Then it worked.

Since then, more and more people started using it, and it hit again. When I am the only user on the site, I type “show processlist” and about 50 connections appear that are still open (they say “Sleep” in the command). Now I don’t know enough to talk about why they are open, but in my code I checked three times and close every open connection.

t

public int getSiteIdFromName(String name, String company)throws DataAccessException,java.sql.SQLException{

Connection conn = this.getSession().connection();
Statement smt = conn.createStatement();
ResultSet rs=null;
String query="SELECT id FROM site WHERE name='"+name+"' and company_id='"+company+"'";

rs=smt.executeQuery(query);
rs.next();

int id=rs.getInt("id");

rs.close();
smt.close();
conn.close();
return id;
}

, - , . - ? , ?

Answer 1

, - conn.close(). ( ) try finally. finally , , . , .

:

public int getSiteIdFromName(String name, String company) throws DataAccessException, java.sql.SQLException {
    Connection conn = null;
    Statement smt = null;
    ResultSet rs = null;
    int id = 0;
    try {
        conn = this.getSession().connection();
        smt = conn.createStatement();
        String query = "SELECT id FROM site WHERE name='" + name + "' and company_id='" + company + "'";
        rs = smt.executeQuery(query);
        rs.next();
        id = rs.getInt("id");
    } finally {
        if (rs != null) try { rs.close(); } catch (SQLException logOrIgnore) {}
        if (smt != null) try { smt.close(); } catch (SQLException logOrIgnore) {}
        if (conn != null) try { conn.close(); } catch (SQLException logOrIgnore) {}
    }
    return id;
}

, SQL injection . PreparedStatement Statement.

. :

• Sun Exceptions: finally
• Sun JDBC:
• Sun JDBC: PreparedStatement
• DAO: JDBC

Answer 2

, , :

• Stmt.executeQuery()
• , rs.next() true false
• rs.getInt( "id" ) ,
• conn.close()

:

• rs.getInt() rs.next()
• finally try

Edit:

-, .

Answer 3

DataAccessException java.sql.SQLException, , ;) try-finally-Block, .

Connection conn = this.getSession().connection();
try {
  // all code
} finally {
  rs.close();
  smt.close();
  conn.close();
}

, , , .

Answer 4

mysql. .

:

mysql .

mysql> SET GLOBAL max_connections = 200;

. , .

-:

/etc/mysql/my.cnf max_connection .

[mysqld]
local-infile=0
datadir=/var/lib/mysql
user=mysql
symbolic-links=0

max_connections = 100

, mysqld:

/etc/init.d/mysqld restart

- , .

, , , .

, , try catch.

, , , , , . try catch, .

try{
//your code 
}
catch(Exception e){

//handle the exceptions here
}

finally{
        try{
            channel.close();
        } catch(Exception e){
            log.error("Error is "+e.getMessage(),e);
            e.printStackTrace();
        }
        try {
            connection.close();
        } catch (IOException e) {
            log.error("Error is "+e.getMessage(),e);
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        catch(Exception e){
            log.error("Error is "+e.getMessage(),e);
            e.printStackTrace();
        }
    }