Spring session table not created in Docker container

Question

When I'm launching my Spring application from my IDE (IntelliJ), there is no problem with the creation of all the tables including the Spring Session table.

However, when I'm launching it with the generated jar (with the same things inside) from my docker containers, it creates my tables excluding Spring Session one.

I receive this error, telling me that the spring session does not exist:

app    | 09:43:33.837 [HikariPool-1 housekeeper] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
app    | 09:43:33.837 [HikariPool-1 housekeeper] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Fill pool skipped, pool is at sufficient level.
app    | 09:44:00.005 [pool-1-thread-1] DEBUG org.springframework.orm.jpa.JpaTransactionManager - Creating new transaction with name [null]: PROPAGATION_REQUIRES_NEW,ISOLATION_DEFAULT
app    | 09:44:00.006 [pool-1-thread-1] DEBUG org.springframework.orm.jpa.JpaTransactionManager - Opened new EntityManager [SessionImpl(1059542725<open>)] for JPA transaction
app    | 09:44:00.014 [pool-1-thread-1] DEBUG org.hibernate.engine.transaction.internal.TransactionImpl - On TransactionImpl creation, JpaCompliance#isJpaTransactionComplianceEnabled == false
app    | 09:44:00.015 [pool-1-thread-1] DEBUG org.hibernate.engine.transaction.internal.TransactionImpl - begin
app    | 09:44:00.019 [pool-1-thread-1] DEBUG org.springframework.orm.jpa.JpaTransactionManager - Exposing JPA transaction as JDBC [org.springframework.orm.jpa.vendor.HibernateJpaDialect$HibernateConnectionHandle@3b3c4387]
app    | 09:44:00.021 [pool-1-thread-1] DEBUG org.springframework.jdbc.core.JdbcTemplate - Executing prepared SQL update
app    | 09:44:00.022 [pool-1-thread-1] DEBUG org.springframework.jdbc.core.JdbcTemplate - Executing prepared SQL statement [DELETE FROM SPRING_SESSION WHERE EXPIRY_TIME < ?]
db     | 2021-09-04 09:44:00.037 UTC [82] ERROR:  relation "spring_session" does not exist at character 13
db     | 2021-09-04 09:44:00.037 UTC [82] STATEMENT:  DELETE FROM SPRING_SESSION WHERE EXPIRY_TIME < $1
app    | 09:44:00.280 [pool-1-thread-1] DEBUG org.springframework.beans.factory.xml.XmlBeanDefinitionReader - Loaded 11 bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
app    | 09:44:00.281 [pool-1-thread-1] DEBUG org.springframework.beans.factory.support.DefaultListableBeanFactory - Creating shared instance of singleton bean 'DB2'
app    | 09:44:00.289 [pool-1-thread-1] DEBUG org.springframework.beans.factory.support.DefaultListableBeanFactory - Creating shared instance of singleton bean 'Derby'
app    | 09:44:00.291 [pool-1-thread-1] DEBUG org.springframework.beans.factory.support.DefaultListableBeanFactory - Creating shared instance of singleton bean 'H2'
app    | 09:44:00.293 [pool-1-thread-1] DEBUG org.springframework.beans.factory.support.DefaultListableBeanFactory - Creating shared instance of singleton bean 'HDB'
app    | 09:44:00.295 [pool-1-thread-1] DEBUG org.springframework.beans.factory.support.DefaultListableBeanFactory - Creating shared instance of singleton bean 'HSQL'
app    | 09:44:00.297 [pool-1-thread-1] DEBUG org.springframework.beans.factory.support.DefaultListableBeanFactory - Creating shared instance of singleton bean 'Informix'
app    | 09:44:00.298 [pool-1-thread-1] DEBUG org.springframework.beans.factory.support.DefaultListableBeanFactory - Creating shared instance of singleton bean 'MS-SQL'
app    | 09:44:00.299 [pool-1-thread-1] DEBUG org.springframework.beans.factory.support.DefaultListableBeanFactory - Creating shared instance of singleton bean 'MySQL'
app    | 09:44:00.300 [pool-1-thread-1] DEBUG org.springframework.beans.factory.support.DefaultListableBeanFactory - Creating shared instance of singleton bean 'Oracle'
app    | 09:44:00.302 [pool-1-thread-1] DEBUG org.springframework.beans.factory.support.DefaultListableBeanFactory - Creating shared instance of singleton bean 'PostgreSQL'
app    | 09:44:00.303 [pool-1-thread-1] DEBUG org.springframework.beans.factory.support.DefaultListableBeanFactory - Creating shared instance of singleton bean 'Sybase'
app    | 09:44:00.305 [pool-1-thread-1] DEBUG org.springframework.jdbc.support.SQLErrorCodesFactory - Looking up default SQLErrorCodes for DataSource [com.zaxxer.hikari.HikariDataSource@77eb5790]
app    | 09:44:00.308 [pool-1-thread-1] DEBUG org.springframework.jdbc.support.SQLErrorCodesFactory - SQL error codes for 'PostgreSQL' found
app    | 09:44:00.309 [pool-1-thread-1] DEBUG org.springframework.jdbc.support.SQLErrorCodesFactory - Caching SQL error codes for DataSource [com.zaxxer.hikari.HikariDataSource@77eb5790]: database product name is 'PostgreSQL'
app    | 09:44:00.310 [pool-1-thread-1] DEBUG org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator - Translating SQLException with SQL state '42P01', error code '0', message [ERROR: relation "spring_session" does not exist
app    |   Position: 13]; SQL was [DELETE FROM SPRING_SESSION WHERE EXPIRY_TIME < ?] for task [PreparedStatementCallback]
app    | 09:44:00.312 [pool-1-thread-1] DEBUG org.springframework.transaction.support.TransactionTemplate - Initiating transaction rollback on application exception
app    | org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [DELETE FROM SPRING_SESSION WHERE EXPIRY_TIME < ?]; nested exception is org.postgresql.util.PSQLException: ERROR: relation "spring_session" does not exist
app    |   Position: 13
app    |        at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:239)
app    |        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
app    |        at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1541)
app    |        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:667)
app    |        at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:960)
app    |        at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:1015)
app    |        at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:1025)
app    |        at org.springframework.session.jdbc.JdbcIndexedSessionRepository.lambda$cleanUpExpiredSessions$8(JdbcIndexedSessionRepository.java:587)
app    |        at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140)
app    |        at org.springframework.session.jdbc.JdbcIndexedSessionRepository.cleanUpExpiredSessions(JdbcIndexedSessionRepository.java:587)
app    |        at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
app    |        at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:95)
app    |        at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
app    |        at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
app    |        at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:304)
app    |        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
app    |        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
app    |        at java.base/java.lang.Thread.run(Thread.java:829)
app    | Caused by: org.postgresql.util.PSQLException: ERROR: relation "spring_session" does not exist
app    |   Position: 13
app    |        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2552)
app    |        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2284)
app    |        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:322)
app    |        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)
app    |        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401)
app    |        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)
app    |        at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:130)
app    |        at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
app    |        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
app    |        at org.springframework.jdbc.core.JdbcTemplate.lambda$update$2(JdbcTemplate.java:965)
app    |        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:651)
app    |        ... 14 common frames omitted
app    | 09:44:00.316 [pool-1-thread-1] DEBUG org.springframework.orm.jpa.JpaTransactionManager - Initiating transaction rollback
app    | 09:44:00.317 [pool-1-thread-1] DEBUG org.springframework.orm.jpa.JpaTransactionManager - Rolling back JPA transaction on EntityManager [SessionImpl(1059542725<open>)]
app    | 09:44:00.318 [pool-1-thread-1] DEBUG org.hibernate.engine.transaction.internal.TransactionImpl - rolling back
app    | 09:44:00.328 [pool-1-thread-1] DEBUG org.springframework.orm.jpa.JpaTransactionManager - Closing JPA EntityManager [SessionImpl(1059542725<open>)] after transaction
app    | 09:44:00.330 [pool-1-thread-1] ERROR org.springframework.scheduling.support.TaskUtils$LoggingErrorHandler - Unexpected error occurred in scheduled task
app    | org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [DELETE FROM SPRING_SESSION WHERE EXPIRY_TIME < ?]; nested exception is org.postgresql.util.PSQLException: ERROR: relation "spring_session" does not exist
app    |   Position: 13
app    |        at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:239)
app    |        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
app    |        at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1541)
app    |        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:667)
app    |        at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:960)
app    |        at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:1015)
app    |        at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:1025)
app    |        at org.springframework.session.jdbc.JdbcIndexedSessionRepository.lambda$cleanUpExpiredSessions$8(JdbcIndexedSessionRepository.java:587)
app    |        at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140)
app    |        at org.springframework.session.jdbc.JdbcIndexedSessionRepository.cleanUpExpiredSessions(JdbcIndexedSessionRepository.java:587)
app    |        at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
app    |        at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:95)
app    |        at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
app    |        at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
app    |        at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:304)
app    |        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
app    |        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
app    |        at java.base/java.lang.Thread.run(Thread.java:829)
app    | Caused by: org.postgresql.util.PSQLException: ERROR: relation "spring_session" does not exist
app    |   Position: 13
app    |        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2552)
app    |        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2284)
app    |        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:322)
app    |        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)
app    |        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401)
app    |        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)
app    |        at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:130)
app    |        at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
app    |        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
app    |        at org.springframework.jdbc.core.JdbcTemplate.lambda$update$2(JdbcTemplate.java:965)
app    |        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:651)
app    |        ... 14 common frames omitted

Here is my configuration :

application.properties

server.port=8080

# Database configuration
spring.datasource.url = jdbc:postgresql://db:5432/mynotes
spring.datasource.username = postgres
spring.datasource.password = postgres
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.PostgreSQLDialect

# JPA configuration
# Hibernate ddl auto (create, create-drop, validate, update)
spring.jpa.hibernate.ddl-auto = create
#Pour ne pas avoir la ligne d'erreur : SOURCE "https://stackoverflow.com/questions/4588755/disabling-contextual-lob-creation-as-createclob-method-threw-error"
spring.jpa.properties.hibernate.temp.use_jdbc_metadata_defaults=false

# Session configuration
spring.session.store-type=jdbc
spring.session.jdbc.initialize-schema=always
spring.session.jdbc.schema=classpath:org/springframework/session/jdbc/schema-postgresql.sql
spring.session.jdbc.table-name=SPRING_SESSION # Name of the database table used to store sessions.
# Pour se connecter avec l'appli mobile
server.address=0.0.0.0

docker-compose.yml

version: '2'

services:
  app:
    image: "magnan/mynotes:${VERSION}"
    build:
      context: ./App
    container_name: app
    depends_on:
      - db
    ports:
      - "8080:8080"
    environment:
      - SPRING_DATASOURCE_URL=jdbc:postgresql://db:5432/mynotes
      - SPRING_DATASOURCE_USERNAME=postgres
      - SPRING_DATASOURCE_PASSWORD=postgres
      - SPRING_JPA_HIBERNATE_DDL_AUTO=create
          
  db:
    image: 'postgres:13.1'
    container_name: db
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
    volumes:
      - ./Db/create_db_mynotes.sql:/docker-entrypoint-initdb.d/create_db_mynotes.sql

App/Dockerfile

FROM openjdk:11
VOLUME /tmp
COPY *.jar app.jar
ENTRYPOINT ["java","-jar","/app.jar"]

create_db_mynotes.sql

CREATE DATABASE mynotes;
GRANT ALL PRIVILEGES ON DATABASE mynotes TO postgres;

Do you have any ideas why this is happening in containers and not in IntelliJ and how to solve it ?

Thanks a lot for your help!

score 0 · Answer 1 · answered Apr 03 '22 at 11:45

An example of how to create a new instance can be seen below:

JdbcTemplate jdbcTemplate = new JdbcTemplate();

// ... configure jdbcTemplate ...

TransactionTemplate transactionTemplate = new TransactionTemplate();

// ... configure transactionTemplate ...

JdbcIndexedSessionRepository sessionRepository = new JdbcIndexedSessionRepository(jdbcTemplate, transactionTemplate);

For additional information on how to create and configure JdbcTemplate and TransactionTemplate, refer to the Spring Framework Reference Documentation. By default, this implementation uses SPRING_SESSION and SPRING_SESSION_ATTRIBUTES tables to store sessions. Note that the table name can be customized using the setTableName(String) method. In that case the table used to store attributes will be named using the provided table name, suffixed with _ATTRIBUTES. Depending on your database, the table definition can be described as below:

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)
);

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 BYTEA 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
);

CREATE INDEX SPRING_SESSION_ATTRIBUTES_IX1 ON SPRING_SESSION_ATTRIBUTES (SESSION_PRIMARY_ID);

Due to the differences between the various database vendors, especially when it comes to storing binary data, make sure to use SQL script specific to your database. Scripts for most major database vendors are packaged as org/springframework/session/jdbc/schema-*.sql, where * is the target database type.

Spring session table not created in Docker container

Here is my configuration :

1 Answers1