Mixing Oracle Results with VOMRS.

Problem Description

The problem we have started to see since 1 June 2008 (no code change in vomrs) VOMRS server is a multi threaded process and it looks like from time to time when the threads are executing the same query at the same time one of the query ends up with:

    SQL exception(Exhausted Resultset)

    There are two examples (the second one reminded me of the problem we
    have seen before ....)


    Timer-3 and Timer-4 are two different threads here, they are trying to
    execute the same query at the beginning and it looks like Timer-4 is
    stuck and ends with the error and Timer-3 is continuing :

    09/28/08 09:07:01,533 :INFO
    :Timer-4:fnal.vox.vomrs.tables.DBTablesBase.selectArray: SQL: SELECT
    m.member_id, m.i_id, m.rep_id, m.member_rights,
    m.member_status,m.status_reason,
    m.status_update_date,m.registration_date, md.distinguished_name,
    md.certificate_auth,md.serial_number,   ca.ca_id,

m.vo_exp_date,m.vo_exp_update_date,m.inst_exp_date,m.inst_exp_update_dat

    e, m.rule_vsn,  m.rule_vsn_update_date, n.notification_params FROM
    LCG_VOMRS_ALICE_W.member_dns md, LCG_VOMRS_ALICE_W.members m,
    LCG_VOMRS_ALICE_W.certificate_authorities ca,
    LCG_VOMRS_ALICE_W.notification_methods n WHERE
    md.distinguished_name='/C=CN/O=HEP/O=CCNU/OU=PHYS/CN=Mengliang Wang'
    AND
    md.certificate_auth='/C=CN/O=HEP/CN=gridca-cn/Email=gridca@ihep.ac.cn'
    AND md.member_id=m.member_id AND

md.certificate_auth=ca.certificate_auth

    AND n.member_id=m.member_id
    09/28/08 09:07:01,533 :INFO
    :Timer-3:fnal.vox.vomrs.tables.DBTablesBase.selectArray: SQL: SELECT
    m.member_id, m.i_id, m.rep_id, m.member_rights,
    m.member_status,m.status_reason,
    m.status_update_date,m.registration_date, md.distinguished_name,
    md.certificate_auth,md.serial_number,   ca.ca_id,

m.vo_exp_date,m.vo_exp_update_date,m.inst_exp_date,m.inst_exp_update_dat

    e, m.rule_vsn,  m.rule_vsn_update_date, n.notification_params FROM
    LCG_VOMRS_ALICE_W.member_dns md, LCG_VOMRS_ALICE_W.members m,
    LCG_VOMRS_ALICE_W.certificate_authorities ca,
    LCG_VOMRS_ALICE_W.notification_methods n WHERE

md.distinguished_name='/C=UK/O=eScience/OU=Birmingham/L=ParticlePhysics/

    CN=zoe louise matthews'  AND
    md.certificate_auth='/C=UK/O=eScienceCA/OU=Authority/CN=CA'  AND
    md.member_id=m.member_id AND md.certificate_auth=ca.certificate_auth

AND

    n.member_id=m.member_id
    09/28/08 09:07:01,535 :INFO
    :Timer-3:fnal.vox.vomrs.tables.DBTablesBase.selectArray: Rows

retrieved:

    1
    09/28/08 09:07:01,535 :INFO
    :Timer-3:fnal.vox.vomrs.tables.DBTablesBase.selectArray: SQL: SELECT
    notification_method, notification_params, notification_retries,
    notification_timeout,notification_status,notification_status_time FROM
    LCG_VOMRS_ALICE_W.notification_methods WHERE member_id=780
    09/28/08 09:07:01,536 :INFO
    :Timer-3:fnal.vox.vomrs.tables.DBTablesBase.selectArray: Rows

retrieved:

    1
    09/28/08 09:07:01,536 :INFO
    :Timer-3:fnal.vox.vomrs.tables.DBTablesBase.selectArray: SQL: SELECT
    pd.personal_name,mi.personal_value FROM

LCG_VOMRS_ALICE_W.personal_data

    pd,LCG_VOMRS_ALICE_W.member_identities mi WHERE mi.member_id=780 AND
    pd.persistency_ind='Y' AND pd.pd_id=mi.pd_id
    09/28/08 09:07:01,538 :INFO
    :Timer-3:fnal.vox.vomrs.tables.DBTablesBase.selectArray: Rows

retrieved:

    4
    09/28/08 09:07:01,538 :INFO
    :Timer-3:fnal.vox.vomrs.tables.DBTablesBase.selectArray: SQL: SELECT
    pd.personal_name,mi.personal_value FROM

LCG_VOMRS_ALICE_W.personal_data

    pd,LCG_VOMRS_ALICE_W.member_identities mi WHERE mi.member_id=780 AND
    pd.voms_attribute='Y' AND pd.pd_id=mi.pd_id
    09/28/08 09:07:01,539 :INFO
    :Timer-3:fnal.vox.vomrs.tables.DBTablesBase.selectArray: Rows

retrieved:

    0
    09/28/08 09:07:01,539 :INFO
    :Timer-3:fnal.vox.vomrs.tables.DBTablesBase.selectArray: SQL: SELECT
    distinguished_name,certificate_auth FROM LCG_VOMRS_ALICE_W.member_dns
    WHERE member_id=780 AND certificate_status='Approved'
    09/28/08 09:07:01,540
    :ERROR:Timer-4:fnal.vox.vomrs.error.DBError.<init>: DB Error: SQL
    exception(Exhausted Resultset)

I have more disturbing situation though:

    Timer-4 and Timer-5 have issued the same query , Timer-5 died with
    Exhausted Resultset error, Timer-4 inherited Timer-5 Resultset!!!!!  
    Member_id returned for Timer-4 should be 868 and member_id returned

from

    Timer-5 is 1319, look what is in Timer-4 after Timer-5 is failed:

    09/18/08 02:33:30,066 :INFO
    :Timer-4:fnal.vox.vomrs.tables.DBTablesBase.selectArray: SQL: SELECT
    pd.personal_name,mi.personal_value FROM

LCG_VOMRS_ATLAS_W.personal_data

    pd,LCG_VOMRS_ATLAS_W.member_identities mi WHERE mi.member_id=1319.....
     ^^^^^^^^^^^

log:

    *********************************************
    09/18/08 02:33:30,010 :INFO
    :Timer-4:fnal.vox.vomrs.tables.DBTablesBase.selectArray: SQL: SELECT
    m.member_id, m.i_id, m.rep_id, m.member_rights,
    m.member_status,m.status_reason,
    m.status_update_date,m.registration_date, md.distinguished_name,
    md.certificate_auth,md.serial_number,   ca.ca_id,

m.vo_exp_date,m.vo_exp_update_date,m.inst_exp_date,m.inst_exp_update_dat

    e, m.rule_vsn,  m.rule_vsn_update_date, n.notification_params FROM
    LCG_VOMRS_ATLAS_W.member_dns md, LCG_VOMRS_ATLAS_W.members m,
    LCG_VOMRS_ATLAS_W.certificate_authorities ca,
    LCG_VOMRS_ATLAS_W.notification_methods n WHERE
    md.distinguished_name='/C=UK/O=eScience/OU=Cambridge/L=UCS/CN=james
    frost'  AND

md.certificate_auth='/C=UK/O=eScienceCA/OU=Authority/CN=CA'

    AND md.member_id=m.member_id AND

md.certificate_auth=ca.certificate_auth

    AND n.member_id=m.member_id
    09/18/08 02:33:30,061 :INFO
    :Timer-5:fnal.vox.vomrs.tables.DBTablesBase.selectArray: SQL: SELECT
    m.member_id, m.i_id, m.rep_id, m.member_rights,
    m.member_status,m.status_reason,
    m.status_update_date,m.registration_date, md.distinguished_name,
    md.certificate_auth,md.serial_number,   ca.ca_id,

m.vo_exp_date,m.vo_exp_update_date,m.inst_exp_date,m.inst_exp_update_dat

    e, m.rule_vsn,  m.rule_vsn_update_date, n.notification_params FROM
    LCG_VOMRS_ATLAS_W.member_dns md, LCG_VOMRS_ATLAS_W.members m,
    LCG_VOMRS_ATLAS_W.certificate_authorities ca,
    LCG_VOMRS_ATLAS_W.notification_methods n WHERE
    md.distinguished_name='/C=CA/O=Grid/OU=physics.utoronto.ca/CN=Pierre
    Savard'  AND md.certificate_auth='/C=CA/O=Grid/CN=Grid Canada
    Certificate Authority'  AND md.member_id=m.member_id AND
    md.certificate_auth=ca.certificate_auth AND n.member_id=m.member_id
    09/18/08 02:33:30,063
    :ERROR:Timer-5:fnal.vox.vomrs.error.DBError.<init>: DB Error: SQL
    exception(Exhausted Resultset)
    09/18/08 02:33:30,063 :INFO
    :Timer-4:fnal.vox.vomrs.tables.DBTablesBase.selectArray: Rows

retrieved:

    1
    09/18/08 02:33:30,063 :INFO
    :Timer-4:fnal.vox.vomrs.tables.DBTablesBase.selectArray: SQL: SELECT
    notification_method, notification_params, notification_retries,
    notification_timeout,notification_status,notification_status_time FROM
    LCG_VOMRS_ATLAS_W.notification_methods WHERE member_id=1319
    09/18/08 02:33:30,064
    :ERROR:Timer-5:fnal.vox.vomrs.error.DBError.<init>: ****** ROLLBACK
    ISSUED *****
    09/18/08 02:33:30,064
    :ERROR:Timer-5:fnal.vox.vomrs.lcg.LCGUserValidator.processImpl: Failed
    to update database DBError: Exhausted Resultset
    09/18/08 02:33:30,064 :INFO
    :Timer-5:fnal.vox.vomrs.lcg.LCGUserValidator.processImpl: LCG User
    Validator ended
    09/18/08 02:33:30,066 :INFO
    :Timer-4:fnal.vox.vomrs.tables.DBTablesBase.selectArray: Rows

retrieved:

    1
    09/18/08 02:33:30,066 :INFO
    :Timer-4:fnal.vox.vomrs.tables.DBTablesBase.selectArray: SQL: SELECT
    pd.personal_name,mi.personal_value FROM

LCG_VOMRS_ATLAS_W.personal_data

    pd,LCG_VOMRS_ATLAS_W.member_identities mi WHERE mi.member_id=1319 AND
    pd.persistency_ind='Y' AND pd.pd_id=mi.pd_id
    09/18/08 02:33:30,067 :INFO
    :Timer-4:fnal.vox.vomrs.tables.DBTablesBase.selectArray: Rows

retrieved:

    4

Update One

ad

-- SteveTraylen - 21 Mar 2009