4

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.

Improve this question
8
  • Adding this setting 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. Therefore it is a combination of this problem and create-drop. Commented Dec 26, 2020 at 21:06
  • what is your connection type? Commented Dec 29, 2020 at 14:27
  • @Perimosh connection type? Not sure what you mean exactly, but it's jdbc url: spring.datasource.url=jdbc:mysql://172.17.0.2:3306/tasker?useUnicode=yes&characterEncoding=UTF-8 Commented Dec 29, 2020 at 16:10
  • is it embedded? if it is, is it hsqldb, h2 or derby? Commented Dec 29, 2020 at 17:50
  • @Perimosh it is mysql running on docker as mentioned in the question. It is not hsqldb, h2 or derby. Commented Dec 29, 2020 at 21:12

3 Answers 3

Reset to default
3
+25

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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.

Improve this answer

5 Comments

Thanks, but this doesn't answer the question why the app is still running and the database gets erased after a timeout of 10 minutes. I am using create-drop for running the app in develop mode locally, it's not part of any automated tests. I start the app, it times out, the data disappears, but the app continues to run
The value create-drop gets passed to Hibernate, where it is understood as follows: "The database schema will be dropped and created afterward. Upon closing the SessionFactory, the database schema will be dropped." -- I would say your SessionFactory gets closed after a while. Can you put a breakpoint there and look if that happens?
Just another page trying to explain the use of spring.jpa.hibernate.ddl-auto=create-drop showing that tables get dropped at SessionFactory#close(): javausecase.com/2017/09/03/hibernate-5-hbm2ddl-auto-explained
You may also check this: dev.mysql.com/doc/refman/5.7/en/…
Please note that keeping the old timeout value and only changing create-drop to create doesn't solve the problem. The same happens - the tables get dropped
0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.