Spring-Session with JDBC configuration: Table 'test.spring_session' doesn't exist

Question

I try to run this example but without using Redis, instead with my local MySQL server.

I have edited this spring boot app like this:

Gradle:

buildscript {
    repositories {
        mavenCentral()
    }
    dependencies {
        classpath("org.springframework.boot:spring-boot-gradle-plugin:$springBootVersion")
    }
}

apply plugin: 'spring-boot'

apply from: JAVA_GRADLE


//this 'if' statement is because I was getting error: Execution failed for task ':samples:findbyusername:findMainClass'.
//> Could not find property 'main' on task ':samples:findbyusername:run'.
if (!hasProperty('mainClass')) {
    ext.mainClass = 'sample.FindByUsernameApplication'
}

tasks.findByPath("artifactoryPublish")?.enabled = false

group = 'samples'

dependencies {
    compile("org.springframework.boot:spring-boot-starter-jdbc:$springBootVersion") 
    compile group: 'mysql', name: 'mysql-connector-java', version: '6.0.2'
    compile group: 'org.springframework.session', name: 'spring-session', version: '1.2.0.RELEASE'



    compile project(':spring-session'),
            "org.springframework.boot:spring-boot-starter-web",
            "org.springframework.boot:spring-boot-starter-thymeleaf",
            "nz.net.ultraq.thymeleaf:thymeleaf-layout-dialect",
            "org.springframework.security:spring-security-web:$springSecurityVersion",
            "org.springframework.security:spring-security-config:$springSecurityVersion",
            "com.maxmind.geoip2:geoip2:2.3.1",
            "org.apache.httpcomponents:httpclient"

    testCompile "org.springframework.boot:spring-boot-starter-test",
                "org.assertj:assertj-core:$assertjVersion"

    integrationTestCompile gebDependencies,
            "org.spockframework:spock-spring:$spockVersion"

}



def reservePort() {
    def socket = new ServerSocket(0)
    def result = socket.localPort
    socket.close()
    result
}

application.properties

spring.datasource.url = jdbc:mysql://localhost:3306/TEST?characterEncoding=UTF-8&useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
spring.datasource.driverClassName = com.mysql.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=but

spring.thymeleaf.cache=false
spring.template.cache=false

HttpSessionConfig.java

@EnableJdbcHttpSession // <1>
public class HttpSessionConfig {
}

Application starts on tomcat but when I hit localhost in my browser I get:

Whitelabel Error Page

This application has no explicit mapping for /error, so you are seeing this as a fallback.

Mon May 23 21:14:31 CEST 2016
There was an unexpected error (type=Internal Server Error, status=500).
PreparedStatementCallback; bad SQL grammar [INSERT INTO SPRING_SESSION(SESSION_ID, CREATION_TIME, LAST_ACCESS_TIME, MAX_INACTIVE_INTERVAL, PRINCIPAL_NAME) VALUES (?, ?, ?, ?, ?)]; nested exception is java.sql.SQLSyntaxErrorException: Table 'test.spring_session' doesn't exist

I don't remember reading anything about manually creating this table so I assumed that spring will handle it for me...

EDIT: I actually tried to manually create tables and then application runs OK. But I guess I shouldn't be doing this manually.

I think you have to create them manually. The docs here for example have a script to set up the tables in the embedded database that it's using: http://docs.spring.io/spring-session/docs/current/reference/html5/guides/httpsession-jdbc.html — PaulNUK, May 23 '16 at 23:23

score 16 · Accepted Answer · answered Jun 10 '16 at 06:08

Spring Session ships with database schema scripts for most major RDBMS's (located in org.springframework.session.jdbc package), but the creation of database tables for Spring Session JDBC supports needs to be taken care of by the users themselves.

The provided scripts can be used untouched, however some users may choose to modify them to fit their specific needs, using the provided scripts as a reference.

An option would be to use a database migration tool, such as Flyway, to handle the creation of database tables.

Since you're using Spring Boot, it might be of your interest that there is a pending PR to add support for automatic initialization of Spring Session JDBC schema: https://github.com/spring-projects/spring-boot/pull/5879

If the documentation misled you into thinking the tables should be created automatically by Spring Session itself, consider reporting the issue so we can update the documentation if necessary: https://github.com/spring-projects/spring-session/issues

I regularly forget this to come back here, only to say "Ha!" and go create my tables. — Hasan Can Saral, Mar 22 '17 at 07:20

score 12 · Answer 2 · edited Jul 09 '19 at 08:47

At least as of now, you don't have to create tables manually.

In my test, tables were created automatically after adding the following line into the file application.properties when this file appears like shown above.

spring.session.jdbc.initialize-schema=always

I found this beautiful line from the following stackoverflow page.

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'test.spring_session' doesn't exist - Spring Boot

I am sorry that I don't know if it was necessary to create tables manually in 2016 or 2017. I will update this answer when I get to know this or get to have some more fruitful related information. I am just wishing that nobody will be led to an idea that automatic creation of session tables is impossible with the lastest Spring Framework version of 2019 or later.

score 2 · Answer 3 · answered Mar 06 '21 at 04:11

2

spring.session.jdbc.initialize-schema=always worked for me

answered Mar 06 '21 at 04:11

Romia Mukherjee

41
4

a trusted solution is always a good answer but OP also need an advice – Willy satrio nugroho Mar 06 '21 at 05:40

Spring-Session with JDBC configuration: Table 'test.spring_session' doesn't exist

3 Answers3