ActiveRecord::StatementInvalid (OCIError: ORA-00001: unique constraint (xxxxxxxx) violated:

Rails
1

Hello,

I have developed a small RoR app(ruby 1.8.7, rails 2.1.0, rubygem 0.9.4)
a
few years ago and now I moved the app on Windows 2008 server.

I have a weird issue that insert doesn’t work all the time. This is the
error message and there is no duplicated PK insertion when I check DB
table.

ActiveRecord::StatementInvalid (OCIError: ORA-00001: unique constraint
(xxxxxxxx) violated: INSERT INTO xxxxxx (xxxx, xxxx, xxxx, xxxx, …)
…
…
…

I am the only one who is testing and there are no opened sessions. I am
sure production DB schema and test DB schema are identical. I tested
with
all three environments with test DB schema, but didn’t help.

One thing to note is that after I get the RoR error message, if I just
hit
F5 key a few times, the insertion works and I get the next successful
page.

This same app is working fine on Windows 2003 server though. (I just
copied
the app dir and all the RoR setup.)

Any clues?

thanks in advance.

Daniel

2

It looks like there may be a unique constraint on the database (not the
Ruby code)

Generally Rails is against database constraints (although there is some
disagreement about this in the community). I would look for a database
constraint, remove it, and see if that fixes the problem.

-Jason

3

Database constraints are extremely valuable - it’s the only way to
guarantee certain conditions (especially uniqueness).

Have you tried increasing logging level, and checking logs for what
queries are actually performed?
You can also run rake db:schema:dump (or even db:structure:dump) on
the production to get production database schema and verify it.
Maybe loading dump of the production database locally (if that’s
possible for you - privacy, etc.) can help you debug those issues.

Michal

2014-09-18 16:39 GMT+02:00 Jason Fleetwood-Boldt [email protected]:

4

If I write the error message instead of xxxxxx, it is:
OCIError: ORA-00001: unique constraint (ABCSIGN_DBA.PK_ATTEND_IP)
violated:
INSERT INTO attend_ip (xxx, xxx, xxx, …)

The PK prefix looks like a violation of the primary key. Maybe,
inserting
same row again or some kind of replay happens. If so, why this is not
happening in production with same code???

How do I check if that case happens?

Daniel

5

Ok, DBA and I figured this out. The test schema and prod schema were not
identical and the sequence has been messed up.
I still don’t know why the pk key constraint name (that was only in prod
db
schema at that time) appeared on the error message during testing.
Anyway, thank you all for your help…!

Daniel

On Sat, Sep 20, 2014 at 11:12 AM, Walter Lee D. [email protected]

6

On Sep 18, 2014, at 5:48 PM, kimda [email protected] wrote:

If I write the error message instead of xxxxxx, it is:
OCIError: ORA-00001: unique constraint (ABCSIGN_DBA.PK_ATTEND_IP) violated:
INSERT INTO attend_ip (xxx, xxx, xxx, …)

The PK prefix looks like a violation of the primary key. Maybe, inserting same
row again or some kind of replay happens. If so, why this is not happening in
production with same code???

How do I check if that case happens?

Daniel

In the config/environments/production.rb file, turn the log level up to
debug, and restart the server, and tail the log. You should be able to
see full queries and all as you hit various test pages in the site.

Walter