Broken Pipe when logging in after idle

[factorial]

Hello,

I have inherited some Java code utilizing Spring with Spring Security and a MySQL db. Whenever the application hasn't been used for a while (specifically, longer than the wait_timeout value under [mysqld] in my.conf), the connection is lost between Spring and MySQL, and the next time someone tries to log in an exception is thrown (quoted below) and they are sent to /login.do?login_error. The next time the login is attempted, it succeeds.

Can someone tell me how to prevent this exception from being thrown (hopefully I can make Spring Security automagically reconnect to MySQL before executing its SQL statement)?

Here is my applicationContext-security.xml file

<?xml version="1.0" encoding="UTF-8"?>
<beans:beans xmlns="http://www.springframework.org/schema/security" xmlns:beans="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd
                        http://www.springframework.org/schema/security http://www.springframework.org/schema/security/spring-security-2.0.1.xsd">
	<global-method-security secured-annotations="enabled">
		<!-- AspectJ pointcut expression that locates our "post" method and applies security that way
			<protect-pointcut expression="execution(* bigbank.*Service.post*(..))" access="ROLE_TELLER"/>
		-->
	</global-method-security>
	<http auto-config="true" access-denied-page="/accessDenied.do">
		<intercept-url pattern="/admin/*.do" access="ROLE_PUBLISHER, ROLE_CONTRIBUTOR" /> 
		<intercept-url pattern="/log*.do" access="IS_AUTHENTICATED_ANONYMOUSLY" />
		<form-login login-page="/login.do" default-target-url="/wall/admin/adminManageProfiles.do" authentication-failure-url="/login.do?login_error"/>
		<logout logout-url="/logout.htm" logout-success-url="/logout.do" />
	</http>
	<authentication-provider user-service-ref="userService"></authentication-provider>
	<beans:bean id="dataSource1" class="org.apache.commons.dbcp.BasicDataSource">
		<beans:property name="driverClassName" value="com.mysql.jdbc.Driver" />
		<beans:property name="url" value="jdbc:mysql://localhost:3306/wall?autoReconnect=true" />
		<beans:property name="username" value="root" />
		<beans:property name="password" value="rootpassword" />	
		<beans:property name="maxIdle" value="10" />
	</beans:bean>
	<beans:bean id="userService" class="org.springframework.security.userdetails.jdbc.JdbcDaoImpl">
		<beans:property name="dataSource" ref="dataSource1" />
		<beans:property name="authoritiesByUsernameQuery">
			<beans:value>SELECT username, authority FROM users WHERE username = ?</beans:value>
		</beans:property>
	</beans:bean>
</beans:beans>

And here is the exception I get in login.jsp from session.getAttribute(AbstractProcessingFilter.SPRI NG_SECURITY_LAST_EXCEPTION_KEY).getMessage()

PreparedStatementCallback; SQL [SELECT username,password,enabled FROM users WHERE username = ?]; Communications link failure due to underlying exception:

** BEGIN NESTED EXCEPTION **

java.net.SocketException
MESSAGE: Broken pipe

STACKTRACE:

java.net.SocketException: Broken pipe
at java.net.SocketOutputStream.socketWrite0(Native Method)
at java.net.SocketOutputStream.socketWrite(SocketOutp utStream.java:109)
at java.net.SocketOutputStream.write(SocketOutputStre am.java:153)
at java.io.BufferedOutputStream.flushBuffer(BufferedO utputStream.java:82)
at java.io.BufferedOutputStream.flush(BufferedOutputS tream.java:140)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2637)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:15 54)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java :1665)
at com.mysql.jdbc.Connection.execSQL(Connection.java: 3176)
at com.mysql.jdbc.PreparedStatement.executeInternal(P reparedStatement.java:1153)
at com.mysql.jdbc.PreparedStatement.executeQuery(Prep aredStatement.java:1266)
at org.apache.commons.dbcp.DelegatingPreparedStatemen t.executeQuery(DelegatingPreparedStatement.java:93 )
at org.springframework.jdbc.core.JdbcTemplate$1.doInP reparedStatement(JdbcTemplate.java:648)
at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:591)
at org.springframework.jdbc.core.JdbcTemplate.query(J dbcTemplate.java:641)
at org.springframework.jdbc.core.JdbcTemplate.query(J dbcTemplate.java:666)
at org.springframework.jdbc.core.JdbcTemplate.query(J dbcTemplate.java:698)
at org.springframework.jdbc.object.SqlQuery.execute(S qlQuery.java:112)
at org.springframework.jdbc.object.SqlQuery.execute(S qlQuery.java:199)
at org.springframework.jdbc.object.SqlQuery.execute(S qlQuery.java:207)
at org.springframework.security.userdetails.jdbc.Jdbc DaoImpl.loadUsersByUsername(JdbcDaoImpl.java:214)
at org.springframework.security.userdetails.jdbc.Jdbc DaoImpl.loadUserByUsername(JdbcDaoImpl.java:175)
at org.springframework.security.providers.dao.DaoAuth enticationProvider.retrieveUser(DaoAuthenticationP rovider.java:83)
at org.springframework.security.providers.dao.Abstrac tUserDetailsAuthenticationProvider.authenticate(Ab stractUserDetailsAuthenticationProvider.java:121)
at org.springframework.security.providers.ProviderMan ager.doAuthentication(ProviderManager.java:188)
at org.springframework.security.AbstractAuthenticatio nManager.authenticate(AbstractAuthenticationManage r.java:46)
at org.springframework.security.ui.webapp.Authenticat ionProcessingFilter.attemptAuthentication(Authenti cationProcessingFilter.java:82)
at org.springframework.security.ui.AbstractProcessing Filter.doFilterHttp(AbstractProcessingFilter.java: 258)
at org.springframework.security.ui.SpringSecurityFilt er.doFilter(SpringSecurityFilter.java:53)
at org.springframework.security.util.FilterChainProxy $VirtualFilterChain.doFilter(FilterChainProxy.java :390)
at org.springframework.security.ui.logout.LogoutFilte r.doFilterHttp(LogoutFilter.java:89)
at org.springframework.security.ui.SpringSecurityFilt er.doFilter(SpringSecurityFilter.java:53)
at org.springframework.security.util.FilterChainProxy $VirtualFilterChain.doFilter(FilterChainProxy.java :390)
at org.springframework.security.context.HttpSessionCo ntextIntegrationFilter.doFilterHttp(HttpSessionCon textIntegrationFilter.java:235)
at org.springframework.security.ui.SpringSecurityFilt er.doFilter(SpringSecurityFilter.java:53)
at org.springframework.security.util.FilterChainProxy $VirtualFilterChain.doFilter(FilterChainProxy.java :390)
at org.springframework.security.util.FilterChainProxy .doFilter(FilterChainProxy.java:175)
at org.springframework.web.filter.DelegatingFilterPro xy.invokeDelegate(DelegatingFilterProxy.java:236)
at org.springframework.web.filter.DelegatingFilterPro xy.doFilter(DelegatingFilterProxy.java:167)
at org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invo ke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invo ke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke( StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke (ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invok e(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.servic e(CoyoteAdapter.java:286)
at org.apache.coyote.http11.Http11Processor.process(H ttp11Processor.java:845)
at org.apache.coyote.http11.Http11Protocol$Http11Conn ectionHandler.process(Http11Protocol.java:583)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run( JIoEndpoint.java:447)
at java.lang.Thread.run(Thread.java:636)


** END NESTED EXCEPTION **

[Luke Taylor]

That's not a Spring Security issue. You're using DBCP, so configure that and/or your JDBC driver appropriately to prevent or deal with timeouts.

[jmuchrisf]

I'm not so sure it's not a spring security issue, as I have the same problem. I'm using SS- 2.01, (I'll upgrade to 2.05 just to make sure it hasn't been fixed)..

I had the same issue with DBCP, and thought it was "it's" problem, but switched to C3P0 and have the same issue.

This is my datasource info:

<bean id="myDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
        <property name="driverClass" value="com.mysql.jdbc.Driver"/>
        <property name="jdbcUrl" value="jdbc:mysql://127.0.0.1:3306/dev_site"/>
        <property name="user" value="username"/>
        <property name="password" value="mypassword"/>
    </bean>

One thing that I just realized, and will test, is that I have all my min/max/timeout settings all done with the hibernate properties for the rest of my application... but SS doesn't use my hibernate session factory, so I think I need to move those settings to the datasource itself.

I'll update back.