diff options
| -rw-r--r-- | mysql-test/r/sp-error.result | 39 | ||||
| -rw-r--r-- | mysql-test/r/trigger.result | 73 | ||||
| -rw-r--r-- | mysql-test/t/sp-error.test | 39 | ||||
| -rw-r--r-- | mysql-test/t/trigger.test | 90 | ||||
| -rw-r--r-- | sql/sp_head.cc | 6 | ||||
| -rw-r--r-- | sql/sp_head.h | 18 | ||||
| -rw-r--r-- | sql/sql_parse.cc | 6 | ||||
| -rw-r--r-- | sql/sql_yacc.yy | 13 |
8 files changed, 259 insertions, 25 deletions
diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result index da1fc58db57..85ea624ce2f 100644 --- a/mysql-test/r/sp-error.result +++ b/mysql-test/r/sp-error.result @@ -634,6 +634,45 @@ flush tables; return 5; end| ERROR 0A000: FLUSH is not allowed in stored function or trigger +create function bug8409() returns int begin reset query cache; +return 1; end| +ERROR 0A000: RESET is not allowed in stored function or trigger +create function bug8409() returns int begin reset master; +return 1; end| +ERROR 0A000: RESET is not allowed in stored function or trigger +create function bug8409() returns int begin reset slave; +return 1; end| +ERROR 0A000: RESET is not allowed in stored function or trigger +create function bug8409() returns int begin flush hosts; +return 1; end| +ERROR 0A000: FLUSH is not allowed in stored function or trigger +create function bug8409() returns int begin flush privileges; +return 1; end| +ERROR 0A000: FLUSH is not allowed in stored function or trigger +create function bug8409() returns int begin flush tables with read lock; +return 1; end| +ERROR 0A000: FLUSH is not allowed in stored function or trigger +create function bug8409() returns int begin flush tables; +return 1; end| +ERROR 0A000: FLUSH is not allowed in stored function or trigger +create function bug8409() returns int begin flush logs; +return 1; end| +ERROR 0A000: FLUSH is not allowed in stored function or trigger +create function bug8409() returns int begin flush status; +return 1; end| +ERROR 0A000: FLUSH is not allowed in stored function or trigger +create function bug8409() returns int begin flush slave; +return 1; end| +ERROR 0A000: FLUSH is not allowed in stored function or trigger +create function bug8409() returns int begin flush master; +return 1; end| +ERROR 0A000: FLUSH is not allowed in stored function or trigger +create function bug8409() returns int begin flush des_key_file; +return 1; end| +ERROR 0A000: FLUSH is not allowed in stored function or trigger +create function bug8409() returns int begin flush user_resources; +return 1; end| +ERROR 0A000: FLUSH is not allowed in stored function or trigger create procedure bug9529_901234567890123456789012345678901234567890123456789012345() begin end| diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result index b41dd66c390..c687d4c49c8 100644 --- a/mysql-test/r/trigger.result +++ b/mysql-test/r/trigger.result @@ -626,12 +626,51 @@ Trigger Event Table Statement Timing Created sql_mode Definer t1_bi INSERT t1 set new.a = '2004-01-00' BEFORE # root@localhost drop table t1; create table t1 (id int); +create trigger t1_ai after insert on t1 for each row reset query cache; +ERROR 0A000: RESET is not allowed in stored function or trigger +create trigger t1_ai after insert on t1 for each row reset master; +ERROR 0A000: RESET is not allowed in stored function or trigger +create trigger t1_ai after insert on t1 for each row reset slave; +ERROR 0A000: RESET is not allowed in stored function or trigger +create trigger t1_ai after insert on t1 for each row flush hosts; +ERROR 0A000: FLUSH is not allowed in stored function or trigger +create trigger t1_ai after insert on t1 for each row flush tables with read lock; +ERROR 0A000: FLUSH is not allowed in stored function or trigger +create trigger t1_ai after insert on t1 for each row flush logs; +ERROR 0A000: FLUSH is not allowed in stored function or trigger +create trigger t1_ai after insert on t1 for each row flush status; +ERROR 0A000: FLUSH is not allowed in stored function or trigger +create trigger t1_ai after insert on t1 for each row flush slave; +ERROR 0A000: FLUSH is not allowed in stored function or trigger +create trigger t1_ai after insert on t1 for each row flush master; +ERROR 0A000: FLUSH is not allowed in stored function or trigger +create trigger t1_ai after insert on t1 for each row flush des_key_file; +ERROR 0A000: FLUSH is not allowed in stored function or trigger +create trigger t1_ai after insert on t1 for each row flush user_resources; +ERROR 0A000: FLUSH is not allowed in stored function or trigger create trigger t1_ai after insert on t1 for each row flush tables; ERROR 0A000: FLUSH is not allowed in stored function or trigger create trigger t1_ai after insert on t1 for each row flush privileges; ERROR 0A000: FLUSH is not allowed in stored function or trigger -create procedure p1() flush tables; +drop procedure if exists p1; create trigger t1_ai after insert on t1 for each row call p1(); +create procedure p1() flush tables; +insert into t1 values (0); +ERROR 0A000: FLUSH is not allowed in stored function or trigger +drop procedure p1; +create procedure p1() reset query cache; +insert into t1 values (0); +ERROR 0A000: RESET is not allowed in stored function or trigger +drop procedure p1; +create procedure p1() reset master; +insert into t1 values (0); +ERROR 0A000: RESET is not allowed in stored function or trigger +drop procedure p1; +create procedure p1() reset slave; +insert into t1 values (0); +ERROR 0A000: RESET is not allowed in stored function or trigger +drop procedure p1; +create procedure p1() flush hosts; insert into t1 values (0); ERROR 0A000: FLUSH is not allowed in stored function or trigger drop procedure p1; @@ -639,6 +678,38 @@ create procedure p1() flush privileges; insert into t1 values (0); ERROR 0A000: FLUSH is not allowed in stored function or trigger drop procedure p1; +create procedure p1() flush tables with read lock; +insert into t1 values (0); +ERROR 0A000: FLUSH is not allowed in stored function or trigger +drop procedure p1; +create procedure p1() flush tables; +insert into t1 values (0); +ERROR 0A000: FLUSH is not allowed in stored function or trigger +drop procedure p1; +create procedure p1() flush logs; +insert into t1 values (0); +ERROR 0A000: FLUSH is not allowed in stored function or trigger +drop procedure p1; +create procedure p1() flush status; +insert into t1 values (0); +ERROR 0A000: FLUSH is not allowed in stored function or trigger +drop procedure p1; +create procedure p1() flush slave; +insert into t1 values (0); +ERROR 0A000: FLUSH is not allowed in stored function or trigger +drop procedure p1; +create procedure p1() flush master; +insert into t1 values (0); +ERROR 0A000: FLUSH is not allowed in stored function or trigger +drop procedure p1; +create procedure p1() flush des_key_file; +insert into t1 values (0); +ERROR 0A000: FLUSH is not allowed in stored function or trigger +drop procedure p1; +create procedure p1() flush user_resources; +insert into t1 values (0); +ERROR 0A000: FLUSH is not allowed in stored function or trigger +drop procedure p1; drop table t1; create table t1 (id int, data int, username varchar(16)); insert into t1 (id, data) values (1, 0); diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test index 2abb923efbb..abb36f040d2 100644 --- a/mysql-test/t/sp-error.test +++ b/mysql-test/t/sp-error.test @@ -899,6 +899,45 @@ begin flush tables; return 5; end| +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create function bug8409() returns int begin reset query cache; +return 1; end| +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create function bug8409() returns int begin reset master; +return 1; end| +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create function bug8409() returns int begin reset slave; +return 1; end| +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create function bug8409() returns int begin flush hosts; +return 1; end| +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create function bug8409() returns int begin flush privileges; +return 1; end| +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create function bug8409() returns int begin flush tables with read lock; +return 1; end| +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create function bug8409() returns int begin flush tables; +return 1; end| +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create function bug8409() returns int begin flush logs; +return 1; end| +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create function bug8409() returns int begin flush status; +return 1; end| +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create function bug8409() returns int begin flush slave; +return 1; end| +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create function bug8409() returns int begin flush master; +return 1; end| +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create function bug8409() returns int begin flush des_key_file; +return 1; end| +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create function bug8409() returns int begin flush user_resources; +return 1; end| # diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test index 7e20b61c1e4..2a145e1eeaa 100644 --- a/mysql-test/t/trigger.test +++ b/mysql-test/t/trigger.test @@ -651,17 +651,105 @@ drop table t1; # of functions and triggers. create table t1 (id int); --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create trigger t1_ai after insert on t1 for each row reset query cache; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create trigger t1_ai after insert on t1 for each row reset master; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create trigger t1_ai after insert on t1 for each row reset slave; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create trigger t1_ai after insert on t1 for each row flush hosts; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create trigger t1_ai after insert on t1 for each row flush tables with read lock; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create trigger t1_ai after insert on t1 for each row flush logs; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create trigger t1_ai after insert on t1 for each row flush status; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create trigger t1_ai after insert on t1 for each row flush slave; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create trigger t1_ai after insert on t1 for each row flush master; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create trigger t1_ai after insert on t1 for each row flush des_key_file; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create trigger t1_ai after insert on t1 for each row flush user_resources; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG create trigger t1_ai after insert on t1 for each row flush tables; --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG create trigger t1_ai after insert on t1 for each row flush privileges; -create procedure p1() flush tables; +--disable_warnings +drop procedure if exists p1; +--enable_warnings + create trigger t1_ai after insert on t1 for each row call p1(); +create procedure p1() flush tables; --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG insert into t1 values (0); + +drop procedure p1; +create procedure p1() reset query cache; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +insert into t1 values (0); + +drop procedure p1; +create procedure p1() reset master; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +insert into t1 values (0); + +drop procedure p1; +create procedure p1() reset slave; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +insert into t1 values (0); + +drop procedure p1; +create procedure p1() flush hosts; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +insert into t1 values (0); + drop procedure p1; create procedure p1() flush privileges; --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG insert into t1 values (0); + +drop procedure p1; +create procedure p1() flush tables with read lock; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +insert into t1 values (0); + +drop procedure p1; +create procedure p1() flush tables; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +insert into t1 values (0); + +drop procedure p1; +create procedure p1() flush logs; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +insert into t1 values (0); + +drop procedure p1; +create procedure p1() flush status; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +insert into t1 values (0); + +drop procedure p1; +create procedure p1() flush slave; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +insert into t1 values (0); + +drop procedure p1; +create procedure p1() flush master; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +insert into t1 values (0); + +drop procedure p1; +create procedure p1() flush des_key_file; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +insert into t1 values (0); + +drop procedure p1; +create procedure p1() flush user_resources; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +insert into t1 values (0); + drop procedure p1; drop table t1; diff --git a/sql/sp_head.cc b/sql/sp_head.cc index eec6e0fc3cd..9761de625be 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -230,6 +230,12 @@ sp_get_flags_for_command(LEX *lex) else flags= sp_head::HAS_COMMIT_OR_ROLLBACK; break; + case SQLCOM_FLUSH: + flags= sp_head::HAS_SQLCOM_FLUSH; + break; + case SQLCOM_RESET: + flags= sp_head::HAS_SQLCOM_RESET; + break; case SQLCOM_CREATE_INDEX: case SQLCOM_CREATE_DB: case SQLCOM_CREATE_VIEW: diff --git a/sql/sp_head.h b/sql/sp_head.h index 4cd34bc9e20..7f2da69aa0c 100644 --- a/sql/sp_head.h +++ b/sql/sp_head.h @@ -115,7 +115,9 @@ public: IS_INVOKED= 32, // Is set if this sp_head is being used HAS_SET_AUTOCOMMIT_STMT= 64,// Is set if a procedure with 'set autocommit' /* Is set if a procedure with COMMIT (implicit or explicit) | ROLLBACK */ - HAS_COMMIT_OR_ROLLBACK= 128 + HAS_COMMIT_OR_ROLLBACK= 128, + HAS_SQLCOM_RESET= 2048, + HAS_SQLCOM_FLUSH= 4096 }; /* TYPE_ENUM_FUNCTION, TYPE_ENUM_PROCEDURE or TYPE_ENUM_TRIGGER */ @@ -335,14 +337,16 @@ public: my_error(ER_SP_NO_RETSET, MYF(0), where); else if (m_flags & HAS_SET_AUTOCOMMIT_STMT) my_error(ER_SP_CANT_SET_AUTOCOMMIT, MYF(0)); - else if (m_type != TYPE_ENUM_PROCEDURE && - (m_flags & sp_head::HAS_COMMIT_OR_ROLLBACK)) - { + else if (m_flags & HAS_COMMIT_OR_ROLLBACK) my_error(ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG, MYF(0)); - return TRUE; - } + else if (m_flags & HAS_SQLCOM_RESET) + my_error(ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG, MYF(0), "RESET"); + else if (m_flags & HAS_SQLCOM_FLUSH) + my_error(ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG, MYF(0), "FLUSH"); + return test(m_flags & - (CONTAINS_DYNAMIC_SQL|MULTI_RESULTS|HAS_SET_AUTOCOMMIT_STMT)); + (CONTAINS_DYNAMIC_SQL|MULTI_RESULTS|HAS_SET_AUTOCOMMIT_STMT| + HAS_COMMIT_OR_ROLLBACK|HAS_SQLCOM_RESET|HAS_SQLCOM_FLUSH)); } #ifndef DBUG_OFF diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index f3ac1280983..3d3e98a08cd 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -6681,11 +6681,7 @@ bool reload_acl_and_cache(THD *thd, ulong options, TABLE_LIST *tables, select_errors=0; /* Write if more errors */ bool tmp_write_to_binlog= 1; - if (thd && thd->in_sub_stmt) - { - my_error(ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG, MYF(0), "FLUSH"); - return 1; - } + DBUG_ASSERT(!thd || !thd->in_sub_stmt); #ifndef NO_EMBEDDED_ACCESS_CHECKS if (options & REFRESH_GRANT) diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 133b6e18fee..6bf76f7e503 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -6783,17 +6783,8 @@ flush: FLUSH_SYM opt_no_write_to_binlog { LEX *lex=Lex; - if (lex->sphead && lex->sphead->m_type != TYPE_ENUM_PROCEDURE) - { - /* - Note that both FLUSH TABLES and FLUSH PRIVILEGES will break - execution in prelocked mode. So it is better to disable - FLUSH in stored functions and triggers completely. - */ - my_error(ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG, MYF(0), "FLUSH"); - YYABORT; - } - lex->sql_command= SQLCOM_FLUSH; lex->type=0; + lex->sql_command= SQLCOM_FLUSH; + lex->type= 0; lex->no_write_to_binlog= $2; } flush_options |