HDR: SPL caching on secondary - BUG
Alexey Sonkin reported an HDR bug on IDS 9.40uc5 (also 9.21): after a function is dropped and recreated on the primary and a checkpoint runs, the secondary keeps returning the old cached SPL result until restarted. He had filed a level-2 bug. rkusenet's update statistics suggestion did not help; on the secondary it gave error -9753. Unresolved.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hi, everybody,
I've just run into a very unpleasant problem obviously related to SPL
caching on HDR secondary.
Steps to reproduce: (ANSI database, IDS 9.40uc5, also reproduces
9.21):
1. Primary:
create function spl_test() returning INT; return 1; end function;
commit;
execute function spl_test();commit;
-- returns 1
2. Secondary:
execute function spl_test();commit;
-- returns 1
3. primary:
drop function spl_test;commit;
create function spl_test() returning INT; return 0; end function;
commit;
execute function spl_test();commit;
-- returns 0
-- CHECKPOINT ON PRIMARY:
onmode -c
4. secondary:
execute function spl_test();commit;
-- returns 1
The only way to get expected "0" on Secondary - restart the Secondary
---------------
I've filed a level-2 bug about it
'
------------------------------------------
Alexey Sonkin
I think I have seen this bug in version 7.30, way back in 1999.
"Alexey Sonkin" <alexey.sonkin@verizon.net> wrote in message news:906b40fc.0410251916.c658f83@posting.google.com...
> 3. primary:
>
> drop function spl_test;> commit;
>
> create function spl_test() returning INT; return 0; end function;>
> commit;
>
> execute function spl_test();> commit;
>
> -- returns 0
>
> -- CHECKPOINT ON PRIMARY:
Try this:
on the primary server, after you drop and recreate the new procedure
update statistics for procedure.
"rkusenet" <rkusenet@sympatico.ca> wrote
> > 3. primary:
> >
> > drop function spl_test;> > commit;
> >
> > create function spl_test() returning INT; return 0; end function;> >
> > commit;
> >
> > execute function spl_test();> > commit;
> >
> > -- returns 0
> >
> > -- CHECKPOINT ON PRIMARY:
>
> Try this:
>
> on the primary server, after you drop and recreate the new procedure
>
> update statistics for procedure.
Doesn't help - forgot to mention in the original post
Willem, With 9.40uc5, after " update statistics for procedure spl_test;" on HDR Secondary, I'm getting SQL Error -9753: Unable to find User Defined Routine with the given ID > -----Original Message----- > From: Willem Roos [mailto:wroos@shoprite.co.za] > > > Also seen on IDS 7.31.ud8 and earlier. As a workaround do 'update > statistics for procedure spl_test' on secondary, you'll get an error but > the sp recompiles. > > > > > Hi, everybody, > > > > I've just run into a very unpleasant problem obviously related to SPL > > caching on HDR secondary. > > > > > > The only way to get expected "0" on Secondary - restart the Secondary > >