| List: | Commits | « Previous MessageNext Message » | |
| From: | ahristov | Date: | March 24 2006 3:44pm |
| Subject: | bk commit into 5.1 tree (andrey:1.2219) BUG#16992 | ||
| 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.2219 06/03/24 15:44:28 andrey@lmy004. +12 -0 fix for bug#16992 Events: information_schema troubles WL#1034 EVENT is no more needed, as SHOW PRIVILEGES, to see own events. To see all events one needs SELECT on mysql.event. SHOW EVENTS always shows events from one database. FULL has no effect SELECT I_S gets from all schemas. If user is missing EVENT on a schema but still has events there he will be able to see them but they won't be executed because he misses EVENT on this schema. They will start executing again if she gets again granted EVENT on this schema. mysql-test/t/events_i_s.test 1.1 06/03/24 15:44:19 andrey@lmy004. +114 -0 mysql-test/t/events_i_s.test 1.0 06/03/24 15:44:19 andrey@lmy004. +0 -0 BitKeeper file /work/mysql-5.1-bug16992-new/mysql-test/t/events_i_s.test mysql-test/r/events_i_s.result 1.1 06/03/24 15:44:18 andrey@lmy004. +125 -0 sql/table.cc 1.218 06/03/24 15:44:18 andrey@lmy004. +12 -11 fix error messages sql/sql_show.cc 1.322 06/03/24 15:44:18 andrey@lmy004. +71 -22 - last_executed is marked as maybe_null therefore call set_notnull() beforehand. - use the newly added second key(db) to speedup lookup on SHOW EVENTS when the user has no SELECT_ACL on mysql.event sql/sql_parse.cc 1.535 06/03/24 15:44:18 andrey@lmy004. +5 -6 no more EVENT_ACL needed for SHOW EVENT/SELECT FROM I_S.EVENTS sql/event_executor.cc 1.42 06/03/24 15:44:18 andrey@lmy004. +2 -0 don't forget to set the value of the global var sql/event.cc 1.38 06/03/24 15:44:18 andrey@lmy004. +13 -3 increase visibility and fix a mem leak scripts/mysql_fix_privilege_tables.sql 1.44 06/03/24 15:44:18 andrey@lmy004. +1 -0 add new key for show events/drop database mysql-test/r/events_i_s.result 1.0 06/03/24 15:44:18 andrey@lmy004. +0 -0 BitKeeper file /work/mysql-5.1-bug16992-new/mysql-test/r/events_i_s.result scripts/mysql_create_system_tables.sh 1.39 06/03/24 15:44:17 andrey@lmy004. +2 -1 add a new key for show events and drop database mysql-test/t/events.test 1.25 06/03/24 15:44:17 andrey@lmy004. +0 -73 move I_S tests to separate test file mysql-test/r/events.result 1.29 06/03/24 15:44:17 andrey@lmy004. +3 -84 update results mysql-test/lib/init_db.sql 1.26 06/03/24 15:44:17 andrey@lmy004. +2 -1 add a key on `db`. used for SHOW EVENTS, can be used during DROP DATABASE to speedup the dropping of events. # 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-bug16992-new --- 1.534/sql/sql_parse.cc 2006-03-21 13:10:09 +01:00 +++ 1.535/sql/sql_parse.cc 2006-03-24 15:44:18 +01:00 @@ -2467,15 +2467,11 @@ mysql_execute_command(THD *thd) if (all_tables) { if (lex->orig_sql_command != SQLCOM_SHOW_STATUS_PROC && - lex->orig_sql_command != SQLCOM_SHOW_STATUS_FUNC && - lex->orig_sql_command != SQLCOM_SHOW_EVENTS) + lex->orig_sql_command != SQLCOM_SHOW_STATUS_FUNC) res= check_table_access(thd, lex->exchange ? SELECT_ACL | FILE_ACL : SELECT_ACL, all_tables, 0); - else if (lex->orig_sql_command == SQLCOM_SHOW_EVENTS) - res= check_access(thd, EVENT_ACL, thd->lex->select_lex.db, 0, 0, 0, - is_schema_db(thd->lex->select_lex.db)); } else res= check_access(thd, @@ -3800,9 +3796,12 @@ end_with_restore_list: break; } - if (check_access(thd, EVENT_ACL, lex->et->dbname.str, 0, 0, 0, + if (check_access(thd, EVENT_ACL, lex->et->dbname.str, 0, 0, 1, is_schema_db(lex->et->dbname.str))) + { + my_error(ER_SPECIFIC_ACCESS_DENIED_ERROR, MYF(0), "EVENT"); break; + } if (end_active_trans(thd)) { --- 1.321/sql/sql_show.cc 2006-03-21 13:10:10 +01:00 +++ 1.322/sql/sql_show.cc 2006-03-24 15:44:18 +01:00 @@ -4050,9 +4050,10 @@ fill_events_copy_to_schema_table(THD *th sch_table->field[15]->store_time(&time, MYSQL_TIMESTAMP_DATETIME); if (et.last_executed.year) + { + sch_table->field[16]->set_notnull(); sch_table->field[16]->store_time(&et.last_executed,MYSQL_TIMESTAMP_DATETIME); - else - sch_table->field[16]->set_null(); + } sch_table->field[17]->store(et.comment.str, et.comment.length, scs); @@ -4079,10 +4080,11 @@ int fill_schema_events(THD *thd, TABLE_L DBUG_ENTER("fill_schema_events"); - strxmov(definer, thd->security_ctx->priv_user,"@",thd->security_ctx->priv_host, - NullS); + strxmov(definer, thd->security_ctx->priv_user,"@", + thd->security_ctx->priv_host, NullS); - DBUG_PRINT("info",("db=%s current_user=%s", thd->lex->select_lex.db, definer)); + DBUG_PRINT("info",("db=%s current_user=%s", thd->lex->select_lex.db ? + thd->lex->select_lex.db:"(null)", definer)); thd->reset_n_backup_open_tables_state(&backup); @@ -4092,33 +4094,75 @@ int fill_schema_events(THD *thd, TABLE_L ret= 1; goto err; } - - event_table->file->ha_index_init(0, 1); - /* - see others' events only if you have PROCESS_ACL !! - thd->lex->verbose is set either if SHOW FULL EVENTS or - in case of SELECT FROM I_S.EVENTS - */ - verbose= (thd->lex->verbose - && (thd->security_ctx->master_access & PROCESS_ACL)); - - if (verbose && thd->security_ctx->user) - { - ret= event_table->file->index_first(event_table->record[0]); - use_prefix_scanning= false; + { + TABLE_LIST tmp_table; + bzero((char*) &tmp_table, sizeof(tmp_table)); + tmp_table.db= (char*) "mysql"; + tmp_table.table_name= tmp_table.alias= (char*) "event"; + verbose= !check_table_access(thd, SELECT_ACL, &tmp_table, 1); + } + + if (verbose) + { + /* + 1. SELECT I_S => use PK(0) + Reasoning: + 2. SHOW EVENTS => second index(1) (db) + Reasoning: Events are per schema, therefore a scan over an index + will save use from doing a table scan and comparing + every single row's `db` with the schema which we show. + Name is used. Because here we have verbose we have to + see all events of every user in the schema. If not + a verbose mode then db+definer is used but for PK. + + */ + if (thd->lex->orig_sql_command == SQLCOM_SHOW_EVENTS) + { + DBUG_PRINT("info",("Using prefix scanning on secondary KEY")); + event_table->file->ha_index_init(1, 1); + DBUG_ASSERT(thd->lex->select_lex.db); + DBUG_PRINT("info",("Using `%s` in the prefix", thd->lex->select_lex.db)); + event_table->field[EVEX_FIELD_DB]-> + store(thd->lex->select_lex.db, strlen(thd->lex->select_lex.db), scs); + key_len= event_table->key_info[1].key_part[0].store_length; + + if (!(key_buf= (byte *)alloc_root(thd->mem_root, key_len))) + { + ret= 1; + goto err; + } + + key_copy(key_buf, event_table->record[0], event_table->key_info + 1, + key_len); + ret= event_table->file->index_read(event_table->record[0], key_buf, + key_len, HA_READ_PREFIX); + } + else + { + DBUG_PRINT("info",("Not using prefix scanning but using PK.")); + event_table->file->ha_index_init(0, 1); + ret= event_table->file->index_first(event_table->record[0]); + use_prefix_scanning= FALSE; + } } else { - event_table->field[EVEX_FIELD_DEFINER]->store(definer, strlen(definer), scs); + DBUG_PRINT("info",("Using prefix scanning on PK")); + + event_table->file->ha_index_init(0, 1); + event_table->field[EVEX_FIELD_DEFINER]->store(definer, strlen(definer), scs); key_len= event_table->key_info->key_part[0].store_length; - if (thd->lex->select_lex.db) + if (thd->lex->orig_sql_command == SQLCOM_SHOW_EVENTS) { + DBUG_PRINT("info",("Using also `%s` in the prefix scanning", + thd->lex->select_lex.db)); event_table->field[EVEX_FIELD_DB]-> store(thd->lex->select_lex.db, strlen(thd->lex->select_lex.db), scs); key_len+= event_table->key_info->key_part[1].store_length; } + if (!(key_buf= (byte *)alloc_root(thd->mem_root, key_len))) { ret= 1; @@ -4135,6 +4179,7 @@ int fill_schema_events(THD *thd, TABLE_L ret= (ret == HA_ERR_END_OF_FILE || ret == HA_ERR_KEY_NOT_FOUND) ? 0 : 1; goto err; } + DBUG_PRINT("info",("Found some rows, let's retrieve them.")); while (!ret) { @@ -4143,20 +4188,24 @@ int fill_schema_events(THD *thd, TABLE_L if (use_prefix_scanning) ret= event_table->file-> - index_next_same(event_table->record[0], key_buf, key_len); + index_next_same(event_table->record[0], key_buf, key_len); else ret= event_table->file->index_next(event_table->record[0]); } + DBUG_PRINT("info",("Scan finished")); // ret is guaranteed to be != 0 ret= (ret != HA_ERR_END_OF_FILE); err: if (event_table) { + DBUG_PRINT("info",("Closing index")); event_table->file->ha_index_end(); close_thread_tables(thd); } thd->restore_backup_open_tables_state(&backup); + + DBUG_PRINT("info",("Return code=%d", ret)); DBUG_RETURN(ret); } --- 1.217/sql/table.cc 2006-03-22 12:10:33 +01:00 +++ 1.218/sql/table.cc 2006-03-24 15:44:18 +01:00 @@ -2379,11 +2379,11 @@ table_check_intact(TABLE *table, uint ta if (strncmp(field->field_name, table_def->name.str, table_def->name.length)) { - sql_print_error("(%s) Expected field %s at position %d, found %s", - table->alias, table_def->name.str, i, + sql_print_error("(mysql.%s) Expected field %s at position %d, " + " found %s", table->alias, table_def->name.str, i, field->field_name); } - + /* IF the type does not match than something is really wrong Check up to length - 1. Why? @@ -2398,22 +2398,23 @@ table_check_intact(TABLE *table, uint ta if (strncmp(sql_type.c_ptr(), table_def->type.str, table_def->type.length - 1)) { - sql_print_error("(%s) Expected field %s at position %d to have type " - "%s, found %s", table->alias, table_def->name.str, - i, table_def->type.str, sql_type.c_ptr()); + sql_print_error("(mysql.%s) Expected field %s at position %d to " + " have type %s, found %s", table->alias, + table_def->name.str, i, table_def->type.str, + sql_type.c_ptr()); error= TRUE; } else if (table_def->cset.str && !field->has_charset()) { - sql_print_error("(%s) Expected field %s at position %d to have " + sql_print_error("(mysql.%s) Expected field %s at position %d to have " "character set '%s' but found no such", table->alias, - table_def->name.str, i, table_def->cset.str); + table_def->name.str, i, table_def->cset.str); error= TRUE; } else if (table_def->cset.str && strcmp(field->charset()->csname, table_def->cset.str)) { - sql_print_error("(%s) Expected field %s at position %d to have " + sql_print_error("(mysql.%s) Expected field %s at position %d to have " "character set '%s' but found '%s'", table->alias, table_def->name.str, i, table_def->cset.str, field->charset()->csname); @@ -2422,8 +2423,8 @@ table_check_intact(TABLE *table, uint ta } else { - sql_print_error("(%s) Expected field %s at position %d to have type %s " - " but no field found.", table_def->name.str, + sql_print_error("(mysql.%s) Expected field %s at position %d to " + " have type %s but no field found.", table_def->name.str, table_def->name.str, i, table_def->type.str); error= TRUE; } --- 1.28/mysql-test/r/events.result 2006-03-17 11:01:35 +01:00 +++ 1.29/mysql-test/r/events.result 2006-03-24 15:44:17 +01:00 @@ -252,7 +252,8 @@ event CREATE TABLE `event` ( `on_completion` enum('DROP','PRESERVE') NOT NULL DEFAULT 'DROP', `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE') NOT NULL DEFAULT '', `comment` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '', - PRIMARY KEY (`definer`,`db`,`name`) + PRIMARY KEY (`definer`,`db`,`name`), + KEY `dbkey` (`db`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Events' SELECT event_name FROM INFORMATION_SCHEMA.EVENTS; ERROR HY000: Cannot load from mysql.event. Table probably corrupted. See error log. @@ -260,10 +261,8 @@ ALTER TABLE mysql.event MODIFY db char(6 "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 +events_test intact_check root@localhost RECURRING NULL 10 HOUR # # ENABLED ALTER TABLE mysql.event MODIFY db char(64) character set cp1251 default ''; -Warnings: -Warning 1265 Data truncated for column 'db' at row 1 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 ''; @@ -280,86 +279,6 @@ 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; -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; "Sleep a bit so the server closes the second connection" 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; --- New file --- +++ mysql-test/r/events_i_s.result 06/03/24 15:44:18 CREATE DATABASE IF NOT EXISTS events_test; USE events_test; SELECT USER(), DATABASE(); USER() DATABASE() root@localhost events_test 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 EVENT ON events_test.* to ev_test@localhost; GRANT SELECT on events_test2.* to ev_test@localhost; "Connect as ev_con1, user ev_test, db events_test2" "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 EVENT ON `events_test`.* TO 'ev_test'@'localhost' GRANT SELECT ON `events_test2`.* TO 'ev_test'@'localhost' "Now the list should be empty:"; SHOW EVENTS; Db Name Definer Type Execute at Interval value Interval field Starts Ends Status "Now create few events as ev_test@localhost in schema events_test" SELECT USER(); USER() ev_test@localhost "ev_test@localhost has no EVENT so error" CREATE EVENT one_event ON SCHEDULE EVERY 20 SECOND DISABLE DO SELECT 123; ERROR 42000: Access denied; you need the EVENT privilege for this operation "Now we will give him EVENT" GRANT EVENT ON events_test2.* TO ev_test@localhost; "Change the current schema so the server will read our new privs" USE events_test; USE events_test2; "Now he has EVENT and will create 3 events" CREATE EVENT one_event ON SCHEDULE EVERY 20 SECOND DISABLE DO SELECT 123; CREATE EVENT two_event ON SCHEDULE EVERY 20 SECOND ON COMPLETION 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 because ev_test has no SELECT on mysql.event:"; SHOW EVENTS; Db Name Definer Type Execute at Interval value Interval field Starts Ends Status events_test2 one_event ev_test@localhost RECURRING NULL 20 SECOND # # DISABLED events_test2 three_event ev_test@localhost RECURRING NULL 20 SECOND # # ENABLED events_test2 two_event ev_test@localhost RECURRING NULL 20 SECOND # # ENABLED "Let's REVOKE his EVENT on events_test2, but he will still see his events" REVOKE EVENT ON events_test2.* FROM ev_test@localhost; "Now he has no EVENT on events_test2 but that's not a problem" SHOW EVENTS; Db Name Definer Type Execute at Interval value Interval field Starts Ends Status events_test2 one_event ev_test@localhost RECURRING NULL 20 SECOND # # DISABLED events_test2 three_event ev_test@localhost RECURRING NULL 20 SECOND # # ENABLED events_test2 two_event ev_test@localhost RECURRING NULL 20 SECOND # # ENABLED "This should show us only 2 events:"; SHOW EVENTS LIKE 't%event'; Db Name Definer Type Execute at Interval value Interval field Starts Ends Status events_test2 three_event ev_test@localhost RECURRING NULL 20 SECOND # # ENABLED events_test2 two_event ev_test@localhost RECURRING NULL 20 SECOND # # ENABLED "This should show us no events:"; SHOW EVENTS FROM test LIKE '%'; Db Name Definer Type Execute at Interval value Interval field Starts Ends Status "This also :"; SHOW EVENTS IN test LIKE '%'; Db Name Definer Type Execute at Interval value Interval field Starts Ends Status "ok, we are back to root@localhost" USE events_test2; CREATE EVENT root_event_1 ON SCHEDULE EVERY 10 HOUR DO SELECT 12345; CREATE EVENT root_event_2 ON SCHEDULE EVERY 20 MINUTE DO SELECT 4567; "As root@localhost we should see 5 event:"; SHOW EVENTS; Db Name Definer Type Execute at Interval value Interval field Starts Ends Status events_test2 one_event ev_test@localhost RECURRING NULL 20 SECOND # # DISABLED events_test2 two_event ev_test@localhost RECURRING NULL 20 SECOND # # ENABLED events_test2 three_event ev_test@localhost RECURRING NULL 20 SECOND # # ENABLED events_test2 root_event_1 root@localhost RECURRING NULL 10 HOUR # # ENABLED events_test2 root_event_2 root@localhost RECURRING NULL 20 MINUTE # # ENABLED "Switching back to ev_con1, should see only his 3 events. Keep in mind he has no EVENT ATM" SHOW EVENTS; Db Name Definer Type Execute at Interval value Interval field Starts Ends Status events_test2 one_event ev_test@localhost RECURRING NULL 20 SECOND # # DISABLED events_test2 three_event ev_test@localhost RECURRING NULL 20 SECOND # # ENABLED events_test2 two_event ev_test@localhost RECURRING NULL 20 SECOND # # ENABLED "Back to root@localhost to GRANT SELECT on mysql.event" GRANT SELECT ON mysql.event TO ev_test@localhost; "Back to ev_test@localhost" "Now we should see 5 events, because of SELECT on mysql.event," "even without EVENT on event_test2. 3 of ev_test@localhost and 2 of root@localhost" SHOW EVENTS; Db Name Definer Type Execute at Interval value Interval field Starts Ends Status events_test2 one_event ev_test@localhost RECURRING NULL 20 SECOND # # DISABLED events_test2 two_event ev_test@localhost RECURRING NULL 20 SECOND # # ENABLED events_test2 three_event ev_test@localhost RECURRING NULL 20 SECOND # # ENABLED events_test2 root_event_1 root@localhost RECURRING NULL 10 HOUR # # ENABLED events_test2 root_event_2 root@localhost RECURRING NULL 20 MINUTE # # ENABLED "we should see 6 events now (additional one from events_test schema:"; 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_test2 one_event ev_test@localhost SELECT 123 RECURRING NULL 20 SECOND DISABLED NOT PRESERVE NULL events_test2 three_event ev_test@localhost SELECT 123 RECURRING NULL 20 SECOND ENABLED PRESERVE three event NULL events_test2 two_event ev_test@localhost SELECT 123 RECURRING NULL 20 SECOND ENABLED PRESERVE two event NULL events_test one_event root@localhost SELECT 123 RECURRING NULL 10 SECOND ENABLED NOT PRESERVE NULL events_test2 root_event_1 root@localhost SELECT 12345 RECURRING NULL 10 HOUR ENABLED NOT PRESERVE NULL events_test2 root_event_2 root@localhost SELECT 4567 RECURRING NULL 20 MINUTE ENABLED NOT PRESERVE REVOKE SELECT ON mysql.event FROM ev_test@localhost; "we should see only 3 events now, we don't have SELECT on mysql.event anymore:"; 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_test2 one_event ev_test@localhost SELECT 123 RECURRING NULL 20 SECOND DISABLED NOT PRESERVE NULL events_test2 three_event ev_test@localhost SELECT 123 RECURRING NULL 20 SECOND ENABLED PRESERVE three event NULL events_test2 two_event ev_test@localhost SELECT 123 RECURRING NULL 20 SECOND ENABLED PRESERVE two event DROP DATABASE events_test2; USE events_test; "we should see only 2 events now, 3 of ev_test@localhost were dropped with DROP DATABASE"; 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 DROP USER ev_test@localhost; DROP EVENT one_event; DROP DATABASE events_test; --- 1.24/mysql-test/t/events.test 2006-03-17 11:01:35 +01:00 +++ 1.25/mysql-test/t/events.test 2006-03-24 15:44:17 +01:00 @@ -238,79 +238,6 @@ 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 --- New file --- +++ mysql-test/t/events_i_s.test 06/03/24 15:44:19 CREATE DATABASE IF NOT EXISTS events_test; USE events_test; # #INFORMATION_SCHEMA.EVENTS test begin # # This test checks the visibility of events during # show events/select from i_s.events . If someone has SELECT on mysql.event # he will see all events otherwise only his. EVENT_ACL is not needed for both # show events/select from i_s.events # SELECT USER(), DATABASE(); 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 EVENT ON events_test.* to ev_test@localhost; GRANT SELECT on events_test2.* to ev_test@localhost; #now we are on con1 --echo "Connect as ev_con1, user ev_test, db events_test2" connect (ev_con1,localhost,ev_test,,events_test2); --echo "NEW CONNECTION"; SELECT USER(), DATABASE(); SHOW GRANTS; --echo "Now the list should be empty:"; --replace_column 8 # 9 # SHOW EVENTS; --echo "Now create few events as ev_test@localhost in schema events_test" SELECT USER(); --echo "ev_test@localhost has no EVENT so error" --error ER_SPECIFIC_ACCESS_DENIED_ERROR CREATE EVENT one_event ON SCHEDULE EVERY 20 SECOND DISABLE DO SELECT 123; --echo "Now we will give him EVENT" connection default; GRANT EVENT ON events_test2.* TO ev_test@localhost; connection ev_con1; --echo "Change the current schema so the server will read our new privs" USE events_test; USE events_test2; --echo "Now he has EVENT and will create 3 events" CREATE EVENT one_event ON SCHEDULE EVERY 20 SECOND DISABLE DO SELECT 123; CREATE EVENT two_event ON SCHEDULE EVERY 20 SECOND ON COMPLETION 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 because ev_test has no SELECT on mysql.event:"; --replace_column 8 # 9 # SHOW EVENTS; --echo "Let's REVOKE his EVENT on events_test2, but he will still see his events" connection default; REVOKE EVENT ON events_test2.* FROM ev_test@localhost; connection ev_con1; --echo "Now he has no EVENT on events_test2 but that's not a problem" --replace_column 8 # 9 # SHOW EVENTS; --echo "This should show us only 2 events:"; --replace_column 8 # 9 # SHOW EVENTS LIKE 't%event'; --echo "This should show us no events:"; --replace_column 8 # 9 # SHOW EVENTS FROM test LIKE '%'; --echo "This also :"; --replace_column 8 # 9 # SHOW EVENTS IN test LIKE '%'; --echo "ok, we are back to root@localhost" connection default; USE events_test2; CREATE EVENT root_event_1 ON SCHEDULE EVERY 10 HOUR DO SELECT 12345; CREATE EVENT root_event_2 ON SCHEDULE EVERY 20 MINUTE DO SELECT 4567; --echo "As root@localhost we should see 5 event:"; --replace_column 8 # 9 # SHOW EVENTS; --echo "Switching back to ev_con1, should see only his 3 events. Keep in mind he has no EVENT ATM" connection ev_con1; --replace_column 8 # 9 # SHOW EVENTS; --echo "Back to root@localhost to GRANT SELECT on mysql.event" connection default; GRANT SELECT ON mysql.event TO ev_test@localhost; --echo "Back to ev_test@localhost" connection ev_con1; --echo "Now we should see 5 events, because of SELECT on mysql.event," --echo "even without EVENT on event_test2. 3 of ev_test@localhost and 2 of root@localhost" --replace_column 8 # 9 # SHOW EVENTS; --echo "we should see 6 events now (additional one from events_test schema:"; 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 default; REVOKE SELECT ON mysql.event FROM ev_test@localhost; connection ev_con1; --echo "we should see only 3 events now, we don't have SELECT on mysql.event anymore:"; 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; disconnect ev_con1; connection default; DROP DATABASE events_test2; USE events_test; --echo "we should see only 2 events now, 3 of ev_test@localhost were dropped with DROP DATABASE"; 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; DROP USER ev_test@localhost; DROP EVENT one_event; # ##INFORMATION_SCHEMA.EVENTS test 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-24 15:44:18 +01:00 @@ -67,7 +67,7 @@ MEM_ROOT evex_mem_root; time_t mysql_event_last_create_time= 0L; -static TABLE_FIELD_W_TYPE event_table_fields[EVEX_FIELD_COUNT] = { +TABLE_FIELD_W_TYPE event_table_fields[EVEX_FIELD_COUNT] = { { {(char *) STRING_WITH_LEN("db")}, {(char *) STRING_WITH_LEN("char(64)")}, @@ -1395,7 +1395,10 @@ evex_show_create_event(THD *thd, sp_name show_str.set_charset(system_charset_info); if (et->get_create_event(thd, &show_str)) - DBUG_RETURN(1); + { + ret= 1; + goto finish; + } field_list.push_back(new Item_empty_string("Event", NAME_LEN)); @@ -1409,7 +1412,10 @@ evex_show_create_event(THD *thd, sp_name show_str.length())); if (protocol->send_fields(&field_list, Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF)) - DBUG_RETURN(1); + { + ret= 1; + goto finish; + } protocol->prepare_for_resend(); protocol->store(et->name.str, et->name.length, system_charset_info); @@ -1419,6 +1425,10 @@ evex_show_create_event(THD *thd, sp_name protocol->store(show_str.c_ptr(), show_str.length(), system_charset_info); ret= protocol->write(); send_eof(thd); + +finish: + /* et was not allocated on a root thus free it */ + delete et; } DBUG_RETURN(ret); --- 1.41/sql/event_executor.cc 2006-03-01 04:21:57 +01:00 +++ 1.42/sql/event_executor.cc 2006-03-24 15:44:18 +01:00 @@ -411,6 +411,7 @@ event_executor_main(void *arg) DBUG_ENTER("event_executor_main"); DBUG_PRINT("event_executor_main", ("EVEX thread started")); + sql_print_information("SCHEDULER: Starting"); /* init memory root */ init_alloc_root(&evex_mem_root, MEM_ROOT_BLOCK_SIZE, MEM_ROOT_PREALLOC); @@ -652,6 +653,7 @@ finish: err_no_thd: VOID(pthread_mutex_lock(&LOCK_evex_running)); evex_is_running= false; + event_executor_running_global_var= false; VOID(pthread_mutex_unlock(&LOCK_evex_running)); free_root(&evex_mem_root, MYF(0)); --- 1.25/mysql-test/lib/init_db.sql 2006-03-01 13:31:12 +01:00 +++ 1.26/mysql-test/lib/init_db.sql 2006-03-24 15:44:17 +01:00 @@ -631,7 +631,8 @@ CREATE TABLE event ( 'HIGH_NOT_PRECEDENCE' ) DEFAULT '' NOT NULL, comment char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', - PRIMARY KEY (definer, db, name) + PRIMARY KEY (definer, db, name), + KEY dbkey(db) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT 'Events'; CREATE DATABASE IF NOT EXISTS cluster; --- 1.43/scripts/mysql_fix_privilege_tables.sql 2006-03-03 09:51:26 +01:00 +++ 1.44/scripts/mysql_fix_privilege_tables.sql 2006-03-24 15:44:18 +01:00 @@ -637,6 +637,7 @@ ALTER TABLE user add Event_priv enum('N' ALTER TABLE db add Event_priv enum('N','Y') character set utf8 DEFAULT 'N' NOT NULL; ALTER TABLE event DROP PRIMARY KEY; ALTER TABLE event ADD PRIMARY KEY(definer, db, name); +ALTER TABLE event ADD KEY dbkey(db); ALTER TABLE event ADD sql_mode set('REAL_AS_FLOAT', 'PIPES_AS_CONCAT', --- 1.38/scripts/mysql_create_system_tables.sh 2006-03-01 13:31:13 +01:00 +++ 1.39/scripts/mysql_create_system_tables.sh 2006-03-24 15:44:17 +01:00 @@ -827,7 +827,8 @@ then c_ev="$c_ev 'HIGH_NOT_PRECEDENCE'" c_ev="$c_ev ) DEFAULT '' NOT NULL," c_ev="$c_ev comment char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default ''," - c_ev="$c_ev PRIMARY KEY (definer, db, name)" + c_ev="$c_ev PRIMARY KEY (definer, db, name)," + c_ev="$c_ev KEY dbkey(db, definer)" c_ev="$c_ev ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT 'Events';" fi
| Thread | ||
|---|---|---|
| • bk commit into 5.1 tree (andrey:1.2219) BUG#16992 | ahristov | 24 Mar |
© 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Page generated in 0.660 sec. using MySQL 5.1.14-beta-log