How to Analyze Database Constraint Errors within censhare

This document will show how to analyse a (Oracle/PostgreSQL) constraint error step by step.

General Hints

A valid database schema documentation is necessary as reference
Tip: check censhare Server documentation release tarball within ../doc/database/

Oracle Database: Important Hints

Per default Oracle doesn't show the values for which the constraint has violated within its error messages therefore it is necessary to activate the special logging called "constraint checks" and provoke the problem again to see those values within the server-0.0.log
Only perform this activity outside of main production hours in order to avoid performance implications for all users
Enable the "Constraint check" within Admin-Client / Services / Database only temporarily in order to avoid performance implications for all users and deactivate it after the troubleshooting has been done

Oracle Database: Problem description

For example, if you get the following error message ...

CODE

Error occurred: Thu Mar 29 09:06:37 CEST 2012
Client-Version: 4.3.3, 27.01.2012
Server-Name: xyz
Server-Version: 4.3.3
Build-Nummer: 906

Fehler:ORA-02091: transaction rolled back
ORA-02291: integrity constraint (CORPUS.ASSET_FEATURE_FK2) violated - parent key not found


Beschreibung:null

Fehler-Code:com.censhare.support.transaction.TransactionException

Stacktrace:
com.censhare.server.rmi.RMIServerException[com.censhare.support.transaction.TransactionException]: ORA-02091: transaction rolled back
ORA-02291: integrity constraint (CORPUS.ASSET_FEATURE_FK2) violated - parent key not found

-----cause-----
com.censhare.support.transaction.TransactionException: ORA-02091: transaction rolled back
ORA-02291: integrity constraint (CORPUS.ASSET_FEATURE_FK2) violated - parent key not found

	at com.censhare.support.transaction.TransactionManagerImpl.commitStep(TransactionManagerImpl.java:358)
	at com.censhare.support.transaction.TransactionManagerImpl.commit(TransactionManagerImpl.java:384)
	at com.censhare.server.kernel.Command.completeAll(Command.java:883)
	at com.censhare.server.kernel.CommandExecutorImpl.executeNextIntern(CommandExecutorImpl.java:442)
	at com.censhare.server.kernel.CommandExecutorImpl.executeNext(CommandExecutorImpl.java:87)
	at sun.reflect.GeneratedMethodAccessor90.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at com.censhare.support.service.ServiceHandlerSync.invoke(ServiceHandlerSync.java:163)
	at $Proxy26.executeNext(Unknown Source)
	at com.censhare.server.kernel.Command.executeNextSync(Command.java:1337)
	at com.censhare.server.rmi.RMICommandImpl.executeNextSync(RMICommandImpl.java:660)
	at sun.reflect.GeneratedMethodAccessor124.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:303)
	at sun.rmi.transport.Transport$1.run(Transport.java:159)
	at java.security.AccessController.doPrivileged(Native Method)
	at sun.rmi.transport.Transport.serviceCall(Transport.java:155)
	at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:535)
	at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run0(TCPTransport.java:790)
	at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:649)
	at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
	at java.lang.Thread.run(Thread.java:662)
Caused by: java.sql.SQLException: ORA-02091: transaction rolled back
ORA-02291: integrity constraint (CORPUS.ASSET_FEATURE_FK2) violated - parent key not found

Oracle Database: Analysis

1. You should be able to reproduce the error with an asset ID and some defined steps, if not you may ask the user (customer) for the detailed steps how to reproduce this with a given asset ID (perhaps a screencast with sound makes sense)

2. Check the meaning of the reported constraint in this example ASSET_FEATURE_FK2 in the database schema documentation for the server version (in this example, version 4.3.3)

CODE

asset_feature_fk2
    (feature) references table feature (key), initially deferred
    Reference to the attribute definition.

This means that a feature references to the feature key and this must be unique.

3. Check the server-0.0.log file for the values of the Oracle database constraint

CODE

2012.03.29-09:27:28.316 INFO   : RMI TCP Connection(4691)-194.209.181.130: RMIServerService: server.20120329.092725.535[xyz]: rmi:execute-next-sync asset.execute failed: com.censhare.support.transaction.TransactionException: ORA-02091: transacti
ORA-02291: integrity constraint (CORPUS.ASSET_FEATURE_FK2) violated - parent key not found

2012.03.29-09:27:28.317 WARNING: RMI TCP Connection(4691)-194.209.181.130: RMIServerService: server.20120329.092725.535[user1]: 
com.censhare.support.transaction.TransactionException: ORA-02091: transaction rolled back
ORA-02291: integrity constraint (CORPUS.ASSET_FEATURE_FK2) violated - parent key not found

        at com.censhare.support.transaction.TransactionManagerImpl.commitStep(TransactionManagerImpl.java:358)
        at com.censhare.support.transaction.TransactionManagerImpl.commit(TransactionManagerImpl.java:384)
        at com.censhare.server.kernel.Command.completeAll(Command.java:883)
        at com.censhare.server.kernel.CommandExecutorImpl.executeNextIntern(CommandExecutorImpl.java:442)
        at com.censhare.server.kernel.CommandExecutorImpl.executeNext(CommandExecutorImpl.java:87)
        at sun.reflect.GeneratedMethodAccessor90.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at com.censhare.support.service.ServiceHandlerSync.invoke(ServiceHandlerSync.java:163)
        at $Proxy26.executeNext(Unknown Source)
        at com.censhare.server.kernel.Command.executeNextSync(Command.java:1337)
        at com.censhare.server.rmi.RMICommandImpl.executeNextSync(RMICommandImpl.java:660)
        at sun.reflect.GeneratedMethodAccessor124.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:303)
        at sun.rmi.transport.Transport$1.run(Transport.java:159)
        at java.security.AccessController.doPrivileged(Native Method)
        at sun.rmi.transport.Transport.serviceCall(Transport.java:155)
        at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:535)
        at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run0(TCPTransport.java:790)
        at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:649)
        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
        at java.lang.Thread.run(Thread.java:662)
Caused by: java.sql.SQLException: ORA-02091: transaction rolled back
ORA-02291: integrity constraint (CORPUS.ASSET_FEATURE_FK2) violated - parent key not found

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:389)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:382)
        at oracle.jdbc.driver.T4C7Ocommoncall.processError(T4C7Ocommoncall.java:93)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
        at oracle.jdbc.driver.T4C7Ocommoncall.doOCOMMIT(T4C7Ocommoncall.java:75)
        at oracle.jdbc.driver.T4CConnection.doCommit(T4CConnection.java:565)
        at oracle.jdbc.driver.PhysicalConnection.commit(PhysicalConnection.java:3835)
        at oracle.jdbc.driver.PhysicalConnection.commit(PhysicalConnection.java:3841)
        at oracle.jdbc.OracleConnectionWrapper.commit(OracleConnectionWrapper.java:133)
        at com.censhare.server.support.wsql.WConnectionImpl.commitStep(WConnectionImpl.java:443)
        at com.censhare.server.manager.WConnectionTR.commit(WConnectionTR.java:78)
        at com.censhare.support.transaction.TransactionManagerImpl.commitStep(TransactionManagerImpl.java:351)
        ... 24 more
2012.03.29-09:27:35.176 INFO   : RMI TCP Connection(4691)-194.209.181.130: RMIServerService: server.20120329.092735.176[ul]: rmi:create-command asset.execute
2012.03.29-09:27:35.210 INFO   : RMI TCP Connection(4691)-194.209.181.130: RMIServerService: server.20120329.092735.176[ul]: rmi:execute-sync asset.execute called
2012.03.29-09:27:35.210 INFO   : RMI TCP Connection(4691)-194.209.181.130: CommandExecutor: server.20120329.092735.176[ul]: asset.execute execute
2012.03.29-09:27:35.213 INFO   : T015: AssetManagementService: xyz.20120329.092735.176[ul]: Check out abort of asset: asset.asset_un[id=114451, currversion=-2]

No values, but an asset ID where this happens: 114451

4. Enable the constraint checks in the database configuration at Admin Client | Configuration | Services | Database | Constraint check (Enabled?) and perform a configuration refresh

5. Provoke the error, in this example by performing a "cancel edit" of asset ID 114451

6. Check the server-0.0.log file for the values in squared brackets of the constraint, e.g. asset.asset_un[id=114451, currversion=-2]

If step 6. is not showing any values: Refer to #3025604 - 29.09.16, 09:29
We already had situations that there are no values within "[" and "]" shown within the log file potentially caused by an Oracle bug, which is fixed by removing the compression of index asset_idx22 by using the following statements (do within maintenance window):
SQL> drop index asset_idx22;
SQL> CREATE INDEX asset_idx22 ON asset (id, currversion, tcn) ;

6a. If you get an error message like below:

CODE

ORA-00054: Resource is busy and request specified with NOWAIT or timeout

then you can try first this:

CODE

ALTER SESSION SET ddl_lock_timeout=30;

and then try to drop the index again.

6b. If you get an error message like below:

CODE

ORA-02429: cannot drop index used for enforcement of unique/primary key

then you can try these below commands to get rid of this error:

CODE

-- disable constraint
alter table asset disable constraint "ASSET_UN" cascade;

-- drop index
drop index asset_idx22;

-- enable contstraint
alter table asset enable constraint "ASSET_UN";

-- re-create index
CREATE INDEX asset_idx22 ON asset (id, currversion, tcn);

You can find all disabled constraints by below command:

CODE

SELECT c.owner, c.table_name, c.constraint_name FROM user_constraints c, user_tables t WHERE c.table_name = t.table_name AND c.status = 'DISABLED' ORDER BY c.constraint_type;

And, you can re-enable it by below command:

CODE

BEGIN 
FOR c IN 
(SELECT c.owner, c.table_name, c.constraint_name 
FROM user_constraints c, user_tables t 
WHERE c.table_name = t.table_name 
AND c.status = 'DISABLED' 
ORDER BY c.constraint_type) 
LOOP 
dbms_utility.exec_ddl_statement('alter table "' || c.owner || '"."' || c.table_name || '" enable constraint ' || c.constraint_name); 
END LOOP; 
END;
/

7. One of the reported values seems not to be unique and/or already existent within the database

8. Replace value xyz with a string that doesn't exist yet and/or change it within the database and check if the error has disappeared

9. Disable the constraint checks in the database configuration at Admin Client | Configuration | Services | Database | Constraint check (Enabled?) and perform a configuration refresh.

PostgreSQL Database: Important Hints

Per default PostgreSQL shows the values for which the constraint has violated within its error messages therefore it is *not* necessary to activate the special logging called "constraint checks" like for Oracle Databases and provoke the problem again to see those values within the server-0.0.log

As the constraint checks enabled false/true parameter won't be recognized by a PostgreSQL database at all it has no performance implications for all users if it is activated

As there are currently no other parameters used than the connection string for PostgreSQL databases the other parameters within the Database Services are only valid for Oracle Databases. Within one of the next censhare versions the Oracle related parameters including the constraint-checks parameter will only be shown in the Admin-Client UI if an Oracle Database connection string is used.

PostgreSQL Database: Problem description

For example, if you get the following error message ...

CODE

Client Version: 2017.2.1, 26.05.2017
Login Name: censhare
Host URL: frmis://cs-test20172-css01/corpus.RMIServerSSL
Server Name: master
Server Version: 2017.2.0
Build Number: 5225
Server DB Version: 2017.2.0.0008
Java Version: 1.6.0_65
VFS Mac: 1.1.2
Operating System: Mac OS X, 10.12.5, x86_64

The following error occurred at May 29, 2017 12:05:47 PM.

com.censhare.server.rmi.RMIServerException[com.censhare.support.transaction.RollbackException]: java.lang.RuntimeException: java.sql.SQLException: ERROR: duplicate key value violates unique constraint "asset_feature_un1"
  Detail: Key (value_asset_key, value_asset_key_feature)=(test:pe, censhare:resource-key) already exists.
	from SQL statement : INSERT INTO asset_feature (feature, value_string, asset_id, asset_currversion, sid, value_asset_key, value_asset_key_feature, party, timestamp, isversioned) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
	and parameter list: [censhare:resource-key, test:pe, 14113, 0, 47818, test:pe, censhare:resource-key, 10, 2017-05-29 12:05:46.0, 0]
-----cause-----
com.censhare.support.transaction.RollbackException: java.lang.RuntimeException: java.sql.SQLException: ERROR: duplicate key value violates unique constraint "asset_feature_un1"
  Detail: Key (value_asset_key, value_asset_key_feature)=(test:pe, censhare:resource-key) already exists.
	from SQL statement : INSERT INTO asset_feature (feature, value_string, asset_id, asset_currversion, sid, value_asset_key, value_asset_key_feature, party, timestamp, isversioned) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
	and parameter list: [censhare:resource-key, test:pe, 14113, 0, 47818, test:pe, censhare:resource-key, 10, 2017-05-29 12:05:46.0, 0]
	at com.censhare.support.transaction.TransactionManagerImpl.end(TransactionManagerImpl.java:250)
	at com.censhare.manager.assetmanager.AssetManager$ExecuteMethod.executeAssetCommand(AssetManager.java:241)
	at com.censhare.manager.assetmanager.AssetManager$ExecuteMethod.execute(AssetManager.java:121)
	at com.censhare.server.kernel.ManagerTemplate.execute(ManagerTemplate.java:201)
	at com.censhare.server.support.service.AbstractStaticService.executeCommand(AbstractStaticService.java:72)
	at sun.reflect.GeneratedMethodAccessor34.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at com.censhare.support.service.ServiceCall$Sync.innerExecute(ServiceCall.java:413)
	at com.censhare.support.service.ServiceCall$Sync.innerRun(ServiceCall.java:559)
	at com.censhare.support.service.ServiceCall$1.run(ServiceCall.java:226)
	at com.censhare.support.context.Platform.execute(Platform.java:104)
	at com.censhare.support.service.ServiceCall.run(ServiceCall.java:223)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at java.lang.Thread.run(Thread.java:745)
	at com.censhare.support.util.ManagedInterruptThread.run(ManagedInterruptThread.java:81)
	at com.censhare.support.service.ServiceHandlerAsync$Call.done(ServiceHandlerAsync.java:85)
	at com.censhare.support.service.ServiceCall$Sync.innerSetException(ServiceCall.java:371)
	at com.censhare.support.service.ServiceCall$Sync.innerExecute(ServiceCall.java:417)
	at com.censhare.support.service.ServiceCall$Sync.innerRun(ServiceCall.java:559)
	at com.censhare.support.service.ServiceCall$1.run(ServiceCall.java:226)
	at com.censhare.support.context.Platform.execute(Platform.java:104)
	at com.censhare.support.service.ServiceCall.run(ServiceCall.java:223)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at java.lang.Thread.run(Thread.java:745)
	at com.censhare.support.util.ManagedInterruptThread.run(ManagedInterruptThread.java:81)
	at com.censhare.support.service.ServiceHandlerAsync.invoke(ServiceHandlerAsync.java:146)
	at com.sun.proxy.$Proxy43.executeCommand(Unknown Source)
	at com.censhare.server.kernel.CommandExecutorImpl.executeCommandIntern(CommandExecutorImpl.java:312)
	at com.censhare.server.kernel.CommandExecutorImpl.executeIntern(CommandExecutorImpl.java:270)
	at com.censhare.server.kernel.CommandExecutorImpl.execute(CommandExecutorImpl.java:58)
	at sun.reflect.GeneratedMethodAccessor33.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at com.censhare.support.service.ServiceHandlerSync.invoke(ServiceHandlerSync.java:163)
	at com.sun.proxy.$Proxy42.execute(Unknown Source)
	at com.censhare.server.kernel.Command.executeSync(Command.java:1339)
	at com.censhare.server.rmi.RMICommandImpl.executeSync(RMICommandImpl.java:442)
	at sun.reflect.GeneratedMethodAccessor258.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:324)
	at sun.rmi.transport.Transport$1.run(Transport.java:200)
	at sun.rmi.transport.Transport$1.run(Transport.java:197)
	at java.security.AccessController.doPrivileged(Native Method)
	at sun.rmi.transport.Transport.serviceCall(Transport.java:196)
	at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:568)
	at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run0(TCPTransport.java:826)
	at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.lambda$run$0(TCPTransport.java:683)
	at java.security.AccessController.doPrivileged(Native Method)
	at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:682)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.RuntimeException: java.lang.RuntimeException: java.sql.SQLException: ERROR: duplicate key value violates 
[output shortened as it repeats]

PostgreSQL Database: Analysis

1. You should be able to reproduce the error with an asset ID and some defined steps, if not you may ask the user (customer) for the detailed steps how to reproduce this with a given asset ID (perhaps a screencast with sound makes sense)

2. Check the meaning of the reported constraint in this example ASSET_FEATURE_UN1 in the database schema documentation for the server version (in this example, version 2017.2.0)

CODE

asset_feature_un1
    unique (value_asset_key, value_asset_key_feature)

This means that a value_asset_key references to the value_asset_key_feature and this must be unique.

3. The PostgreSQL error messages are mostly self-explanatory "Key (value_asset_key, value_asset_key_feature)=(test:pe, censhare:resource-key) already exists." and it's not necessary to check the server-0.0.log file for the values of the PostgreSQL database constraint. Sometimes it is even not necessary to ask the user (customer) for a reproducible case, because you directly see the cause from the error message. The following message shows in its Details that you've tried to set a censhare:resource-key with the value "test:pe" which is already existent within the PostgreSQL Database. This is not allowed due to the constraint asset_feature_un1

CODE

com.censhare.server.rmi.RMIServerException[com.censhare.support.transaction.RollbackException]: java.lang.RuntimeException: java.sql.SQLException: ERROR: duplicate key value violates unique constraint "asset_feature_un1"

 Detail: Key (value_asset_key, value_asset_key_feature)=(test:pe, censhare:resource-key) already exists.
	from SQL statement : INSERT INTO asset_feature (feature, value_string, asset_id, asset_currversion, sid, value_asset_key, value_asset_key_feature, party, timestamp, isversioned) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
	and parameter list: [censhare:resource-key, test:pe, 14113, 0, 47818, test:pe, censhare:resource-key, 10, 2017-05-29 12:05:46.0, 0]