stored procedures - MySQL SP - Percona 5.6 official on Centos 7 - temp table empty at different times after being populated -

given table:

create table test (id varchar(36) not null default ''); 

populated by

insert test set id = '04d513a1-738a-11e7-a698-2025640c3931'; insert test set id = '048df10c-738a-11e7-a698-2025640c3931'; insert test set id = 'ffa685c2-7389-11e7-a698-2025640c3931'; 

can explain why temp table created data null @ different times in sp:

create definer=`user`@`%` procedure `sp_fixcdrsrc`() begin  declare lcount int; declare lid varchar(36);  drop table if exists tmpcdrid;  create temporary table tmpcdrid (cdrid varchar(36)) engine=memory;  insert tmpcdrid select id test;  select count(1) tmpcdrid lcount;  while lcount > 0      select * tmpcdrid limit 1 lid;                     select 'value: ',lid;      delete tmpcdrid cdrid = lid;           set lcount = lcount - 1; end while;  drop table if exists tmpcdrid; end 

if do

select * tmpcdrid 

just above

while lcount > 0 

i 3 items inserted, , returned, temp table tmpcdrid populated - select on returns items.

however, output sp is

+---------+------+ | value:  | lid  | +---------+------+ | value:  | null | +---------+------+ 1 row in set (0.26 sec)   +---------+------+ | value:  | lid  | +---------+------+ | value:  | null | +---------+------+ 1 row in set (0.24 sec)   +---------+------+ | value:  | lid  | +---------+------+ | value:  | null | +---------+------+ 1 row in set (0.25 sec) 

why?

if in sp

select * tmpcdrid limit 1 lid;                

lid null... of time!

if add

select * tmpcdrid 

just above

drop table if exists tmpcdrid; 

all data still there in tmpcdrid table , returned correctly.

so sp literally cannot "find" data in temp table if selecting variable of same type, in loop.

outside loop can find data in temp table, if selecting console.

i changed line

select * tmpcdrid limit 1 lid;

into

select id tmpcdrid limit 1 lid;

and works 1 or 2 iterations of sp, e. g. output changes to

+---------+--------------------------------------+ | value:  | lid                                  | +---------+--------------------------------------+ | value:  | 04d513a1-738a-11e7-a698-2025640c3931 | +---------+--------------------------------------+ 1 row in set (0.25 sec) 

which correct behaviour.

but after 3 or 4 calls sp changes (with no change sp) returning null.

changing line

select id tmpcdrid limit 1 lid; 

will start working 3 or 4 iterations - lid not null, go being null.

then remains that, e. g.

select * tmpcdrid limit 1 lid; 

returns lid null, until restart mysql server instance...

then same flip-flopping - e. g. 3 or 4 times 1 "id way" of getting data temp table work in side loop, 3 or 4 times "* way" of getting data temp table work inside loop, stops working , null until server instance restarted.

anybody got idea going on here?

i same behaviour on percona 5.7 instance on different centos 7 machine, , same behaviour on windows instance of mysql 5.6.

thx!