| List: | Commits | « Previous MessageNext Message » | |
| From: | ahristov | Date: | March 7 2006 12:03pm |
| Subject: | bk commit into 5.1 tree (andrey:1.2214) BUG#16394 | ||
| View as plain text | |||
Below is the list of changes that have just been committed into a local 5.1 repository of andrey. When andrey does a push these changes will be propagated to the main repository and, within 24 hours after the push, to the public repository. For information on how to access the public repository see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html ChangeSet 1.2214 06/03/07 12:02:43 andrey@lmy004. +19 -0 fix for bug #16394: Events: Crash if schedule contains SELECT (SUBSelects are forbidden, though the infrastructure is there, by allowing subselects with lex->command_forbid_subselect=false one can experiment with subqueries. For now disabled, can be enabled in the future when we have less bugs on the plate) mysql-test/t/events_table_intact_check.test 1.1 06/03/07 12:02:25 andrey@lmy004. +72 -0 mysql-test/t/events_table_intact_check.test 1.0 06/03/07 12:02:25 andrey@lmy004. +0 -0 BitKeeper file /work/mysql-5.1-bug17494/mysql-test/t/events_table_intact_check.test mysql-test/t/events_static_tests.test 1.1 06/03/07 12:02:24 andrey@lmy004. +261 -0 mysql-test/t/events_static_tests.test 1.0 06/03/07 12:02:24 andrey@lmy004. +0 -0 BitKeeper file /work/mysql-5.1-bug17494/mysql-test/t/events_static_tests.test mysql-test/r/events_table_intact_check.result 1.1 06/03/07 12:02:11 andrey@lmy004. +57 -0 mysql-test/r/events_table_intact_check.result 1.0 06/03/07 12:02:11 andrey@lmy004. +0 -0 BitKeeper file /work/mysql-5.1-bug17494/mysql-test/r/events_table_intact_check.result mysql-test/r/events_static_tests.result 1.1 06/03/07 12:02:10 andrey@lmy004. +298 -0 sql/sql_yacc.yy 1.472 06/03/07 12:02:10 andrey@lmy004. +43 -89 - use late fix_fields of AT/EVERY/STARTS/ENDS - some commands cannot handle, or don't want to handle SUBqueries, so give them a way to express that -> lex->command_forbid_subselect mysql-test/r/events_static_tests.result 1.0 06/03/07 12:02:10 andrey@lmy004. +0 -0 BitKeeper file /work/mysql-5.1-bug17494/mysql-test/r/events_static_tests.result sql/sql_show.cc 1.316 06/03/07 12:02:09 andrey@lmy004. +15 -1 - trace info to SELECT FROM I_S.PROCESSLIST, in the trace log we didn't see what goes to the user. sql/sql_prepare.cc 1.165 06/03/07 12:02:09 andrey@lmy004. +26 -9 - more data to trace log. It's always nice for tracking to know the number of the PS (stmt_id). sql/sql_parse.cc 1.527 06/03/07 12:02:08 andrey@lmy004. +1 -0 - print time (with microseconds) in the trace log. sql/sql_lex.h 1.218 06/03/07 12:02:08 andrey@lmy004. +8 -0 - new variable to st_lex sql/sql_lex.cc 1.172 06/03/07 12:02:08 andrey@lmy004. +1 -0 - add a member variable to help in cases where SUBSELECT is inappropriate sql/sql_base.cc 1.310 06/03/07 12:02:08 andrey@lmy004. +1 -0 - trace log message sql/sp_head.cc 1.211 06/03/07 12:02:07 andrey@lmy004. +3 -2 - move the code before the comment, where it's more appropriate sql/event_timed.cc 1.46 06/03/07 12:02:07 andrey@lmy004. +111 -29 - late parsing of the AT/EVERY/STARTS/ENDS to support SUBqueries sql/event_executor.cc 1.42 06/03/07 12:02:07 andrey@lmy004. +36 -8 - 1s is too much time for sleeping, kick in. - add timing to mysqld.trace, much easier to track timing problems with it. (threads switching) sql/event.h 1.27 06/03/07 12:02:07 andrey@lmy004. +24 -1 - add member variables to class Event_timed which are used for the late checking of Item* (not in the parser). sql/event.cc 1.38 06/03/07 12:02:07 andrey@lmy004. +152 -46 - initialization of AT, EVERY, STARTS and ENDS moved from the parser to after parsing - tables are opened in one shot, a subquery may open mysql.event again but for read ( SELECT * FROM information_schema.events) for instance but that's not a problem and is tested. - check the rights with SELECT before attempting open because if we add mysql.event before the check the user has to have always SELECT on mysql.event to be able to use CREATE EVENT mysql-test/t/events_bugs.test 1.2 06/03/07 12:02:07 andrey@lmy004. +91 -43 add tests mysql-test/t/events.test 1.23 06/03/07 12:02:06 andrey@lmy004. +19 -324 add more tests mysql-test/r/events_bugs.result 1.3 06/03/07 12:02:06 andrey@lmy004. +97 -57 update results # This is a BitKeeper patch. What follows are the unified diffs for the # set of deltas contained in the patch. The rest of the patch, the part # that BitKeeper cares about, is below these diffs. # User: andrey # Host: lmy004. # Root: /work/mysql-5.1-bug17494 --- 1.309/sql/sql_base.cc 2006-02-27 18:11:46 +01:00 +++ 1.310/sql/sql_base.cc 2006-03-07 12:02:08 +01:00 @@ -2917,6 +2917,7 @@ static bool check_lock_and_start_stmt(TH { int error; DBUG_ENTER("check_lock_and_start_stmt"); + DBUG_PRINT("enter", ("table=0xlx LOCK=%d", table, lock_type)); if ((int) lock_type >= (int) TL_WRITE_ALLOW_READ && (int) table->reginfo.lock_type < (int) TL_WRITE_ALLOW_READ) --- 1.171/sql/sql_lex.cc 2006-02-09 11:34:36 +01:00 +++ 1.172/sql/sql_lex.cc 2006-03-07 12:02:08 +01:00 @@ -178,6 +178,7 @@ void lex_start(THD *thd, const uchar *bu lex->proc_list.first= 0; lex->query_tables_own_last= 0; lex->escape_used= lex->et_compile_phase= FALSE; + lex->command_forbid_subselect= false; lex->name= 0; lex->et= NULL; --- 1.217/sql/sql_lex.h 2006-02-28 18:33:25 +01:00 +++ 1.218/sql/sql_lex.h 2006-03-07 12:02:08 +01:00 @@ -859,6 +859,14 @@ typedef struct st_lex uint fk_delete_opt, fk_update_opt, fk_match_option; uint slave_thd_opt, start_transaction_opt; int nest_level; + + /* + Usually `expr` rule of yacc is quite reused but some commands better + not support subqueries which comes standard with this rule, like + KILL, HA_OPEN, CREATE/ALTER EVENT etc. Set this to `true` to get + syntax error back. + */ + bool command_forbid_subselect; /* In LEX representing update which were transformed to multi-update stores total number of tables. For LEX representing multi-delete --- 1.526/sql/sql_parse.cc 2006-03-01 21:39:23 +01:00 +++ 1.527/sql/sql_parse.cc 2006-03-07 12:02:08 +01:00 @@ -1561,6 +1561,7 @@ bool dispatch_command(enum enum_server_c NET *net= &thd->net; bool error= 0; DBUG_ENTER("dispatch_command"); + DBUG_PRINT_TIME(); if (thd->killed == THD::KILL_QUERY || thd->killed == THD::KILL_BAD_DATA) thd->killed= THD::NOT_KILLED; --- 1.315/sql/sql_show.cc 2006-02-28 18:33:25 +01:00 +++ 1.316/sql/sql_show.cc 2006-03-07 12:02:09 +01:00 @@ -1535,7 +1535,7 @@ int fill_schema_processlist(THD* thd, TA bool verbose; ulong max_query_length; time_t now= time(0); - DBUG_ENTER("fill_process_list"); + DBUG_ENTER("fill_schema_processlist"); user= thd->security_ctx->master_access & PROCESS_ACL ? NullS : thd->security_ctx->priv_user; @@ -1566,6 +1566,8 @@ int fill_schema_processlist(THD* thd, TA val= tmp_sctx->user ? tmp_sctx->user : (tmp->system_thread ? "system user" : "unauthenticated user"); table->field[1]->store(val, strlen(val), cs); + DBUG_PRINT("info", ("thd_id=[%lu] user=[%s] db=[%s]", + tmp->thread_id, val,tmp->db?tmp->db:"NULL")); /* HOST */ if (tmp->peer_port && (tmp_sctx->host || tmp_sctx->ip) && thd->security_ctx->host_or_ip[0]) @@ -1574,10 +1576,15 @@ int fill_schema_processlist(THD* thd, TA my_snprintf(host, LIST_PROCESS_HOST_LEN, "%s:%u", tmp_sctx->host_or_ip, tmp->peer_port); table->field[2]->store(host, strlen(host), cs); + DBUG_PRINT("info", ("host=%s", host)); } else + { + DBUG_PRINT("info", ("host=%s", tmp_sctx->host_or_ip)); table->field[2]->store(tmp_sctx->host_or_ip, strlen(tmp_sctx->host_or_ip), cs); + } + /* DB */ if (tmp->db) { @@ -1591,8 +1598,13 @@ int fill_schema_processlist(THD* thd, TA if ((val= (char *) (tmp->killed == THD::KILL_CONNECTION? "Killed" : 0))) table->field[4]->store(val, strlen(val), cs); else + { + DBUG_PRINT("info",("command=[%*s]",command_name[tmp->command].length, + command_name[tmp->command].str)); table->field[4]->store(command_name[tmp->command].str, command_name[tmp->command].length, cs); + } + /* TIME */ table->field[5]->store((uint32)(tmp->start_time ? now - tmp->start_time : 0), TRUE); @@ -1613,6 +1625,7 @@ int fill_schema_processlist(THD* thd, TA #endif if (val) { + DBUG_PRINT("info", ("state=%s", val)); table->field[6]->store(val, strlen(val), cs); table->field[6]->set_notnull(); } @@ -1623,6 +1636,7 @@ int fill_schema_processlist(THD* thd, TA /* INFO */ if (tmp->query) { + DBUG_PRINT("info",("query=[%*s]",min(500,tmp->query_length),tmp->query)); table->field[7]->store(tmp->query, min(max_query_length, tmp->query_length), cs); table->field[7]->set_notnull(); --- 1.471/sql/sql_yacc.yy 2006-03-01 21:39:23 +01:00 +++ 1.472/sql/sql_yacc.yy 2006-03-07 12:02:10 +01:00 @@ -770,7 +770,7 @@ bool my_yyoverflow(short **a, YYSTYPE ** signed_literal now_or_signed_literal opt_escape sp_opt_default simple_ident_nospvar simple_ident_q - field_or_var limit_option + field_or_var limit_option ev_starts ev_ends part_func_expr %type <item_num> @@ -1317,7 +1317,11 @@ create: YYTHD->client_capabilities |= $<ulong_num>4; sp->restore_thd_mem_root(YYTHD); } - | CREATE EVENT_SYM opt_if_not_exists sp_name + | CREATE EVENT_SYM + { + Lex->command_forbid_subselect= true; + } + opt_if_not_exists sp_name /* BE CAREFUL when you add a new rule to update the block where YYTHD->client_capabilities is set back to original value @@ -1336,7 +1340,7 @@ create: YYABORT; } - lex->create_info.options= $3; + lex->create_info.options= $4; if (!(lex->et= new Event_timed())) // implicitly calls Event_timed::init() YYABORT; @@ -1351,7 +1355,7 @@ create: if (!lex->et_compile_phase) { - lex->et->init_name(YYTHD, $4); + lex->et->init_name(YYTHD, $5); lex->et->init_definer(YYTHD); } } @@ -1365,16 +1369,19 @@ create: Restore flag if it was cleared above $1 - CREATE $2 - EVENT_SYM - $3 - opt_if_not_exists - $4 - sp_name - $5 - the block above + $3 - block setting forbidden subselect + $4 - opt_if_not_exists + $5 - sp_name + $6 - the block above (where options are preserved) */ - YYTHD->client_capabilities |= $<ulong_num>5; + YYTHD->client_capabilities |= $<ulong_num>6; /* sql_command is set here because some rules in ev_sql_stmt can overwrite it */ + Lex->select_lex.db= 0; + Lex->command_forbid_subselect= false; Lex->sql_command= SQLCOM_CREATE_EVENT; } | CREATE @@ -1402,53 +1409,27 @@ create: ; -ev_schedule_time: EVERY_SYM expr interval - { - LEX *lex=Lex; +ev_schedule_time: EVERY_SYM expr interval ev_starts ev_ends + { + LEX *lex= Lex; if (!lex->et_compile_phase) { - switch (lex->et->init_interval(YYTHD , $2, $3)) { - case EVEX_PARSE_ERROR: - yyerror(ER(ER_SYNTAX_ERROR)); - YYABORT; - break; - case EVEX_BAD_PARAMS: - my_error(ER_EVENT_INTERVAL_NOT_POSITIVE_OR_TOO_BIG, MYF(0)); - case EVEX_MICROSECOND_UNSUP: + if (lex->et->is_valid_interval_type($3)) + { my_error(ER_NOT_SUPPORTED_YET, MYF(0), "MICROSECOND"); YYABORT; - break; } + lex->et->parse_items.interval_expr= $2; + lex->et->interval= $3; + lex->et->parse_items.starts= $4; + lex->et->parse_items.ends= $5; } } - ev_starts - ev_ends | AT_SYM expr { LEX *lex=Lex; if (!lex->et_compile_phase) - { - switch (lex->et->init_execute_at(YYTHD, $2)) { - case EVEX_PARSE_ERROR: - yyerror(ER(ER_SYNTAX_ERROR)); - YYABORT; - break; - case ER_WRONG_VALUE: - { - char buff[120]; - String str(buff,(uint32) sizeof(buff), system_charset_info); - String *str2= $2->val_str(&str); - my_error(ER_WRONG_VALUE, MYF(0), "AT", - str2? str2->c_ptr():"NULL"); - YYABORT; - break; - } - case EVEX_BAD_PARAMS: - my_error(ER_EVENT_EXEC_TIME_IN_THE_PAST, MYF(0)); - YYABORT; - break; - } - } + lex->et->parse_items.execute_at= $2; } ; @@ -1472,51 +1453,18 @@ opt_ev_status: /* empty */ { $$= 0; } ev_starts: /* empty */ { - Lex->et->init_starts(YYTHD, new Item_func_now_local()); + $$= new Item_func_now_local(); } | STARTS_SYM expr { - LEX *lex= Lex; - if (!lex->et_compile_phase) - { - - switch (lex->et->init_starts(YYTHD, $2)) { - case EVEX_PARSE_ERROR: - yyerror(ER(ER_SYNTAX_ERROR)); - YYABORT; - break; - case EVEX_BAD_PARAMS: - { - char buff[20]; - String str(buff,(uint32) sizeof(buff), system_charset_info); - String *str2= $2->val_str(&str); - my_error(ER_WRONG_VALUE, MYF(0), "STARTS", str2? str2->c_ptr(): - NULL); - YYABORT; - break; - } - } - } + $$= $2; } ; -ev_ends: /* empty */ +ev_ends: /* empty */ { $$= NULL;} | ENDS_SYM expr { - LEX *lex= Lex; - if (!lex->et_compile_phase) - { - switch (lex->et->init_ends(YYTHD, $2)) { - case EVEX_PARSE_ERROR: - yyerror(ER(ER_SYNTAX_ERROR)); - YYABORT; - break; - case EVEX_BAD_PARAMS: - my_error(ER_EVENT_ENDS_BEFORE_STARTS, MYF(0)); - YYABORT; - break; - } - } + $$= $2; } ; @@ -4836,6 +4784,7 @@ alter: YYABORT; } lex->spname= 0;//defensive programming + lex->command_forbid_subselect= true; if (!(et= new Event_timed()))// implicitly calls Event_timed::init() YYABORT; @@ -4861,6 +4810,7 @@ alter: opt_ev_comment opt_ev_sql_stmt { + Lex->command_forbid_subselect= false; /* $1 - ALTER $2 - EVENT_SYM @@ -4878,6 +4828,7 @@ alter: yyerror(ER(ER_SYNTAX_ERROR)); YYABORT; } + Lex->select_lex.db= 0; Lex->sql_command= SQLCOM_ALTER_EVENT; } | ALTER TABLESPACE alter_tablespace_info @@ -7150,9 +7101,7 @@ select_derived2: { LEX *lex= Lex; lex->derived_tables|= DERIVED_SUBQUERY; - if (((int)lex->sql_command >= (int)SQLCOM_HA_OPEN && - lex->sql_command <= (int)SQLCOM_HA_READ) || - lex->sql_command == (int)SQLCOM_KILL) + if (lex->command_forbid_subselect) { yyerror(ER(ER_SYNTAX_ERROR)); YYABORT; @@ -8647,11 +8596,16 @@ purge_option: /* kill threads */ kill: - KILL_SYM kill_option expr + KILL_SYM + { + Lex->command_forbid_subselect= true; + } + kill_option expr { LEX *lex=Lex; lex->value_list.empty(); - lex->value_list.push_front($3); + lex->value_list.push_front($4); + lex->command_forbid_subselect= true; lex->sql_command= SQLCOM_KILL; }; @@ -10087,6 +10041,7 @@ handler: my_error(ER_SP_BADSTATEMENT, MYF(0), "HANDLER"); YYABORT; } + lex->command_forbid_subselect= true; lex->sql_command = SQLCOM_HA_OPEN; if (!lex->current_select->add_table_to_list(lex->thd, $2, $4, 0)) YYABORT; @@ -10111,6 +10066,7 @@ handler: my_error(ER_SP_BADSTATEMENT, MYF(0), "HANDLER"); YYABORT; } + lex->command_forbid_subselect= true; lex->sql_command = SQLCOM_HA_READ; lex->ha_rkey_mode= HA_READ_KEY_EXACT; /* Avoid purify warnings */ lex->current_select->select_limit= new Item_int((int32) 1); @@ -10739,9 +10695,7 @@ subselect_start: '(' SELECT_SYM { LEX *lex=Lex; - if (((int)lex->sql_command >= (int)SQLCOM_HA_OPEN && - lex->sql_command <= (int)SQLCOM_HA_READ) || - lex->sql_command == (int)SQLCOM_KILL) + if (lex->command_forbid_subselect) { yyerror(ER(ER_SYNTAX_ERROR)); YYABORT; --- 1.2/mysql-test/r/events_bugs.result 2006-02-21 02:40:15 +01:00 +++ 1.3/mysql-test/r/events_bugs.result 2006-03-07 12:02:06 +01:00 @@ -1,46 +1,52 @@ create database if not exists events_test; use events_test; +"START: BUG #16407: Events: Changes in sql_mode won't be taken into account" set global event_scheduler=0; "Wait a bit to settle down" delete from mysql.event; set global event_scheduler= 1; set @old_sql_mode:=@@sql_mode; set sql_mode=ansi; -select get_lock('test_bug16407', 60); +select get_lock('test_bug16407', 60) /* main */; get_lock('test_bug16407', 60) 1 create event e_16407 on schedule every 60 second do begin -select get_lock('test_bug16407', 60); +select get_lock('test_bug16407', 60) /*e_16407*/; drop table "hashed_num"; end| -"Now if everything is fine the event has compiled and is locked -show processlist; -Id User Host db Command Time State Info -# root localhost events_test Query # NULL show processlist -# event_scheduler localhost NULL Connect # Sleeping NULL -# root localhost events_test Connect # User lock select get_lock('test_bug16407', 60) +"Now if everything is fine the event has compiled and is locked. " +"We don't create the table. Don't care that the execution will return error code" +SELECT /*1*/ USER, HOST, DB, COMMAND, STATE, INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE INFO IS NULL OR INFO NOT LIKE '%PROCESSLIST%' ORDER BY INFO; +USER HOST DB COMMAND STATE INFO +event_scheduler localhost NULL Connect Sleeping NULL +root localhost events_test Connect User lock select get_lock('test_bug16407', 60) /*e_16407*/ select release_lock('test_bug16407'); release_lock('test_bug16407') 1 set global event_scheduler= 0; -select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name; -event_schema event_name sql_mode +"Let's see what sql_mode has been saved" +SELECT event_schema, event_name, @old_sql_mode:=sql_mode FROM INFORMATION_SCHEMA.EVENTS ORDER BY event_schema, event_name; +event_schema event_name @old_sql_mode:=sql_mode events_test e_16407 REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI "Let's check whether we change the sql_mode on ALTER EVENT" set sql_mode='traditional'; alter event e_16407 do select 1; -select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name; -event_schema event_name sql_mode +"Now the sql_mode should be different from above" +SELECT event_schema, event_name, @new_sql_mode:=sql_mode FROM INFORMATION_SCHEMA.EVENTS ORDER BY event_schema, event_name; +event_schema event_name @new_sql_mode:=sql_mode events_test e_16407 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER drop event e_16407; +select @old_sql_mode = @new_sql_mode; +@old_sql_mode = @new_sql_mode +0 "Another sql_mode test" set sql_mode="traditional"; create table events_smode_test(ev_name char(10), a date) engine=myisam; -"This should never insert something" +"This should never insert something!!" create event ee_16407_2 on schedule every 60 second do begin -select get_lock('ee_16407_2', 60); +select get_lock('ee_16407_2', 60) /* ee_16407_2 */; insert into events_test.events_smode_test values('ee_16407_2','1980-19-02'); end| insert into events_smode_test values ('test','1980-19-02')| @@ -48,7 +54,7 @@ ERROR 22007: Incorrect date value: '1980 "This is ok" create event ee_16407_3 on schedule every 60 second do begin -select get_lock('ee_16407_2', 60); +select get_lock('ee_16407_2', 60) /* ee_16407_3 */; insert into events_test.events_smode_test values ('ee_16407_3','1980-02-19'); insert into events_test.events_smode_test values ('ee_16407_3','1980-02-29'); end| @@ -56,94 +62,128 @@ set sql_mode=""| "This will insert rows but they will be truncated" create event ee_16407_4 on schedule every 60 second do begin -select get_lock('ee_16407_2', 60); +select get_lock('ee_16407_2', 60) /* ee_16407_4 */; insert into events_test.events_smode_test values ('ee_16407_4','10-11-1956'); end| -select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name; +"ee_16407_2 and ee_16407_3 should have non-empty sql_mode, ee_16407_4's should be empty" +SELECT event_schema, event_name, sql_mode FROM INFORMATION_SCHEMA.EVENTS ORDER BY event_schema, event_name; event_schema event_name sql_mode events_test ee_16407_2 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER events_test ee_16407_3 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER events_test ee_16407_4 -set sql_mode="ansi"; -select get_lock('ee_16407_2', 60); +"Let's execute them. First get the lock" +SET sql_mode="ansi"; +select get_lock('ee_16407_2', 60) /* main */; get_lock('ee_16407_2', 60) 1 -set global event_scheduler= 1; -show processlist; -Id User Host db Command Time State Info -# root localhost events_test Query # NULL show processlist -# event_scheduler localhost NULL Connect # Sleeping NULL -# root localhost events_test Connect # User lock select get_lock('ee_16407_2', 60) -# root localhost events_test Connect # User lock select get_lock('ee_16407_2', 60) -# root localhost events_test Connect # User lock select get_lock('ee_16407_2', 60) +"Start the scheduler and wait a second" +SET GLOBAL event_scheduler= 1; +"Now we should have 3 events locked on ee_16407_2" +SELECT /*2*/ USER, HOST, DB, COMMAND, STATE, INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE INFO IS NULL OR INFO NOT LIKE '%PROCESSLIST%' ORDER BY INFO; +USER HOST DB COMMAND STATE INFO +event_scheduler localhost NULL Connect Sleeping NULL +root localhost events_test Connect User lock select get_lock('ee_16407_2', 60) /* ee_16407_2 */ +root localhost events_test Connect User lock select get_lock('ee_16407_2', 60) /* ee_16407_3 */ +root localhost events_test Connect User lock select get_lock('ee_16407_2', 60) /* ee_16407_4 */ +"Release lock on ee_16407_2" select release_lock('ee_16407_2'); release_lock('ee_16407_2') 1 -show processlist; -Id User Host db Command Time State Info -# root localhost events_test Query # NULL show processlist -# event_scheduler localhost NULL Connect # Sleeping NULL -set global event_scheduler= 0; +"Sleep a bit. Only event_scheduler must be running" +SELECT /*3*/ USER, HOST, DB, COMMAND, STATE, INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE INFO IS NULL OR INFO NOT LIKE '%PROCESSLIST%' ORDER BY INFO; +USER HOST DB COMMAND STATE INFO +event_scheduler localhost NULL Connect Sleeping NULL +SET GLOBAL event_scheduler= 0; select * from events_smode_test order by ev_name, a; ev_name a ee_16407_3 1980-02-19 ee_16407_3 1980-02-29 ee_16407_4 0000-00-00 "OK, last check before we drop them" -select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name; +SELECT event_schema, event_name, sql_mode FROM INFORMATION_SCHEMA.EVENTS ORDER BY event_schema, event_name; event_schema event_name sql_mode events_test ee_16407_2 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER events_test ee_16407_3 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER events_test ee_16407_4 +"Drop ee_16407_2, ee_16407_3 and ee_16407_4. We don't need them anymore" drop event ee_16407_2; drop event ee_16407_3; drop event ee_16407_4; -"And now one last test regarding sql_mode and call of SP from an event" +"And now one last test regarding SQL_MODE and call of SP from an event" delete from events_smode_test; -set sql_mode='traditional'; +SET sql_mode='traditional'; create procedure ee_16407_5_pendant() begin insert into events_test.events_smode_test values('ee_16407_5','2001-02-29'); end| create procedure ee_16407_6_pendant() begin insert into events_test.events_smode_test values('ee_16407_6','2004-02-29'); end| create event ee_16407_5 on schedule every 60 second do begin -select get_lock('ee_16407_5', 60); +select get_lock('ee_16407_5', 60) /*ee_16407_5*/; call events_test.ee_16407_5_pendant(); end| create event ee_16407_6 on schedule every 60 second do begin -select get_lock('ee_16407_5', 60); +select get_lock('ee_16407_5', 60) /*ee_16407_6*/; call events_test.ee_16407_6_pendant(); end| set sql_mode='ansi'; -select get_lock('ee_16407_5', 60); +select get_lock('ee_16407_5', 60) /*main*/; get_lock('ee_16407_5', 60) 1 -set global event_scheduler= 1; -show processlist; -Id User Host db Command Time State Info -# root localhost events_test Query # NULL show processlist -# event_scheduler localhost NULL Connect # Sleeping NULL -# root localhost events_test Connect # User lock select get_lock('ee_16407_5', 60) -# root localhost events_test Connect # User lock select get_lock('ee_16407_5', 60) +SET GLOBAL event_scheduler= 1; +"Should see event_scheduler, ee_16407_5 and ee_16407_6" +SELECT /*4*/ USER, HOST, DB, COMMAND, STATE, INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE INFO IS NULL OR INFO NOT LIKE '%PROCESSLIST%' ORDER BY INFO; +USER HOST DB COMMAND STATE INFO +event_scheduler localhost NULL Connect Sleeping NULL +root localhost events_test Connect User lock select get_lock('ee_16407_5', 60) /*ee_16407_5*/ +root localhost events_test Connect User lock select get_lock('ee_16407_5', 60) /*ee_16407_6*/ +"Release the lock and sleep a bit" select release_lock('ee_16407_5'); release_lock('ee_16407_5') 1 -show processlist; -Id User Host db Command Time State Info -# root localhost events_test Query # NULL show processlist -# event_scheduler localhost NULL Connect # Sleeping NULL +"Only the scheduler should be running:" +SELECT /*5*/ USER, HOST, DB, COMMAND, STATE, INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE INFO IS NULL OR INFO NOT LIKE '%PROCESSLIST%' ORDER BY INFO; +USER HOST DB COMMAND STATE INFO +event_scheduler localhost NULL Connect Sleeping NULL +"'2004-02-29' should be there but not '2001-02-29'" select * from events_smode_test order by ev_name, a; ev_name a ee_16407_6 2004-02-29 "And here we check one more time before we drop the events" -select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name; +SELECT event_schema, event_name, sql_mode FROM INFORMATION_SCHEMA.EVENTS ORDER BY event_schema, event_name; event_schema event_name sql_mode events_test ee_16407_5 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER events_test ee_16407_6 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER -drop event ee_16407_5; -drop event ee_16407_6; -drop procedure ee_16407_5_pendant; -drop procedure ee_16407_6_pendant; -set global event_scheduler= 0; -drop table events_smode_test; -set sql_mode=@old_sql_mode; +"Clean up" +DROP EVENT ee_16407_5; +DROP EVENT ee_16407_6; +DROP PROCEDURE ee_16407_5_pendant; +DROP PROCEDURE ee_16407_6_pendant; +SET GLOBAL event_scheduler= 0; +DROP TABLE events_smode_test; +SET sql_mode=@old_sql_mode; +"END: BUG #16407: Events: Changes in sql_mode won't be taken into account" +"START: BUG #17289 Events: missing privilege check for drop database" +CREATE USER pauline@localhost; +CREATE DATABASE db_x; +GRANT EVENT ON db_x.* TO pauline@localhost; +USE db_x; +CREATE TABLE x_table(a int); +CREATE EVENT e_x1 ON SCHEDULE EVERY 1 SECOND DO DROP DATABASE db_x; +CREATE EVENT e_x2 ON SCHEDULE EVERY 1 SECOND DO DROP TABLE x_table; +SHOW DATABASES LIKE 'db_x'; +Database (db_x) +db_x +SET GLOBAL event_scheduler=1; +SHOW DATABASES LIKE 'db_x'; +Database (db_x) +db_x +SHOW TABLES FROM db_x; +Tables_in_db_x +x_table +SET GLOBAL event_scheduler=0; +DROP EVENT e_x1; +DROP EVENT e_x2; +DROP DATABASE db_x; +DROP USER pauline@localhost; +USE events_test; +"END: BUG #17289 Events: missing privilege check for drop database" drop database events_test; --- New file --- +++ mysql-test/r/events_static_tests.result 06/03/07 12:02:10 create database if not exists events_test; use events_test; create event e_26 on schedule at NULL disable do set @a = 5; ERROR HY000: Incorrect AT value: 'NULL' create event e_26 on schedule at 'definitely not a datetime' disable do set @a = 5; ERROR HY000: Incorrect AT value: 'definitely not a datetime' create event e_26 on schedule every NULL minute do set @a=7; ERROR HY000: Incorrect INTERVAL value: 'NULL' create event e_26 on schedule every 1 minute starts NULL do set @a=8; ERROR HY000: Incorrect STARTS value: 'NULL' create event e_26 on schedule every 1 minute ends NULL do set @a=9; ERROR HY000: ENDS is either invalid or before STARTS create event e_26 on schedule every 1 minute comment NULL do set @a=10; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL do set @a=10' at line 1 create event e_26 on schedule every -2 minute do set @a=11; ERROR HY000: Incorrect INTERVAL value: '-2' create event e_26 on schedule every text minute do set @a=12; ERROR 42S22: Unknown column 'text' in 'field list' create event e_26 on schedule every 'text' minute do set @a=13; ERROR HY000: Incorrect INTERVAL value: 'text' create event e_26 on schedule every '13' minute do set @a=14; DROP EVENT e_26; create event e_26 on schedule every 'a13' minute do set @a=14; ERROR HY000: Incorrect INTERVAL value: 'a13' "START: BUG #16537 (Events: mysql.event.starts is null)" CREATE EVENT event_starts_test ON SCHEDULE EVERY 10 SECOND COMMENT "" DO SELECT 1; SHOW EVENTS; Db Name Definer Type Execute at Interval value Interval field Starts Ends Status events_test event_starts_test root@localhost RECURRING NULL 10 SECOND # # ENABLED SELECT starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test'; starts IS NULL ends IS NULL comment 0 1 ALTER EVENT event_starts_test ON SCHEDULE AT '2020-02-02 20:00:02'; SHOW EVENTS; Db Name Definer Type Execute at Interval value Interval field Starts Ends Status events_test event_starts_test root@localhost ONE TIME 2020-02-02 17:00:02 NULL NULL NULL NULL ENABLED SELECT starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test'; starts IS NULL ends IS NULL comment 1 1 ALTER EVENT event_starts_test COMMENT "non-empty comment"; SHOW EVENTS; Db Name Definer Type Execute at Interval value Interval field Starts Ends Status events_test event_starts_test root@localhost ONE TIME 2020-02-02 17:00:02 NULL NULL NULL NULL ENABLED SELECT starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test'; starts IS NULL ends IS NULL comment 1 1 non-empty comment ALTER EVENT event_starts_test COMMENT ""; SHOW EVENTS; Db Name Definer Type Execute at Interval value Interval field Starts Ends Status events_test event_starts_test root@localhost ONE TIME 2020-02-02 17:00:02 NULL NULL NULL NULL ENABLED SELECT starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test'; starts IS NULL ends IS NULL comment 1 1 DROP EVENT event_starts_test; CREATE EVENT event_starts_test ON SCHEDULE EVERY 20 SECOND STARTS '2020-02-02 20:00:02' ENDS '2022-02-02 20:00:02' DO SELECT 2; SHOW EVENTS; Db Name Definer Type Execute at Interval value Interval field Starts Ends Status events_test event_starts_test root@localhost RECURRING NULL 20 SECOND 2020-02-02 17:00:02 2022-02-02 17:00:02 ENABLED SELECT starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test'; starts IS NULL ends IS NULL comment 0 0 ALTER EVENT event_starts_test COMMENT "non-empty comment"; SHOW EVENTS; Db Name Definer Type Execute at Interval value Interval field Starts Ends Status events_test event_starts_test root@localhost RECURRING NULL 20 SECOND 2020-02-02 17:00:02 2022-02-02 17:00:02 ENABLED SELECT starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test'; starts IS NULL ends IS NULL comment 0 0 non-empty comment ALTER EVENT event_starts_test COMMENT ""; SHOW EVENTS; Db Name Definer Type Execute at Interval value Interval field Starts Ends Status events_test event_starts_test root@localhost RECURRING NULL 20 SECOND 2020-02-02 17:00:02 2022-02-02 17:00:02 ENABLED DROP EVENT event_starts_test; "END: BUG #16537 (Events: mysql.event.starts is null)" "START: BUG #17453: Creating Event crash the server" create event white_space on schedule every 10 hour disable do select 1; select event_schema, event_name, definer, event_body from information_schema.events where event_name='white_space'; event_schema event_name definer event_body events_test white_space root@localhost select 1 drop event white_space; "Use CR:" create event white_space on schedule every 10 hour disable do select 2; "Get the result, the CR should be stripped" select event_schema, event_name, definer, event_body from information_schema.events where event_name='white_space'; event_schema event_name definer event_body events_test white_space root@localhost select 2 drop event white_space; "Use series of spaces. After parsing they should be gone." create event white_space on schedule every 10 hour disable do select 3; select event_schema, event_name, definer, event_body from information_schema.events where event_name='white_space'; event_schema event_name definer event_body events_test white_space root@localhost select 3 drop event white_space; "END: BUG #17453: Creating Event crash the server" set names utf8; "START: 'SHOW CREATE EVENT' tests. Testing all possible intervals as well as utf8/cp1251 code pages" CREATE EVENT root6 ON SCHEDULE EVERY '10:20' MINUTE_SECOND ON COMPLETION PRESERVE ENABLE COMMENT 'some comment' DO select 1; SHOW CREATE EVENT root6; Event sql_mode Create Event root6 CREATE EVENT `events_test`.`root6` ON SCHEDULE EVERY '10:20' MINUTE_SECOND ON COMPLETION PRESERVE ENABLE COMMENT 'some comment' DO select 1 create event root7 on schedule every 2 year do select 1; SHOW CREATE EVENT root7; Event sql_mode Create Event root7 CREATE EVENT `events_test`.`root7` ON SCHEDULE EVERY 2 YEAR ON COMPLETION NOT PRESERVE ENABLE DO select 1 create event root8 on schedule every '2:5' year_month do select 1; SHOW CREATE EVENT root8; Event sql_mode Create Event root8 CREATE EVENT `events_test`.`root8` ON SCHEDULE EVERY '2-5' YEAR_MONTH ON COMPLETION NOT PRESERVE ENABLE DO select 1 create event root8_1 on schedule every '2:15' year_month do select 1; SHOW CREATE EVENT root8_1; Event sql_mode Create Event root8_1 CREATE EVENT `events_test`.`root8_1` ON SCHEDULE EVERY '3-3' YEAR_MONTH ON COMPLETION NOT PRESERVE ENABLE DO select 1 create event root9 on schedule every 2 week ON COMPLETION PRESERVE DISABLE COMMENT 'коментар на кирилица' do select 1; SHOW CREATE EVENT root9; Event sql_mode Create Event root9 CREATE EVENT `events_test`.`root9` ON SCHEDULE EVERY 2 WEEK ON COMPLETION PRESERVE DISABLE COMMENT 'коментар на кирилица' DO select 1 create event root10 on schedule every '20:5' day_hour do select 1; SHOW CREATE EVENT root10; Event sql_mode Create Event root10 CREATE EVENT `events_test`.`root10` ON SCHEDULE EVERY '20 5' DAY_HOUR ON COMPLETION NOT PRESERVE ENABLE DO select 1 create event root11 on schedule every '20:25' day_hour do select 1; SHOW CREATE EVENT root11; Event sql_mode Create Event root11 CREATE EVENT `events_test`.`root11` ON SCHEDULE EVERY '21 1' DAY_HOUR ON COMPLETION NOT PRESERVE ENABLE DO select 1 create event root12 on schedule every '20:25' hour_minute do select 1; SHOW CREATE EVENT root12; Event sql_mode Create Event root12 CREATE EVENT `events_test`.`root12` ON SCHEDULE EVERY '20:25' HOUR_MINUTE ON COMPLETION NOT PRESERVE ENABLE DO select 1 create event root13 on schedule every '25:25' hour_minute do select 1; SHOW CREATE EVENT root13; Event sql_mode Create Event root13 CREATE EVENT `events_test`.`root13` ON SCHEDULE EVERY '25:25' HOUR_MINUTE ON COMPLETION NOT PRESERVE ENABLE DO select 1 create event root13_1 on schedule every '11:65' hour_minute do select 1; SHOW CREATE EVENT root13_1; Event sql_mode Create Event root13_1 CREATE EVENT `events_test`.`root13_1` ON SCHEDULE EVERY '12:5' HOUR_MINUTE ON COMPLETION NOT PRESERVE ENABLE DO select 1 create event root14 on schedule every '35:35' minute_second do select 1; SHOW CREATE EVENT root14; Event sql_mode Create Event root14 CREATE EVENT `events_test`.`root14` ON SCHEDULE EVERY '35:35' MINUTE_SECOND ON COMPLETION NOT PRESERVE ENABLE DO select 1 create event root15 on schedule every '35:66' minute_second do select 1; SHOW CREATE EVENT root15; Event sql_mode Create Event root15 CREATE EVENT `events_test`.`root15` ON SCHEDULE EVERY '36:6' MINUTE_SECOND ON COMPLETION NOT PRESERVE ENABLE DO select 1 create event root16 on schedule every '35:56' day_minute do select 1; SHOW CREATE EVENT root16; Event sql_mode Create Event root16 CREATE EVENT `events_test`.`root16` ON SCHEDULE EVERY '1 11:56' DAY_MINUTE ON COMPLETION NOT PRESERVE ENABLE DO select 1 create event root17 on schedule every '35:12:45' day_minute do select 1; SHOW CREATE EVENT root17; Event sql_mode Create Event root17 CREATE EVENT `events_test`.`root17` ON SCHEDULE EVERY '35 12:45' DAY_MINUTE ON COMPLETION NOT PRESERVE ENABLE DO select 1 create event root17_1 on schedule every '35:25:65' day_minute do select 1; SHOW CREATE EVENT root17_1; Event sql_mode Create Event root17_1 CREATE EVENT `events_test`.`root17_1` ON SCHEDULE EVERY '36 2:5' DAY_MINUTE ON COMPLETION NOT PRESERVE ENABLE DO select 1 create event root18 on schedule every '35:12:45' hour_second do select 1; SHOW CREATE EVENT root18; Event sql_mode Create Event root18 CREATE EVENT `events_test`.`root18` ON SCHEDULE EVERY '35:12:45' HOUR_SECOND ON COMPLETION NOT PRESERVE ENABLE DO select 1 create event root19 on schedule every '15:59:85' hour_second do select 1; SHOW CREATE EVENT root19; Event sql_mode Create Event root19 CREATE EVENT `events_test`.`root19` ON SCHEDULE EVERY '16:0:25' HOUR_SECOND ON COMPLETION NOT PRESERVE ENABLE DO select 1 create event root20 on schedule every '50:20:12:45' day_second do select 1; SHOW CREATE EVENT root20; Event sql_mode Create Event root20 CREATE EVENT `events_test`.`root20` ON SCHEDULE EVERY '50 20:12:45' DAY_SECOND ON COMPLETION NOT PRESERVE ENABLE DO select 1 set names cp1251; create event ðóóò21 on schedule every '50:23:59:95' day_second COMMENT 'òîâà å 1251 êîìåíòàð' do select 1; SHOW CREATE EVENT ðóóò21; Event sql_mode Create Event ðóóò21 CREATE EVENT `events_test`.`ðóóò21` ON SCHEDULE EVERY '51 0:0:35' DAY_SECOND ON COMPLETION NOT PRESERVE ENABLE COMMENT 'òîâà å 1251 êîìåíòàð' DO select 1 insert into mysql.event (db, name, body, definer, interval_value, interval_field) values (database(), "root22", "select 1", user(), 100, "SECOND_MICROSECOND"); show create event root22; ERROR 42000: This version of MySQL doesn't yet support 'MICROSECOND' SHOW EVENTS; ERROR 42000: This version of MySQL doesn't yet support 'MICROSECOND' drop event root22; drop event root6; drop event root7; drop event root8; drop event root8_1; drop event root9; drop event root10; drop event root11; drop event root12; drop event root13; drop event root13_1; drop event root14; drop event root15; drop event root16; drop event root17; drop event root17_1; drop event root18; drop event root19; drop event root20; drop event ðóóò21; set names latin1; "END: 'SHOW CREATE EVENT' tests." "START: BUG 16394: Events: Crash if schedule contains SELECT" CREATE EVENT e_53 ON SCHEDULE AT (select s1 from ttx) DO DROP TABLE t; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select s1 from ttx) DO DROP TABLE t' at line 1 "END: BUG 16394: Events: Crash if schedule contains SELECT" "START: BUG #16410 Events: CREATE EVENT is legal in a CREATE TRIGGER statement" create table t_16 (s1 int); create trigger t_16_bi before insert on t_16 for each row create event e_16 on schedule every 1 second do set @a=5; ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger. drop table t_16; "END: BUG #16410 Events: CREATE EVENT is legal in a CREATE TRIGGER statement" "START: 'INFORMATION_SCHEMA.EVENTS' tests" create event one_event on schedule every 10 second do select 123; SHOW EVENTS; Db Name Definer Type Execute at Interval value Interval field Starts Ends Status events_test one_event root@localhost RECURRING NULL 10 SECOND # # ENABLED SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT from information_schema.events; EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT NULL events_test one_event root@localhost select 123 RECURRING NULL 10 SECOND ENABLED NOT PRESERVE CREATE DATABASE events_test2; CREATE USER ev_test@localhost; GRANT ALL ON events_test.* to ev_test@localhost; GRANT ALL on events_test2.* to ev_test@localhost; REVOKE EVENT ON events_test2.* FROM ev_test@localhost; REVOKE PROCESS on *.* from ev_test@localhost; select "NEW CONNECTION"; NEW CONNECTION NEW CONNECTION SELECT USER(), DATABASE(); USER() DATABASE() ev_test@localhost events_test2 SHOW GRANTS; Grants for ev_test@localhost GRANT USAGE ON *.* TO 'ev_test'@'localhost' GRANT ALL PRIVILEGES ON `events_test`.* TO 'ev_test'@'localhost' GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON `events_test2`.* TO 'ev_test'@'localhost' "Here comes an error:"; SHOW EVENTS; ERROR 42000: Access denied for user 'ev_test'@'localhost' to database 'events_test2' USE events_test; "Now the list should be empty:"; SHOW EVENTS; Db Name Definer Type Execute at Interval value Interval field Starts Ends Status select concat("Let's create some new events from the name of ",user()); concat("Let's create some new events from the name of ",user()) Let's create some new events from the name of ev_test@localhost create event one_event on schedule every 20 second do select 123; create event two_event on schedule every 20 second on completion not preserve comment "two event" do select 123; create event three_event on schedule every 20 second on completion preserve comment "three event" do select 123; "Now we should see 3 events:"; SHOW EVENTS; Db Name Definer Type Execute at Interval value Interval field Starts Ends Status events_test one_event ev_test@localhost RECURRING NULL 20 SECOND # # ENABLED events_test three_event ev_test@localhost RECURRING NULL 20 SECOND # # ENABLED events_test two_event ev_test@localhost RECURRING NULL 20 SECOND # # ENABLED "This should show us only 3 events:"; SHOW FULL EVENTS; Db Name Definer Type Execute at Interval value Interval field Starts Ends Status events_test one_event ev_test@localhost RECURRING NULL 20 SECOND # # ENABLED events_test three_event ev_test@localhost RECURRING NULL 20 SECOND # # ENABLED events_test two_event ev_test@localhost RECURRING NULL 20 SECOND # # ENABLED "This should show us only 2 events:"; SHOW FULL EVENTS LIKE 't%event'; Db Name Definer Type Execute at Interval value Interval field Starts Ends Status events_test three_event ev_test@localhost RECURRING NULL 20 SECOND # # ENABLED events_test two_event ev_test@localhost RECURRING NULL 20 SECOND # # ENABLED "This should show us no events:"; SHOW FULL EVENTS FROM test LIKE '%'; Db Name Definer Type Execute at Interval value Interval field Starts Ends Status DROP DATABASE events_test2; "should see 1 event:"; SHOW EVENTS; Db Name Definer Type Execute at Interval value Interval field Starts Ends Status events_test one_event root@localhost RECURRING NULL 10 SECOND # # ENABLED "we should see 4 events now:"; SHOW FULL EVENTS; Db Name Definer Type Execute at Interval value Interval field Starts Ends Status events_test one_event ev_test@localhost RECURRING NULL 20 SECOND # # ENABLED events_test three_event ev_test@localhost RECURRING NULL 20 SECOND # # ENABLED events_test two_event ev_test@localhost RECURRING NULL 20 SECOND # # ENABLED events_test one_event root@localhost RECURRING NULL 10 SECOND # # ENABLED SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT from information_schema.events; EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT NULL events_test one_event ev_test@localhost select 123 RECURRING NULL 20 SECOND ENABLED NOT PRESERVE NULL events_test three_event ev_test@localhost select 123 RECURRING NULL 20 SECOND ENABLED PRESERVE three event NULL events_test two_event ev_test@localhost select 123 RECURRING NULL 20 SECOND ENABLED NOT PRESERVE two event NULL events_test one_event root@localhost select 123 RECURRING NULL 10 SECOND ENABLED NOT PRESERVE drop event one_event; drop event two_event; drop event three_event; drop user ev_test@localhost; drop event one_event; "END: 'INFORMATION_SCHEMA.EVENTS' tests" drop database events_test; --- New file --- +++ mysql-test/r/events_table_intact_check.result 06/03/07 12:02:11 create database if not exists events_test; use events_test; CREATE EVENT intact_check ON SCHEDULE EVERY 10 HOUR DO SELECT "nothing"; SHOW EVENTS; Db Name Definer Type Execute at Interval value Interval field Starts Ends Status events_test intact_check root@localhost RECURRING NULL 10 HOUR # # ENABLED "Add new column at beginning - that's an error" ALTER TABLE mysql.event ADD dummy INT FIRST; SHOW EVENTS; ERROR HY000: Column count of mysql.event is wrong. Expected 16, found 17. Table probably corrupted "Drop and add new which is varchar. db is char, so this will clash." ALTER TABLE mysql.event DROP dummy, ADD dummy2 VARCHAR(64) FIRST; SHOW EVENTS; ERROR HY000: Column count of mysql.event is wrong. Expected 16, found 17. Table probably corrupted ALTER TABLE mysql.event DROP dummy2; SHOW EVENTS; Db Name Definer Type Execute at Interval value Interval field Starts Ends Status events_test intact_check root@localhost RECURRING NULL 10 HOUR # # ENABLED CREATE TABLE event_like LIKE mysql.event; INSERT INTO event_like SELECT * FROM mysql.event; "Shorten `db` a bit:" ALTER TABLE mysql.event MODIFY db char(20) character set utf8 collate utf8_bin default ''; CREATE EVENT wont_create ON SCHEDULE EVERY 10 MINUTE DO SELECT 3 FROM DUAL; ERROR HY000: Cannot load from mysql.event. Table probably corrupted. See error log. ALTER EVENT intact_check COMMENT "new comment"; ERROR HY000: Cannot load from mysql.event. Table probably corrupted. See error log. DROP EVENT intact_check; ERROR HY000: Cannot load from mysql.event. Table probably corrupted. See error log. SELECT event_name FROM INFORMATION_SCHEMA.EVENTS; ERROR HY000: Cannot load from mysql.event. Table probably corrupted. See error log. "Restore `db`, should make things work" ALTER TABLE mysql.event MODIFY db char(64) character set utf8 collate utf8_bin default ''; "This should work" SHOW EVENTS; Db Name Definer Type Execute at Interval value Interval field Starts Ends Status events_test intact_check root@localhost RECURRING NULL 10 HOUR # # ENABLED "Change charset. We should catch this change:" ALTER TABLE mysql.event MODIFY db char(64) character set cp1251 default ''; SELECT event_name FROM INFORMATION_SCHEMA.EVENTS; ERROR HY000: Cannot load from mysql.event. Table probably corrupted. See error log. ALTER TABLE mysql.event MODIFY db varchar(64) character set utf8 collate utf8_bin default ''; SELECT event_name FROM INFORMATION_SCHEMA.EVENTS; ERROR HY000: Cannot load from mysql.event. Table probably corrupted. See error log. "Drop columns from the end. Should result in a error later:" ALTER TABLE mysql.event DROP comment, DROP starts; SELECT event_name FROM INFORMATION_SCHEMA.EVENTS; ERROR HY000: Column count of mysql.event is wrong. Expected 16, found 14. Table probably corrupted DROP TABLE mysql.event; "Restore original mysql.event" CREATE TABLE mysql.event like event_like; INSERT INTO mysql.event SELECT * FROM event_like; DROP TABLE event_like; SHOW EVENTS; Db Name Definer Type Execute at Interval value Interval field Starts Ends Status events_test intact_check root@localhost RECURRING NULL 10 HOUR # # ENABLED DROP EVENT intact_check; drop database events_test; --- 1.22/mysql-test/t/events.test 2006-02-28 11:46:00 +01:00 +++ 1.23/mysql-test/t/events.test 2006-03-07 12:02:06 +01:00 @@ -1,36 +1,6 @@ create database if not exists events_test; use events_test; -# -# START: BUG #17289 Events: missing privilege check for drop database -# -CREATE USER pauline@localhost; -CREATE DATABASE db_x; -GRANT EVENT ON db_x.* TO pauline@localhost; -USE db_x; -CREATE TABLE x_table(a int); -connect (priv_conn,localhost,pauline,,db_x); -CREATE EVENT e_x1 ON SCHEDULE EVERY 1 SECOND DO DROP DATABASE db_x; -CREATE EVENT e_x2 ON SCHEDULE EVERY 1 SECOND DO DROP TABLE x_table; -connection default; -SHOW DATABASES LIKE 'db_x'; -SET GLOBAL event_scheduler=1; ---sleep 2 -SHOW DATABASES LIKE 'db_x'; -SHOW TABLES FROM db_x; -SET GLOBAL event_scheduler=0; ---sleep 1 -connection priv_conn; -DROP EVENT e_x1; -DROP EVENT e_x2; -disconnect priv_conn; -connection default; -DROP DATABASE db_x; -DROP USER pauline@localhost; -USE events_test; -# -# END: BUG #17289 Events: missing privilege check for drop database -# SET GLOBAL event_scheduler=0; --sleep 1 drop event if exists event1; @@ -48,38 +18,6 @@ drop event event2; create event event2 on schedule every 2 second starts now() ends date_add(now(), interval 5 hour) comment "some" DO begin end; drop event event2; -# BUG #16537 (Events: mysql.event.starts is null) -CREATE EVENT event_starts_test ON SCHEDULE EVERY 10 SECOND COMMENT "" DO SELECT 1; ---replace_column 8 # 9 # -SHOW EVENTS; -SELECT starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test'; -ALTER EVENT event_starts_test ON SCHEDULE AT '2020-02-02 20:00:02'; ---replace_column 8 # 9 # -SHOW EVENTS; -SELECT starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test'; -ALTER EVENT event_starts_test COMMENT "non-empty comment"; ---replace_column 8 # 9 # -SHOW EVENTS; -SELECT starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test'; -ALTER EVENT event_starts_test COMMENT ""; ---replace_column 8 # 9 # -SHOW EVENTS; -SELECT starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test'; -DROP EVENT event_starts_test; -CREATE EVENT event_starts_test ON SCHEDULE EVERY 20 SECOND STARTS '2020-02-02 20:00:02' ENDS '2022-02-02 20:00:02' DO SELECT 2; ---replace_column 8 # 9 # -SHOW EVENTS; -SELECT starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test'; -ALTER EVENT event_starts_test COMMENT "non-empty comment"; ---replace_column 8 # 9 # -SHOW EVENTS; -SELECT starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test'; -ALTER EVENT event_starts_test COMMENT ""; ---replace_column 8 # 9 # -SHOW EVENTS; -DROP EVENT event_starts_test; -# -# create event e_43 on schedule every 1 second do set @a = 5; set global event_scheduler = 1; --sleep 2 @@ -101,227 +39,16 @@ set global event_scheduler = 0; create table t_event3 (a int, b float); drop event if exists event3; create event event3 on schedule every 50 + 10 minute starts date_add("20100101", interval 5 minute) ends date_add("20151010", interval 5 day) comment "portokala_comment" DO insert into t_event3 values (unix_timestamp(), rand()); -set max_allowed_packet=128000000; select count(*) from t_event3; drop event event3; drop table t_event3; -set names utf8; -# -# SHOW CREATE EVENT test begin -# -CREATE EVENT root6 ON SCHEDULE EVERY '10:20' MINUTE_SECOND ON COMPLETION PRESERVE ENABLE COMMENT 'some comment' DO select 1; -SHOW CREATE EVENT root6; -create event root7 on schedule every 2 year do select 1; -SHOW CREATE EVENT root7; -create event root8 on schedule every '2:5' year_month do select 1; -SHOW CREATE EVENT root8; -create event root8_1 on schedule every '2:15' year_month do select 1; -SHOW CREATE EVENT root8_1; -create event root9 on schedule every 2 week ON COMPLETION PRESERVE DISABLE COMMENT 'коментар на кирилица' do select 1; -SHOW CREATE EVENT root9; -create event root10 on schedule every '20:5' day_hour do select 1; -SHOW CREATE EVENT root10; -create event root11 on schedule every '20:25' day_hour do select 1; -SHOW CREATE EVENT root11; -create event root12 on schedule every '20:25' hour_minute do select 1; -SHOW CREATE EVENT root12; -create event root13 on schedule every '25:25' hour_minute do select 1; -SHOW CREATE EVENT root13; -create event root13_1 on schedule every '11:65' hour_minute do select 1; -SHOW CREATE EVENT root13_1; -create event root14 on schedule every '35:35' minute_second do select 1; -SHOW CREATE EVENT root14; -create event root15 on schedule every '35:66' minute_second do select 1; -SHOW CREATE EVENT root15; -create event root16 on schedule every '35:56' day_minute do select 1; -SHOW CREATE EVENT root16; -create event root17 on schedule every '35:12:45' day_minute do select 1; -SHOW CREATE EVENT root17; -create event root17_1 on schedule every '35:25:65' day_minute do select 1; -SHOW CREATE EVENT root17_1; -create event root18 on schedule every '35:12:45' hour_second do select 1; -SHOW CREATE EVENT root18; -create event root19 on schedule every '15:59:85' hour_second do select 1; -SHOW CREATE EVENT root19; -create event root20 on schedule every '50:20:12:45' day_second do select 1; -SHOW CREATE EVENT root20; -set names cp1251; -create event ðóóò21 on schedule every '50:23:59:95' day_second COMMENT 'òîâà å 1251 êîìåíòàð' do select 1; -SHOW CREATE EVENT ðóóò21; -insert into mysql.event (db, name, body, definer, interval_value, interval_field) values (database(), "root22", "select 1", user(), 100, "SECOND_MICROSECOND"); ---error 1235 -show create event root22; ---error 1235 -SHOW EVENTS; -drop event root22; -drop event root6; -drop event root7; -drop event root8; -drop event root8_1; -drop event root9; -drop event root10; -drop event root11; -drop event root12; -drop event root13; -drop event root13_1; -drop event root14; -drop event root15; -drop event root16; -drop event root17; -drop event root17_1; -drop event root18; -drop event root19; -drop event root20; -drop event ðóóò21; - -set names latin1; -# -# SHOW CREATE EVENT test end -# - -# -# mysql.event intact checking start -# -# There should be at least 1 second between the ALTERs or we can't catch the change of create_time!! -# -CREATE EVENT intact_check ON SCHEDULE EVERY 10 HOUR DO SELECT "nothing"; ---replace_column 8 # 9 # -SHOW EVENTS; -ALTER TABLE mysql.event ADD dummy INT FIRST; ---error 1525 -SHOW EVENTS; -ALTER TABLE mysql.event DROP dummy, ADD dummy2 VARCHAR(64) FIRST; ---error 1525 -SHOW EVENTS; -ALTER TABLE mysql.event DROP dummy2; ---replace_column 8 # 9 # -SHOW EVENTS; -CREATE TABLE event_like LIKE mysql.event; -INSERT INTO event_like SELECT * FROM mysql.event; -#sleep a bit or we won't catch the change of time ---sleep 1 -ALTER TABLE mysql.event MODIFY db char(20) character set utf8 collate utf8_bin default ''; -#wait a bit or we won't see the difference because of seconds resolution ---sleep 1 -SHOW CREATE TABLE mysql.event; ---error 1526 -SELECT event_name FROM INFORMATION_SCHEMA.EVENTS; ---sleep 1 -ALTER TABLE mysql.event MODIFY db char(64) character set utf8 collate utf8_bin default ''; ---sleep 1 ---echo "This should work" ---replace_column 8 # 9 # -SHOW EVENTS; ---sleep 1 -ALTER TABLE mysql.event MODIFY db char(64) character set cp1251 default ''; ---error 1526 -SELECT event_name FROM INFORMATION_SCHEMA.EVENTS; ---sleep 1 -ALTER TABLE mysql.event MODIFY db varchar(64) character set utf8 collate utf8_bin default ''; ---error 1526 -SELECT event_name FROM INFORMATION_SCHEMA.EVENTS; ---sleep 1 -ALTER TABLE mysql.event DROP comment, DROP starts; ---sleep 1 ---error 1525 -SELECT event_name FROM INFORMATION_SCHEMA.EVENTS; -DROP TABLE mysql.event; -CREATE TABLE mysql.event like event_like; -INSERT INTO mysql.event SELECT * FROM event_like; -DROP TABLE event_like; ---replace_column 8 # 9 # -SHOW EVENTS; -DROP EVENT intact_check; -# -# mysql.event intact checking end -# - -# -#INFORMATION_SCHEMA.EVENTS test begin -# -create event one_event on schedule every 10 second do select 123; ---replace_column 8 # 9 # -SHOW EVENTS; -SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT from information_schema.events; -CREATE DATABASE events_test2; -CREATE USER ev_test@localhost; -GRANT ALL ON events_test.* to ev_test@localhost; -GRANT ALL on events_test2.* to ev_test@localhost; -REVOKE EVENT ON events_test2.* FROM ev_test@localhost; -REVOKE PROCESS on *.* from ev_test@localhost; -#now we are on con1 -connect (ev_con1,localhost,ev_test,,events_test2); -select "NEW CONNECTION"; -SELECT USER(), DATABASE(); -SHOW GRANTS; - ---echo "Here comes an error:"; -#NO EVENT_ACL on events_test2 ---error 1044 -SHOW EVENTS; -USE events_test; - ---echo "Now the list should be empty:"; ---replace_column 8 # 9 # -SHOW EVENTS; -#now create an event with the same name but we are different user -select concat("Let's create some new events from the name of ",user()); -create event one_event on schedule every 20 second do select 123; -create event two_event on schedule every 20 second on completion not preserve comment "two event" do select 123; -create event three_event on schedule every 20 second on completion preserve comment "three event" do select 123; - ---echo "Now we should see 3 events:"; ---replace_column 8 # 9 # -SHOW EVENTS; - ---echo "This should show us only 3 events:"; ---replace_column 8 # 9 # -SHOW FULL EVENTS; - ---echo "This should show us only 2 events:"; ---replace_column 8 # 9 # -SHOW FULL EVENTS LIKE 't%event'; - ---echo "This should show us no events:"; ---replace_column 8 # 9 # -SHOW FULL EVENTS FROM test LIKE '%'; -#ok, we are back -connection default; -DROP DATABASE events_test2; - ---echo "should see 1 event:"; ---replace_column 8 # 9 # -SHOW EVENTS; - ---echo "we should see 4 events now:"; ---replace_column 8 # 9 # -SHOW FULL EVENTS; -SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT from information_schema.events; - -connection ev_con1; -drop event one_event; -drop event two_event; -drop event three_event; -disconnect ev_con1; -connection default; -drop user ev_test@localhost; -drop event one_event; -# -##INFORMATION_SCHEMA.EVENTS test end -# - --echo "Sleep a bit so the server closes the second connection" --sleep 2 create event e_26 on schedule at '2017-01-01 00:00:00' disable do set @a = 5; select db, name, body, definer, convert_tz(execute_at, 'UTC', 'SYSTEM'), on_completion from mysql.event; drop event e_26; ---error 1503 -create event e_26 on schedule at NULL disabled do set @a = 5; ---error 1503 -create event e_26 on schedule at 'definitely not a datetime' disabled do set @a = 5; - set names utf8; create event задачка on schedule every 123 minute starts now() ends now() + interval 1 month do select 1; drop event задачка; @@ -337,13 +64,12 @@ set global event_scheduler=2; set global event_scheduler=0; select definer, name, db from mysql.event; select get_lock("test_lock1", 20); -create event закачка on schedule every 10 hour do select get_lock("test_lock1", 20); +create event закачка on schedule every 10 hour do select get_lock("test_lock1", 20)/*1*/; --echo "Should return 1 row" select definer, name, db from mysql.event; ---echo "Should be only 1 process" ---replace_column 1 # 6 # -show processlist; +--echo "Should be 0 process (we don't show ours)" +SELECT /*1*/ USER, HOST, DB, COMMAND, STATE, INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE INFO IS NULL OR INFO NOT LIKE '%PROCESSLIST%' ORDER BY INFO; select release_lock("test_lock1"); drop event закачка; --echo "Should have 0 events" @@ -356,12 +82,11 @@ select count(*) from mysql.event; set global event_scheduler=1; select get_lock("test_lock2", 20); --echo "Create an event which tries to acquire a mutex. The event locks on the mutex" -create event закачка on schedule every 10 hour do select get_lock("test_lock2", 20); +create event закачка on schedule every 10 hour do select get_lock("test_lock2", 20)/*2*/; --echo "Let some time pass to the event starts" --sleep 2 ---echo "Should have only 3 processes: the scheduler, our conn and the locked event" ---replace_column 1 # 6 # -show processlist; +--echo "Should have only 2 processes: the scheduler and the locked event (we skip our own conn)" +SELECT /*2*/ USER, HOST, DB, COMMAND, STATE, INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE INFO IS NULL OR INFO NOT LIKE '%PROCESSLIST%' ORDER BY INFO; --echo "Release the mutex, the event worker should finish." select release_lock("test_lock2"); drop event закачка; @@ -374,64 +99,34 @@ drop event закачка; ## 5. kill the scheduler, it will wait for the child to stop ## 6. both processes should be there on show processlist ## 7. release the lock and sleep, both scheduler and child should end +--sleep 1 set global event_scheduler=1; select get_lock("test_lock2_1", 20); create event закачка21 on schedule every 10 hour do select get_lock("test_lock2_1", 20); --sleep 1 ---echo "Should see 2 processes, one locked on get_lock(" -#--replace_column 1 # 6 # -#show processlist; +--echo "Should see 2 processes: scheduler and one locked on get_lock(" +SELECT /*3*/ USER, HOST, DB, COMMAND, STATE, INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE INFO IS NULL OR INFO NOT LIKE '%PROCESSLIST%' ORDER BY INFO; --echo "Shutting down the scheduler, it should wait for the running event" set global event_scheduler=0; --sleep 1 ---echo "Should have only 3 processes: the scheduler, our conn and the locked event" ---replace_column 1 # 6 # -show processlist; +--echo "Should have only 2 processes: the scheduler and the locked event. We filter out our conn." +--echo "Though we asked for shutdown of the scheduler it's still running because" +--echo "have the lock test_lock2_1" +SELECT /*4*/ USER, HOST, DB, COMMAND, STATE, INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE INFO IS NULL OR INFO NOT LIKE '%PROCESSLIST%' ORDER BY INFO; --echo "Release the lock so the child process should finish. Hence the scheduler also" select release_lock("test_lock2_1"); --sleep 1 ---echo "Should have only our process now:" ---replace_column 1 # 6 # -show processlist; +SET @abc:="Should have 0 process now. Our conn is filtered out."; +--echo "Should have 0 process now. Our conn is filtered out." +SELECT /*5*/ USER, HOST, DB, COMMAND, STATE, INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE INFO IS NULL OR INFO NOT LIKE '%PROCESSLIST%' ORDER BY INFO; drop event закачка21; -#### -# Bug #16410 Events: CREATE EVENT is legal in a CREATE TRIGGER statement -# -create table t_16 (s1 int); ---error 1422 -create trigger t_16_bi before insert on t_16 for each row create event e_16 on schedule every 1 second do set @a=5; -drop table t_16; -# -# end of test case -#### - -# -# START: BUG #17453: Creating Event crash the server -# -create event white_space -on schedule every 10 hour -disable -do -select 1; -select event_schema, event_name, definer, event_body from information_schema.events where event_name='white_space'; -drop event white_space; -create event white_space on schedule every 10 hour disable do - select 2; -select event_schema, event_name, definer, event_body from information_schema.events where event_name='white_space'; -drop event white_space; -create event white_space on schedule every 10 hour disable do select 3; -select event_schema, event_name, definer, event_body from information_schema.events where event_name='white_space'; -drop event white_space; -# -# END: BUG #17453: Creating Event crash the server -# ##set global event_scheduler=1; ##select get_lock("test_lock3", 20); ##create event закачка on schedule every 10 hour do select get_lock("test_lock3", 20); ##select sleep(2); -##show processlist; +##SELECT /*6*/ USER, HOST, DB, COMMAND, STATE, INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE INFO IS NULL OR INFO NOT LIKE '%PROCESSLIST%' ORDER BY INFO; ##drop event закачка; ##select release_lock("test_lock3"); @@ -442,14 +137,14 @@ drop event white_space; ##create event закачка4 on schedule every 1 second do select get_lock("test_lock4", 20); ##select sleep(3); ##--replace_column 1 # 6 # -##show processlist; +##SELECT /*7*/ USER, HOST, DB, COMMAND, STATE, INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE INFO IS NULL OR INFO NOT LIKE '%PROCESSLIST%' ORDER BY INFO; ##drop event закачка4; ##select release_lock("test_lock4"); ##set global event_scheduler=0; ##select sleep(2); ##--replace_column 1 # 6 # -##show processlist; +##SELECT /*8*/ USER, HOST, DB, COMMAND, STATE, INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE INFO IS NULL OR INFO NOT LIKE '%PROCESSLIST%' ORDER BY INFO; ##select count(*) from mysql.event; drop database events_test; --- 1.1/mysql-test/t/events_bugs.test 2006-02-20 23:52:12 +01:00 +++ 1.2/mysql-test/t/events_bugs.test 2006-03-07 12:02:07 +01:00 @@ -1,8 +1,11 @@ create database if not exists events_test; use events_test; + + # # Start - 16407: Events: Changes in sql_mode won't be taken into account # +--echo "START: BUG #16407: Events: Changes in sql_mode won't be taken into account" set global event_scheduler=0; --echo "Wait a bit to settle down" --sleep 1 @@ -10,35 +13,38 @@ delete from mysql.event; set global event_scheduler= 1; set @old_sql_mode:=@@sql_mode; set sql_mode=ansi; -select get_lock('test_bug16407', 60); +select get_lock('test_bug16407', 60) /* main */; delimiter |; create event e_16407 on schedule every 60 second do begin - select get_lock('test_bug16407', 60); + select get_lock('test_bug16407', 60) /*e_16407*/; drop table "hashed_num"; end| delimiter ;| --sleep 1 ---echo "Now if everything is fine the event has compiled and is locked ---replace_column 1 # 6 # -show processlist; +--echo "Now if everything is fine the event has compiled and is locked. " +--echo "We don't create the table. Don't care that the execution will return error code" +SELECT /*1*/ USER, HOST, DB, COMMAND, STATE, INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE INFO IS NULL OR INFO NOT LIKE '%PROCESSLIST%' ORDER BY INFO; select release_lock('test_bug16407'); set global event_scheduler= 0; -select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name; +--echo "Let's see what sql_mode has been saved" +SELECT event_schema, event_name, @old_sql_mode:=sql_mode FROM INFORMATION_SCHEMA.EVENTS ORDER BY event_schema, event_name; --echo "Let's check whether we change the sql_mode on ALTER EVENT" set sql_mode='traditional'; alter event e_16407 do select 1; -select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name; +--echo "Now the sql_mode should be different from above" +SELECT event_schema, event_name, @new_sql_mode:=sql_mode FROM INFORMATION_SCHEMA.EVENTS ORDER BY event_schema, event_name; drop event e_16407; +select @old_sql_mode = @new_sql_mode; --echo "Another sql_mode test" set sql_mode="traditional"; create table events_smode_test(ev_name char(10), a date) engine=myisam; ---echo "This should never insert something" +--echo "This should never insert something!!" delimiter |; create event ee_16407_2 on schedule every 60 second do begin - select get_lock('ee_16407_2', 60); + select get_lock('ee_16407_2', 60) /* ee_16407_2 */; insert into events_test.events_smode_test values('ee_16407_2','1980-19-02'); end| --error 1292 @@ -46,7 +52,7 @@ insert into events_smode_test values ('t --echo "This is ok" create event ee_16407_3 on schedule every 60 second do begin - select get_lock('ee_16407_2', 60); + select get_lock('ee_16407_2', 60) /* ee_16407_3 */; insert into events_test.events_smode_test values ('ee_16407_3','1980-02-19'); insert into events_test.events_smode_test values ('ee_16407_3','1980-02-29'); end| @@ -54,68 +60,110 @@ set sql_mode=""| --echo "This will insert rows but they will be truncated" create event ee_16407_4 on schedule every 60 second do begin - select get_lock('ee_16407_2', 60); + select get_lock('ee_16407_2', 60) /* ee_16407_4 */; insert into events_test.events_smode_test values ('ee_16407_4','10-11-1956'); end| delimiter ;| -select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name; -set sql_mode="ansi"; -select get_lock('ee_16407_2', 60); -set global event_scheduler= 1; +--echo "ee_16407_2 and ee_16407_3 should have non-empty sql_mode, ee_16407_4's should be empty" +SELECT event_schema, event_name, sql_mode FROM INFORMATION_SCHEMA.EVENTS ORDER BY event_schema, event_name; +--echo "Let's execute them. First get the lock" +SET sql_mode="ansi"; +select get_lock('ee_16407_2', 60) /* main */; +--echo "Start the scheduler and wait a second" +SET GLOBAL event_scheduler= 1; --sleep 1 ---replace_column 1 # 6 # -show processlist; +--echo "Now we should have 3 events locked on ee_16407_2" +SELECT /*2*/ USER, HOST, DB, COMMAND, STATE, INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE INFO IS NULL OR INFO NOT LIKE '%PROCESSLIST%' ORDER BY INFO; +--echo "Release lock on ee_16407_2" select release_lock('ee_16407_2'); ---sleep 3 ---replace_column 1 # 6 # -show processlist; -set global event_scheduler= 0; +--echo "Sleep a bit. Only event_scheduler must be running" +--sleep 2 +SELECT /*3*/ USER, HOST, DB, COMMAND, STATE, INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE INFO IS NULL OR INFO NOT LIKE '%PROCESSLIST%' ORDER BY INFO; +SET GLOBAL event_scheduler= 0; select * from events_smode_test order by ev_name, a; --echo "OK, last check before we drop them" -select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name; +SELECT event_schema, event_name, sql_mode FROM INFORMATION_SCHEMA.EVENTS ORDER BY event_schema, event_name; +--echo "Drop ee_16407_2, ee_16407_3 and ee_16407_4. We don't need them anymore" drop event ee_16407_2; drop event ee_16407_3; drop event ee_16407_4; - ---echo "And now one last test regarding sql_mode and call of SP from an event" +--echo "And now one last test regarding SQL_MODE and call of SP from an event" delete from events_smode_test; -set sql_mode='traditional'; +SET sql_mode='traditional'; delimiter |; create procedure ee_16407_5_pendant() begin insert into events_test.events_smode_test values('ee_16407_5','2001-02-29'); end| create procedure ee_16407_6_pendant() begin insert into events_test.events_smode_test values('ee_16407_6','2004-02-29'); end| create event ee_16407_5 on schedule every 60 second do begin - select get_lock('ee_16407_5', 60); + select get_lock('ee_16407_5', 60) /*ee_16407_5*/; call events_test.ee_16407_5_pendant(); end| create event ee_16407_6 on schedule every 60 second do begin - select get_lock('ee_16407_5', 60); + select get_lock('ee_16407_5', 60) /*ee_16407_6*/; call events_test.ee_16407_6_pendant(); end| delimiter ;| set sql_mode='ansi'; -select get_lock('ee_16407_5', 60); -set global event_scheduler= 1; +select get_lock('ee_16407_5', 60) /*main*/; +SET GLOBAL event_scheduler= 1; --sleep 1 ---replace_column 1 # 6 # -show processlist; +--echo "Should see event_scheduler, ee_16407_5 and ee_16407_6" +SELECT /*4*/ USER, HOST, DB, COMMAND, STATE, INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE INFO IS NULL OR INFO NOT LIKE '%PROCESSLIST%' ORDER BY INFO; +--echo "Release the lock and sleep a bit" select release_lock('ee_16407_5'); ---sleep 3 ---replace_column 1 # 6 # -show processlist; +--sleep 2 +--echo "Only the scheduler should be running:" +SELECT /*5*/ USER, HOST, DB, COMMAND, STATE, INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE INFO IS NULL OR INFO NOT LIKE '%PROCESSLIST%' ORDER BY INFO; +--echo "'2004-02-29' should be there but not '2001-02-29'" select * from events_smode_test order by ev_name, a; --echo "And here we check one more time before we drop the events" -select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name; -drop event ee_16407_5; -drop event ee_16407_6; -drop procedure ee_16407_5_pendant; -drop procedure ee_16407_6_pendant; -set global event_scheduler= 0; -drop table events_smode_test; -set sql_mode=@old_sql_mode; +SELECT event_schema, event_name, sql_mode FROM INFORMATION_SCHEMA.EVENTS ORDER BY event_schema, event_name; +--echo "Clean up" +DROP EVENT ee_16407_5; +DROP EVENT ee_16407_6; +DROP PROCEDURE ee_16407_5_pendant; +DROP PROCEDURE ee_16407_6_pendant; +SET GLOBAL event_scheduler= 0; +DROP TABLE events_smode_test; +SET sql_mode=@old_sql_mode; +--echo "END: BUG #16407: Events: Changes in sql_mode won't be taken into account" # -# End - 16407: Events: Changes in sql_mode won't be taken into account +# END - 16407: Events: Changes in sql_mode won't be taken into account # + +# +# START: BUG #17289 Events: missing privilege check for drop database +# +--echo "START: BUG #17289 Events: missing privilege check for drop database" +CREATE USER pauline@localhost; +CREATE DATABASE db_x; +GRANT EVENT ON db_x.* TO pauline@localhost; +USE db_x; +CREATE TABLE x_table(a int); +connect (priv_conn,localhost,pauline,,db_x); +CREATE EVENT e_x1 ON SCHEDULE EVERY 1 SECOND DO DROP DATABASE db_x; +CREATE EVENT e_x2 ON SCHEDULE EVERY 1 SECOND DO DROP TABLE x_table; +connection default; +SHOW DATABASES LIKE 'db_x'; +SET GLOBAL event_scheduler=1; +--sleep 2 +SHOW DATABASES LIKE 'db_x'; +SHOW TABLES FROM db_x; +SET GLOBAL event_scheduler=0; +--sleep 1 +connection priv_conn; +DROP EVENT e_x1; +DROP EVENT e_x2; +disconnect priv_conn; +connection default; +DROP DATABASE db_x; +DROP USER pauline@localhost; +USE events_test; +--echo "END: BUG #17289 Events: missing privilege check for drop database" +# +# END: BUG #17289 Events: missing privilege check for drop database +# + drop database events_test; --- New file --- +++ mysql-test/t/events_static_tests.test 06/03/07 12:02:24 create database if not exists events_test; use events_test; --error 1503 create event e_26 on schedule at NULL disable do set @a = 5; --error 1503 create event e_26 on schedule at 'definitely not a datetime' disable do set @a = 5; --error 1503 create event e_26 on schedule every NULL minute do set @a=7; --error 1503 create event e_26 on schedule every 1 minute starts NULL do set @a=8; --error 1521 create event e_26 on schedule every 1 minute ends NULL do set @a=9; --error 1064 create event e_26 on schedule every 1 minute comment NULL do set @a=10; --error 1503 create event e_26 on schedule every -2 minute do set @a=11; --error 1054 create event e_26 on schedule every text minute do set @a=12; --error 1503 create event e_26 on schedule every 'text' minute do set @a=13; create event e_26 on schedule every '13' minute do set @a=14; DROP EVENT e_26; --error 1503 create event e_26 on schedule every 'a13' minute do set @a=14; # # START BUG #16537 (Events: mysql.event.starts is null) # --echo "START: BUG #16537 (Events: mysql.event.starts is null)" CREATE EVENT event_starts_test ON SCHEDULE EVERY 10 SECOND COMMENT "" DO SELECT 1; --replace_column 8 # 9 # SHOW EVENTS; SELECT starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test'; ALTER EVENT event_starts_test ON SCHEDULE AT '2020-02-02 20:00:02'; SHOW EVENTS; SELECT starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test'; ALTER EVENT event_starts_test COMMENT "non-empty comment"; SHOW EVENTS; SELECT starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test'; ALTER EVENT event_starts_test COMMENT ""; SHOW EVENTS; SELECT starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test'; DROP EVENT event_starts_test; CREATE EVENT event_starts_test ON SCHEDULE EVERY 20 SECOND STARTS '2020-02-02 20:00:02' ENDS '2022-02-02 20:00:02' DO SELECT 2; SHOW EVENTS; SELECT starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test'; ALTER EVENT event_starts_test COMMENT "non-empty comment"; SHOW EVENTS; SELECT starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test'; ALTER EVENT event_starts_test COMMENT ""; SHOW EVENTS; DROP EVENT event_starts_test; --echo "END: BUG #16537 (Events: mysql.event.starts is null)" # # END: BUG #16537 (Events: mysql.event.starts is null) # # # START: BUG #17453: Creating Event crash the server # --echo "START: BUG #17453: Creating Event crash the server" create event white_space on schedule every 10 hour disable do select 1; select event_schema, event_name, definer, event_body from information_schema.events where event_name='white_space'; drop event white_space; --echo "Use CR:" create event white_space on schedule every 10 hour disable do select 2; --echo "Get the result, the CR should be stripped" select event_schema, event_name, definer, event_body from information_schema.events where event_name='white_space'; drop event white_space; --echo "Use series of spaces. After parsing they should be gone." create event white_space on schedule every 10 hour disable do select 3; select event_schema, event_name, definer, event_body from information_schema.events where event_name='white_space'; drop event white_space; --echo "END: BUG #17453: Creating Event crash the server" # # END: BUG #17453: Creating Event crash the server # set names utf8; # # START - SHOW CREATE EVENT test # --echo "START: 'SHOW CREATE EVENT' tests. Testing all possible intervals as well as utf8/cp1251 code pages" CREATE EVENT root6 ON SCHEDULE EVERY '10:20' MINUTE_SECOND ON COMPLETION PRESERVE ENABLE COMMENT 'some comment' DO select 1; SHOW CREATE EVENT root6; create event root7 on schedule every 2 year do select 1; SHOW CREATE EVENT root7; create event root8 on schedule every '2:5' year_month do select 1; SHOW CREATE EVENT root8; create event root8_1 on schedule every '2:15' year_month do select 1; SHOW CREATE EVENT root8_1; create event root9 on schedule every 2 week ON COMPLETION PRESERVE DISABLE COMMENT 'коментар на кирилица' do select 1; SHOW CREATE EVENT root9; create event root10 on schedule every '20:5' day_hour do select 1; SHOW CREATE EVENT root10; create event root11 on schedule every '20:25' day_hour do select 1; SHOW CREATE EVENT root11; create event root12 on schedule every '20:25' hour_minute do select 1; SHOW CREATE EVENT root12; create event root13 on schedule every '25:25' hour_minute do select 1; SHOW CREATE EVENT root13; create event root13_1 on schedule every '11:65' hour_minute do select 1; SHOW CREATE EVENT root13_1; create event root14 on schedule every '35:35' minute_second do select 1; SHOW CREATE EVENT root14; create event root15 on schedule every '35:66' minute_second do select 1; SHOW CREATE EVENT root15; create event root16 on schedule every '35:56' day_minute do select 1; SHOW CREATE EVENT root16; create event root17 on schedule every '35:12:45' day_minute do select 1; SHOW CREATE EVENT root17; create event root17_1 on schedule every '35:25:65' day_minute do select 1; SHOW CREATE EVENT root17_1; create event root18 on schedule every '35:12:45' hour_second do select 1; SHOW CREATE EVENT root18; create event root19 on schedule every '15:59:85' hour_second do select 1; SHOW CREATE EVENT root19; create event root20 on schedule every '50:20:12:45' day_second do select 1; SHOW CREATE EVENT root20; set names cp1251; create event ðóóò21 on schedule every '50:23:59:95' day_second COMMENT 'òîâà å 1251 êîìåíòàð' do select 1; SHOW CREATE EVENT ðóóò21; insert into mysql.event (db, name, body, definer, interval_value, interval_field) values (database(), "root22", "select 1", user(), 100, "SECOND_MICROSECOND"); --error 1235 show create event root22; --error 1235 SHOW EVENTS; drop event root22; drop event root6; drop event root7; drop event root8; drop event root8_1; drop event root9; drop event root10; drop event root11; drop event root12; drop event root13; drop event root13_1; drop event root14; drop event root15; drop event root16; drop event root17; drop event root17_1; drop event root18; drop event root19; drop event root20; drop event ðóóò21; set names latin1; --echo "END: 'SHOW CREATE EVENT' tests." # # END - SHOW CREATE EVENT test # # # Start - 16394: Events: Crash if schedule contains SELECT # --echo "START: BUG 16394: Events: Crash if schedule contains SELECT" --error 1064 CREATE EVENT e_53 ON SCHEDULE AT (select s1 from ttx) DO DROP TABLE t; --echo "END: BUG 16394: Events: Crash if schedule contains SELECT" # # End - 16394: Events: Crash if schedule contains SELECT # # # START - Bug #16410 Events: CREATE EVENT is legal in a CREATE TRIGGER statement # --echo "START: BUG #16410 Events: CREATE EVENT is legal in a CREATE TRIGGER statement" create table t_16 (s1 int); --error 1422 create trigger t_16_bi before insert on t_16 for each row create event e_16 on schedule every 1 second do set @a=5; drop table t_16; --echo "END: BUG #16410 Events: CREATE EVENT is legal in a CREATE TRIGGER statement" # # END - Bug #16410 Events: CREATE EVENT is legal in a CREATE TRIGGER statement # # # START - INFORMATION_SCHEMA.EVENTS # --echo "START: 'INFORMATION_SCHEMA.EVENTS' tests" create event one_event on schedule every 10 second do select 123; --replace_column 8 # 9 # SHOW EVENTS; SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT from information_schema.events; CREATE DATABASE events_test2; CREATE USER ev_test@localhost; GRANT ALL ON events_test.* to ev_test@localhost; GRANT ALL on events_test2.* to ev_test@localhost; REVOKE EVENT ON events_test2.* FROM ev_test@localhost; REVOKE PROCESS on *.* from ev_test@localhost; #now we are on con1 connect (ev_con1,localhost,ev_test,,events_test2); select "NEW CONNECTION"; SELECT USER(), DATABASE(); SHOW GRANTS; --echo "Here comes an error:"; #NO EVENT_ACL on events_test2 --error 1044 SHOW EVENTS; USE events_test; --echo "Now the list should be empty:"; --replace_column 8 # 9 # SHOW EVENTS; #now create an event with the same name but we are different user select concat("Let's create some new events from the name of ",user()); create event one_event on schedule every 20 second do select 123; create event two_event on schedule every 20 second on completion not preserve comment "two event" do select 123; create event three_event on schedule every 20 second on completion preserve comment "three event" do select 123; --echo "Now we should see 3 events:"; --replace_column 8 # 9 # SHOW EVENTS; --echo "This should show us only 3 events:"; --replace_column 8 # 9 # SHOW FULL EVENTS; --echo "This should show us only 2 events:"; --replace_column 8 # 9 # SHOW FULL EVENTS LIKE 't%event'; --echo "This should show us no events:"; --replace_column 8 # 9 # SHOW FULL EVENTS FROM test LIKE '%'; #ok, we are back connection default; DROP DATABASE events_test2; --echo "should see 1 event:"; --replace_column 8 # 9 # SHOW EVENTS; --echo "we should see 4 events now:"; --replace_column 8 # 9 # SHOW FULL EVENTS; SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT from information_schema.events; connection ev_con1; drop event one_event; drop event two_event; drop event three_event; disconnect ev_con1; connection default; drop user ev_test@localhost; drop event one_event; --echo "END: 'INFORMATION_SCHEMA.EVENTS' tests" # # END - INFORMATION_SCHEMA.EVENTS # drop database events_test; --- New file --- +++ mysql-test/t/events_table_intact_check.test 06/03/07 12:02:25 create database if not exists events_test; use events_test; # # mysql.event intact checking start # # There should be at least 1 second between the ALTERs or we can't catch the change of create_time!! # CREATE EVENT intact_check ON SCHEDULE EVERY 10 HOUR DO SELECT "nothing"; --replace_column 8 # 9 # SHOW EVENTS; --echo "Add new column at beginning - that's an error" ALTER TABLE mysql.event ADD dummy INT FIRST; --error 1525 SHOW EVENTS; --echo "Drop and add new which is varchar. db is char, so this will clash." ALTER TABLE mysql.event DROP dummy, ADD dummy2 VARCHAR(64) FIRST; --error 1525 SHOW EVENTS; ALTER TABLE mysql.event DROP dummy2; --replace_column 8 # 9 # SHOW EVENTS; CREATE TABLE event_like LIKE mysql.event; INSERT INTO event_like SELECT * FROM mysql.event; #sleep a bit or we won't catch the change of time --echo "Shorten `db` a bit:" --sleep 1 ALTER TABLE mysql.event MODIFY db char(20) character set utf8 collate utf8_bin default ''; #wait a bit or we won't see the difference because of seconds resolution --sleep 1 --error 1526 CREATE EVENT wont_create ON SCHEDULE EVERY 10 MINUTE DO SELECT 3 FROM DUAL; --error 1526 ALTER EVENT intact_check COMMENT "new comment"; --error 1526 DROP EVENT intact_check; --error 1526 SELECT event_name FROM INFORMATION_SCHEMA.EVENTS; --echo "Restore `db`, should make things work" --sleep 1 ALTER TABLE mysql.event MODIFY db char(64) character set utf8 collate utf8_bin default ''; --sleep 1 --echo "This should work" --replace_column 8 # 9 # SHOW EVENTS; --echo "Change charset. We should catch this change:" --sleep 1 ALTER TABLE mysql.event MODIFY db char(64) character set cp1251 default ''; --error 1526 SELECT event_name FROM INFORMATION_SCHEMA.EVENTS; --sleep 1 ALTER TABLE mysql.event MODIFY db varchar(64) character set utf8 collate utf8_bin default ''; --error 1526 SELECT event_name FROM INFORMATION_SCHEMA.EVENTS; --echo "Drop columns from the end. Should result in a error later:" --sleep 1 ALTER TABLE mysql.event DROP comment, DROP starts; --sleep 1 --error 1525 SELECT event_name FROM INFORMATION_SCHEMA.EVENTS; DROP TABLE mysql.event; --echo "Restore original mysql.event" CREATE TABLE mysql.event like event_like; INSERT INTO mysql.event SELECT * FROM event_like; DROP TABLE event_like; --replace_column 8 # 9 # SHOW EVENTS; DROP EVENT intact_check; # # mysql.event intact checking end # drop database events_test; --- 1.37/sql/event.cc 2006-02-28 20:32:30 +01:00 +++ 1.38/sql/event.cc 2006-03-07 12:02:07 +01:00 @@ -463,7 +463,7 @@ common_1_lev_code: /* - Open mysql.event table for read + Open mysql.event table SYNOPSIS evex_open_event_table_for_read() @@ -481,7 +481,7 @@ int evex_open_event_table(THD *thd, enum thr_lock_type lock_type, TABLE **table) { TABLE_LIST tables; - DBUG_ENTER("open_proc_table"); + DBUG_ENTER("evex_open_event_table"); bzero((char*) &tables, sizeof(tables)); tables.db= (char*) "mysql"; @@ -505,6 +505,48 @@ evex_open_event_table(THD *thd, enum thr /* + Open mysql.event table for write for CREATE/ALTER EVENT + + SYNOPSIS + evex_open_all_tables() + thd Thread context + lock_type How to lock the table + table The table pointer + + RETURNS + 0 OK + 1 Error during open_n_load + 2 mysql.event tampered +*/ + +static int +evex_open_all_tables(THD *thd, TABLE **table) +{ + TABLE_LIST *table_list; + LEX_STRING dbname={(char*) STRING_WITH_LEN("mysql")}; + LEX_STRING tname= {(char*) STRING_WITH_LEN("event")}; + + DBUG_ENTER("evex_open_all_tables"); + + Table_ident *ident= new Table_ident(thd, dbname, tname, 1); + if (check_table_access(thd, SELECT_ACL, thd->lex->query_tables, 0) || + !(table_list= thd->lex->select_lex. + add_table_to_list(thd, ident, 0, 0, TL_WRITE, (List<String> *) 0, + (List<String> *) 0)) || + open_and_lock_tables(thd, thd->lex->query_tables)) + DBUG_RETURN(1); + + if (table_check_intact(table_list->table, EVEX_FIELD_COUNT, event_table_fields, + &mysql_event_last_create_time, + ER_CANNOT_LOAD_FROM_TABLE)) + DBUG_RETURN(2); + + *table= table_list->table; + DBUG_RETURN(0); +} + + +/* Find row in open mysql.event table representing event SYNOPSIS @@ -710,8 +752,9 @@ trunc_err: db_create_event() thd THD et Event_timed object containing information for the event - create_if_not - if an warning should be generated in case event exists - rows_affected - how many rows were affected + create_if_not If an warning should be generated in case event exists + rows_affected How many rows were affected + table Table to use Return value 0 - OK @@ -723,10 +766,9 @@ trunc_err: static int db_create_event(THD *thd, Event_timed *et, my_bool create_if_not, - uint *rows_affected) + uint *rows_affected, TABLE *table) { int ret= 0; - TABLE *table; char olddb[128]; bool dbchanged= false; DBUG_ENTER("db_create_event"); @@ -734,11 +776,6 @@ db_create_event(THD *thd, Event_timed *e *rows_affected= 0; DBUG_PRINT("info", ("open mysql.event for update")); - if (evex_open_event_table(thd, TL_WRITE, &table)) - { - my_error(ER_EVENT_OPEN_TABLE_FAILED, MYF(0)); - goto err; - } DBUG_PRINT("info", ("check existance of an event with the same name")); if (!evex_db_find_event_aux(thd, et, table)) @@ -828,15 +865,11 @@ db_create_event(THD *thd, Event_timed *e ok: if (dbchanged) (void) mysql_change_db(thd, olddb, 1); - if (table) - close_thread_tables(thd); DBUG_RETURN(EVEX_OK); err: if (dbchanged) (void) mysql_change_db(thd, olddb, 1); - if (table) - close_thread_tables(thd); DBUG_RETURN(EVEX_GENERAL_ERROR); } @@ -849,6 +882,7 @@ err: thd THD sp_name the name of the event to alter et event's data + table Table to use NOTES sp_name is passed since this is the name of the event to @@ -856,10 +890,10 @@ err: */ static int -db_update_event(THD *thd, Event_timed *et, sp_name *new_name) +db_update_event(THD *thd, Event_timed *et, sp_name *new_name, TABLE *table) { - TABLE *table; int ret= EVEX_OPEN_TABLE_FAILED; + DBUG_ENTER("db_update_event"); DBUG_PRINT("enter", ("dbname: %.*s", et->dbname.length, et->dbname.str)); DBUG_PRINT("enter", ("name: %.*s", et->name.length, et->name.str)); @@ -868,12 +902,6 @@ db_update_event(THD *thd, Event_timed *e DBUG_PRINT("enter", ("rename to: %.*s", new_name->m_name.length, new_name->m_name.str)); - if (evex_open_event_table(thd, TL_WRITE, &table)) - { - my_error(ER_EVENT_OPEN_TABLE_FAILED, MYF(0)); - goto err; - } - /* first look whether we overwrite */ if (new_name) { @@ -881,14 +909,14 @@ db_update_event(THD *thd, Event_timed *e !sortcmp_lex_string(et->dbname, new_name->m_db, system_charset_info)) { my_error(ER_EVENT_SAME_NAME, MYF(0), et->name.str); - goto err; + DBUG_RETURN(EVEX_GENERAL_ERROR); } if (!evex_db_find_event_by_name(thd, new_name->m_db, new_name->m_name, et->definer, table)) { my_error(ER_EVENT_ALREADY_EXISTS, MYF(0), new_name->m_name.str); - goto err; + DBUG_RETURN(EVEX_GENERAL_ERROR); } } /* @@ -900,7 +928,7 @@ db_update_event(THD *thd, Event_timed *e if (EVEX_KEY_NOT_FOUND == evex_db_find_event_aux(thd, et, table)) { my_error(ER_EVENT_DOES_NOT_EXIST, MYF(0), et->name.str); - goto err; + DBUG_RETURN(EVEX_GENERAL_ERROR); } store_record(table,record[1]); @@ -910,7 +938,7 @@ db_update_event(THD *thd, Event_timed *e /* evex_fill_row() calls my_error() in case of error so no need to handle it here */ if ((ret= evex_fill_row(thd, table, et, true))) - goto err; + DBUG_RETURN(EVEX_GENERAL_ERROR); if (new_name) { @@ -923,17 +951,10 @@ db_update_event(THD *thd, Event_timed *e if ((ret= table->file->ha_update_row(table->record[1], table->record[0]))) { my_error(ER_EVENT_STORE_FAILED, MYF(0), et->name.str, ret); - goto err; + DBUG_RETURN(EVEX_GENERAL_ERROR); } - /* close mysql.event or we crash later when loading the event from disk */ - close_thread_tables(thd); DBUG_RETURN(0); - -err: - if (table) - close_thread_tables(thd); - DBUG_RETURN(EVEX_GENERAL_ERROR); } @@ -962,7 +983,7 @@ db_find_event(THD *thd, sp_name *name, L int ret; Event_timed *et=NULL; DBUG_ENTER("db_find_event"); - DBUG_PRINT("enter", ("name: %*s", name->m_name.length, name->m_name.str)); + DBUG_PRINT("enter", ("name: %.*s", name->m_name.length, name->m_name.str)); if (!root) root= &evex_mem_root; @@ -1036,7 +1057,7 @@ evex_load_and_compile_event(THD * thd, s Open_tables_state backup; DBUG_ENTER("db_load_and_compile_event"); - DBUG_PRINT("enter", ("name: %*s", spn->m_name.length, spn->m_name.str)); + DBUG_PRINT("enter", ("name: %.*s", spn->m_name.length, spn->m_name.str)); tmp_mem_root= thd->mem_root; thd->mem_root= &evex_mem_root; @@ -1152,6 +1173,78 @@ done: /* + Check params which we got from the parsing phase. + + SYNOPSIS + evex_check_timing_params() + thd THD + et event's data + + RETURNS + 0 OK + EVEX_BAD_PARAMS Error + + REMARKS + Issues error messages +*/ + +static +int evex_check_timing_params(THD *thd, Event_timed *et) +{ + const char *pos= NULL; + Item *bad_item; + + DBUG_ENTER("evex_check_timing_params"); + DBUG_PRINT("info", ("execute_at=0x%d expr=0x%d starts=0x%d ends=0x%d", + et->parse_items.execute_at, et->parse_items.interval_expr, + et->parse_items.starts, et->parse_items.ends)); + + if (et->parse_items.execute_at) + { + DBUG_PRINT("info", ("ONE TIME")); + if (et->init_execute_at(thd, et->parse_items.execute_at)) + { + pos= "AT"; + bad_item= et->parse_items.execute_at; + goto wrong_value; + } + } + else + { + DBUG_PRINT("info", ("RECURRING")); + if (et->parse_items.interval_expr && + et->init_interval(thd, et->parse_items.interval_expr, et->interval)) + { + pos= "INTERVAL"; + bad_item= et->parse_items.interval_expr; + goto wrong_value; + } + + if (et->parse_items.starts && et->init_starts(thd, et->parse_items.starts)) + { + pos= "STARTS"; + bad_item= et->parse_items.starts; + goto wrong_value; + } + + if (et->parse_items.ends && et->init_ends(thd, et->parse_items.ends)) + { + my_error(ER_EVENT_ENDS_BEFORE_STARTS, MYF(0)); + DBUG_RETURN(EVEX_BAD_PARAMS); + } + } + DBUG_RETURN(0); +wrong_value: + { + char buff[120]; + String str(buff,(uint32) sizeof(buff), system_charset_info); + String *str2= bad_item->fixed? bad_item->val_str(&str):NULL; + my_error(ER_WRONG_VALUE, MYF(0), pos, str2? str2->c_ptr():"NULL"); + DBUG_RETURN(EVEX_BAD_PARAMS); + } +} + +/* The function exported to the world for creating of events. SYNOPSIS @@ -1172,14 +1265,22 @@ evex_create_event(THD *thd, Event_timed uint *rows_affected) { int ret = 0; + TABLE *table; DBUG_ENTER("evex_create_event"); - DBUG_PRINT("enter", ("name: %*s options:%d", et->name.length, + DBUG_PRINT("enter", ("name: %.*s options:%d", et->name.length, et->name.str, create_options)); - if ((ret = db_create_event(thd, et, - create_options & HA_LEX_CREATE_IF_NOT_EXISTS, - rows_affected))) + if ((ret= evex_open_all_tables(thd, &table))) + DBUG_RETURN(ret); + + if ((ret= evex_check_timing_params(thd, et))) + goto done; + + ret = db_create_event(thd, et, create_options & HA_LEX_CREATE_IF_NOT_EXISTS, + rows_affected, table); + close_thread_tables(thd); + if (ret) goto done; VOID(pthread_mutex_lock(&LOCK_evex_running)); @@ -1217,17 +1318,23 @@ evex_update_event(THD *thd, Event_timed uint *rows_affected) { int ret; - bool need_second_pass= true; + TABLE *table; DBUG_ENTER("evex_update_event"); - DBUG_PRINT("enter", ("name: %*s", et->name.length, et->name.str)); + DBUG_PRINT("enter", ("name: %.*s", et->name.length, et->name.str)); + + if ((ret= evex_open_all_tables(thd, &table))) + DBUG_RETURN(ret); + + if ((ret= evex_check_timing_params(thd, et))) + goto done; /* db_update_event() opens & closes the table to prevent crash later in the code when loading and compiling the new definition. Also on error conditions my_error() is called so no need to handle here */ - if ((ret= db_update_event(thd, et, new_name))) + if ((ret= db_update_event(thd, et, new_name, table))) goto done; VOID(pthread_mutex_lock(&LOCK_evex_running)); @@ -1337,7 +1444,6 @@ evex_drop_event(THD *thd, Event_timed *e DBUG_ENTER("evex_drop_event"); - VOID(pthread_mutex_lock(&LOCK_evex_running)); if (evex_is_running) ret= evex_remove_from_cache(&et->dbname, &et->name, true, true); @@ -1376,7 +1482,7 @@ evex_show_create_event(THD *thd, sp_name Open_tables_state backup; DBUG_ENTER("evex_update_event"); - DBUG_PRINT("enter", ("name: %*s", spn->m_name.length, spn->m_name.str)); + DBUG_PRINT("enter", ("name: %.*s", spn->m_name.length, spn->m_name.str)); thd->reset_n_backup_open_tables_state(&backup); ret= db_find_event(thd, spn, &definer, &et, NULL, thd->mem_root); --- 1.26/sql/event.h 2006-02-28 18:33:25 +01:00 +++ 1.27/sql/event.h 2006-03-07 12:02:07 +01:00 @@ -17,6 +17,16 @@ #ifndef _EVENT_H_ #define _EVENT_H_ +#ifndef DBUG_OFF +#define DBUG_PRINT_TIME() { \ + ulonglong __t= my_getsystime();\ + DBUG_PRINT("info", ("tv.sec=%llu tv.usec=%llu %s::%d", \ + __t/10000000, (__t%10000000)/10, __FILE__, __LINE__)); \ +} +#else +#define DBUG_PRINT_TIME +#endif + #include "sp.h" #include "sp_head.h" @@ -87,8 +97,17 @@ class Event_timed bool status_changed; bool last_executed_changed; + public: + struct parse_items + { + Item *execute_at; + Item *interval_expr; + Item *starts; + Item *ends; + } parse_items; + TIME last_executed; LEX_STRING dbname; @@ -124,7 +143,8 @@ public: Event_timed():in_spawned_thread(0),locked_by_thread_id(0), running(0), status_changed(false), - last_executed_changed(false), expression(0), created(0), + last_executed_changed(false), + expression(0), created(0), modified(0), on_completion(MYSQL_EVENT_ON_COMPLETION_DROP), status(MYSQL_EVENT_ENABLED), sphead(0), sql_mode(0), body_begin(0), dropped(false), @@ -152,6 +172,9 @@ public: int init_execute_at(THD *thd, Item *expr); + static int + is_valid_interval_type(interval_type new_interval); + int init_interval(THD *thd, Item *expr, interval_type new_interval); --- 1.41/sql/event_executor.cc 2006-03-01 04:21:57 +01:00 +++ 1.42/sql/event_executor.cc 2006-03-07 12:02:07 +01:00 @@ -206,6 +206,8 @@ init_events() { #ifndef DBUG_FAULTY_THR /* TODO Andrey: Change the error code returned! */ + DBUG_PRINT_TIME(); + DBUG_PRINT("info", ("fork main thread")); if (pthread_create(&th, &connection_attrib, event_executor_main,(void*)NULL)) DBUG_RETURN(ER_SLAVE_THREAD); #else @@ -320,6 +322,7 @@ executor_wait_till_next_event_exec(THD * int t2sleep; DBUG_ENTER("executor_wait_till_next_event_exec"); + DBUG_PRINT_TIME(); /* now let's see how much time to sleep, we know there is at least 1 element in the queue. @@ -352,6 +355,8 @@ executor_wait_till_next_event_exec(THD * VOID(pthread_mutex_unlock(&LOCK_event_arrays)); DBUG_PRINT("evex main thread",("unlocked LOCK_event_arrays")); + /* count the time in 0.05s */ + t2sleep*=20; if (t2sleep > 0) { ulonglong modified= et->modified; @@ -365,8 +370,13 @@ executor_wait_till_next_event_exec(THD * evex_queue_first_element(&EVEX_EQ_NAME, Event_timed*)->modified == modified)) { - DBUG_PRINT("evex main thread",("will sleep a bit more.")); - my_sleep(1000000); + if (!(t2sleep % 10)) + { + /* Don't sleep too much. We should catch changes quickly. */ + DBUG_PRINT("evex main thread",("will sleep a bit more.")); + DBUG_PRINT_TIME(); + } + my_sleep(50000); } DBUG_PRINT("info",("saved_modified=%llu current=%llu", modified, evex_queue_num_elements(EVEX_EQ_NAME)? @@ -382,6 +392,7 @@ executor_wait_till_next_event_exec(THD * if (thd->killed && event_executor_running_global_var) ret= WAIT_STATUS_STOP_EXECUTOR; + DBUG_PRINT("info", ("retcode=%d", ret)); DBUG_RETURN(ret); } @@ -407,6 +418,7 @@ event_executor_main(void *arg) THD *thd; /* needs to be first for thread_stack */ uint i=0, j=0; my_ulonglong cnt= 0; + int ret; DBUG_ENTER("event_executor_main"); DBUG_PRINT("event_executor_main", ("EVEX thread started")); @@ -464,7 +476,8 @@ event_executor_main(void *arg) evex_is_running= true; VOID(pthread_mutex_unlock(&LOCK_evex_running)); - thd->security_ctx->user= my_strdup("event_scheduler", MYF(0)); + thd->security_ctx->user= thd->security_ctx->priv_user= + my_strdup("event_scheduler", MYF(0)); if (evex_load_events_from_db(thd)) goto finish; @@ -489,7 +502,7 @@ event_executor_main(void *arg) if (!evex_queue_num_elements(EVEX_EQ_NAME)) { - my_sleep(1000000);// sleep 1s + my_sleep(100000);// sleep 0.1s continue; } @@ -552,7 +565,12 @@ restart_ticking: DBUG_PRINT("evex main thread", ("[%10s] next exec at [%llu]", et->name.str, TIME_to_ulonglong_datetime(&et->execute_at))); - et->update_fields(thd); + DBUG_PRINT_TIME(); + if ((ret= et->update_fields(thd))) + { + sql_print_error("SCHEDULER: Error while update_fields. Code=%d", ret); + UNLOCK_MUTEX_AND_BAIL_OUT(LOCK_event_arrays, finish); + } #ifndef DBUG_FAULTY_THR thread_safe_increment(workers_count, &LOCK_workers_count); switch ((fork_ret_code= et->spawn_now(event_executor_worker))) { @@ -590,7 +608,8 @@ restart_ticking: VOID(pthread_mutex_unlock(&LOCK_event_arrays)); }/* while */ finish: - + DBUG_PRINT("evex main thread", ("After main loop. Stopping.")); + DBUG_PRINT_TIME(); /* First manifest that this thread does not work and then destroy */ VOID(pthread_mutex_lock(&LOCK_evex_running)); evex_is_running= false; @@ -616,12 +635,14 @@ finish: } VOID(pthread_mutex_unlock(&LOCK_workers_count)); my_sleep(1000000);// 1s + DBUG_PRINT_TIME(); } /* First we free all objects ... Lock because a DROP DATABASE could be running in parallel and it locks on these */ + DBUG_PRINT_TIME(); sql_print_information("SCHEDULER: Emptying the queue."); VOID(pthread_mutex_lock(&LOCK_event_arrays)); for (i= 0; i < evex_queue_num_elements(EVEX_EQ_NAME); ++i) @@ -633,6 +654,7 @@ finish: VOID(pthread_mutex_unlock(&LOCK_event_arrays)); /* ... then we can thrash the whole queue at once */ evex_queue_destroy(&EVEX_EQ_NAME); + DBUG_PRINT_TIME(); thd->proc_info = "Clearing"; DBUG_ASSERT(thd->net.buff != 0); @@ -655,6 +677,7 @@ err_no_thd: VOID(pthread_mutex_unlock(&LOCK_evex_running)); free_root(&evex_mem_root, MYF(0)); + DBUG_PRINT_TIME(); sql_print_information("SCHEDULER: Stopped."); #ifndef DBUG_FAULTY_THR @@ -682,6 +705,7 @@ event_executor_worker(void *event_void) MEM_ROOT worker_mem_root; DBUG_ENTER("event_executor_worker"); + DBUG_PRINT_TIME(); init_alloc_root(&worker_mem_root, MEM_ROOT_BLOCK_SIZE, MEM_ROOT_PREALLOC); @@ -722,7 +746,9 @@ event_executor_worker(void *event_void) event->dbname.str, event->name.str, event->definer.str, (int) event->expression); + DBUG_PRINT_TIME(); ret= event->execute(thd, &worker_mem_root); + DBUG_PRINT_TIME(); evex_print_warnings(thd, event); sql_print_information("SCHEDULER: Executed event %s.%s of %s [EXPR:%d]. " @@ -801,6 +827,7 @@ evex_load_events_from_db(THD *thd) uint count= 0; DBUG_ENTER("evex_load_events_from_db"); + DBUG_PRINT_TIME(); if ((ret= evex_open_event_table(thd, TL_READ, &table))) { @@ -862,7 +889,7 @@ evex_load_events_from_db(THD *thd) DBUG_PRINT("evex_load_events_from_db", ("Adding to the exec list.")); evex_queue_insert(&EVEX_EQ_NAME, (EVEX_PTOQEL) et); - DBUG_PRINT("evex_load_events_from_db", ("%p %*s", + DBUG_PRINT("evex_load_events_from_db", ("%p %.*s", et, et->name.length,et->name.str)); count++; } @@ -904,6 +931,7 @@ sys_var_event_executor::update(THD *thd, { /* here start the thread if not running. */ DBUG_ENTER("sys_var_event_executor::update"); + DBUG_PRINT_TIME(); VOID(pthread_mutex_lock(&LOCK_evex_running)); *value= var->save_result.ulong_value; @@ -979,7 +1007,7 @@ evex_print_warnings(THD *thd, Event_time err_msg.append(err->msg, strlen(err->msg), system_charset_info); err_msg.append("]"); DBUG_ASSERT(err->level < 3); - (sql_print_xxx_handlers[err->level])("%*s", err_msg.length(), err_msg.c_ptr()); + (sql_print_xxx_handlers[err->level])("%.*s", err_msg.length(), err_msg.c_ptr()); } --- 1.45/sql/event_timed.cc 2006-03-02 21:01:56 +01:00 +++ 1.46/sql/event_timed.cc 2006-03-07 12:02:07 +01:00 @@ -47,6 +47,8 @@ Event_timed::init() definer_user.length= definer_host.length= 0; sql_mode= 0; + parse_items.execute_at= parse_items.interval_expr= parse_items.starts= + parse_items.ends= NULL; DBUG_VOID_RETURN; } @@ -155,7 +157,10 @@ Event_timed::init_execute_at(THD *thd, I DBUG_ENTER("Event_timed::init_execute_at"); if (expr->fix_fields(thd, &expr)) - DBUG_RETURN(EVEX_PARSE_ERROR); + { + DBUG_PRINT("error", ("fix_fields failed")); + DBUG_RETURN(EVEX_BAD_PARAMS); + } /* no starts and/or ends in case of execute_at */ DBUG_PRINT("info", ("starts_null && ends_null should be 1 is %d", @@ -167,11 +172,14 @@ Event_timed::init_execute_at(THD *thd, I (my_time_t) thd->query_start()); if ((not_used= expr->get_date(<ime, TIME_NO_ZERO_DATE))) - DBUG_RETURN(ER_WRONG_VALUE); + DBUG_RETURN(EVEX_BAD_PARAMS); if (TIME_to_ulonglong_datetime(<ime) < TIME_to_ulonglong_datetime(&time_tmp)) + { + my_error(ER_EVENT_EXEC_TIME_IN_THE_PAST, MYF(0)); DBUG_RETURN(EVEX_BAD_PARAMS); + } /* @@ -187,7 +195,55 @@ Event_timed::init_execute_at(THD *thd, I /* - Set time for execution for transient events. + Checks whether we support the interval type + + SYNOPSIS + Event_timed::is_valid_interval_type() + new_interval what is the interval + + RETURNS + 0 OK + EVEX_MICROSECOND_UNSUP Microseconds are not supported. +*/ + +int +Event_timed::is_valid_interval_type(interval_type new_interval) +{ + DBUG_ENTER("Event_timed::is_valid_interval_type"); + switch (new_interval) { + case INTERVAL_MICROSECOND: + case INTERVAL_SECOND_MICROSECOND: + case INTERVAL_MINUTE_MICROSECOND: + case INTERVAL_HOUR_MICROSECOND: + case INTERVAL_DAY_MICROSECOND: + DBUG_RETURN(EVEX_MICROSECOND_UNSUP); +#ifndef DBUG_OFF + case INTERVAL_YEAR: + case INTERVAL_QUARTER: + case INTERVAL_MONTH: + case INTERVAL_WEEK: + case INTERVAL_DAY: + case INTERVAL_HOUR: + case INTERVAL_MINUTE: + case INTERVAL_SECOND: + case INTERVAL_YEAR_MONTH: + case INTERVAL_DAY_HOUR: + case INTERVAL_DAY_MINUTE: + case INTERVAL_HOUR_MINUTE: + case INTERVAL_HOUR_SECOND: + case INTERVAL_MINUTE_SECOND: + case INTERVAL_DAY_SECOND: + DBUG_RETURN(0); + default: + DBUG_PRINT("error", ("type %d is unknown", (int) new_interval)); + DBUG_ASSERT(0); +#endif + } +} + + +/* + Set time for execution for reccuring events. SYNOPSIS Event_timed::init_interval() @@ -210,11 +266,14 @@ Event_timed::init_interval(THD *thd, Ite DBUG_ENTER("Event_timed::init_interval"); if (expr->fix_fields(thd, &expr)) - DBUG_RETURN(EVEX_PARSE_ERROR); + DBUG_RETURN(EVEX_BAD_PARAMS); value.alloc(MAX_DATETIME_FULL_WIDTH*MY_CHARSET_BIN_MB_MAXLEN); if (get_interval_value(expr, new_interval, &value, &interval)) - DBUG_RETURN(EVEX_PARSE_ERROR); + { + DBUG_PRINT("error", ("fix_fields failed")); + DBUG_RETURN(EVEX_BAD_PARAMS); + } expression= 0; @@ -250,17 +309,20 @@ Event_timed::init_interval(THD *thd, Ite break; case INTERVAL_HOUR_SECOND: /* day is anyway 0 */ case INTERVAL_DAY_SECOND: - /* DAY_SECOND having problems because of leap seconds? */ + /* QQ: DAY_SECOND having problems because of leap seconds? */ expression= ((interval.day* 24 + interval.hour) * 60 + interval.minute)*60 + interval.second; break; case INTERVAL_MINUTE_MICROSECOND: /* day and hour are 0 */ case INTERVAL_HOUR_MICROSECOND: /* day is anyway 0 */ case INTERVAL_DAY_MICROSECOND: - DBUG_RETURN(EVEX_MICROSECOND_UNSUP); + my_error(ER_NOT_SUPPORTED_YET, MYF(0), "MICROSECOND"); + DBUG_RETURN(EVEX_MICROSECOND_UNSUP); +#ifdef WE_SUPPORT_MICROSEC expression= ((((interval.day*24) + interval.hour)*60+interval.minute)*60 + interval.second) * 1000000L + interval.second_part; break; +#endif case INTERVAL_HOUR_MINUTE: expression= interval.hour * 60 + interval.minute; break; @@ -268,13 +330,17 @@ Event_timed::init_interval(THD *thd, Ite expression= interval.minute * 60 + interval.second; break; case INTERVAL_SECOND_MICROSECOND: - DBUG_RETURN(EVEX_MICROSECOND_UNSUP); + my_error(ER_NOT_SUPPORTED_YET, MYF(0), "MICROSECOND"); + DBUG_RETURN(EVEX_MICROSECOND_UNSUP); +#ifdef WE_SUPPORT_MICROSEC expression= interval.second * 1000000L + interval.second_part; break; +#endif case INTERVAL_MICROSECOND: + my_error(ER_NOT_SUPPORTED_YET, MYF(0), "MICROSECOND"); DBUG_RETURN(EVEX_MICROSECOND_UNSUP); } - if (interval.neg || expression > EVEX_MAX_INTERVAL_VALUE) + if (interval.neg || expression > EVEX_MAX_INTERVAL_VALUE || !expression) DBUG_RETURN(EVEX_BAD_PARAMS); this->interval= new_interval; @@ -307,25 +373,21 @@ int Event_timed::init_starts(THD *thd, Item *new_starts) { my_bool not_used; - TIME ltime, time_tmp; + TIME ltime, ltime_now; DBUG_ENTER("Event_timed::init_starts"); if (new_starts->fix_fields(thd, &new_starts)) - DBUG_RETURN(EVEX_PARSE_ERROR); + { + DBUG_PRINT("error", ("fix_fields failed")); + DBUG_RETURN(EVEX_BAD_PARAMS); + } if ((not_used= new_starts->get_date(<ime, TIME_NO_ZERO_DATE))) DBUG_RETURN(EVEX_BAD_PARAMS); /* Let's check whether time is in the past */ - thd->variables.time_zone->gmt_sec_to_TIME(&time_tmp, - (my_time_t) thd->query_start()); - - DBUG_PRINT("info",("now =%lld", TIME_to_ulonglong_datetime(&time_tmp))); - DBUG_PRINT("info",("starts=%lld", TIME_to_ulonglong_datetime(<ime))); - if (TIME_to_ulonglong_datetime(<ime) < - TIME_to_ulonglong_datetime(&time_tmp)) - DBUG_RETURN(EVEX_BAD_PARAMS); + my_tz_UTC->gmt_sec_to_TIME(<ime_now, (my_time_t) (thd->query_start())); /* This may result in a 1970-01-01 date if ltime is > 2037-xx-xx @@ -333,6 +395,12 @@ Event_timed::init_starts(THD *thd, Item */ my_tz_UTC->gmt_sec_to_TIME(<ime, TIME_to_timestamp(thd, <ime, ¬_used)); + DBUG_PRINT("info",("NOW =%lld", TIME_to_ulonglong_datetime(<ime_now))); + DBUG_PRINT("info",("STARTS=%lld", TIME_to_ulonglong_datetime(<ime))); + if (TIME_to_ulonglong_datetime(<ime) < + TIME_to_ulonglong_datetime(<ime_now)) + DBUG_RETURN(EVEX_BAD_PARAMS); + starts= ltime; starts_null= FALSE; DBUG_RETURN(0); @@ -370,22 +438,34 @@ Event_timed::init_ends(THD *thd, Item *n DBUG_ENTER("Event_timed::init_ends"); if (new_ends->fix_fields(thd, &new_ends)) + { + DBUG_PRINT("error", ("fix_fields failed")); DBUG_RETURN(EVEX_PARSE_ERROR); + } DBUG_PRINT("info", ("convert to TIME")); - if ((not_used= new_ends->get_date(<ime, TIME_NO_ZERO_DATE))) + if ((not_used= new_ends->get_date(<ime, TIME_NO_ZERO_DATE)) && 0) + { + DBUG_PRINT("error", ("get_date returned error")); DBUG_RETURN(EVEX_BAD_PARAMS); + } + + DBUG_PRINT("info", ("ENDS =%lld", TIME_to_ulonglong_datetime(<ime))); + + DBUG_PRINT("info", ("get the UTC time")); + my_tz_UTC->gmt_sec_to_TIME(<ime, TIME_to_timestamp(thd, <ime, ¬_used)); + + DBUG_PRINT("info", ("STARTS=%lld", TIME_to_ulonglong_datetime(&starts))); + DBUG_PRINT("info", ("ENDS =%lld", TIME_to_ulonglong_datetime(<ime))); /* This may result in a 1970-01-01 date if ltime is > 2037-xx-xx ? CONVERT_TZ has similar problem ? */ - DBUG_PRINT("info", ("get the UTC time")); - my_tz_UTC->gmt_sec_to_TIME(<ime, TIME_to_timestamp(thd, <ime, ¬_used)); /* Check whether ends is after starts */ DBUG_PRINT("info", ("ENDS after STARTS?")); - if (!starts_null && my_time_compare(&starts, <ime) != -1) + if (!starts_null && my_time_compare(&starts, <ime) == 1) DBUG_RETURN(EVEX_BAD_PARAMS); /* @@ -393,8 +473,9 @@ Event_timed::init_ends(THD *thd, Item *n set before NOW() and in this case the following check should be done. Check whether ENDS is not in the past. */ + my_tz_UTC->gmt_sec_to_TIME(<ime_now, (my_time_t) thd->query_start()); DBUG_PRINT("info", ("ENDS after NOW?")); - my_tz_UTC->gmt_sec_to_TIME(<ime_now, thd->query_start()); + DBUG_PRINT("info", ("NOW =%lld", TIME_to_ulonglong_datetime(<ime_now))); if (my_time_compare(<ime_now, <ime) == 1) DBUG_RETURN(EVEX_BAD_PARAMS); @@ -979,18 +1060,18 @@ bool Event_timed::update_fields(THD *thd) { TABLE *table; - Open_tables_state backup; +// Open_tables_state backup; int ret= 0; DBUG_ENTER("Event_timed::update_time_fields"); - DBUG_PRINT("enter", ("name: %*s", name.length, name.str)); + DBUG_PRINT("enter", ("name: %.*s", name.length, name.str)); /* No need to update if nothing has changed */ if (!(status_changed || last_executed_changed)) goto done; - thd->reset_n_backup_open_tables_state(&backup); +// thd->reset_n_backup_open_tables_state(&backup); if (evex_open_event_table(thd, TL_WRITE, &table)) { @@ -1024,8 +1105,9 @@ Event_timed::update_fields(THD *thd) ret= EVEX_WRITE_ROW_FAILED; done: - close_thread_tables(thd); - thd->restore_backup_open_tables_state(&backup); + if (table) + close_thread_tables(thd); +// thd->restore_backup_open_tables_state(&backup); DBUG_RETURN(ret); } --- 1.210/sql/sp_head.cc 2006-03-01 16:27:46 +01:00 +++ 1.211/sql/sp_head.cc 2006-03-07 12:02:07 +01:00 @@ -2308,12 +2308,13 @@ sp_instr_stmt::execute(THD *thd, uint *n if (!(res= alloc_query(thd, m_query.str, m_query.length+1)) && !(res=subst_spvars(thd, this, &m_query))) { + if (unlikely((thd->options & OPTION_LOG_OFF)==0)) + general_log_print(thd, COM_QUERY, "%*s", thd->query_length, thd->query); + /* (the order of query cache and subst_spvars calls is irrelevant because queries with SP vars can't be cached) */ - if (unlikely((thd->options & OPTION_LOG_OFF)==0)) - general_log_print(thd, COM_QUERY, "%s", thd->query); if (query_cache_send_result_to_client(thd, thd->query, thd->query_length) <= 0) --- 1.164/sql/sql_prepare.cc 2006-02-25 19:35:09 +01:00 +++ 1.165/sql/sql_prepare.cc 2006-03-07 12:02:09 +01:00 @@ -175,6 +175,7 @@ inline bool is_param_null(const uchar *p static Prepared_statement * find_prepared_statement(THD *thd, ulong id, const char *where) { + DBUG_ENTER("find_prepared_statement"); /* To strictly separate namespaces of SQL prepared statements and C API prepared statements find() will return 0 if there is a named prepared @@ -187,9 +188,10 @@ find_prepared_statement(THD *thd, ulong char llbuf[22]; my_error(ER_UNKNOWN_STMT_HANDLER, MYF(0), sizeof(llbuf), llstr(id, llbuf), where); - return 0; + DBUG_RETURN(0); } - return (Prepared_statement *) stmt; + DBUG_PRINT("info", ("stmt=0x%lx", stmt)); + DBUG_RETURN((Prepared_statement *) stmt); } @@ -1873,7 +1875,10 @@ void mysql_stmt_prepare(THD *thd, const thd->stmt_map.erase(stmt); } else + { + DBUG_PRINT("info", ("prepared stmt_id [%lu]", stmt->id)); general_log_print(thd, COM_STMT_PREPARE, "[%lu] %s", stmt->id, packet); + } /* check_prepared_statemnt sends the metadata packet in case of success */ DBUG_VOID_RETURN; @@ -2040,7 +2045,10 @@ void mysql_sql_stmt_prepare(THD *thd) thd->stmt_map.erase(stmt); } else + { + DBUG_PRINT("info", ("Statement prepared")); send_ok(thd, 0L, 0L, "Statement prepared"); + } DBUG_VOID_RETURN; } @@ -2202,7 +2210,7 @@ void mysql_stmt_execute(THD *thd, char * Prepared_statement *stmt; bool error; DBUG_ENTER("mysql_stmt_execute"); - + DBUG_PRINT("enter",("stmt_id=[%lu]", stmt_id)); packet+= 9; /* stmt_id + 5 bytes of flags */ /* First of all clear possible warnings from the previous command */ @@ -2332,6 +2340,7 @@ void mysql_stmt_fetch(THD *thd, char *pa Statement stmt_backup; Server_side_cursor *cursor; DBUG_ENTER("mysql_stmt_fetch"); + DBUG_PRINT("enter", ("stmt_id=[%lu]", stmt_id)); /* First of all clear possible warnings from the previous command */ mysql_reset_thd_for_next_command(thd); @@ -2395,6 +2404,7 @@ void mysql_stmt_reset(THD *thd, char *pa ulong stmt_id= uint4korr(packet); Prepared_statement *stmt; DBUG_ENTER("mysql_stmt_reset"); + DBUG_PRINT("enter", ("stmt_id=[%lu]", stmt_id)); /* First of all clear possible warnings from the previous command */ mysql_reset_thd_for_next_command(thd); @@ -2430,6 +2440,7 @@ void mysql_stmt_close(THD *thd, char *pa ulong stmt_id= uint4korr(packet); Prepared_statement *stmt; DBUG_ENTER("mysql_stmt_close"); + DBUG_PRINT("enter", ("stmt_id=[%lu]", stmt_id)); if (!(stmt= find_prepared_statement(thd, stmt_id, "mysql_stmt_close"))) DBUG_VOID_RETURN; @@ -2512,6 +2523,7 @@ void mysql_stmt_get_longdata(THD *thd, c #endif stmt_id= uint4korr(packet); + DBUG_PRINT("enter", ("stmt_id=[%lu]", stmt_id)); packet+= 4; if (!(stmt=find_prepared_statement(thd, stmt_id, @@ -2732,6 +2744,7 @@ bool Prepared_statement::prepare(const c Statement stmt_backup; Query_arena *old_stmt_arena; DBUG_ENTER("Prepared_statement::prepare"); + DBUG_PRINT("enter", ("stmt=0x%lx", this)); /* If this is an SQLCOM_PREPARE, we also increase Com_prepare_sql. However, it seems handy if com_stmt_prepare is increased always, @@ -2837,6 +2850,8 @@ bool Prepared_statement::execute(String Query_arena *old_stmt_arena; Item *old_free_list; bool error= TRUE; + DBUG_ENTER("Prepared_statement::execute"); + DBUG_PRINT("enter", ("stmt=0x%lx", this)); statistic_increment(thd->status_var.com_stmt_execute, &LOCK_status); @@ -2844,12 +2859,12 @@ bool Prepared_statement::execute(String if (state == Query_arena::ERROR) { my_message(last_errno, last_error, MYF(0)); - return TRUE; + DBUG_RETURN(true); } if (flags & (uint) IS_IN_USE) { my_error(ER_PS_NO_RECURSION, MYF(0)); - return TRUE; + DBUG_RETURN(true); } /* @@ -2866,7 +2881,7 @@ bool Prepared_statement::execute(String { DBUG_PRINT("info",("Cursor asked for not SELECT stmt")); my_error(ER_SP_BAD_CURSOR_QUERY, MYF(0)); - return TRUE; + DBUG_RETURN(true); } /* In case the command has a call to SP which re-uses this statement name */ @@ -2931,7 +2946,7 @@ bool Prepared_statement::execute(String error: flags&= ~ (uint) IS_IN_USE; - return error; + DBUG_RETURN(error); } @@ -2939,14 +2954,16 @@ error: bool Prepared_statement::deallocate() { + DBUG_ENTER("Prepared_statement::deallocate"); + DBUG_PRINT("enter", ("stmt=0x%lx", this)); /* We account deallocate in the same manner as mysql_stmt_close */ statistic_increment(thd->status_var.com_stmt_close, &LOCK_status); if (flags & (uint) IS_IN_USE) { my_error(ER_PS_NO_RECURSION, MYF(0)); - return TRUE; + DBUG_RETURN(true); } /* Statement map calls delete stmt on erase */ thd->stmt_map.erase(this); - return FALSE; + DBUG_RETURN(false); }
| Thread | ||
|---|---|---|
| • bk commit into 5.1 tree (andrey:1.2214) BUG#16394 | ahristov | 7 Mar |
© 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Page generated in 1.475 sec. using MySQL 5.1.14-beta-log