| Bug #33082 | Stored Procedure: crash if table replaced with a view in a loop | ||
|---|---|---|---|
| Submitted: | 8 Dec 2007 2:13 | Modified: | 5 Aug 2008 15:59 |
| Reporter: | Konstantin Osipov | ||
| Status: | Verified | ||
| Category: | Server: SP | Severity: | S1 (Critical) |
| Version: | 5.1, 5.0 | OS: | Any |
| Assigned to: | Konstantin Osipov | Target Version: | |
| Triage: | Triaged: D1 (Critical) / R4 (High) / E5 (Major) | ||
[8 Dec 2007 2:15]
Konstantin Osipov
See also Bug#27690, which inspired this bug. Note: this bug does not use prepared statements.
[8 Dec 2007 20:06]
Sveta Smirnova
Thank you for the report.
Verified as described with modified test case:
delimiter |;
create procedure crash()
begin
declare iteration integer default 0;
while iteration < 10 do
if iteration % 2 then
drop table v1;
create view v1 as select A.a, A.b from t1 A, t1 B;
else
drop table if exists t1, v1, v2;
create table t1 (a int, b int);
insert into t1 values (1,1),(2,2);
create table v1 as select * from t1;
create table v2 as select * from t1;
end if;
select * from v1, v2;
set iteration= iteration+1;
end while;
end|
call crash()|
[5 Aug 2008 15:43]
Susanne Ebrecht
Can't repeat the crash with actual MySQL bzr tree for 5.0, 5.1 and 6.0.
[5 Aug 2008 15:59]
Susanne Ebrecht
By using test from Konstantin all will work fine but by using test case from Sveta this is still crashing 5.1 and 5.0 bzr tree. It won't crash 6.0 bzr tree.
[24 Feb 9:12]
Konstantin Osipov
Having set the risk and effort for a complete fix of the bug, there still could be possible to fix the crash (but not the wrong result). The assignee should investigate the cause of the crash.
Description: A stored procedure that mixes DDL and DML in a loop leads to a crash, when a table is replaced with a view: mysql> create procedure crash() -> begin -> declare iteration integer default 0; -> while iteration < 10 do -> if iteration % 1 then -> drop table v1; -> create view v1 as select A.a, A.b from t1 A, t1 B; -> else -> drop table if exists t1, v1, v2; -> create table t1 (a int, b int); -> insert into t1 values (1,1),(2,2); -> -> create table v1 as select * from t1; -> create table v2 as select * from t1; -> end if; -> select * from v1, v2; -> set iteration= iteration+1; -> end while; -> end| Query OK, 0 rows affected (0.00 sec) mysql> mysql> call crash()| ERROR 2013 (HY000): Lost connection to MySQL server during query How to repeat: mysql> create procedure crash() -> begin -> declare iteration integer default 0; -> while iteration < 10 do -> if iteration % 1 then -> drop table v1; -> create view v1 as select A.a, A.b from t1 A, t1 B; -> else -> drop table if exists t1, v1, v2; -> create table t1 (a int, b int); -> insert into t1 values (1,1),(2,2); -> -> create table v1 as select * from t1; -> create table v2 as select * from t1; -> end if; -> select * from v1, v2; -> set iteration= iteration+1; -> end while; -> end| Query OK, 0 rows affected (0.00 sec) mysql> mysql> call crash()| ERROR 2013 (HY000): Lost connection to MySQL server during query Suggested fix: See WL#4179