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.

ACV
  • 9,964
  • 5
  • 76
  • 81
  • 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. – ACV Dec 26 '20 at 21:06
  • what is your connection type? – Perimosh Dec 29 '20 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` – ACV Dec 29 '20 at 16:10
  • is it embedded? if it is, is it hsqldb, h2 or derby? – Perimosh Dec 29 '20 at 17:50
  • @Perimosh it is `mysql` running on docker as mentioned in the question. It is not hsqldb, h2 or derby. – ACV Dec 29 '20 at 21:12
  • Ok then using create-drop is not good as it is designed for in memory/embedded dbs. – Perimosh Dec 29 '20 at 23:35
  • @Perimosh create drop is designed for creating and dropping the DB every time the app is restarted, regardless of type of DB. It is for development/testing purposes. – ACV Dec 30 '20 at 16:41
  • In your case it may fit OK because you have your db inside a container – Perimosh Dec 30 '20 at 17:12

3 Answers3

3

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.

achabahe
  • 2,445
  • 1
  • 12
  • 21
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.

Janos Vinceller
  • 1,208
  • 11
  • 25
  • 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 – ACV Dec 23 '20 at 12:45
  • 1
    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? – Janos Vinceller Dec 23 '20 at 22:04
  • 1
    Just another page trying to explain the use of `spring.jpa.hibernate.ddl-auto=create-drop` showing that tables get dropped at `SessionFactory#close()`: https://javausecase.com/2017/09/03/hibernate-5-hbm2ddl-auto-explained/ – Janos Vinceller Dec 26 '20 at 07:10
  • 1
    You may also check this: https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_wait_timeout – Janos Vinceller Dec 26 '20 at 07:44
  • 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 – ACV Dec 26 '20 at 21:17
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.

ACV
  • 9,964
  • 5
  • 76
  • 81