oracle-database primary-key quartz-scheduler ora-00001

Identical Oracle db setups: exception on just one of them

edit: Look to the end of this question for what caused the error and how I found out.

I have a very strange exception thrown on me from Hibernate when I run an app that does batch inserts of data into an oracle database. The error comes from the Oracle database, ORA-00001, which

" means that an attempt has been made to insert a record with a duplicate (unique) key. This error will also be generated if an existing record is updated to generate a duplicate (unique) key."

The error is weird because I have created the same table (exactly same definition) on another machine where I do NOT get the same error if I use that through my app. AND all the data get inserted into the database, so nothing is really rejected.

There has to be something different between the two setups, but the only thing I can see that is different is the banner output that I get when issuing

select * from v$version where banner like 'Oracle%';

The database that gives me trouble: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
The one that works: Oracle Database 10g Release 10.2.0.3.0 - 64bit Production

Table definitions, input, and the app I wrote is the same for both. The table involved is basically a four column table with a composite id (serviceid, date, value1, value2) - nothing fancy.

Any ideas on what can be wrong? I have started out clean several times, dropping both tables to start on equal grounds, but I still get the error from the database.

Some more of the output:

Caused by: java.sql.BatchUpdateException: ORA-00001: unique constraint (STATISTICS.PRIMARY_KEY_CONSTRAINT) violated

    at oracle.jdbc.driver.DatabaseError.throwBatchUpdateException(DatabaseError.java:367)
    at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:8728)
    at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)

How I found out what caused the problem

Thanks to APC and ik_zelf I was able to pinpoint the root cause of this error. It turns out the Quartz scheduler was wrongly configured for the production database (where the error turned up). For the job running against the non-failing oracle server I had <cronTriggerExpression>0/5 * * * * ?</cronTriggerExpression> which ran the batch job every five seconds. I figured that once a minute was sufficent for the other oracle server, and set the quartz scheduler up with * */1 * * * ?. This turns out to be wrong, and instead of running every minute, this ran every second!

Each job took approximately 1.5-2 seconds, and thus two or more jobs were running concurrently, thus causing simultaneous inserts on the server. So instead of inserting 529 elements, I was getting anywhere from 1000 to 2000 inserts. Changing the crontrigger expression to the same as the other one, running every five seconds, fixed the problem.

To find out what was wrong I had to set true in hibernate.cfg.xml and disable the primary key constraint on the table.

-- To catch exceptions
-- to find the offending rows run the following query
-- SELECT * FROM uptime_statistics, EXCEPTIONS WHERE MY_TABLE.rowid = EXCEPTIONS.row_id;
create table exceptions(row_id rowid,
                        owner varchar2(30),
                        table_name varchar2(30),
                        constraint varchar2(30));

-- This table was set up
CREATE TABLE MY_TABLE
  (
    LOGDATE DATE NOT NULL,
    SERVICEID           VARCHAR2(255 CHAR) NOT NULL,
    PROP_A   NUMBER(10,0),
    PROP_B NUMBER(10,0),
    CONSTRAINT PK_CONSTRAINT PRIMARY KEY (LOGDATE, SERVICEID)
  );

-- Removed the constraint to see what was inserted twice or more
alter table my_table
  disable constraint PK_CONSTRAINT;

-- Enable this later on to find rows that offend the constraints
alter table my_table
  enable constraint PK_CONSTRAINT
    exceptions into exceptions;

Solution

You have a unique compound constraint. ORA-00001 means that you have two or more rows which have duplicate values in ServiceID, Date, Value1 and/or Value2. You say the input is the same for both databases. So either:

you are imagining that your program is hurling ORA-00001
you are mistaken that the input is the same in both runs.

The more likely explanation is the second one: one or more of your key columns is populated by an external source or default value (e.g. code table for ServiceId or SYSDATE for the date column). In your failing database this automatic population is failing to provide a unique value. There can be any number of reasons why this might be so, depending on what mechanism(s) you're using. Remember that in a unique compound key NULL entries count. That is, you can have any number of records (NULL,NULL.NULL,NULL) but only one for (42,NULL,NULL,NULL).

It is hard for us to guess what the actual problem might be, and almost as hard for you (although you do have the advantage of being the code's author, which ought to grant you some insight). What you need is some trace statements. My preferred solution would be to use Bulk DML Exception Handling but then I am a PL/SQL fan. Hibernate allows you to hook in some logging to your programs: I suggest you switch it on. Code is a heck of a lot easier to debug when it has decent instrumentation.

As a last resort, disable the constraint before running the batch insert. Afterwards re-enable it like this:

alter table t42
    enable constraint t42_uk
        exceptions into my_exceptions
/

This will fail if you have duplicate rows but crucially the MY_EXCEPTIONS table will list all the rows which clash. That at least will give you some clue as to the source of the duplication. If you don't already have an exceptions table you will have to run a script: $ORACLE_HOME/rdbms/admin/utlexcptn.sql ( you may need a DBA to gain access to this directory).

tl;dr

insight requires information: instrument your code.