Cause: When you configure JPP with Oracle DB (reproduced on Oracle 11gR2) and then you login as root, create user in JPP UI and then try to delete this user in UI, you will see error "ORA-02292: integrity constraint (XXXXX) violated" in server.log and user won't be deleted.
Consequence: The root cause is the fact that Oracle doesn't know empty strings and it treats them as null.
Fix: The fix will always use value null for value of UserProfile attribute if it's empty string. The empty string is used in JPP just for attribute "user.language" of UserProfile as this attribute is in UI represented by comboBox. Other UserProfile attributes represented by text-fields are already send to UserProfileHandler with value null if they are empty. So actually just attribute "user.language" is usually causing this whole issue.
Result: After applying of the fix, it would be possible to delete newly created user without problem. BUT for JPP customers, who already have pre-filled DB, the issue may still exists and it may not be possible to delete some users, because there may be still some attributes in table JBID_IO_ATTR_TEXT_VALUES, which have value null. To fix the issue in existing environment, customer will need to manually clean null attributes by executing this SQL query against his Oracle database:
delete from JBID_IO_ATTR_TEXT_VALUES where ATTR_VALUE is null;
DescriptionTakayuki Konishi
2013-12-05 07:54:46 UTC
Description of problem:
Cannot delete users with OracleDB 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.
Comment 1JBoss JIRA Server
2013-12-09 22:47:24 UTC
Marek Posolda <mposolda> made a comment on jira GTNPORTAL-3341
The issue is caused by the fact that Oracle doesn't know empty strings and it treats them as null.
I added the fix on GateIn side. The fix will always use value null for value of UserProfile attribute if it's empty string. It's quite dirty fix IMO, but it works for all scenarios I tested (The empty string is used in GateIn just for attribute "user.language" of UserProfile as this attribute is in UI represented by comboBox. Other UserProfile attributes represented by text-fields are already send to UserProfileHandler with value null if they are empty).
Description of problem: Cannot delete users with OracleDB 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.