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
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!