Java Spring JDBC SPRING_SESSION table doesn't exist

Question

I am developing a simple RESTfull service in Java Spring and using JDBCTemplate.

However, I am getting a run time error, which I don't understand. It complains about SPRING_SESSION table not existing, however I think Spring should be able to create necessary tables as needed.

application.properties:

spring.jpa.hibernate.ddl-auto=create
spring.datasource.url=jdbc:mysql://localhost:3306/getfit?useSSL=false
spring.datasource.username=root
spring.datasource.password=***

Exception:

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [DELETE FROM SPRING_SESSION WHERE EXPIRY_TIME < ?]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'getfit.SPRING_SESSION' doesn't exist
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:235) ~[spring-jdbc-5.0.4.RELEASE.jar:5.0.4.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) ~[spring-jdbc-5.0.4.RELEASE.jar:5.0.4.RELEASE]

Exception is triggered by some process attempting to delete record from table which doesn't exist.

Manually creating the table leads to more errors (not existing columns). Setting

hibernate.ddl-auto=update

also doesn't help.

Additionally, I have modified MySQL configuration to allow upper case characters and this also did not help.

Interestingly enough, this issue happened after I formatted my OS and cloned project from github. Before everything was working fine.

Do you have any ideas what could go wrong? Let me know if you need me to show you some code.

Thanks for looking in to this :)

Answer 1

try to set the initialize-schema to always:

spring.session.jdbc.initialize-schema: always

Answer 2

Spring session creates a table to store sessions in the database. Since the required table doesn't exist it throws the error.

Here's a link you can refer

https://sivalabs.in/2018/02/session-management-using-spring-session-jdbc-datastore/