Forum OpenACS Q&A: sec

1: sec_sessions

Posted by Allan Regenbaum on 07/10/02 05:34 AM

On an ACS TCL 3.x installation, I have suddenly started to get

[09/Jul/2002:17:15:45][3468.2051][-sched-] Error:
ora8.c:3930:ora_tcl_command: error in `OCIStmtExecute ()': ORA-02292:
integrity constraint (DP1DEV.SYS_C007675) violated - child record
found

SQL:
delete from sec_sessions
where :current_time - last_hit > :session_life

every time sec_sweep_sessions is run. I see that
sec_session_properties is the only child linkage, and that these
tables track transient session data. Not knowing why this error has
suddenly appeared I wanted to clear these tables,(sec_sessions and
sec_session_properties) but before doing so wanted to ask if anyone
was aware of any collateral damage that may arise ?

Feedback is appreciated

2: Response to sec_sessions (response to 1)

Posted by David Walker on 07/10/02 07:25 AM

It should be safe to clear these tables.

I was getting a similar error in the Postgres version and I changed the sweep_sessions function to

   
proc sec_sweep_sessions {} {   
    set db [ns_db gethandle log]   
   
    set ns_time [ns_time]   
   
    ns_db dml $db "   
        delete from sec_session_properties where session_id in   
        (select session_id from sec_sessions   
      where  ${ns_time} - last_hit > [ad_parameter 
SessionLifetime "" 176800])   
    "   
   
    ns_db dml $db "   
        delete from sec_sessions   
        where  ${ns_time} - last_hit > [ad_parameter 
SessionLifetime "" 176800]   
    "   
    ns_db releasehandle $db   
}

so that the same time was used in both queries.

3: Response to sec_sessions (response to 1)

Posted by Brian Fenton on 07/10/02 11:15 AM

We've seen this a few times. I don't know what causes it but the following is what we do to clear it.

truncate table sec_session_properties;
drop table sec_session_properties;
truncate table sec_sessions;
create table sec_session_properties (
    session_id     references sec_sessions not null,
    module         varchar2(50) not null,
    property_name  varchar2(50) not null,
    property_value clob,
    -- transmitted only across secure connections?
    secure_p       char(1) check(secure_p in ('t','f')),
    primary key(session_id, module, property_name),
    foreign key(session_id) references sec_sessions on delete cascade
);

4: Response to sec_sessions (response to 1)

Posted by Allan Regenbaum on 07/10/02 05:46 PM

Modified the table definition per Davids guidance..seems problem taken care of ... open source collaboration strikes again 😊

Thanks Brian and David.

5: Response to sec_sessions (response to 1)

Posted by Cynthia Kiser on 07/10/02 06:38 PM

Allan, before you started getting these errors, had you either upgraded your ACS installation from a previous release? OR exported and reimported the data from your database? There had been some oddness about the referential integrity constraints in the security stuff. A constraint defined twice. When initially installed it runs without error but export/import causes the constraints to be reenabled in the opposite order and that throws errors.

You can see what constraints are enabled on the table with:

select c.constraint_name, search_condition, delete_rule,
column_name, position
from user_constraints c, user_cons_columns cc
where c.constraint_name	= cc.constraint_name
and c.owner = cc.owner
and c.table_name = 'SEC_SESSIONS';

6: Response to sec_sessions (response to 1)

Posted by Allan Regenbaum on 07/10/02 06:55 PM

I think this was most definitely could have been the cause. This data was imported from another instance ... thanks

Forum OpenACS Q&A: sec_sessions