Java – Connection with MySql is being aborted automatically. How to configure Connector/J properly

connection-timeoutjavajpaMySQLspring

I read this advice from error message:

You should consider either expiring
and/or testing connection validity
before use in your application,
increasing the server configured
values for client timeouts, or using
the Connector/J connection property
'autoReconnect=true' to avoid this
problem.

I'm using Spring and JPA. Where should I configure Connector/J? (in persistence.xml, or in entityManagerFactory spring configuration, or in dateSource spring configuration, or somewhere else?)

Best Answer

The text describes three solutions to prevent connection aborts:

Configure the connection string with autoReconnect=true. This is a property of the URL connection string, which works at the driver level. You need to change the connection string in the data source configuration.
```
url="jdbc:mysql://localhost:3306/confluence?autoReconnect=true"
```
Increase the timeout. This is normally a property of the database. You can increase this value to see if you get less connection abort.
Configure the connection pool to test the connection validatiy. This is done at the pool, not a the driver level. This will depend on the data source implementation that you use. But it should be configurable in the property of the data source, if you use a pooled one, e.g. c3p0.

Additionnal comments:

The datasource/pool can also have a timeout, which corresponds to the time an idle connection remains in the pool. To not confused with the db timeout.
There are several way to test the validity of a connection. One common way is to have dummy test table. The pool will issue a select on the dummy test table to see if the connection is still OK.

Related Solutions

Spring Boot JPA – configuring auto reconnect

I assume that boot is configuring the DataSource for you. In this case, and since you are using MySQL, you can add the following to your application.properties up to 1.3

spring.datasource.testOnBorrow=true
spring.datasource.validationQuery=SELECT 1

As djxak noted in the comment, 1.4+ defines specific namespaces for the four connections pools Spring Boot supports: tomcat, hikari, dbcp, dbcp2 (dbcp is deprecated as of 1.5). You need to check which connection pool you are using and check if that feature is supported. The example above was for tomcat so you'd have to write it as follows in 1.4+:

spring.datasource.tomcat.testOnBorrow=true 
spring.datasource.tomcat.validationQuery=SELECT 1

Note that the use of autoReconnect is not recommended:

The use of this feature is not recommended, because it has side effects related to session state and data consistency when applications don't handle SQLExceptions properly, and is only designed to be used when you are unable to configure your application to handle SQLExceptions resulting from dead and stale connections properly.

C# – How to add custom ExpectedConditions for Selenium

An "expected condition" is nothing more than an anonymous method using a lambda expression. These have become a staple of .NET development since .NET 3.0, especially with the release of LINQ. Since the vast majority of .NET developers are comfortable with the C# lambda syntax, the WebDriver .NET bindings' ExpectedConditions implementation only has a few methods.

Creating a wait like you're asking for would look something like this:

WebDriverWait wait = new WebDriverWait(driver, TimeSpan.FromSeconds(10));
wait.Until<IWebElement>((d) =>
{
    IWebElement element = d.FindElement(By.Id("myid"));
    if (element.Displayed &&
        element.Enabled &&
        element.GetAttribute("aria-disabled") == null)
    {
        return element;
    }

    return null;
});

If you're not experienced with this construct, I would recommend becoming so. It is only likely to become more prevalent in future versions of .NET.

Best Answer

Related Solutions

Spring Boot JPA – configuring auto reconnect

C# – How to add custom ExpectedConditions for Selenium

Related Topic