Spring boot catch exception to autoReconnect MYSQL

Question

I have a problem with an automatic connection to MySQL. I set up already options:

spring.datasource.url = jdbc:mysql://localhost/dbname?autoReconnect=true
spring.datasource.username = root
spring.datasource.password = xxx
spring.datasource.driver-class-name = com.mysql.jdbc.Driver
spring.datasource.testOnBorrow = true
spring.datasource.validationQuery = SELECT 1

I got this error:

This application has no explicit mapping for / error, so you are seeing this as a fallback.
Mon Aug 29, 2016 2:44:10 p.m. EDT
There was an unexpected error (type = Internal Server Error, status = 500).
PreparedStatementCallback; SQL [main_cat SELECT FROM WHERE category_mapping cat =?]; No operations allowed after connection closed .; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.

These options, however, do not cause reconnect. I would do it in a little different way. Namely, I want to catch this exception and restart connection to MySQL.

application.properties:

spring.datasource.url = jdbc: mysql://localhost/dbname?autoReconnect=true
spring.datasource.username = root
spring.datasource.password = zxxxx
spring.datasource.driver-class-name = com.mysql.jdbc.Driver
spring.datasource.testOnBorrow = true
spring.datasource.validationQuery = SELECT 1

CategoryRepository (here I would like to catch an exception):

Public Optional <Long> findMainCategory (Long category) {
        String sql = "SELECT FROM main_cat category_mapping WHERE cat =?";
        return Optional.ofNullable (
                jdbcTemplate.queryForObject (sql, Long.class, category));
    }

How can I do this? Any suggestions ?

I'm counting on your help.

Please add the full stacktrace. Also I'm tempted to close it as a duplicate of http://stackoverflow.com/questions/30451470/connection-to-db-dies-after-424-in-spring-boot-jpa-hibernate/30455408#30455408 . — M. Deinum, Aug 30 '16 at 10:19
I`v not got stacktrace. I have only this error. And its not a duplicate because I Try use that all options in your link but it didnt work. I want to catch exception and do manually reconnect — rad11, Aug 30 '16 at 10:21
You cannot manually reconnect as that depends on your connection pool. I also doubt you really tried all the options (as there are several lacking from your properties). And there is a stacktrace, check your console NOT the web frontend. — M. Deinum, Aug 30 '16 at 10:23

score 0 · Answer 1 · answered Aug 30 '16 at 12:11

Try to execute your SQL

SELECT FROM main_cat category_mapping WHERE cat = some_value

from MySQL command prompt or MySQL workbench. I guess, you have an error in query.

Try to find

conn.close();

in connection initialization method. Probably, your jdbcTemplate using closed connection.

Spring boot catch exception to autoReconnect MYSQL

1 Answers1