diff options
Diffstat (limited to 'mysql-test/suite/funcs_1/t/is_events.test')
| -rw-r--r-- | mysql-test/suite/funcs_1/t/is_events.test | 168 |
1 files changed, 168 insertions, 0 deletions
diff --git a/mysql-test/suite/funcs_1/t/is_events.test b/mysql-test/suite/funcs_1/t/is_events.test new file mode 100644 index 00000000..69d1af6b --- /dev/null +++ b/mysql-test/suite/funcs_1/t/is_events.test @@ -0,0 +1,168 @@ +# suite/funcs_1/t/is_events.test +# +# Check the layout of information_schema.events and some functionality of it. +# +# This test is not intended for checking storage engine properties +# Therefore please do not alter $engine_type and $other_engine_type. +# +# Author: +# 2008-02-29 mleich WL#4203 Reorganize and fix the data dictionary tests of +# testsuite funcs_1 +# + +let $engine_type = MEMORY; +let $other_engine_type = MyISAM; + +let $is_table = EVENTS; + +# The table INFORMATION_SCHEMA.EVENTS must exist +eval SHOW TABLES FROM information_schema LIKE '$is_table'; + +--echo ####################################################################### +--echo # Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT +--echo ####################################################################### +# Ensure that every INFORMATION_SCHEMA table can be queried with a SELECT +# statement, just as if it were an ordinary user-defined table. +# +--source suite/funcs_1/datadict/is_table_query.inc + + +--echo ######################################################################### +--echo # Testcase 3.2.12.1: INFORMATION_SCHEMA.EVENTS layout +--echo ######################################################################### +# Ensure that the INFORMATION_SCHEMA.EVENTS table has the following columns, +# in the following order: +# +# EVENT_CATALOG always NULL +# EVENT_SCHEMA The name of the schema (database) to which this +# event belongs. +# EVENT_NAME The name of the event. +# DEFINER The user who created the event. +# 'user_name'@'host_name' format! +# TIME_ZONE The time zone in effect when schedule for the event was +# last modified +# EVENT_BODY The language used for the statements in the event's +# DO clause. (always SQL) +# EVENT_DEFINITION The text of the SQL statement making up the event's +# DO clause. +# EVENT_TYPE One of the two values ONE TIME or RECURRING. +# EXECUTE_AT one-time event: DATETIME value specified in the AT clause +# of the CREATE EVENT statement used to +# create the event +# or of the last ALTER EVENT statement that modified +# the event. +# INTERVAL_VALUE recurring events; numeric portion of the event's +# EVERY clause. +# INTERVAL_FIELD recurring events: units portion of the EVERY clause +# governing the timing of the event, prefixed with +# 'INTERVAL_'. Example: 'INTERVAL_DAY' +# SQL_MODE The SQL mode in effect at the time the event was created +# or altered. +# STARTS For recurring event whose definition includes a STARTS +# clause, this column contains the corresponding +# DATETIME value. If there is no STARTS clause affecting +# the timing of the event, this column is empty. +# ENDS For a recurring event whose definition includes a ENDS +# clause, this column contains the corresponding DATETIME +# value. If there is no ENDS clause affecting the timing +# of the event, this column contains NULL. +# STATUS ENABLED, DISABLED or SLAVESIDE_DISABLED +# ON_COMPLETION PRESERVE or NOT PRESERVE. +# CREATED Date and time of event creation +# LAST_ALTERED Date and time of the last event modification +# never modified -> value = CREATED +# LAST_EXECUTED Date and time of start of last event execution +# never executed -> value IS NULL. +# EVENT_COMMENT The text of a comment. Default: empty string. +# ORIGINATOR The server ID of the MySQL server on which the event was +# created; used in replication. Default: 0 +# CHARACTER_SET_CLIENT Session value of the character_set_client system variable +# when the event was created. +# COLLATION_CONNECTION Session value of the collation_connection system +# variable when the event was created. +# DATABASE_COLLATION Collation of the database with which the event +# is associated. +# +eval DESCRIBE information_schema.$is_table; +--replace_result ENGINE=MyISAM "" ENGINE=Aria "" " PAGE_CHECKSUM=1" "" " PAGE_CHECKSUM=0" "" +eval SHOW CREATE TABLE information_schema.$is_table; +eval SHOW COLUMNS FROM information_schema.$is_table; + +# Note: Retrieval of information within information_schema.columns about +# information_schema.events is in is_columns_is.test. + +# Check event_catalog event_body, event_type, event_type, status, on_completion +SELECT event_catalog, event_name, event_body, event_type, event_type, + status, on_completion +FROM information_schema.events +WHERE event_catalog IS NOT NULL or + event_body NOT IN ('SQL') or + event_type NOT IN ('ONE TIME','RECURRING') or + status NOT IN ('ENABLED','DISABLED','SLAVESIDE_DISABLED') or + on_completion NOT IN ('PRESERVE','NOT PRESERVE'); + +# FIXME: Check the regression tests and implement tests checking the +# functionality if missing. + + +--echo ######################################################################## +--echo # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and +--echo # DDL on INFORMATION_SCHEMA tables are not supported +--echo ######################################################################## +# 3.2.1.3: Ensure that no user may execute an INSERT statement on any +# INFORMATION_SCHEMA table. +# 3.2.1.4: Ensure that no user may execute an UPDATE statement on any +# INFORMATION_SCHEMA table. +# 3.2.1.5: Ensure that no user may execute a DELETE statement on any +# INFORMATION_SCHEMA table. +# 3.2.1.8: Ensure that no user may create an index on an +# INFORMATION_SCHEMA table. +# 3.2.1.9: Ensure that no user may alter the definition of an +# INFORMATION_SCHEMA table. +# 3.2.1.10: Ensure that no user may drop an INFORMATION_SCHEMA table. +# 3.2.1.11: Ensure that no user may move an INFORMATION_SCHEMA table to any +# other database. +# 3.2.1.12: Ensure that no user may directly add to, alter, or delete any data +# in an INFORMATION_SCHEMA table. +# +--disable_warnings +DROP DATABASE IF EXISTS db_datadict; +--enable_warnings +CREATE DATABASE db_datadict; +--replace_result $engine_type <engine_type> +eval +CREATE TABLE db_datadict.t1 (f1 BIGINT) +ENGINE = $engine_type; + +--error ER_DBACCESS_DENIED_ERROR +INSERT INTO information_schema.events +SELECT * FROM information_schema.events; + +--error ER_DBACCESS_DENIED_ERROR,ER_NON_UPDATABLE_TABLE +UPDATE information_schema.events SET event_name = '1234567' +WHERE table_name = 't1'; + +--error ER_DBACCESS_DENIED_ERROR +DELETE FROM information_schema.events WHERE event_catalog IS NULL; +--error ER_DBACCESS_DENIED_ERROR +TRUNCATE information_schema.events; + +--error ER_DBACCESS_DENIED_ERROR +CREATE INDEX my_idx_on_events ON information_schema.events(event_name); + +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.events DROP PRIMARY KEY; +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.events ADD f1 INT; + +--error ER_DBACCESS_DENIED_ERROR +DROP TABLE information_schema.events; + +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.events RENAME db_datadict.events; +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.events RENAME information_schema.xevents; + +# Cleanup +DROP DATABASE db_datadict; + |