Forum Stats

  • 3,782,596 Users
  • 2,254,669 Discussions
  • 7,880,132 Comments

Discussions

Inexplicable constraint differences across two seemingly identical 10g DBs

766965
766965 Member Posts: 6
edited Apr 15, 2010 12:23PM in General Database Discussions
Hi,

I have a moderate amount of general database experience, but very little when it comes to Oracle, so I apologize if the answer to this question is obvious or if more information is needed. I've inherited a Ruby on Rails site to maintain and it's driven by an Oracle 10g database. There are two installations of the site--dev and production--and both are identical in terms of Rails codebase (with the exception of things like caching settings and DB credentials). The problem I'm having is that on production, I'm getting a constraint violation when inserting to a table:

ActiveRecord::StatementInvalid (OCIError: ORA-00001: unique constraint (DATABASE_OWNER.PK_REGISTRATION_OWNERSHIP) violated: INSERT INTO registration_ownerships (updated_at, company_ownership_id, created_by, updated_by, registration_id, created_at) VALUES ('2006-05-04 16:30:47', 3, NULL, NULL, 2920, '2006-05-04 16:30:47')):
/usr/local/lib/ruby/gems/1.8/gems/activerecord-oracle-adapter-1.0.0.9250/lib/active_record/connection_adapters/oracle_adapter.rb:221:in `execute'
app/controllers/vendors_controller.rb:94:in `create'

My application includes a model for users, which contain one registration, and each registration may be mapped to a company ownership type through a registration_ownerships table. When users sign up with a registration ownership on the production site, they get a 500 internal server error, and the logs reflect the above; however, on dev, everything works fine. To make things worse, if a user signs up on production without a company ownership and then later (through the account management utility, which is almost identical in terms of functionality to signing up) adds a company ownership to their account, everything works fine (on both production and dev).

I've gone through the error logs and tried to find primary key violations in "registration_ownerships" on the production server, and I've come up empty each time. The PK_REGISTRATION_OWNERSHIP itself is defined as an index which is a combination of primary keys from the registrations and company_ownerships tables. I used SQL developer to run a diff across the two databases and didn't find anything meaningful.

What could be causing this behavior? I'd very much appreciate any feedback!

Best,
Justin
Tagged:
«1

Answers

  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    This is really a data dependent error.
    So the problem is user DATA & not database metadata
  • 249425
    249425 Member Posts: 1,482
    Please try to run that command via sql*plus on production db
    INSERT INTO registration_ownerships (updated_at, company_ownership_id, created_by, updated_by, registration_id, created_at) VALUES ('2006-05-04 16:30:47', 3, NULL, NULL, 2920, '2006-05-04 16:30:47')):
    Please also post your DDL (creation scripts for your tables, constraints, indexes), it would be easier to troubleshoot.

    With kind regards
    Krystian Zieja
    http://www.projectenvision.com
  • 766965
    766965 Member Posts: 6
    Thanks for the response. As I mentioned in my first post, selecting against the constrained table with the apparently conflicting values doesn't yield any results; what other data could be causing the constraint violation?

    Incidentally, as the error only occurs on production, I'm a little leery of trying anything too aggressive because I can't afford taking the (high-volume) site down inadvertently.
  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    We don't know what you have.
    We don't know what you do.
    We don't know what you see.

    You claim all is OK.
    Oracle reports a problem.
    I believe Oracle unless & until proof positive is provided that proves otherwise.

    use sqlplus along with CUT & PASTE so we can see what you do & how Oracle responds.

    With Oracle characters between single quote marks are STRINGS!
    'This is a string, 2009-12-31, not a date'
    When a DATE datatype is desired, then use TO_DATE() function.
  • 766965
    766965 Member Posts: 6
    Hi Krystian,

    Thanks for the response. Running the SQL generates an error (ORA-01861: literal does not match format string ); if I format the query with the appropriate to_date invocations (i.e. by wrapping the date strings with a to_date('yyyy-mm-dd hh24:mi:ss') then the query completes successfully without throwing a constraint error.

    With respect to the DDL: what's the easiest way to generate it? As I said, I inherited the project for maintenance--I didn't build the DB initially.

    Thanks,
    Justin
  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    When failure occurs, are the VALUES associated with the INSERT logged for subsequent testing?
  • 766965
    766965 Member Posts: 6
    I don't have access to the Oracle logs, unless there's a way I can attain them just through a standard DB connection. I'm a web application developer; my main focus is on the application codebase (which is in Rails and therefore at a high level of SQL abstraction). The failed insert's values are logged to the Rails logs, and in fact the error snippet that I pasted in earlier was directly taken from there. It seems that that can't be the SQL that's actually being executed, though, because as you pointed out, the dates weren't formatted correctly.

    As I mentioned, I've tried selecting against the values in the logged error message and can't find any preexisting data that would pose a conflict across the index. If you like I can cut and paste the results of that query.
  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    I don't have access to the Oracle logs
    Oracle does not consider ORA-00001 to be anything it cares about & does not log this failure itself.
    Any such logging would have to be done by the application, because this error is really an application dependent error.

    There is an APPLICATION bug which precipitating this error; whether you believe this or not.
  • 766965
    766965 Member Posts: 6
    I'd be glad to hear that it's an application bug and not something Oracle-related--I'll take another hard look at the differences between the production and dev codebases and see if I can't spot it. Thanks!
  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    I'll take another hard look at the differences between the production and dev codebases and see if I can't spot it.
    The code should be the same.
    This error is not directly code based.
    It could be an error of omission within the code.
    This error is a combination of code not doing things 100% correct & DATA already existing within DB.
    Error occurs when trying to INSERT and UNIQUE constraint is violated.
This discussion has been closed.