Friday, August 26, 2011

OCIError: ORA-00001: unique constraint (APP.SYS_C9876543210) violated: INSERT INTO sessions

Maintaining an older app that uses Rails session persistence in the database SESSIONS table, and after a restore, we got the following issue:

OCIError: ORA-00001: unique constraint (APP.SYS_C9876543210) violated: INSERT INTO sessions (data, created_at, updated_at, session_id, id) VALUES(empty_clob(), TO_DATE('2011-08-26 10:55:08','YYYY-MM-DD HH24:MI:SS'), TO_DATE('2011-08-26 10:55:08','YYYY-MM-DD HH24:MI:SS'), 'd33f1cb3386333e7fa7a4c8b206a0716a08e396f07b28171bd9ed13a14bc8', 3478952) ...

The issue is that the session id that it is trying to insert is not unique, so it is violating the primary key constraint on the table. You can fix this manually by clearing out the SESSIONS table, or you can use:

$ rake db:sessions:clear
Why this happened: in the example above, the '3478952' value is from the sequence associated with the SESSIONS table. I had erroneously requested a restore of tables to the database, so the DBA didn't restore the sequences and other objects with the Oracle user. The restored data (copied from a different environment) had more data in SESSIONS and a greater sequence number. To ensure there won't be other issues with sequences, etc. I'm asking the DBA to do another restore, this time copying all objects.

(I replaced a few values in the error message to make it more generic for this post.)

No comments: