All geek questions in one place

Spring 2.5 Failed to get JDBC connection

I am using spring 2.5 SimpleJdbcTemplate to access MySQL db. When I try to access the database too often (using Quartz to access it every minute), I get this stack trace:

org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (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 org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:82) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:382) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:458) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:466) at org.springframework.jdbc.core.JdbcTemplate.queryForList(JdbcTemplate.java:497) at org.springframework.jdbc.core.simple.SimpleJdbcTemplate.queryForList(SimpleJdbcTemplate.java:223) at com.db.timexis.dao.UserDaoJdbc.getListOfAllUsers(UserDaoJdbc.java:137) at com.db.timexis.service.AuthServiceImpl.getListOfAllUsers(AuthServiceImpl.java:77) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307) at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) at $Proxy0.getListOfAllUsers(Unknown Source) at com.mail.timexis.ReminderBean.execute(ReminderBean.java:25) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.springframework.util.MethodInvoker.invoke(MethodInvoker.java:276) at org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean$MethodInvokingJob.executeInternal(MethodInvokingJobDetailFactoryBean.java:260) at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:86) at org.quartz.core.JobRunShell.run(JobRunShell.java:216) at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:549) Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (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 org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1225) at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880) at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:113) at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:79) ... 30 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.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39) 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.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39) 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 org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38) at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:294) at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1247) at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1221) ... 33 more Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The driver was unable to create a connection due to an inability to establish the client portion of a socket. This is usually caused by a limit on the number of sockets imposed by the operating system. This limit is usually configurable. For Unix-based platforms, see the manual page for the 'ulimit' command. Kernel or system reconfiguration may also be required. For Windows-based platforms, see Microsoft Knowledge Base Article 196271 (Q196271). at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39) 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) ... 46 more Caused by: java.net.BindException: Address already in use: connect at java.net.PlainSocketImpl.socketConnect(Native Method) at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:333) at java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:195) at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:182) at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:366) at java.net.Socket.connect(Socket.java:519) at java.net.Socket.connect(Socket.java:469) at java.net.Socket.<init>(Socket.java:366) at java.net.Socket.<init>(Socket.java:209) at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:256) at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:293) ... 47 more

Code executed every minute:

 public void execute() { List<User> regularUsers = null; try { // get all regular users ApplicationContext ctx = new ClassPathXmlApplicationContext( "applicationContext.xml"); AuthService authService = (AuthService) ctx.getBean("authService"); regularUsers = authService.getListOfAllUsers(); } catch (Exception e) { e.printStackTrace(); } try { if (regularUsers != null) { for (User u : regularUsers) { if (u.getEmail().matches("^[_A-Za-z0-9-]+(\\.[_A-Za-z0-9-]+)*@[A-Za-z0-9-]+(\\.[A-Za-z0-9-]+)*(\\.[_A-Za-z0-9-]+)")) { mailSender.sendMailReminder(u.getEmail(), u.getFirstName()); // log mail sending Date date = Calendar.getInstance().getTime(); SimpleDateFormat sdf = new SimpleDateFormat( "yyyy/MM/dd HH:mm:ss"); String strDate = sdf.format(date); System.out.println(strDate + "Reminder mail send..."); // log mail sending }else{ System.out.println("invalid mail address"); } } } } catch (Exception e) { e.printStackTrace(); } }

Runs every minute for testing. It will be performed once a week in the future.

My DBCP properties:

 jdbc.maxActive=15 jdbc.maxIdle=10 jdbc.minIdle=5 jdbc.maxWait=5000 jdbc.validationQuery=select 1 jdbc.timeBetweenEvictionRunsMillis=1000 jdbc.numTestsPerEvictionRun=10 jdbc.minEvictableIdleTimeMillis=1200000

The database is accessible through the command line.

+4
source share
2 answers

Please note that you create a new instance of the application context each time you call the method and do not close it. This means that resources allocated by beans decowed in this context (for example, connections created by the connection pool) are not freed.

As a rule, you need to create the application context only once during the launch of your application and close it at the end of the work, so I suggest you implement it this way.

If you need to create a new application context every time you call this method, don't forget to close() it at the end of the method (in the finally block for reliability).

Also note that you must declare a suitable destroy-method for the bean connection pool to free resources when the context is closed.

+6
source

The main exception is "java.net.BindException: address is already in use: connect". It seems you have a connection pool leak. Are you sure each connection closes after use? Please, check him.

+1
source

Source: https://habr.com/ru/post/1393786/

More articles:


All Articles