search

wso2 / product-is

Welcome to the WSO2 Identity Server source code! For info on working with the WSO2 Identity Server repository and contributing code, click the link below.
http://wso2.github.io/
Apache License 2.0
748 stars 728 forks source link

[ISSUE] Script issues while source oracle scripts against oracle 12c and 19c with 5.9.0 latest wum #8039

Closed ShanikaWickramasinghe closed 4 years ago

ShanikaWickramasinghe commented 4 years ago

Environment wso2is-5.9.0+1585323544240.full

Steps to Reproduce

Source dbscripts/oracle.sql against oracle 12c and oracle 19c

Observation Below errors were noticed

table REG_CLUSTER_LOCK created.
table REG_LOG created.
sequence REG_LOG_SEQUENCE created.
index REG_LOG_IND_BY_REGLOG created.
TRIGGER REG_LOG_TRIGGER compiled
table REG_PATH created.
Error starting at line 40 in command:
CREATE INDEX REG_PATH_IND_BY_PATH_VALUE ON REG_PATH(REG_PATH_VALUE, REG_TENANT_ID)
Error at Command Line:40 Column:53
Error report:
SQL Error: ORA-01408: such column list already indexed
01408. 00000 -  "such column list already indexed"
*Cause:    
*Action:
index REG_PATH_IND_BY_PARENT_ID created.
sequence REG_PATH_SEQUENCE created.
TRIGGER UM_HYBRID_REMEMBER_ME_TRIGGER compiled
table UM_SYSTEM_ROLE created.
sequence UM_SYSTEM_ROLE_SEQUENCE created.
Error starting at line 784 in command:
CREATE INDEX SYSTEM_ROLE_IND_BY_RN_TI ON UM_SYSTEM_ROLE(UM_ROLE_NAME, UM_TENANT_ID)
Error at Command Line:784 Column:57
Error report:
SQL Error: ORA-01408: such column list already indexed
01408. 00000 -  "such column list already indexed"
*Cause:    
*Action:
TRIGGER UM_SYSTEM_ROLE_TRIGGER compiled
table UM_SYSTEM_USER_ROLE created.
sequence UM_SYSTEM_USER_ROLE_SEQUENCE created.
TRIGGER UM_SYSTEM_USER_ROLE_TRIGGER compiled

Identity/uma/oracle.sql

While executing the uma oracle script noticed below errors

table IDN_UMA_RESOURCE created.
Error starting at line 14 in command:
CREATE SEQUENCE IDN_UMA_RESOURCE_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
  /

CREATE OR REPLACE TRIGGER IDN_UMA_RESOURCE_TRIG
BEFORE INSERT
ON IDN_UMA_RESOURCE
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT IDN_UMA_RESOURCE_SEQ.nextval INTO :NEW.ID FROM dual
Error at Command Line:15 Column:3
Error report:
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"
*Cause:    
*Action:

Error starting at line 24 in command:
END
Error report:
Unknown Command
index IDX_RID created.
index IDX_USER created.
index IDX_USER_RID created.
Error starting at line 36 in command:
CREATE TABLE IDN_UMA_RESOURCE_META_DATA (
  ID                INTEGER,
  RESOURCE_IDENTITY INTEGER                NOT NULL,
  PROPERTY_KEY      VARCHAR2(40),
  PROPERTY_VALUE    VARCHAR2(255),
  PRIMARY KEY (ID),
  FOREIGN KEY (RESOURCE_IDENTITY) REFERENCES IDN_UMA_RESOURCE (ID) ON DELETE CASCADE
)
  /

CREATE SEQUENCE IDN_UMA_RESOURCE_META_DATA_SEQ START WITH 1 INCREMENT BY 1 NOCACHE

Error at Command Line:44 Column:3
Error report:
SQL Error: ORA-00922: missing or invalid option
00922. 00000 -  "missing or invalid option"
*Cause:    
*Action:
Error starting at line 49 in command:
CREATE OR REPLACE TRIGGER IDN_UMA_RESOURCE_METADATA_TRIG
BEFORE INSERT
ON IDN_UMA_RESOURCE_META_DATA
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT IDN_UMA_RESOURCE_META_DATA_SEQ.nextval INTO :NEW.ID FROM dual;
END;
Error report:
ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:
Error starting at line 59 in command:
CREATE TABLE IDN_UMA_RESOURCE_SCOPE (
  ID                INTEGER,
  RESOURCE_IDENTITY INTEGER                NOT NULL,
  SCOPE_NAME        VARCHAR2(255),
  PRIMARY KEY (ID),
  FOREIGN KEY (RESOURCE_IDENTITY) REFERENCES IDN_UMA_RESOURCE (ID) ON DELETE CASCADE
)
  /

CREATE SEQUENCE IDN_UMA_RESOURCE_SCOPE_SEQ START WITH 1 INCREMENT BY 1 NOCACHE

Error at Command Line:66 Column:3
Error report:
SQL Error: ORA-00922: missing or invalid option
00922. 00000 -  "missing or invalid option"
*Cause:    
*Action:
Error starting at line 71 in command:
CREATE OR REPLACE TRIGGER IDN_UMA_RESOURCE_SCOPE_TRIG
BEFORE INSERT
ON IDN_UMA_RESOURCE_SCOPE
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT IDN_UMA_RESOURCE_SCOPE_SEQ.nextval INTO :NEW.ID FROM dual;
END;
Error report:
ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:
Error starting at line 81 in command:
CREATE INDEX IDX_RS ON IDN_UMA_RESOURCE_SCOPE (SCOPE_NAME)
Error at Command Line:81 Column:24
Error report:
SQL Error: ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:
Error starting at line 84 in command:
CREATE TABLE IDN_UMA_PERMISSION_TICKET (
  ID              INTEGER,
  PT              VARCHAR2(255)           NOT NULL,
  TIME_CREATED    TIMESTAMP              NOT NULL,
  EXPIRY_TIME     TIMESTAMP              NOT NULL,
  TICKET_STATE    VARCHAR2(25) DEFAULT 'ACTIVE',
  TENANT_ID       INTEGER     DEFAULT -1234,
  TOKEN_ID        VARCHAR(255),
  PRIMARY KEY (ID)
)
  /

CREATE SEQUENCE IDN_UMA_PERMISSION_TICKET_SEQ START WITH 1 INCREMENT BY 1 NOCACHE

Error at Command Line:94 Column:3
Error report:
SQL Error: ORA-00922: missing or invalid option
00922. 00000 -  "missing or invalid option"
*Cause:    
*Action:
Error starting at line 99 in command:
CREATE OR REPLACE TRIGGER IDN_UMA_PERMISSION_TICKET_TRIG
BEFORE INSERT
ON IDN_UMA_PERMISSION_TICKET
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT IDN_UMA_PERMISSION_TICKET_SEQ.nextval INTO :NEW.ID FROM dual;
END;
Error report:
ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:
Error starting at line 109 in command:
CREATE INDEX IDX_PT ON IDN_UMA_PERMISSION_TICKET (PT)
Error at Command Line:109 Column:24
Error report:
SQL Error: ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:
Error starting at line 112 in command:
CREATE TABLE IDN_UMA_PT_RESOURCE (
  ID             INTEGER,
  PT_RESOURCE_ID INTEGER                NOT NULL,
  PT_ID          INTEGER                NOT NULL,
  PRIMARY KEY (ID),
  FOREIGN KEY (PT_ID) REFERENCES IDN_UMA_PERMISSION_TICKET (ID) ON DELETE CASCADE,
  FOREIGN KEY (PT_RESOURCE_ID) REFERENCES IDN_UMA_RESOURCE (ID) ON DELETE CASCADE
)
  /

CREATE SEQUENCE IDN_UMA_PT_RESOURCE_SEQ START WITH 1 INCREMENT BY 1 NOCACHE

Error at Command Line:120 Column:3
Error report:
SQL Error: ORA-00922: missing or invalid option
00922. 00000 -  "missing or invalid option"
*Cause:    
*Action:
Error starting at line 125 in command:
CREATE OR REPLACE TRIGGER IDN_UMA_PT_RESOURCE_TRIG
BEFORE INSERT
ON IDN_UMA_PT_RESOURCE
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT IDN_UMA_PT_RESOURCE_SEQ.nextval INTO :NEW.ID FROM dual;
END;
Error report:
ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:
Error starting at line 135 in command:
CREATE TABLE IDN_UMA_PT_RESOURCE_SCOPE (
  ID             INTEGER,
  PT_RESOURCE_ID INTEGER                NOT NULL,
  PT_SCOPE_ID    INTEGER                NOT NULL,
  PRIMARY KEY (ID),
  FOREIGN KEY (PT_RESOURCE_ID) REFERENCES IDN_UMA_PT_RESOURCE (ID) ON DELETE CASCADE,
  FOREIGN KEY (PT_SCOPE_ID) REFERENCES IDN_UMA_RESOURCE_SCOPE (ID) ON DELETE CASCADE
)
  /

CREATE SEQUENCE IDN_UMA_PT_RESOURCE_SCOPE_SEQ START WITH 1 INCREMENT BY 1 NOCACHE

Error at Command Line:143 Column:3
Error report:
SQL Error: ORA-00922: missing or invalid option
00922. 00000 -  "missing or invalid option"
*Cause:    
*Action:
Error starting at line 148 in command:
CREATE OR REPLACE TRIGGER IDN_UMA_PT_RESOURCE_SCOPE_TRIG
BEFORE INSERT
ON IDN_UMA_PT_RESOURCE_SCOPE
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT IDN_UMA_PT_RESOURCE_SCOPE_SEQ.nextval INTO :NEW.ID FROM dual;
END;
Error report:
ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:
nilasini commented 4 years ago

Couldn't reproduce in 5.11.0-alpha hence closing the issue.