You can ignore my last email or read through if you encounter similar problems and need a fix. I just found out from the MySQL J/Connector documentation that connections are cut off after 8 hours of inactivity.
---- "I have a servlet/application that works fine for a day, and then stops working overnight". Resolution: MySQL closes connections after 8 hours of inactivity. You either need to use a connection pool that handles stale connections or use the "autoReconnect" parameter (see "USAGE AND INSTALLATION"). [...] ---- I guess (from my bad experience) that the Struts connection pool can't handle stale connections so I just added "autoReconnect" to my struts-config.xml. I hope this fix keeps my webapp work overnight this time... Tomcat's JDBCRealm seems to handle stale connections automatically... Maybe this would be a nice to have thing for the Struts connection pool, too?! BTW: You cannot use "set-property" for those special properties like "autoReconnect" or "profileSql". They have to be in the connectionURL like this (I tried it differently with "profileSql" but nothing happend on stderr): Use instead similar to: <set-property property="url" value="jdbc:mysql://localhost:3307/praksis?autoReconnect=true&maxRec onnects=5&initialTimeout=2&profileSql=true"/> Thanks anyway. Michael -----Original Message----- From: mech [mailto:[EMAIL PROTECTED]] Sent: Freitag, 17. Januar 2003 12:52 To: '[EMAIL PROTECTED]' Subject: Problem with JDBC & Struts Connection Pool (possible to reconnect?) Hi, I'm running Tomcat 4.1.18 on Solaris 9 with a local MySQL 3.23.54-max (InnoDB) database on port 3307 using started with mysqld (not safemysld...difference btw?). After not using any connection over night for let's say 12 hours or more, I get SQLExceptions next morning: "java.sql.SQLException: Communication link failure: java.net.SocketException" In my application I use JDBC for Tomcats security realm feature and form-based auth. When I try to login for the first time, I get this error log (see below), but if I try to login twice the db connection seems to get reset automatically. (A solution I could live with, although I have now idea why the connection was cut while it should be idle overnight) Unfortunatelly my struts connection pool that I use in my application's data access classes doesn't get reinitialized after such a SocketException (see below). The only solution so far was to reload or stop/start the webapp with the Tomcat manager. Bad solution.... In my struts-config.xml I already have something to reconnect (see below), but it obviously didn't do the trick with my "mysql-connector-java-2.0.14.jar" J/Connector. I simply lost the connection somehow... Can anyone give my an hint what happens to my MySQL connections? I don't seem to have a problem if I send a query from time to time, but if I wait too long and do nothing, I'm cut off... :-( In case there's no way to stop this, is there a solution how to tell my application (a java code snippet would be great...) to reinitialize the Struts connection pool when this exception is thrown. For Tomcat's JDBCRealm it is done somehow, how can I reset the MySQL connection for Struts? I think I could live with a reconnect on the second try for a query. Thanks you! Michael ------ <data-sources> <data-source> <set-property property="driverClass" value="com.mysql.jdbc.Driver"/> <set-property property="url" value="jdbc:mysql://localhost:3307/praksis"/> <set-property property="maxCount" value="25"/> <set-property property="minCount" value="5"/> <set-property property="user" value="praksis"/> <set-property property="password" value="siskarp"/> <set-property property="autoReconnect" value="true"/> <set-property property="maxReconnects" value="3"/> <set-property property="initialTimeout" value="2"/> <set-property property="profileSql" value="true"/> </data-source> </data-sources> EXCEPTION from log file: (first time to try a login and utilize the connection again after long idle time) 2003-01-17 12:28:55 JDBCRealm[/praksis]: Exception performing authentication java.sql.SQLException: Communication link failure: java.net.SocketException at com.mysql.jdbc.MysqlIO.sendCommand(Unknown Source) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(Unknown Source) at com.mysql.jdbc.Connection.execSQL(Unknown Source) at com.mysql.jdbc.PreparedStatement.executeQuery(Unknown Source) at org.apache.catalina.realm.JDBCRealm.authenticate(JDBCRealm.java:445) at org.apache.catalina.realm.JDBCRealm.authenticate(JDBCRealm.java:394) at org.apache.catalina.authenticator.FormAuthenticator.authenticate(FormAut henticator.java:263) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(Authenticator Base.java:480) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.i nvokeNext(StandardPipeline.java:641) at org.apache.catalina.valves.CertificatesValve.invoke(CertificatesValve.ja va:246) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.i nvokeNext(StandardPipeline.java:641) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:4 80) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995) at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:241 5) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java :180) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.i nvokeNext(StandardPipeline.java:643) at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherVa lve.java:170) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.i nvokeNext(StandardPipeline.java:641) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java :172) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.i nvokeNext(StandardPipeline.java:641) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:4 80) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve. java:174) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.i nvokeNext(StandardPipeline.java:643) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:4 80) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995) at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:223) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:43 2) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processC onnection(Http11Protocol.java:386) at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:53 4) at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool .java:530) at java.lang.Thread.run(Thread.java:536) (For the JDBCRealm Tomcat seems to reconnect, so authentification works on the second try again...) 2003-01-17 12:29:00 JDBCRealm[/praksis]: Username mech@praksis successfully authenticated EXCEPTION debug print from own application (This is on of my data access class where I do my queries using the Struts connection pool): Class: praksis.ormapping.TopicDAO Error: Communication link failure: java.net.SocketException (Unfortunately, my struts connection does not get reinitialized after such an error... How can I do it?) -- To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
