Description:
A stored procedure creates a (temporary, or not) table with fields which depend on the procedure's inputs. It then calls select * from this table.
The first time it's run in a session, it works fine. If called with the same arguments, it continues to work fine. If called with different arguments, the select * gets an error:
ERROR 1054 (42S22): Unknown column 'test.buggy.foo' in 'field list'
The query cache is NOT on (query_cache_size == 0).
Quitting the session, and then re-connecting to the server, clears out whatever is getting stuck. The procedure can be called with a new parameter.
How to repeat:
I tested this with a recent 5.0.17-BK build. I started the server as:
./bin/mysqld_safe --no-defaults --basedir=$PWD --datadir=$PWD/data
--tmpdir=$PWD/tmp --log-error=$PWD/data/log.err --socket=mysql.sock --port=33500 --server-id=33500 --log-bin=binlog &
delimiter //
drop procedure if exists buggy//
create procedure buggy (fname varchar(100))
begin
drop table if exists buggy;
set @query = concat("create table buggy select 1 as ", fname, " from dual");
select @query;
prepare stmt from @query;
execute stmt;
deallocate prepare stmt;
select * from buggy;
drop table buggy;
end //
-- This works
call buggy('foo')//
-- This fails with "Unknown column 'test.buggy.foo' in 'field list'"
call buggy('bar')//
-- This still works...
call buggy('foo')//
Suggested fix:
n/a
Description: A stored procedure creates a (temporary, or not) table with fields which depend on the procedure's inputs. It then calls select * from this table. The first time it's run in a session, it works fine. If called with the same arguments, it continues to work fine. If called with different arguments, the select * gets an error: ERROR 1054 (42S22): Unknown column 'test.buggy.foo' in 'field list' The query cache is NOT on (query_cache_size == 0). Quitting the session, and then re-connecting to the server, clears out whatever is getting stuck. The procedure can be called with a new parameter. How to repeat: I tested this with a recent 5.0.17-BK build. I started the server as: ./bin/mysqld_safe --no-defaults --basedir=$PWD --datadir=$PWD/data --tmpdir=$PWD/tmp --log-error=$PWD/data/log.err --socket=mysql.sock --port=33500 --server-id=33500 --log-bin=binlog & delimiter // drop procedure if exists buggy// create procedure buggy (fname varchar(100)) begin drop table if exists buggy; set @query = concat("create table buggy select 1 as ", fname, " from dual"); select @query; prepare stmt from @query; execute stmt; deallocate prepare stmt; select * from buggy; drop table buggy; end // -- This works call buggy('foo')// -- This fails with "Unknown column 'test.buggy.foo' in 'field list'" call buggy('bar')// -- This still works... call buggy('foo')// Suggested fix: n/a