Announcement Announcement Module
Collapse
No announcement yet.
Spring openning 2 connections and using only one Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Spring openning 2 connections and using only one

    Dear all,

    I realized a problem with db connections while doing some performance testing just before releasing my app.

    The problem is that Spring opens a connection for my statement and correctly closes it later, BUT it also opens a 2nd connection which is left open. And i have the situation that with one connection left open per thread my application dies after some few hundreds requests.

    The problem is easy to reproduce, btw my Spring version is 2.5.6:
    Code:
    	
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"  
    		p:driverClassName="com.mysql.jdbc.Driver"
    		p:url="jdbc:mysql://${DB.url}" 
    		p:username="${DB.username}"
    		p:password="${DB.password}">
    	</bean>
    	<bean id="template" class="org.springframework.jdbc.core.JdbcTemplate" scope="prototype" lazy-init="true">
    		<property name="dataSource" ref="dataSource" />
    	</bean>
    Code:
    @Path("test")
    @Component("testResource")
    public class TestResource {
    
        @Resource(name = "template")
        private JdbcTemplate template;
    
        @Path("a")
        @GET
        @Produces(MediaType.TEXT_PLAIN)
        public String testA() {
    
            List l = template.query("select 1", new RowMapper() {
    
                @Override
                public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
    
                    return Integer.valueOf(1);
                }
            });
            return l.toString();
        }
    }
    According to my profiler does every single request generate 2 connections:
    - the first one does not execute any statement and is left open. Stacktrace:
    Code:
    java.sql.DriverManager.getConnection(String, Properties)
    org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriverManager(String, Properties)
    org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(Properties)
    org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnectionFromDriver(String, String)
    org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnection()
    org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSource)
    org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSource)
    org.springframework.jdbc.core.JdbcTemplate.execute(StatementCallback)
    org.springframework.jdbc.core.JdbcTemplate.query(String, ResultSetExtractor)
    org.springframework.jdbc.core.JdbcTemplate.query(String, RowMapper)
    TestResource.testA()
    java.lang.Thread.run()
    - the 2nd executes my statement and closes correctly. Stacktrace:
    Code:
    org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnection()
    org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSource)
    org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSource)
    org.springframework.jdbc.core.JdbcTemplate.execute(StatementCallback)
    org.springframework.jdbc.core.JdbcTemplate.query(String, ResultSetExtractor)
    org.springframework.jdbc.core.JdbcTemplate.query(String, RowMapper)
    TestResource.testA()
    java.lang.Thread.run()
    <del>it seems to be that problem is MySql related.</del>
    The same for oracle.

    any help would be greatly appreciated.
    thanks in advance
    Michael

    ps: this seems to be a spring and not a mysql issue since when i manage the connections by myself everything works as expected:
    Code:
        @Path("b")
        @GET
        @Produces(MediaType.TEXT_PLAIN)
        public String testB() {
    
            String ret = "";
            Connection con = null;
            PreparedStatement stmt = null;
            ResultSet rs = null;
            try {
                Class.forName("com.mysql.jdbc.Driver");
                Properties p = new Properties();
                p.put("user", "xxx");
                p.put("password", "xxx");
                con = DriverManager.getConnection("jdbc:mysql://xxx", p);
                stmt = con.prepareStatement("select 2");
                rs = stmt.executeQuery();
                while (rs.next()) {
                    ret += rs.getString(1);
                }
            } catch (Exception e) {
                ret += e.getMessage();
                e.printStackTrace();
            } finally {
                if (rs != null) {
                    try {
                        rs.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                if (stmt != null) {
                    try {
                        stmt.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                if (con != null) {
                    try {
                        con.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
            return ret;
        }
    ps2: step by step debugging did not help, as far as i can tell only one connection is actually created. euh!?

    ps3: here the exceptions i start to get after a while from my application:
    Code:
    The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
    	at sun.reflect.GeneratedConstructorAccessor76.newInstance(Unknown Source)
    	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
    	at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
    	at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
    	at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1122)
    	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2260)
    	at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:787)
    	at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:49)
    	at sun.reflect.GeneratedConstructorAccessor39.newInstance(Unknown Source)
    	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
    	at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
    	at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
    	at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:357)
    	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285)
    	at java.sql.DriverManager.getConnection(DriverManager.java:582)
    	at java.sql.DriverManager.getConnection(DriverManager.java:154)
    	at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriverManager(DriverManagerDataSource.java:174)
    	at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:165)
    	at org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnectionFromDriver(AbstractDriverBasedDataSource.java:149)
    	at org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnection(AbstractDriverBasedDataSource.java:119)
    	at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:113)
    	at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:79)
    	... 40 more
    Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
    
    The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
    	at sun.reflect.GeneratedConstructorAccessor76.newInstance(Unknown Source)
    	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
    	at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
    	at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
    	at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1122)
    	at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:344)
    	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2181)
    	... 56 more
    Caused by: java.net.SocketException: No buffer space available (maximum connections reached?): connect
    	at java.net.PlainSocketImpl.socketConnect(Native Method)
    	at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:351)
    	at java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:213)
    	at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:200)
    	at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:366)
    	at java.net.Socket.connect(Socket.java:529)
    	at java.net.Socket.connect(Socket.java:478)
    	at java.net.Socket.<init>(Socket.java:375)
    	at java.net.Socket.<init>(Socket.java:218)
    	at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:256)
    	at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:293)
    	... 57 more
    Last edited by mmoossen; Oct 14th, 2011, 04:51 AM.

  • #2
    at the end my problem was caused by this:
    http://support.microsoft.com/kb/196271

    despite i am using windows 7 where this problem should be already fixed.

    funnily my profiler (YourKit 10) still shows the open connections, but everything works as expected, so i suppose it is a profiler bug :s

    thanks for reading.

    Comment

    Working...
    X