CAS 5.0.x - Identifier too long JPA Service Registry for Oracle DB

[Olivier Lamarche]

The generated script contains data type that are not compatible with Oracle.

Thus, I've got the following error : Failed to initialize pool: ORA-00942: Table ou vue inexistante

Here is the generated script to create table :

create table RegexRegisteredService (expression_type VARCHAR(15) DEFAULT 'ant' not null, id bigint not null, access_strategy blob(2147483647), attribute_release blob(2147483647), description varchar(255) not null, evaluation_order integer not null, logo varchar(255), logout_type integer, logout_url varchar(255), mfa_policy blob(2147483647), name varchar(255) not null, proxy_policy blob(2147483647), public_key blob(2147483647), required_handlers blob(2147483647), serviceId varchar(255) not null, theme varchar(255), username_attr blob(2147483647), bypassApprovalPrompt boolean, clientId varchar(255), clientSecret varchar(255), generateRefreshToken boolean, jsonFormat boolean, encryptAssertions boolean, metadataCriteriaDirection varchar(255), mcp varchar(255), mcreed boolean, mcrred boolean, metadataCriteriaRoles varchar(255), metadataLocation varchar(255), metadataMaxValidity bigint, msl varchar(255), racc varchar(255), requiredNameIdFormat varchar(255), signAssertions boolean, signResponses boolean, primary key (id))

According to this website : https://www.techonthenet.com/oracle/datatypes.php

1. bigint is not a valid data type

2. boolean is not a valid data type

[Ray Bon]

Oliver,

I created an sql script to install into oracle. This was before I realized that the database parameter was incorrect in CAS 4.2.x in cas.properties (https://github.com/apereo/cas/blob/4.2.x/cas-server-webapp/src/main/webapp/WEB-INF/cas.properties). I would suspect that it is the same in 5.x. The fields for dialect and batch size are incorrect. Presently they are:

svcreg.database.hibernate.dialect=org.hibernate.dialect.OracleDialect|MySQLInnoDBDialect|HSQLDialect

svcreg.database.hibernate.batchSize=10

but they should be:

svcreg.database.dialect=org.hibernate.dialect.OracleDialect|MySQLInnoDBDialect|HSQLDialect

svcreg.database.batchSize=10

The result is to fall back to a default dialect (I think HSQL).

I have been meaning to submit a ticket for this.

Anyway here is the sql:

create sequence hibernate_sequence start with 1 increment by 1;

create table RegisteredServiceImplProperty (

  id number(19, 0) not null,

  property_values blob,

  primary key (id)

);

create table RegisteredServiceImpl (

  id number(19, 0) NOT NULL ENABLE,

  access_strategy blob,

  attribute_release blob,

  bypassApprovalPrompt varchar(255),

  clientId varchar(255),

  clientSecret varchar(255),

  description varchar(255) not null,

  evaluation_order integer not null,

  expression_type VARCHAR(15) DEFAULT 'ant' not null,

  logo varchar(255),

  logout_type integer,

  logout_url varchar(255),

  name varchar(255) not null,

  proxy_policy blob,

  public_key blob,

  required_handlers blob,

  serviceId varchar(255) not null,

  theme varchar(255),

  username_attr blob,

  primary key (id)

);

create table RegisteredServiceImpl_Props (

  AbstractRegisteredService_id number(19, 0) not null,

  properties_id number(19, 0) not null,

  properties_KEY varchar(255) not null,

  primary key (AbstractRegisteredService_id, properties_KEY)

);

alter table RegisteredServiceImpl_Props

  add constraint FK_Property

  foreign key (properties_id)

  references RegisteredServiceImplProperty;

alter table RegisteredServiceImpl_Props

  add constraint FK_RegisteredServiceImpl

  foreign key (AbstractRegisteredService_id)

  references RegisteredServiceImpl;

Ray

-- 
Ray Bon
Programmer analyst
Development Services, University Systems
2507128831 | CLE 023 | rb...@uvic.ca

[Olivier Lamarche]

Hi Ray,

Thank you for the fast feedback!

I've got a similar issue when I try to setup the cas-management console.

At startup I think that is looking for services, but he failed due to Identifier too long :

Hibernate: select abstractre0_.id as id2_0_, abstractre0_.access_strategy as access_strategy3_0_, abstractre0_.attribute_release as attribute_release4_0_, abstractre0_.description as description5_0_, abstractre0_.evaluation_order as evaluation_order6_0_, abstractre0_.logo as logo7_0_, abstractre0_.logout_type as logout_type8_0_, abstractre0_.logout_url as logout_url9_0_, abstractre0_.mfa_policy as mfa_policy10_0_, abstractre0_.name as name11_0_, abstractre0_.proxy_policy as proxy_policy12_0_, abstractre0_.public_key as public_key13_0_, abstractre0_.required_handlers as required_handlers14_0_, abstractre0_.serviceId as serviceId15_0_, abstractre0_.theme as theme16_0_, abstractre0_.username_attr as username_attr17_0_, abstractre0_.bypassApprovalPrompt as bypassApprovalPro18_0_, abstractre0_.clientId as clientId19_0_, abstractre0_.clientSecret as clientSecret20_0_, abstractre0_.generateRefreshToken as generateRefreshTo21_0_, abstractre0_.jsonFormat as jsonFormat22_0_, abstractre0_.jwks as jwks23_0_, abstractre0_.signIdToken as signIdToken24_0_, abstractre0_.encryptAssertions as encryptAssertions25_0_, abstractre0_.metadataCriteriaDirection as metadataCriteriaD26_0_, abstractre0_.metadataCriteriaPattern as metadataCriteriaP27_0_, abstractre0_.metadataCriteriaRemoveEmptyEntitiesDescriptors as metadataCriteriaR28_0_, abstractre0_.metadataCriteriaRemoveRolelessEntityDescriptors as metadataCriteriaR29_0_, abstractre0_.metadataCriteriaRoles as metadataCriteriaR30_0_, abstractre0_.metadataLocation as metadataLocation31_0_, abstractre0_.metadataMaxValidity as metadataMaxValidi32_0_, abstractre0_.metadataSignatureLocation as metadataSignature33_0_, abstractre0_.requiredAuthenticationContextClass as requiredAuthentic34_0_, abstractre0_.requiredNameIdFormat as requiredNameIdFor35_0_, abstractre0_.signAssertions as signAssertions36_0_, abstractre0_.signResponses as signResponses37_0_, abstractre0_.expression_type as expression_type1_0_ from RegexRegisteredService abstractre0_

--
- CAS gitter chatroom: https://gitter.im/apereo/cas
- CAS mailing list guidelines: https://apereo.github.io/cas/Mailing-Lists.html
- CAS documentation website: https://apereo.github.io/cas
- CAS project website: https://github.com/apereo/cas
---
You received this message because you are subscribed to the Google Groups "CAS Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cas-user+u...@apereo.org.
To view this discussion on the web visit https://groups.google.com/a/apereo.org/d/msgid/cas-user/1493054587.25318.2.camel%40uvic.ca.

[Ray Bon]

Oliver,

Try to run the query in your database tool, PL/SQL Developer or some such. It may give a more info.

Near the end of the query is this field, requiredAuthenticationContextClass. I think Oracle has a limit of 32 characters.

I have not yet worked with CAS 5.x and do not recognise the field so I am not sure if you can change it.

Ray

[Olivier Lamarche]

Thx Ray,

I have already tried

I ended up by overriding the SamlRegistredService class and set manually the name for each column that are too long. 

I did the same with the cas-management webapp.

Now both webapp start, but cas-management can't auto initialize ... see my other post : https://groups.google.com/forum/#!topic/jasig-cas-user/nsu8IOw_Teo

To view this discussion on the web visit https://groups.google.com/a/apereo.org/d/msgid/cas-user/1493135884.25318.25.camel%40uvic.ca.