Apereo CAS 6.x: embeded HSQLDB not initialized

Question

I want to use an embedded HSQLDB in CAS 6.2-RC5 and I want Spring to initialize it at startup.

First, I added the following depedencies to the cas-overlay-template:

implementation "org.apereo.cas:cas-server-support-json-service-registry:${casServerVersion}"
implementation "org.apereo.cas:cas-server-support-ldap:${casServerVersion}"
implementation "org.apereo.cas:cas-server-support-session-jdbc:${casServerVersion}"
#implementation "org.apereo.cas:cas-server-support-jpa-ticket-registry:${casServerVersion}"

Second, I am using the following configuration in application.yml:

--
spring:
#  datasource:
#    url: "jdbc:hsqldb:mem:testdb" # "jdbc:hsqldb:mem:testdb"|"jdbc:h2:mem:testdb"
#    username: "sa"
#    password: ""
#    initialization-mode: embedded
  session:
    store-type: jdbc
    jdbc:
      initialize-schema: embedded 
      schema: "classpath:org/springframework/session/jdbc/schema-@@platform@@.sql"
      table-name: "SPRING_SESSION"
  h2:
    console: enabled

It do not work.

I have tried to change spring.session.jdbc attribute values and the error is the same:

cas             | 2020-06-09 09:59:00,336 ERROR [org.springframework.scheduling.support.TaskUtils$LoggingErrorHandler] - <Unexpected error occurred in scheduled task>
cas             | org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [DELETE FROM SPRING_SESSION WHERE EXPIRY_TIME < ?]; nested exception is java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: SPRING_SESSION in statement [DELETE FROM SPRING_SESSION WHERE EXPIRY_TIME < ?]
cas             |       at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:93) ~[spring-jdbc-5.2.6.RELEASE.jar!/:5.2.6.RELEASE]
cas             |       at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) ~[spring-jdbc-5.2.6.RELEASE.jar!/:5.2.6.RELEASE]
cas             |       at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.2.6.RELEASE.jar!/:5.2.6.RELEASE]
cas             |       at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1443) ~[spring-jdbc-5.2.6.RELEASE.jar!/:5.2.6.RELEASE]
cas             |       at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633) ~[spring-jdbc-5.2.6.RELEASE.jar!/:5.2.6.RELEASE]
cas             |       at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:862) ~[spring-jdbc-5.2.6.RELEASE.jar!/:5.2.6.RELEASE]
cas             |       at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:917) ~[spring-jdbc-5.2.6.RELEASE.jar!/:5.2.6.RELEASE]
cas             |       at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:927) ~[spring-jdbc-5.2.6.RELEASE.jar!/:5.2.6.RELEASE]
cas             |       at org.springframework.session.jdbc.JdbcIndexedSessionRepository.lambda$cleanUpExpiredSessions$8(JdbcIndexedSessionRepository.java:553) ~[spring-session-jdbc-2.2.2.RELEASE.jar!/:2.2.2.RELEASE]
cas             |       at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140) ~[spring-tx-5.2.6.RELEASE.jar!/:5.2.6.RELEASE]
cas             |       at org.springframework.session.jdbc.JdbcIndexedSessionRepository.cleanUpExpiredSessions(JdbcIndexedSessionRepository.java:553) ~[spring-session-jdbc-2.2.2.RELEASE.jar!/:2.2.2.RELEASE]
cas             |       at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54) ~[spring-context-5.2.6.RELEASE.jar!/:5.2.6.RELEASE]
cas             |       at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:93) ~[spring-context-5.2.6.RELEASE.jar!/:5.2.6.RELEASE]
cas             |       at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source) ~[?:?]
cas             |       at java.util.concurrent.FutureTask.run(Unknown Source) ~[?:?]
cas             |       at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source) ~[?:?]
cas             |       at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) ~[?:?]
cas             |       at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) ~[?:?]
cas             |       at java.lang.Thread.run(Unknown Source) [?:?]
cas             | Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: SPRING_SESSION in statement [DELETE FROM SPRING_SESSION WHERE EXPIRY_TIME < ?]
cas             |       at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) ~[hsqldb-2.5.0.jar!/:2.5.0]
cas             |       at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) ~[hsqldb-2.5.0.jar!/:2.5.0]
cas             |       at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source) ~[hsqldb-2.5.0.jar!/:2.5.0]
cas             |       at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source) ~[hsqldb-2.5.0.jar!/:2.5.0]
cas             |       at com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:337) ~[HikariCP-3.4.5.jar!/:?]
cas             |       at com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java) ~[HikariCP-3.4.5.jar!/:?]
cas             |       at org.springframework.jdbc.core.JdbcTemplate$SimplePreparedStatementCreator.createPreparedStatement(JdbcTemplate.java:1556) ~[spring-jdbc-5.2.6.RELEASE.jar!/:5.2.6.RELEASE]
cas             |       at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:615) ~[spring-jdbc-5.2.6.RELEASE.jar!/:5.2.6.RELEASE]
cas             |       ... 14 more
cas             | Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: SPRING_SESSION
cas             |       at org.hsqldb.error.Error.error(Unknown Source) ~[hsqldb-2.5.0.jar!/:2.5.0]
cas             |       at org.hsqldb.error.Error.error(Unknown Source) ~[hsqldb-2.5.0.jar!/:2.5.0]
cas             |       at org.hsqldb.ParserDQL.readTableName(Unknown Source) ~[hsqldb-2.5.0.jar!/:2.5.0]
cas             |       at org.hsqldb.ParserDQL.readRangeVariableForDataChange(Unknown Source) ~[hsqldb-2.5.0.jar!/:2.5.0]
cas             |       at org.hsqldb.ParserDML.compileDeleteStatement(Unknown Source) ~[hsqldb-2.5.0.jar!/:2.5.0]
cas             |       at org.hsqldb.ParserCommand.compilePart(Unknown Source) ~[hsqldb-2.5.0.jar!/:2.5.0]
cas             |       at org.hsqldb.ParserCommand.compileStatement(Unknown Source) ~[hsqldb-2.5.0.jar!/:2.5.0]
cas             |       at org.hsqldb.Session.compileStatement(Unknown Source) ~[hsqldb-2.5.0.jar!/:2.5.0]
cas             |       at org.hsqldb.StatementManager.compile(Unknown Source) ~[hsqldb-2.5.0.jar!/:2.5.0]
cas             |       at org.hsqldb.Session.execute(Unknown Source) ~[hsqldb-2.5.0.jar!/:2.5.0]
cas             |       at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source) ~[hsqldb-2.5.0.jar!/:2.5.0]
cas             |       at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source) ~[hsqldb-2.5.0.jar!/:2.5.0]
cas             |       at com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:337) ~[HikariCP-3.4.5.jar!/:?]
cas             |       at com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java) ~[HikariCP-3.4.5.jar!/:?]
cas             |       at org.springframework.jdbc.core.JdbcTemplate$SimplePreparedStatementCreator.createPreparedStatement(JdbcTemplate.java:1556) ~[spring-jdbc-5.2.6.RELEASE.jar!/:5.2.6.RELEASE]
cas             |       at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:615) ~[spring-jdbc-5.2.6.RELEASE.jar!/:5.2.6.RELEASE]
cas             |       ... 14 more

What could be wrong? What more might I check?

Thank you

Answer 1

I answer my own question.

First of all, CAS does not need to use a shared Spring Session repository because it mainly relies on the TGC (Ticket Granting Cookie) to maintain the session in a cluster.

The TGT is stored in the TicketRegistry and it is the TicketRegistry what has to be accesible from all instances. One implementation is the JpaTicketRegistry (6.2 and previous(very ancient) had a bug in TicketDefinition, corrected in 6.3.0RC1).

The default behabiour is to create-drop the schema at startup and shutdown. I used the following configuration which tries to update the schema at startup:

#cas.ticket.registry.jpa.ddlAuto=update

It is better to only enable in a instance and disable in others:

cas.ticket.registry.jpa.ddlAuto=none

This setting is also needed but it is already enabled by default:

#cas.jdbc.genDdl=true

It does not generate indexes. I created the following ones:

CREATE INDEX CAS.INDEX1 ON CAS.SERVICETICKET
(TICKET_GRANTING_TICKET_ID)
LOGGING
TABLESPACE CAS6DA00
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
           INITIAL          64K
           NEXT             1M
           MINEXTENTS       1
           MAXEXTENTS       UNLIMITED
           PCTINCREASE      0
           BUFFER_POOL      DEFAULT
          );
          
          
CREATE INDEX CAS.INDEX2 ON CAS.TICKETGRANTINGTICKET
(TICKET_GRANTING_TICKET_ID)
LOGGING
TABLESPACE CAS6DA00
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
           INITIAL          64K
           NEXT             1M
           MINEXTENTS       1
           MAXEXTENTS       UNLIMITED
           PCTINCREASE      0
           BUFFER_POOL      DEFAULT
          );

Related to the ticket registry is the TicketCleaner implementation. The default implementation, used in jpa too, has to be run in a single node. There is no automatic master election so you need to let it enabled in a node and disabled in all the others:

cas.ticket.registry.cleaner.schedule.enabled=false

Hope it helps to others!