Uploaded image for project: 'GateIn Portal'
  1. GateIn Portal
  2. GTNPORTAL-3341

Cannot delete users with OracleDB because of "ORA-02292: integrity constraint (XXXXX) violated"

    Details

    • Workaround:
      Workaround Exists
    • Workaround Description:
      Hide

      One possible workaround, which works for me (not deeply tested) is to manually disable the mentioned foreign key constraint with some tool like Oracle SQL developer.

      Basically you need to run this SQL command in your Oracle DB:
      alter table "JBID_IO_ATTR_TEXT_VALUES" disable constraint "FK_9T69D6ILQTU0TR4J8AB2BW9EV"

      Name of the constraint will be probably different for you, it's the one mentioned in exception stacktrace (It's constraint on table JBID_IO_ATTR_TEXT_VALUES, which references primary key in table JBID_IO_ATTR)

      Note that workaround has downside that there are some uncleaned items in table JBID_IO_ATTR_TEXT_VALUES with null values and those items won't be deleted after removing user. (This whole issue is caused by the fact that OracleDB treats empty strings as nulls)

      Show
      One possible workaround, which works for me (not deeply tested) is to manually disable the mentioned foreign key constraint with some tool like Oracle SQL developer. Basically you need to run this SQL command in your Oracle DB: alter table "JBID_IO_ATTR_TEXT_VALUES" disable constraint "FK_9T69D6ILQTU0TR4J8AB2BW9EV" Name of the constraint will be probably different for you, it's the one mentioned in exception stacktrace (It's constraint on table JBID_IO_ATTR_TEXT_VALUES, which references primary key in table JBID_IO_ATTR) Note that workaround has downside that there are some uncleaned items in table JBID_IO_ATTR_TEXT_VALUES with null values and those items won't be deleted after removing user. (This whole issue is caused by the fact that OracleDB treats empty strings as nulls)
    • Bugzilla Update:
      Perform

      Description

      Description of problem:
      Cannot delete users with OracleDB (reproducible at least on version 11gR2) because of "ORA-02292: integrity constraint (XXXXX) violated - child record found"[1]. The child record is in the JBID_IO_ATTR table[2].

      [1]
      2013-12-05 15:40:25,846 DEBUG [org.hibernate.util.JDBCExceptionReporter] (http-127.0.0.1-8080-1) Could not execute JDBC batch update [delete from jbid_io where ID=?]
      java.sql.BatchUpdateException: ORA-02292: integrity constraint (ORAUSER.FK4DC61D7E992317F0) violated - child record found

      at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:10345)
      at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:230)
      at org.jboss.resource.adapter.jdbc.CachedPreparedStatement.executeBatch(CachedPreparedStatement.java:476)
      at org.jboss.resource.adapter.jdbc.WrappedStatement.executeBatch(WrappedStatement.java:774)
      at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
      at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)
      at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:265)
      at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:171)
      at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
      at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:50)
      at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1030)
      at sun.reflect.GeneratedMethodAccessor346.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:597)
      at org.hibernate.context.ThreadLocalSessionContext$TransactionProtectionWrapper.invoke(ThreadLocalSessionContext.java:343)
      at com.sun.proxy.$Proxy266.flush(Unknown Source)
      at org.picketlink.idm.impl.store.hibernate.HibernateIdentityStoreImpl.removeIdentityObject(HibernateIdentityStoreImpl.java:622)
      at org.picketlink.idm.impl.repository.WrapperIdentityStoreRepository.removeIdentityObject(WrapperIdentityStoreRepository.java:158)
      at org.picketlink.idm.impl.api.session.managers.PersistenceManagerImpl.removeUser(PersistenceManagerImpl.java:231)
      at org.exoplatform.services.organization.idm.UserDAOImpl.removeUser(UserDAOImpl.java:268)

      [2]
      JBID_IO_ATTR:

      ATTRIBUTE_ID 423
      IDENTITY_OBJECT_ID 413
      NAME user.language
      ATTRIBUTE_TYPE text
      BIN_VALUE_ID NULL

      How reproducible: 100 %

      Steps to Reproduce:
      1. Change gatein-idm and gatein-jcr to use OracleDB.
      2. Start EPP
      3. Login as root
      4. Create a user
      5. Delete the user

      Actual results:

      Cannot delete the user.

      Expected results:
      The user is deleted.

      Additional info:
      Some user attributes are deleted, so this process is not transactional.

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

                People

                • Assignee:
                  mposolda Marek Posolda
                  Reporter:
                  mposolda Marek Posolda
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  3 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved: