Description:
A session has no way to flush its local stored_program_cache.
How to repeat:
simple test of this is to manually update stored procs in mysql db. in the same session
insert into mysql.proc
set `db` ='test',
`name`='check_user',
`type`='PROCEDURE',
`specific_name`='check_user',
`language`='SQL',
`sql_data_access`='CONTAINS_SQL',
`is_deterministic`='NO',
`security_type`='DEFINER',
`param_list`='',
`returns`='',
`body`="select current_user(),user()",
`definer`='root@localhost',
`created`=NOW(),
`modified`=NOW(),
`sql_mode`='NO_AUTO_VALUE_ON_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION',
`comment`='',
`character_set_client`='utf8',
`collation_connection`='utf8_general_ci',
`db_collation`='utf8_general_ci',
`body_utf8`="select current_user(),user()";
call test.check_user();
show create procedure test.check_user;
update mysql.proc set definer='root@127.0.0.1' where db='test' and name='check_user' and type='PROCEDURE';
call test.check_user();
show create procedure test.check_user;
*then, as a control, in a new session*
call test.check_user();
show create procedure test.check_user;
Suggested fix:
either:
- make a session scope setup for var stored_program_cache with min value 0, i'm guessing there's a mechanism in place to purge records when the var is decreased, as it's dynamic and global scope - that mechanism could be used when var is set to 0. note: possible to do the same without session scope, but would affect other users as well. not a big deal, as it's not a common task, but session scope is the "right" way even if more difficult.
- a "flush routines" command or something similar which would explicitly purge it for a session or globally.
