# 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 # if (`SELECT VERSION() LIKE '%embedded%'`) { --skip Bug#37456 funcs_1: Several tests crash when used with embedded server } 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=MARIA "" " 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 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 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;