stored procedures - Why is mysql caching the column names of a temporay table that is deleted? -

i've reduced issue down simple sp. column names getting cached in select * @ end. have no idea why or how stop it. tried adding sql_no_cache makes no difference.

drop table if exists foo; create table foo( col1 int, col2 int); insert foo values(1,2),(3,4),(5,6); drop procedure if exists mysp; delimiter ;; create definer=root@localhost procedure mysp(c int) begin drop table if exists mydata; set @mycol='col1'; if c > 0 set @mycol:='col2'; end if; set @s=concat('create temporary table mydata select ', @mycol, ' foo'); prepare stmt @s; execute stmt; deallocate prepare stmt; -- following select call fails on 2nd , subsequent executions of sp select sql_no_cache * mydata; select "please see new temp table mydata" result; end ;; delimiter ; 

version

mysql> select version(); +------------+ | version() | +------------+ | 5.5.15-log | +------------+ 1 row in set (0.00 sec) 

first run works fine expected

mysql> call mysp(0); +------+ | col1 | +------+ | 1 | | 3 | | 5 | +------+ 3 rows in set (0.17 sec) +----------------------------------+ | result | +----------------------------------+ | please see new temp table mydata | +----------------------------------+ 1 row in set (0.17 sec) query ok, 0 rows affected (0.17 sec) 

now if try , run again using other column

mysql> call mysp(1); error 1054 (42s22): unknown column 'qlgqp1.mydata.col1' in 'field list' mysql> select @mycol; +--------+ | @mycol | +--------+ | col2 | +--------+ 1 row in set (0.00 sec) 

if recreate storedprocedure again works

mysql> call mysp(1); +------+ | col2 | +------+ | 2 | | 4 | | 6 | +------+ 3 rows in set (0.18 sec) +----------------------------------+ | result | +----------------------------------+ | please see new temp table mydata | +----------------------------------+ 1 row in set (0.18 sec) query ok, 0 rows affected (0.18 sec) 

but if try switching first column - if try dropping temp table first - still doesn't work

mysql> call mysp(0); error 1054 (42s22): unknown column 'qlgqp1.mydata.col2' in 'field list' mysql> drop table mydata; query ok, 0 rows affected (0.03 sec) mysql> call mysp(0); error 1054 (42s22): unknown column 'qlgqp1.mydata.col2' in 'field list' mysql> 

*additional info asked eggyal. tried on mysql version same result. *

mysql> call mysp(1); +------+ | col2 | +------+ | 2 | | 4 | | 6 | +------+ 3 rows in set (0.20 sec) +----------------------------------+ | result | +----------------------------------+ | please see new temp table mydata | +----------------------------------+ 1 row in set (0.20 sec) query ok, 0 rows affected (0.20 sec) mysql> describe mydata; +-------+---------+------+-----+---------+-------+ | field | type | null | key | default | | +-------+---------+------+-----+---------+-------+ | col2 | int(11) | yes | | null | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.00 sec) mysql> call mysp(0); error 1054 (42s22): unknown column 'test.mydata.col2' in 'field list' mysql> describe mydata; +-------+---------+------+-----+---------+-------+ | field | type | null | key | default | | +-------+---------+------+-----+---------+-------+ | col1 | int(11) | yes | | null | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.00 sec) 

interesting development of fix - changing last few lines prepared statement works - using same query before.

-- following select call fails on 2nd , subsequent executions of sp prepare stmt 'select sql_no_cache * mydata'; execute stmt; deallocate prepare stmt; select "please see new temp table mydata" result; 

i understand relatively old (+6 months) encountered problem in prepared statements, , way got around concatenate field names , use prepared statement calls "select *" uses actual field names. i'm using prepared statements create temporary table, , standard field first one, while rest causes caching problem on "select *".

• select fields want use temp table

• iterate through table rows of field names, , on each iteration:

  set sql01 = concat(sql01,',',sfieldname,''); (just fields) and: set sql02 = concat(sql02,',',sfieldname,' varchar(50) '); (fields + field type only, create table statement)

• create output table:

  set @sql = concat('create temporary table toutput(firstfield varchar(50), ',sql02,');'); prepare stmt @sql; execute stmt; deallocate prepare stmt;

• at end:

  set @sql = concat('select firstfield,',sql01,' toutput;'); prepare stmt @sql; execute stmt; deallocate prepare stmt;