unique constraint alert in FileRouter from BlockArrive merge statement

[ericvaandering]

Original Savannah ticket 96911 reported by magini on Tue Aug 21 10:43:24 2012.

Hi,

alert seen a couple of times since the Prod instance upgrade to 4.1.0:

+verbatim+ 2012-08-09 14:55:23: FileRouter[23487]: alert: database error: DBD::Oracle::st execute failed: ORA-00001: unique constraint (CMS_TRANSFERMGMT.PK_STATUS_BLOCK_ARRIVE) violated (DBD ERROR: OCIStmtExecute) [for Statement "merge into t_status_block_arrive barr using ( select bp.destination, bp.block, b.files, b.bytes, bp.priority, 0 basis, max(bp.time_arrive) time_arrive from t_status_block_path bp join t_dps_block b on b.id=bp.block group by bp.destination, bp.block, b.files, b.bytes, bp.priority ) bpaths on ( barr.block=bpaths.block and barr.destination=bpaths.destination ) when not matched then insert ( time_update, destination, block, files, bytes, priority, basis, time_arrive ) values ( :now, bpaths.destination, bpaths.block, bpaths.files, bpaths.bytes, bpaths.priority, bpaths.basis, bpaths.time_arrive )" with ParamValues: :now=1344524120.968] at /data/ProdNodes/PHEDEX/perl_lib/PHEDEX/Core/DB.pm line 322. -verbatim-

Goes away automatically after a few hours, but causes FileRouter not to commit the update of the block routing statistics.

Probable reason: after a priority change in the subscription, the path priorities are not updated immediately, so the statement can select two rows with different priority for the same block/destination.

Possible solution: join t_dps_block_dest and use t_dps_block_dest.priority instead.

[ericvaandering]

Closed by magini on Mon Dec 17 08:40:52 2012

[ericvaandering]

Comment by magini on Mon Dec 17 08:40:52 2012

Hi,

fix released in PHEDEX_4_1_1 and deployed to Production

N.