From fc33b5ddd7be431d558f8251eb7ef0052a76ad68 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 2 Dec 2005 22:59:45 +0100 Subject: Bug#13012: REPAIR/BACKUP/RESTORE TABLE cause "packet out of order" in SP. Mark them properly as result-returning statements --- mysql-test/r/sp-error.result | 33 +++++++++++++++++++++++++++++++++ mysql-test/r/sp.result | 22 ++++++++++++++++++++-- mysql-test/t/sp-error.test | 39 ++++++++++++++++++++++++++++++++++++++- mysql-test/t/sp.test | 21 +++++++++++++++++++++ 4 files changed, 112 insertions(+), 3 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result index 963f14820be..f1d65ada4bf 100644 --- a/mysql-test/r/sp-error.result +++ b/mysql-test/r/sp-error.result @@ -981,6 +981,8 @@ END | drop table t1| drop function bug_13627_f| drop function if exists bug12329; +Warnings: +Note 1305 FUNCTION bug12329 does not exist create table t1 as select 1 a; create table t2 as select 1 a; create function bug12329() returns int return (select a from t1); @@ -1055,3 +1057,34 @@ Db Name Type Definer Modified Created Security_type Comment mysqltest2 p1 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER drop database mysqltest2; use test; +DROP FUNCTION IF EXISTS bug13012| +CREATE FUNCTION bug13012() RETURNS INT +BEGIN +REPAIR TABLE t1; +RETURN 1; +END| +ERROR 0A000: Not allowed to return a result set from a function +CREATE FUNCTION bug13012() RETURNS INT +BEGIN +BACKUP TABLE t1 TO '/tmp'; +RETURN 1; +END| +ERROR 0A000: Not allowed to return a result set from a function +CREATE FUNCTION bug13012() RETURNS INT +BEGIN +RESTORE TABLE t1 FROM '/tmp'; +RETURN 1; +END| +ERROR 0A000: Not allowed to return a result set from a function +create table t1 (a int)| +CREATE PROCEDURE bug13012_1() REPAIR TABLE t1| +CREATE FUNCTION bug13012_2() RETURNS INT +BEGIN +CALL bug13012_1(); +RETURN 1; +END| +SELECT bug13012_2()| +ERROR 0A000: Not allowed to return a result set from a function +drop table t1| +drop procedure bug13012_1| +drop function bug13012_2| diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 6f0a8623a4c..41279534007 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -4100,8 +4100,6 @@ x 4711 drop procedure bug14376| drop procedure if exists p1| -Warnings: -Note 1305 PROCEDURE p1 does not exist drop table if exists t1| create table t1 (a varchar(255))| insert into t1 (a) values ("a - table column")| @@ -4153,4 +4151,24 @@ A local variable in a nested compound statement takes precedence over table colu a - local variable in a nested compound statement A local variable in a nested compound statement takes precedence over table column in cursors a - local variable in a nested compound statement +drop procedure p1| +drop procedure if exists bug13012| +create procedure bug13012() +BEGIN +REPAIR TABLE t1; +BACKUP TABLE t1 to '../tmp'; +DROP TABLE t1; +RESTORE TABLE t1 FROM '../tmp'; +END| +call bug13012()| +Table Op Msg_type Msg_text +test.t1 repair status OK +Table Op Msg_type Msg_text +test.t1 backup status OK +Table Op Msg_type Msg_text +test.t1 restore status OK +drop procedure bug13012| +select * from t1| +a +a - table column drop table t1,t2; diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test index 69e5f73817b..ae13758a10e 100644 --- a/mysql-test/t/sp-error.test +++ b/mysql-test/t/sp-error.test @@ -1410,7 +1410,6 @@ delimiter ;| # BUG#12329: "Bogus error msg when executing PS with stored procedure after # SP was re-created". See also test for related bug#13399 in trigger.test ---disable_warnings drop function if exists bug12329; --enable_warnings create table t1 as select 1 a; @@ -1518,6 +1517,44 @@ show procedure status; drop database mysqltest2; use test; +# +# Bug#13012 "SP: REPAIR/BACKUP/RESTORE TABLE crashes the server" +# +delimiter |; +--disable_warnings +DROP FUNCTION IF EXISTS bug13012| +--enable_warnings +--error ER_SP_NO_RETSET +CREATE FUNCTION bug13012() RETURNS INT +BEGIN + REPAIR TABLE t1; + RETURN 1; +END| +--error ER_SP_NO_RETSET +CREATE FUNCTION bug13012() RETURNS INT +BEGIN + BACKUP TABLE t1 TO '/tmp'; + RETURN 1; +END| +--error ER_SP_NO_RETSET +CREATE FUNCTION bug13012() RETURNS INT +BEGIN + RESTORE TABLE t1 FROM '/tmp'; + RETURN 1; +END| +create table t1 (a int)| +CREATE PROCEDURE bug13012_1() REPAIR TABLE t1| +CREATE FUNCTION bug13012_2() RETURNS INT +BEGIN + CALL bug13012_1(); + RETURN 1; +END| +--error ER_SP_NO_RETSET +SELECT bug13012_2()| +drop table t1| +drop procedure bug13012_1| +drop function bug13012_2| +delimiter ;| # BUG#NNNN: New bug synopsis # diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index 760110b0a64..fcd0fddb147 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -4908,8 +4908,10 @@ drop procedure bug14376| # variable declarations. In MySQL 5.0 it's vice versa. # +--disable_warnings drop procedure if exists p1| drop table if exists t1| +--enable_warnings create table t1 (a varchar(255))| insert into t1 (a) values ("a - table column")| create procedure p1(a varchar(255)) @@ -4944,6 +4946,25 @@ begin end; end| call p1("a - stored procedure parameter")| +drop procedure p1| + +# +# Bug#13012 "SP: REPAIR/BACKUP/RESTORE TABLE crashes the server" +# +--disable_warnings +drop procedure if exists bug13012| +--enable_warnings +create procedure bug13012() +BEGIN + REPAIR TABLE t1; + BACKUP TABLE t1 to '../tmp'; + DROP TABLE t1; + RESTORE TABLE t1 FROM '../tmp'; +END| +--replace_result ": 7" ": X" ": 17" ": X" $MYSQL_TEST_DIR MYSQL_TEST_DIR +call bug13012()| +drop procedure bug13012| +select * from t1| # # BUG#NNNN: New bug synopsis -- cgit v1.2.1 From 6506ce3bcba9a6c53a760e380b2f544738fc0d0d Mon Sep 17 00:00:00 2001 From: unknown Date: Sat, 3 Dec 2005 15:02:09 +0100 Subject: this has nothing to do with the bug#13012. it's about mysql_admin_commands not being reexecution-safe (and CHECK still isn't) mysql-test/r/sp-error.result: optimize is now allowed in SP mysql-test/r/sp.result: test repair/optimize/analyze in SP mysql-test/t/backup.test: clean up after itself mysql-test/t/sp-error.test: optimize is now allowed in SP mysql-test/t/sp.test: test repair/optimize/analyze in SP sql/sp_head.cc: all mysql_admin commands return result set sql/sql_parse.cc: all mysql_admin commands modify table list and we should restore it for SP sql/sql_table.cc: optimization - don't execute views when no view is expected/allowed sql/sql_yacc.yy: optimize is now allowed in SP --- mysql-test/r/sp-error.result | 2 +- mysql-test/r/sp.result | 63 ++++++++++++++++++++++++++++++++++++++++++++ mysql-test/t/backup.test | 1 + mysql-test/t/sp-error.test | 2 +- mysql-test/t/sp.test | 13 ++++++++- 5 files changed, 78 insertions(+), 3 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result index f1d65ada4bf..4ba097e3d9f 100644 --- a/mysql-test/r/sp-error.result +++ b/mysql-test/r/sp-error.result @@ -768,7 +768,7 @@ BEGIN OPTIMIZE TABLE t1; RETURN 1; END| -ERROR 0A000: OPTIMIZE TABLE is not allowed in stored procedures +ERROR 0A000: Not allowed to return a result set from a function DROP FUNCTION IF EXISTS bug12995| CREATE FUNCTION bug12995() RETURNS INT BEGIN diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 41279534007..bfc16c826a5 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -4168,6 +4168,69 @@ test.t1 backup status OK Table Op Msg_type Msg_text test.t1 restore status OK drop procedure bug13012| +create view v1 as select * from t1| +create procedure bug13012() +BEGIN +REPAIR TABLE t1,t2,t3,v1; +OPTIMIZE TABLE t1,t2,t3,v1; +ANALYZE TABLE t1,t2,t3,v1; +END| +call bug13012()| +Table Op Msg_type Msg_text +test.t1 repair status OK +test.t2 repair status OK +test.t3 repair error Table 'test.t3' doesn't exist +test.v1 repair note Unknown table 'test.v1' +Table Op Msg_type Msg_text +test.t1 optimize status OK +test.t2 optimize status OK +test.t3 optimize error Table 'test.t3' doesn't exist +test.v1 optimize note Unknown table 'test.v1' +Table Op Msg_type Msg_text +test.t1 analyze status Table is already up to date +test.t2 analyze status Table is already up to date +test.t3 analyze error Table 'test.t3' doesn't exist +test.v1 analyze note Unknown table 'test.v1' +Warnings: +Error 1146 Table 'test.t3' doesn't exist +call bug13012()| +Table Op Msg_type Msg_text +test.t1 repair status OK +test.t2 repair status OK +test.t3 repair error Table 'test.t3' doesn't exist +test.v1 repair note Unknown table 'test.v1' +Table Op Msg_type Msg_text +test.t1 optimize status OK +test.t2 optimize status OK +test.t3 optimize error Table 'test.t3' doesn't exist +test.v1 optimize note Unknown table 'test.v1' +Table Op Msg_type Msg_text +test.t1 analyze status Table is already up to date +test.t2 analyze status Table is already up to date +test.t3 analyze error Table 'test.t3' doesn't exist +test.v1 analyze note Unknown table 'test.v1' +Warnings: +Error 1146 Table 'test.t3' doesn't exist +call bug13012()| +Table Op Msg_type Msg_text +test.t1 repair status OK +test.t2 repair status OK +test.t3 repair error Table 'test.t3' doesn't exist +test.v1 repair note Unknown table 'test.v1' +Table Op Msg_type Msg_text +test.t1 optimize status OK +test.t2 optimize status OK +test.t3 optimize error Table 'test.t3' doesn't exist +test.v1 optimize note Unknown table 'test.v1' +Table Op Msg_type Msg_text +test.t1 analyze status Table is already up to date +test.t2 analyze status Table is already up to date +test.t3 analyze error Table 'test.t3' doesn't exist +test.v1 analyze note Unknown table 'test.v1' +Warnings: +Error 1146 Table 'test.t3' doesn't exist +drop procedure bug13012| +drop view v1; select * from t1| a a - table column diff --git a/mysql-test/t/backup.test b/mysql-test/t/backup.test index 3034129ad4b..40a9fa73b60 100644 --- a/mysql-test/t/backup.test +++ b/mysql-test/t/backup.test @@ -52,5 +52,6 @@ unlock tables; connection con1; reap; drop table t5; +--system rm $MYSQL_TEST_DIR/var/tmp/t?.* # End of 4.1 tests diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test index ae13758a10e..b6c7d5476e7 100644 --- a/mysql-test/t/sp-error.test +++ b/mysql-test/t/sp-error.test @@ -1095,7 +1095,7 @@ delimiter |; --disable_warnings DROP FUNCTION IF EXISTS bug12953| --enable_warnings ---error ER_SP_BADSTATEMENT +--error ER_SP_NO_RETSET CREATE FUNCTION bug12953() RETURNS INT BEGIN OPTIMIZE TABLE t1; diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index fcd0fddb147..22dd1285b75 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -4961,9 +4961,20 @@ BEGIN DROP TABLE t1; RESTORE TABLE t1 FROM '../tmp'; END| ---replace_result ": 7" ": X" ": 17" ": X" $MYSQL_TEST_DIR MYSQL_TEST_DIR call bug13012()| drop procedure bug13012| +create view v1 as select * from t1| +create procedure bug13012() +BEGIN + REPAIR TABLE t1,t2,t3,v1; + OPTIMIZE TABLE t1,t2,t3,v1; + ANALYZE TABLE t1,t2,t3,v1; +END| +call bug13012()| +call bug13012()| +call bug13012()| +drop procedure bug13012| +drop view v1; select * from t1| # -- cgit v1.2.1 From 8cf0316339b908bfb1ce37a57f3bbb8b4c84cdc9 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 5 Dec 2005 12:08:30 +0100 Subject: better error for optimize/repair/etc a view --- mysql-test/r/sp.result | 24 ++++++++++++------------ mysql-test/r/view.result | 17 +++++++++++------ 2 files changed, 23 insertions(+), 18 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index bfc16c826a5..e8f8439927e 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -4180,55 +4180,55 @@ Table Op Msg_type Msg_text test.t1 repair status OK test.t2 repair status OK test.t3 repair error Table 'test.t3' doesn't exist -test.v1 repair note Unknown table 'test.v1' +test.v1 repair error 'test.v1' is not BASE TABLE Table Op Msg_type Msg_text test.t1 optimize status OK test.t2 optimize status OK test.t3 optimize error Table 'test.t3' doesn't exist -test.v1 optimize note Unknown table 'test.v1' +test.v1 optimize error 'test.v1' is not BASE TABLE Table Op Msg_type Msg_text test.t1 analyze status Table is already up to date test.t2 analyze status Table is already up to date test.t3 analyze error Table 'test.t3' doesn't exist -test.v1 analyze note Unknown table 'test.v1' +test.v1 analyze error 'test.v1' is not BASE TABLE Warnings: -Error 1146 Table 'test.t3' doesn't exist +Error 1347 'test.v1' is not BASE TABLE call bug13012()| Table Op Msg_type Msg_text test.t1 repair status OK test.t2 repair status OK test.t3 repair error Table 'test.t3' doesn't exist -test.v1 repair note Unknown table 'test.v1' +test.v1 repair error 'test.v1' is not BASE TABLE Table Op Msg_type Msg_text test.t1 optimize status OK test.t2 optimize status OK test.t3 optimize error Table 'test.t3' doesn't exist -test.v1 optimize note Unknown table 'test.v1' +test.v1 optimize error 'test.v1' is not BASE TABLE Table Op Msg_type Msg_text test.t1 analyze status Table is already up to date test.t2 analyze status Table is already up to date test.t3 analyze error Table 'test.t3' doesn't exist -test.v1 analyze note Unknown table 'test.v1' +test.v1 analyze error 'test.v1' is not BASE TABLE Warnings: -Error 1146 Table 'test.t3' doesn't exist +Error 1347 'test.v1' is not BASE TABLE call bug13012()| Table Op Msg_type Msg_text test.t1 repair status OK test.t2 repair status OK test.t3 repair error Table 'test.t3' doesn't exist -test.v1 repair note Unknown table 'test.v1' +test.v1 repair error 'test.v1' is not BASE TABLE Table Op Msg_type Msg_text test.t1 optimize status OK test.t2 optimize status OK test.t3 optimize error Table 'test.t3' doesn't exist -test.v1 optimize note Unknown table 'test.v1' +test.v1 optimize error 'test.v1' is not BASE TABLE Table Op Msg_type Msg_text test.t1 analyze status Table is already up to date test.t2 analyze status Table is already up to date test.t3 analyze error Table 'test.t3' doesn't exist -test.v1 analyze note Unknown table 'test.v1' +test.v1 analyze error 'test.v1' is not BASE TABLE Warnings: -Error 1146 Table 'test.t3' doesn't exist +Error 1347 'test.v1' is not BASE TABLE drop procedure bug13012| drop view v1; select * from t1| diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index ebb2c190eb1..315744c6878 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -2383,20 +2383,25 @@ CREATE TABLE t1(id INT); CREATE VIEW v1 AS SELECT id FROM t1; OPTIMIZE TABLE v1; Table Op Msg_type Msg_text -test.v1 optimize note Unknown table 'test.v1' +test.v1 optimize error 'test.v1' is not BASE TABLE +Warnings: +Error 1347 'test.v1' is not BASE TABLE ANALYZE TABLE v1; Table Op Msg_type Msg_text -test.v1 analyze note Unknown table 'test.v1' +test.v1 analyze error 'test.v1' is not BASE TABLE +Warnings: +Error 1347 'test.v1' is not BASE TABLE REPAIR TABLE v1; Table Op Msg_type Msg_text -test.v1 repair note Unknown table 'test.v1' +test.v1 repair error 'test.v1' is not BASE TABLE +Warnings: +Error 1347 'test.v1' is not BASE TABLE DROP TABLE t1; OPTIMIZE TABLE v1; Table Op Msg_type Msg_text -test.v1 optimize note Unknown table 'test.v1' +test.v1 optimize error 'test.v1' is not BASE TABLE Warnings: -Error 1146 Table 'test.t1' doesn't exist -Error 1356 View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +Error 1347 'test.v1' is not BASE TABLE DROP VIEW v1; create definer = current_user() sql security invoker view v1 as select 1; show create view v1; -- cgit v1.2.1