Docker MySQL drops tables after timeout: No operations allowed after connection closed

user_912829 0
Viewed 723

A very weird situation. I am using Spring Boot with Spring Data JPA and MySQL running in a docker container. After starting the application all works fine (the DB is initialized upon startup with spring.jpa.hibernate.ddl-auto=create-drop).

If I leave the app running, after ~10 minutes, when running another request, I get back table doesn't exist. Checking the DB, I can see all the tables are gone (the schema is still there)!

Logs show me this WARN right before the error:

2020-12-20 16:15:41.151  WARN 11018 --- [nio-8080-exec-4] com.zaxxer.hikari.pool.PoolBase          : myDS - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@67dd33b2 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
2020-12-20 16:15:41.153  WARN 11018 --- [nio-8080-exec-4] com.zaxxer.hikari.pool.PoolBase          : myDS - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@3817c06d (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
2020-12-20 16:15:41.155  WARN 11018 --- [nio-8080-exec-4] com.zaxxer.hikari.pool.PoolBase          : myDS - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@536cd1b2 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.

And then:

2020-12-20 16:15:41.161  WARN 11018 --- [nio-8080-exec-4] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1146, SQLState: 42S02
2020-12-20 16:15:41.161 ERROR 11018 --- [nio-8080-exec-4] o.h.engine.jdbc.spi.SqlExceptionHelper   : Table 'tasker.account' doesn't exist
2020-12-20 16:15:41.173  INFO 11018 --- [nio-8080-exec-4] o.h.e.internal.DefaultLoadEventListener  : HHH000327: Error performing load command

I've also added this setting in application.properties:

spring.datasource.hikari.max-lifetime=600000 to match mysql's settings.

Docker is started with:

docker run --name=mysql1 --restart on-failure -d mysql/mysql-server:8.0

Note: This never happens with local instance of mysql (native, not in docker)

Any help would be appreciated.

spring-data-jpa spring-boot docker mysql java
3 Answers

create-drop will drop the schema upon closing the SessionFactory, now let's treat the part of SessionFactory closing , it can be related to network connections timeouting which leads to SessionFactory beeing closed automaticaly by springboot.

You can add a heartbeat to your application.properties to prevent this behaviour from occuring , any way this part has solved so many problems in my production env so it is very helpfull to add this bit:

#HeartBeat the database so that connections don't die otherwise the connections die silently
#and when a query commes along the JPA will throw an error and keep throwing errors and a restart of the process
#is inevitable
spring.datasource.testWhileIdle=true
spring.datasource.test-on-borrow=true
spring.datasource.validationQuery=SELECT 1

I will put here link to an answer that deals with checking connections and pool health Connection to Db dies after >4<24 in spring-boot jpa hibernate

An other thing since this is just a dev envrionement you can change create-drop to create which configures springboot to drop existing schema and create a new one at the startup of your springboot app this way the schema will never get destroyed when connection is lost.

user_53283780

Look at this answer:

How does spring.jpa.hibernate.ddl-auto property exactly work in Spring?

As you're using create-drop, your probably interested in the following section of his answer:

Typically in test case scenarios, you'll likely use create-drop so that you create your schema, your test case adds some mock data, you run your tests, and then during the test case cleanup, the schema objects are dropped, leaving an empty database.

user_34520580

The problem seemed to be in this setting:

spring.datasource.hikari.max-lifetime

Adding this setting in application.properties seems to have solved the problem: spring.datasource.hikari.max-lifetime=100000 while spring.datasource.hikari.max-lifetime=600000 which matches exactly the mySQL value, does not work - i.e. results in tables being dropped.

user_9128290
Related