Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?)

like image 248
Roman Avatar asked Jan 16 '10 11:01

Roman


People also ask

What is connector J in MySQL?

MySQL Connector/J is the official JDBC driver for MySQL. MySQL Connector/J 8.0 is compatible with all MySQL versions starting with MySQL 5.6. Additionally, MySQL Connector/J 8.0 supports the new X DevAPI for development with MySQL Server 8.0. Online Documentation: MySQL Connector/J Installation Instructions.

What is autoReconnect true?

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.

Is MySQL connector required?

Although optional, the mysql-community-client-plugins package is required to use newer authentication methods, such as caching_sha2_password that's the default authentication method as of MySQL 8.0.


2 Answers

The text describes three solutions to prevent connection aborts:

  1. 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" 
  2. Increase the timeout. This is normally a property of the database. You can increase this value to see if you get less connection abort.

  3. 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.

    • Connection pooling options with JDBC: DBCP vs C3P0
    • http://snipplr.com/view/14725/c3p0-datasource-config-connectionpool/
    • http://www.mchange.com/projects/c3p0/index.html#configuring_connection_testing

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.
like image 111
ewernli Avatar answered Sep 26 '22 02:09

ewernli


AutoReconnect is not recommended. From MySQL here

Should the driver try to re-establish stale and/or dead connections? If enabled the driver will throw an exception for a queries issued on a stale or dead connection, which belong to the current transaction, but will attempt reconnect before the next query issued on the connection in a new transaction. 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. Alternatively, as a last option, investigate setting the MySQL server variable "wait_timeout" to a high value, rather than the default of 8 hours.

like image 23
Gaurav Agarwal Avatar answered Sep 23 '22 02:09

Gaurav Agarwal