How to initialize schema in spring-session with JDBC

Question

I'm using Spring Boot 2.0.x, Hibernate and Spring Session Jdbc with Mysql 5.7. I'm working under development environment, so Hibernate is configured to generate schemas every time:

spring.jpa.hibernate.ddl-auto=create-drop

And it works fine, but I have a problem with Spring Session... I tried set initialize-schema, but it doesn't work.

spring.session.jdbc.initialize-schema=always

Is it possible to auto generate full schema (all entities and SPRING_SESSION)?

It doesn't work for me with MySQL and H2 (I tried embedded option)

Did you ever find a solution to this? I'm using 2.2.7.RELEASE with `spring.session.jdbc.initialize-schema=always` and `spring.session.store-type=jdbc` and the tables aren't being created. — Girrafish, May 21 '20 at 15:27
@theGirrafish No, but I didn't try new version of spring-session. Maybe it has been fixed... — jswieca, May 26 '20 at 19:34

Cocuthemyth · Answer 1 · 2018-12-18T13:39:57.617

3

I am describing the steps.It worked for me.

1- Add the dependency in your pom file.

<dependency>
        <groupId>org.springframework.session</groupId>
        <artifactId>spring-session-jdbc</artifactId>
    </dependency>

2-Add the sql file in your project with same path and same name Under "resources" file.

PATH : /org/springframework/session/jdbc/schema-mysql.sql

schema-mysql.sql

CREATE TABLE SPRING_SESSION (
    PRIMARY_ID CHAR(36) NOT NULL,
    SESSION_ID CHAR(36) NOT NULL,
    CREATION_TIME BIGINT NOT NULL,
    LAST_ACCESS_TIME BIGINT NOT NULL,
    MAX_INACTIVE_INTERVAL INT NOT NULL,
    EXPIRY_TIME BIGINT NOT NULL,
    PRINCIPAL_NAME VARCHAR(100),
    CONSTRAINT SPRING_SESSION_PK PRIMARY KEY (PRIMARY_ID)
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

CREATE UNIQUE INDEX SPRING_SESSION_IX1 ON SPRING_SESSION (SESSION_ID);
CREATE INDEX SPRING_SESSION_IX2 ON SPRING_SESSION (EXPIRY_TIME);
CREATE INDEX SPRING_SESSION_IX3 ON SPRING_SESSION (PRINCIPAL_NAME);

CREATE TABLE SPRING_SESSION_ATTRIBUTES (
    SESSION_PRIMARY_ID CHAR(36) NOT NULL,
    ATTRIBUTE_NAME VARCHAR(200) NOT NULL,
    ATTRIBUTE_BYTES BLOB NOT NULL,
    CONSTRAINT SPRING_SESSION_ATTRIBUTES_PK PRIMARY KEY (SESSION_PRIMARY_ID, ATTRIBUTE_NAME),
    CONSTRAINT SPRING_SESSION_ATTRIBUTES_FK FOREIGN KEY (SESSION_PRIMARY_ID) REFERENCES SPRING_SESSION(PRIMARY_ID) ON DELETE CASCADE
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

I got the sql file from official spring github account.

https://github.com/spring-projects/spring-session/blob/master/spring-session-jdbc/src/main/resources/org/springframework/session/jdbc/schema-mysql.sql

3-Add below properties into application.properties

spring.session.jdbc.initialize-schema=always
spring.session.jdbc.schema=classpath:org/springframework/session/jdbc/schema-mysql.sql

If it don't work again,please let me know.Like I said,It worked for me.

edited Dec 18 '18 at 13:39

answered Dec 18 '18 at 09:48

Cocuthemyth

264
1
2
11

1

I have schema in mysql, I know how to connect etc. Hibernate dll-auto=create-drop works fine. I have a problem with `spring.session.jdbc.initialize-schema=always`, because it doesn't initialize SPRING_SESSION tables on application start. Is it possible at all? – jswieca Dec 18 '18 at 11:49
Which version of Spring Boot are You use? – jswieca Dec 18 '18 at 14:34
I use 2.0.4.RELEASE – Cocuthemyth Dec 18 '18 at 14:57
I use 2.0.6.RELEASE and it doesn't work... `spring.session.jdbc.initialize-schema=always` `spring.session.jdbc.schema=classpath:org/springframework/session/jdbc/schema-mysql.sql` `spring.jpa.hibernate.ddl-auto=create-drop` `spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL57Dialect` – jswieca Dec 18 '18 at 17:24
Your hibernate dialect is MySQL57Dialect.Are u sure it is true? As I know it is MySQLDialect. If it is possible,Just create an empty schema in db with SQL "create schema example;" code for one time,than it will update it for every time.You opened and closed the project you wont be create any more.Also could you please update the question if a fail is occuring? – Cocuthemyth Dec 18 '18 at 18:01
MySQL57Dialect is correct and it provide innodb to entities tables. – jswieca Dec 18 '18 at 18:16
I used this and it worked. Then I realized I had a typo in my table sql. I dropped the tables and restarted the app and now it ignores the schema file. – Josh J Sep 16 '21 at 13:30
Something to keep in mind is not to have @EnableJdbcHttpSession together with entries in *application.properties* , otherwise Spring Boot won't do its auto configuration. – Kristjan Veskimäe Jul 18 '22 at 21:03

joash · Answer 2 · 2020-01-14T09:48:35.580

2

Try this setting in the application.properties

spring.session.store-type=jdbc
spring.session.jdbc.initialize-schema=always //spring will create required tables for us
spring.session.timeout.seconds=900

edited Jan 14 '20 at 09:48

answered Jan 14 '20 at 09:27

joash

2,205
2
26
31

How to initialize schema in spring-session with JDBC

2 Answers2

Linked