| Bug #15217 | Using a SP cursor on a table created with PREPARE fails with weird error | ||
|---|---|---|---|
| Submitted: | 24 Nov 2005 11:43 | Modified: | 23 Jul 2006 5:58 |
| Reporter: | Anders Karlsson | ||
| Status: | Closed | ||
| Category: | Server: SP | Severity: | S3 (Non-critical) |
| Version: | 5.0.16 | OS: | Microsoft Windows (Windows XP) |
| Assigned to: | Konstantin Osipov | Target Version: | |
[24 Nov 2005 11:52]
Anders Karlsson
What I meant to say what seems to work was a CREATE TABLE ... SELECT in one go.
[24 Nov 2005 12:53]
Hartmut Holzgraefe
Verified, in my case i get ERROR 1054 (42S22): Unknown column '��.IGN_COLS�����h40.c1' in 'field list' on the last statement
[7 Apr 2006 13:13]
Konstantin Osipov
Was able to repeat without temporary tables:
drop table if exists t1;
create table t1(c1 int);
insert into t1 values(1);
drop procedure if exists p1;
delimiter //
create procedure p1(tab varchar(64))
begin
drop table if exists t2;
set @stmt= concat('create table t2 as select * from ', tab);
prepare p1 from @stmt;
execute p1;
deallocate prepare p1;
begin
declare var1 char(255);
declare cur1 cursor for select * from t2;
open cur1;
fetch cur1 into var1;
select concat('data was: /', var1, '/');
close cur1;
end;
end//
delimiter ;
call p1('t1');
call p1('t1');
[16 Jun 2006 3:58]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/7738
[22 Jun 2006 17:39]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/8086
[27 Jun 2006 16:59]
Konstantin Osipov
Pushed into 5.0.23 merged up to 5.1.12
[23 Jul 2006 5:58]
Paul DuBois
Noted in 5.0.23, 5.1.12 changelogs. Re-executing a stored procedure with a complex stored procedure cursor query could lead to a server crash.
Description: Creating a table using a PREPARE in a stored procedure, and then opening a cursor on this, fails under certain circumstances. The testcase shows a case where this does happen, a slight change to this code, for example doing a INSERT INTO ... SELECT in one PREPARE seems to work, whereas doing it in two steps, and in the reproducible case, fails. As the error message seems to indicate that this is a pointer gone astray, this seems serious enough, although in this reproducible case all you get is an error. How to repeat: Run the following from, say, MySQL Command prompt. The first call will succeed, assuming there is no "tmp1" table. The second will fail: DROP TABLE IF EXISTS t1; CREATE TABLE t1(c1 INT); INSERT INTO t1 VALUES(1); DROP PROCEDURE IF EXISTS p1; delimiter // CREATE PROCEDURE p1(tab VARCHAR(64)) BEGIN DECLARE var1 CHAR(255); DROP TEMPORARY TABLE IF EXISTS tmp1; SET @stmt = CONCAT('CREATE TEMPORARY TABLE tmp1 LIKE ', tab); PREPARE p1 FROM @stmt; EXECUTE p1; DEALLOCATE PREPARE p1; SET @stmt = CONCAT('INSERT INTO tmp1 SELECT * FROM ', tab); PREPARE p1 FROM @stmt; EXECUTE p1; DEALLOCATE PREPARE p1; BEGIN DECLARE cur1 CURSOR FOR SELECT * FROM tmp1; OPEN cur1; FETCH cur1 INTO var1; SELECT CONCAT('Data was: /', var1, '/'); CLOSE cur1; END; END // delimiter ; CALL p1('t1'); CALL p1('t1'); Result from the last two statements in my environment: +----------------------------------+ | CONCAT('Data was: /', var1, '/') | +----------------------------------+ | Data was: /1/ | +----------------------------------+ 1 row in set (0.07 sec) Query OK, 0 rows affected, 1 warning (0.07 sec) ERROR 1054 (42S22): Unknown column 'test.t1.:╢♥É:╢♥≡:╢♥i▌╗♥Pâa' in 'field list'