Uploaded image for project: 'OpenIDM'
  1. OpenIDM
  2. OPENIDM-4638

Import Oracle DB schema has errors and start OpenIDM failed.

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Blocker
    • Resolution: Fixed
    • Affects Version/s: OpenIDM 4.0.0
    • Fix Version/s: OpenIDM 4.0.0
    • Labels:
    • Environment:
      OpenIDM build 795 runs on a CentOS with Oracle 12c DB as repo
    • Sprint:
      OpenIDM Sprint 53

      Description

      When importing Oracle DB schema, different errors were seen:

      Creating Table auditaccess ...
        http_request_queryparameters CLOB(2M) NULL ,
                                         *
      ERROR at line 19:
      ORA-00907: missing right parenthesis
      COMMENT ON COLUMN auditaccess.activitydate IS 'Date format: 2011-09-09T14:58:17.654+02:00'
                        *
      ERROR at line 1:
      ORA-00942: table or view does not exist
      
      Creating Primary Key Constraint PRIMARY_8 on table auditactivity ...
      ADD CONSTRAINT PRIMARY_8 PRIMARY KEY
                     *
      ERROR at line 2:
      ORA-02264: name already used by an existing constraint
      Creating Index idx_auditactivity_transactionid on auditactivity ...
      CREATE INDEX idx_auditactivity_transactionid ON auditactivity
                   *
      ERROR at line 1:
      ORA-00972: identifier is too long
      

      And start OpenIDM would fail, the complete output of the import is attached.

      To reproduce:
      1. create a service account user openidm with password and grant the user rights as follows

      $ sqlplus /nolog
      SQL> connect SYSTEM/password
      Connected.
      SQL> CREATE USER openidm IDENTIFIED BY password;
      User created.
      SQL> GRANT CREATE SESSION TO openidm;
      Grant succeeded.
      SQL> GRANT RESOURCE TO openidm;
      Grant succeeded.
      

      2. Connect to database as the service account user

      SQL> connect openidm/password;
      Connected.
      

      3.Import the openidm schema

        
      @/home/oracle/openidm/db/oracle/scripts/openidm.sql;
      

      Observe the output with errors. Some errors look like the service account lacks privileges, the steps to create the user and grant rights was used in our previous setup/testing. It would be good to find out what rights should be given to the service account as well.

      OpenIDM startup failed with errors:

      $ ./startup.sh 
      Executing ./startup.sh...
      Using OPENIDM_HOME:   /home/oracle/openidm
      Using PROJECT_HOME:   /home/oracle/openidm
      Using OPENIDM_OPTS:   -Xmx1024m -Xms1024m
      Using LOGGING_CONFIG: -Djava.util.logging.config.file=/home/oracle/openidm/conf/logging.properties
      Using boot properties at /home/oracle/openidm/conf/boot/boot.properties
      Nov 19, 2015 5:14:08 PM org.forgerock.openidm.datasource.jdbc.impl.BoneCPDatabaseShutdownHook onConnectionException
      WARNING: Database down at 2015-11-19T17:14:08.693-05:00
      Nov 19, 2015 5:14:08 PM com.jolbox.bonecp.MemorizeTransactionProxy invoke
      SEVERE: Connection failed. Attempting to recover transaction on Thread #23
      Nov 19, 2015 5:14:08 PM org.forgerock.openidm.datasource.jdbc.impl.BoneCPDatabaseShutdownHook onConnectionException
      WARNING: Database down at 2015-11-19T17:14:08.787-05:00
      Nov 19, 2015 5:14:08 PM org.forgerock.openidm.repo.jdbc.impl.JDBCRepoService rollback
      WARNING: Rolling back transaction reported failure 
      java.sql.SQLException: Could not recover transaction. Original exception follows.java.sql.SQLException: Could not rollback with auto-commit set on
      	at com.jolbox.bonecp.MemorizeTransactionProxy.invoke(MemorizeTransactionProxy.java:214)
      	at com.sun.proxy.$Proxy9.rollback(Unknown Source)
      	at com.jolbox.bonecp.ConnectionHandle.rollback(ConnectionHandle.java:1045)
      ...
      

        Attachments

          Activity

            People

            Assignee:
            jim.mitchener Jim Mitchener
            Reporter:
            Tinghua.Xu Tinghua Xu
            QA Assignee:
            Tinghua Xu Tinghua Xu
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: