"java.sql.SQLException: Communication link failure" when using Hibernate, Tomcat and MySQL with Spring

I have recently obtained an error after leaving my Hibernate/Tomcat/MySQL web-application running overnight (>8 hours) with the following root-cause :

Root Cause: java.sql.SQLException: Communication link failure: java.net.SocketException, underlying cause: Software caused connection abort: recv failed

Situation: When first deployed the application works fine. However, when the application is left running for a long time (eg > 8 hours) without any interactions, the connection to the MySQL database is lost and the web-app throws an exception. The full stacktrace of that exception is below, for those interested. I am using Tomcat 6.0.18 and MySQL 5.0.27-community-nt.

Aside: It is perhaps also worth noting that another computer with the same code-base does NOT throw this error - even though the MySQL time-out settings are the same. The main difference appears to be that the other computer is running Tomcat 6.0.14 and MySQL 5.0.18-nt so it is possible that these versions don't require the fix below - but I haven't confirmed that it isn't some other minor difference.

Background:

This is a fairly common problem, but a range of solutions presented elsewhere on the internet didn't fix my problem - and they aren't documented together anywhere I could find - so I'm recording some of these here, along with the one that finally worked for me.

How to test:

The first thing to note is that this is caused by MySQL closing down connections that are idle for a period of time. One fix is to just drastically increase the "Interactive timeout" and "Wait timeout" in the my.ini file that is read on startup in MySQL. Don't forget to restart MySQL each time you change these (and to stop Tomcat before restarting MySQL). A good way to test if this timeout is really your problem is to change these to a short value (eg 40) (that is, 40 seconds) and wait to see if the error occurs in a minute or so. That way you don't have to wait overnight to test whether changes fix your problem.

Proposed Solutions:

NB: Only one of these worked (or was deemed "acceptable") for my case - and that one is the last one, but I record them all here as I couldn't find one location with all these summarized.

1. One solution is to just increase the MySQL Timeout settings as described in the "testing" section above. However, while this fixed one application I was working on, in one (different) case I found that the error was occurring overnight even if I set this to a very large value (eg 604800 sec). In any case, just increasing this value isn't a great solution as you don't want the error to occur ever - so just stalling it for a week isn't great!

2. Another solution that I didn't like, but should mention, is to put a try/catch around the Java code that accesses the database. This isn't really a hibernate solution, but I thought I'd mention it. See the suggestions at the following URL if you want to try this. It's inelegant if you're using Hibernate, but might be good if you are doing your connections manually.
http://forums.mysql.com/read.php?39,55337,139123#msg-139123

3. Another suggestion that I found on the web that didn't work - but might for similar problems - is to put the following into your hibernate config file:

<property name="connection.autoReconnect">true</property>
<property name="connection.autoReconnectForPools">true</property>
<property name="connection.is-connection-validation-required">true</property>

4. A related suggestion is to add autoReconnect=true to jdbc URL. I did try this and it didn't work for me, but it might be worth considering.

5. Another suggestion was to set the C3P0 connection-pooling properties as follows:
<session-configuration>
<property name=“hibernate.c3p0.acquire_increment">3</property>
<property name=“hibernate.c3p0.idle_test_period">14400</property>
<property name=“hibernate.c3p0.timeout">25200</property>
<property name=“hibernate.c3p0.max_size">15</property>
<property name=“hibernate.c3p0.min_size">3</property>
<property name=“hibernate.c3p0.max_statements">0</property>
<property name=“hibernate.c3p0.preferredTestQuery">select 1</property>
</session-configuration>

See https://www.hibernate.org/214.html for more details on C3PO config. This would set C3PO pooling values in the configuration - in particular setting the idle_test_period and timeout properties to be less than the values set for MySQL timeout (see 1 - I had the MySQL timeout set to 691200, so it was much longer). In fact, even before experiencing this problem I did actually already have these values set and yet the error still occurred! Still, this may be necessary /fix the problem for some people and I am still using these settings, above.

Aside: A similar set of properties for dbcp is given at https://forum.hibernate.org/viewtopic.php?f=1&t=933088&view=previous.

6. Solution (Actually worked for me): The final change that actually worked was to modify the Spring data-source to be:

<bean id="dataSource"
class="com.mchange.v2.c3p0.ComboPooledDataSource" abstract="false"
lazy-init="default" autowire="default" dependency-check="default"
destroy-method="close">
<property name="driverClass"> <value>com.mysql.jdbc.Driver</value> </property>
<property name="jdbcUrl"><value>jdbc:mysql://localhost/myApp</value></property>
<property name="user"> <value>myUser</value></property>
<property name="password"><value>myPassword</value></property>
<property name="autoCommitOnClose"> <value>true</value></property>
<property name="idleConnectionTestPeriod"><value>15</value> </property>
<property name="maxIdleTime"> <value>15</value> </property>
</bean>

The key properties here are to set the idleConnectionTestPeriod and maxIdleTime to values that are less than the mySQL timeout values mentioned in (1) above. Setting these two new properties for the datasource in applicationContext-jdbc.xml file in my Spring config settings fixed the problem.



More StackTrace Info for Error:

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.TransactionException: JDBC begin failed:
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:408)
org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:350)
javax.servlet.http.HttpServlet.service(HttpServlet.java:690)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:174)
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:77)
jcifs.http.CustomNtlmFilter.doFilter(CustomNtlmFilter.java:111)

root cause:

java.sql.SQLException: Communication link failure: java.net.SocketException, underlying cause: Software caused connection abort: recv failed** BEGIN NESTED EXCEPTION ** java.net.SocketExceptionMESSAGE: Software caused connection abort: recv failedSTACKTRACE:java.net.SocketException: Software caused connection abort: recv failed at java.net.SocketInputStream.socketRead0(Native Method) at java.net.SocketInputStream.read(Unknown Source) at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1391) at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:1538) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1929) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1167) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1278) at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:1224) at com.mysql.jdbc.Connection.execSQL(Connection.java:2248) at com.mysql.jdbc.Connection.execSQL(Connection.java:2208) at com.mysql.jdbc.Connection.execSQL(Connection.java:2189) at com.mysql.jdbc.Connection.setAutoCommit(Connection.java:546) at com.mchange.v2.c3p0.impl.NewProxyConnection.setAutoCommit(NewProxyConnection.java:756) at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:63) at org.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:1326) at org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:496) at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:322) at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:255) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:102) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:176) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:210)

java.lang.UnsupportedClassVersionError: Bad version number in .class file

I was recently deploying a webApp in Eclipse (MyEclipse) using Tomcat when suddenly I got a UnsupportedClassVersionError:

java.lang.UnsupportedClassVersionError: Bad version number in .class file
at java.lang.ClassLoader.defineClass1(Native Method)
at java.lang.ClassLoader.defineClass(Unknown Source)
at java.security.SecureClassLoader.defineClass(Unknown Source)
at org.apache.catalina.loader.WebappClassLoader.findClassInternal(WebappClassLoader.java:1817)
at org.apache.catalina.loader.WebappClassLoader.findClass(WebappClassLoader.java:872)
at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1325)
at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1204)
at org.apache.catalina.startup.WebAnnotationSet.loadClassAnnotation(WebAnnotationSet.java:145)
at org.apache.catalina.startup.WebAnnotationSet.loadApplicationListenerAnnotations(WebAnnotationSet.java:73)
at org.apache.catalina.startup.WebAnnotationSet.loadApplicationAnnotations(WebAnnotationSet.java:56)
at org.apache.catalina.startup.ContextConfig.applicationAnnotationsConfig(ContextConfig.java:297)
at org.apache.catalina.startup.ContextConfig.start(ContextConfig.java:1064)
at org.apache.catalina.startup.ContextConfig.lifecycleEvent(ContextConfig.java:261)
at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:117)
at org.apache.catalina.core.StandardContext.start(StandardContext.java:4236)
at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:791)
at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:771)
at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:525)
at org.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java:920)
at org.apache.catalina.startup.HostConfig.deployDirectories(HostConfig.java:883)
at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:492)
at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1138)
at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:311)
at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:117)
at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1053)
at org.apache.catalina.core.StandardHost.start(StandardHost.java:719)
at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1045)
at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:443)
at org.apache.catalina.core.StandardService.start(StandardService.java:516)
at org.apache.catalina.core.StandardServer.start(StandardServer.java:710)
at org.apache.catalina.startup.Catalina.start(Catalina.java:566)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:288)
at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:413)


So my initial thought was "ok, a class is not loaded, but which one???".

Well - it turns out that this information would probably have led me down the wrong path anyway... and you don't need it (so don't get frustrated with whoever created that error message!).

The problem occurs, generally, when you compile a .java file with one version of JDK and then run the (compiled) .class with a different version of the Java Virtual Machine.

So... how was I doing this? Well - the .class file is being run within Tomcat and the .java file is being compiled by the preferred java compiler set in myEclipse. Ideally, these two versions would be the same - however they can be quite different. This won't be a problem AS LONG AS the Tomcat Java version (ie the Java running the .class files) is not an older version than the Java version used to compile the .class file from .java. It turns out that I had accidently set the Tomcat Java version to be an older one than the JAVA version being used to compile the code.

In MyEclipse:

Tomcat Java Version is set by:

• MyEclipse Tomcat[*] -> Configure -> Jdk -> Tomcat JDK Name

MyEclipse Compiler Java Version is set by:

• Project-> Properties -> Java Compiler -> Compiler Compliance Level
• Window -> Preferences -> Java -> Installed JREs -> Selected JRE

So, all I had to do to fix this was to set both the Tomcat JDK Name and Selected JRE to jre1.6.0_05 and compiler compliance to 1.6 and I'd fixed the problem.

[*] or whichever Tomcat you are using...

YouTube - JavaScript Turned off or an old version of Adobe Flash Player - Bug

Earlier today I was sent a link for a YouTube video, however when I went to the link I received the error message:

"Hello, you either have JavaScript turned off or an old version of Adobe's Flash Player. Get the latest Flash player. "

I have the latest Flash player (and, indeed, did follow the link and upgrade it just to make sure). However, this kept occurring for a number of YouTube videoes I then tried to watch as a test.

A bit of investigation indicated a huge range of suggestions for how to fix this - everything from people claiming it is a bug with YouTube and "YouTube Engineers are working on it" through to those advising turning off various bits of your Internet Explorer security settings (such as any "Add blockers" etc) and deleting temporary internet files and cookies.

Given that these fixes didn't work for me - and I don't want to leave my browser in an unsafe state with popups etc, anyway - I changed all these settings back to their usual state.

SOLUTION (Well, almost!):

I did, however, come up with a way to watch the video.

Now, to be honest, I haven't actually fixed the problem - when I go to the original URL I get the same error message still - but, on the positive side, I can watch the YouTube video (which, after all was the point). To do this, you just need to modify the URL you are using slightly.

In this particular case (as embarrassing as this may be - I didn't choose this video), my friend wanted me to view the link:

http://www.youtube.com/watch?v=AbAX9A8Cq0k

This didn't (and still doesn't at the moment of writing this) work for me. However, if you modify the link to:

• remove the words "watch?"
• replace "=" with "/"

I obtain a new URL that does work:

http://www.youtube.com/v/AbAX9A8Cq0k

So, the good news is this worked. The bad news is that had I known what the link was for I wouldn't have wasted the time :-) Ok - so maybe it isn't that bad?!?

Anyway, music taste aside, the good news is that this works on all the YouTube files I've tried so far. They're all giving me this same error message but, if I really wanted to, I could watch them by changing the URL manually...

So... it isn't a long term fix - but maybe this is a good start for others if the problem is with something at YouTube's end, or if you're just in a real hurry to watch that link you've been sent and don't wanna spend days re-installing Adobe products and Virus scanners!!!

Booleans in Hibernate

As the following helpful article (http://www.databasesandlife.com/hibernate-boolean-fields-mysql-50/) points out:

"There's a problem persisting boolean fields using Hibernate 3.2.2 to MySQL 5.0, if you allow Hibernate to generate your schema and you leave Hibernate to generate the schema in the default way". The problem is, by default, it generates a MqSQL field of type "bit(1)" - which causes Hibernate to throw:

could not insert: [com.company.MyObject] org.hibernate.exception.DataException: could not insert: [com.company.MyObject] at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:77) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) .... Caused by: java.sql.SQLException: Data too long for column 'my_field' at row 1 at ....

When I try to save.

That's causing my tests to fail… so what to do?

Well the article I mention above suggests mapping the field using

<column sql-type="BOOLEAN" />,

which does work.

However, I'm using annotations, so my preferred mapping for booleans is:

@Column(columnDefinition = "tinyint", nullable = false)
private boolean exampleBooleanValue;

An alternative is to use:

@Column
private Boolean exampleBooleanValue;

But I prefer to work with primitives rather than boxed primitives wherever possible.
Anyway, the first solution above works wonderfully.

Log4J Warnings

Annoyingly, when I run a junit test that is testing my Hibernate DAO objects, I get the following warnings:

log4j:WARN No appenders could be found for logger (jpt.dao.impl.test.GenericDaoImplTest). log4j:WARN Please initialize the log4j system properly.

This shouldn't be surprising, it's saying that Log4j hasn't been initialized properly. Why not? Well, It can't find my log4j.properties file. That's because hibernate needs it to be in the class path. So, I need to put log4j.properties into the classpath.

Well that's fine, and doing so does fix the problem - I just put log4j.properties into my src folder (or, in this case, specifically my test-src folder).

However, the problem is that I already have log4j.properties in the WebRoot/WEB-INF folder. When running my web-app (not the tests, but the web app itself), hibernate knows to look for it there because web.xml has:


<context-param>
<param-name>log4jConfigLocation</PARAM-NAME>
<param-value>/WEB-INF/log4j.properties</PARAM-VALUE>
</CONTEXT-PARAM>


What's more, that's exactly where I want my log4j.properties to be. Sure, I could change this to point somewhere else, but I don't want to do that. I want log4j.properties to be located in this directory for my web-app and configured just so.

So… the question is, how do I get Junit to initialize using this log4j.properties, rather than looking into src (on the classpath) and finding it missing. Well, I haven't been able to do it yet. I can manually configure log4j in my test class - but that's after the warnings are already shown. The only way I've been able to avoid those warnings is by placing log4j.properties into the classpath.
So, in the end, I've now got two versions of log4j.properties. One is in test-src and defines the logging levels etc for the debug running. One is in WebRoot/WEB-INF and defines logging levels for the webapp.

This is fine - and some might even say the best of both worlds - because I can have different default logging for my tests and my web-app. However, I still don't really like it. It could be confusing having two files named log4j.properites in the one project. What if I want to have only one!

Surely it shouldn't be THAT hard to configure my Junit test runs (that extend AbstractTransactionalDataSourceSprignContextTests) so that they search for the log4j.properties in the web-inf directory rather than on the classpath?!?!?!? Any ideas? (By the way, trying to add web-inf to the classpath doesn't work, as I'm using MyEclipse and it doesn't allow this!)

Hibernate "PreInsertEvent.getSource()" NoSuchMethodError.

I recently obtained the following error:

java.lang.NoSuchMethodError: org.hibernate.event.PreInsertEvent.getSource()Lorg/hibernate/engine/SessionImplementor;

This occurred when attempting to run a test on one of my Hibernate DAO entities.

The problem started when I updated some (but not all) of my hibernate .jar libraries, to enable the use of spring-test.jar to provide access to the helper org.springframework.test.AbstractTransactionalDataSourceSpringContextTests methods.

It turns out (http://opensource.atlassian.com/projects/hibernate/browse/HV-67) that the problem was:

Method getSource from an older version of the Hibernate Core is referenced.

It is stated within the compatibility matrix that Hibernate Validator 3.1.0.CR1 is compatible only with Hibernate Core 3.3.x version(s).Exception in thread "main" java.lang.NoSuchMethodError: org.hibernate.event.PreInsertEvent.getSource()Lorg/hibernate/engine/SessionImplementor;

That is, I was using the latest Hibernate Core (3.3.x), but didn't have the most up to date Hibernate Validator. I downloaded a new hibernate-validator.jar and everything worked again.

Problems with @Test annotations with AbstractTransactionalDataSourceSpringContextTests.

I've recently been writing tests, most of which use the Junit @Test (http://junit.org/apidocs/org/junit/Test.html) annotations to identify the methods for testing as junit tests. This works really well, and also allows me to use other great annotations like @Before.

However I decided I needed to do some integration testing of my DAOs with the database. So, to do this, I took advantage of Spring's AbstractTransactionalDataSourceSpringContextTests class (http://static.springframework.org/spring/docs/2.0.x/api/index.html?org/springframework/test/AbstractTransactionalDataSourceSpringContextTests.html).

This is great, however, I noticed that all of my test annotations for classes extended AbstractTransactionalDataSourceSpringContextTests were being ignored. This means that tests that were named "testExample" would run, but those named "doTestExample" would not - even if annotated. More to the point, something called "testExample" would run as a test even if not annotated.

The reason soon became clear. AbstractTransactionalDataSourceSpringContextTests extends the Junit.Framework.TestCase class. Now JUnit4 and JUnit3 tests really should not be mixed and they don't go together. So, because I'm extending TestCase (although I don't really want to), I need to go back to old fashioned Junit Tests, without test annotations. This is commented, briefly in:
http://www.jetbrains.net/jira/browse/IDEA-16466