is_events.result 6.37 KB
Newer Older
unknown's avatar
unknown committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
SHOW TABLES FROM information_schema LIKE 'EVENTS';
Tables_in_information_schema (EVENTS)
EVENTS
#######################################################################
# Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
#######################################################################
DROP VIEW      IF EXISTS test.v1;
DROP PROCEDURE IF EXISTS test.p1;
DROP FUNCTION  IF EXISTS test.f1;
CREATE VIEW test.v1 AS     SELECT * FROM information_schema.EVENTS;
CREATE PROCEDURE test.p1() SELECT * FROM information_schema.EVENTS;
CREATE FUNCTION test.f1() returns BIGINT
BEGIN
DECLARE counter BIGINT DEFAULT NULL;
SELECT COUNT(*) INTO counter FROM information_schema.EVENTS;
RETURN counter;
END//
# Attention: The printing of the next result sets is disabled.
SELECT * FROM information_schema.EVENTS;
SELECT * FROM test.v1;
CALL test.p1;
SELECT test.f1();
DROP VIEW test.v1;
DROP PROCEDURE test.p1;
DROP FUNCTION test.f1;
#########################################################################
# Testcase 3.2.12.1: INFORMATION_SCHEMA.EVENTS layout
#########################################################################
DESCRIBE          information_schema.EVENTS;
Field	Type	Null	Key	Default	Extra
31
EVENT_CATALOG	varchar(64)	NO			
unknown's avatar
unknown committed
32 33 34 35 36 37 38 39 40 41
EVENT_SCHEMA	varchar(64)	NO			
EVENT_NAME	varchar(64)	NO			
DEFINER	varchar(77)	NO			
TIME_ZONE	varchar(64)	NO			
EVENT_BODY	varchar(8)	NO			
EVENT_DEFINITION	longtext	NO		NULL	
EVENT_TYPE	varchar(9)	NO			
EXECUTE_AT	datetime	YES		NULL	
INTERVAL_VALUE	varchar(256)	YES		NULL	
INTERVAL_FIELD	varchar(18)	YES		NULL	
42
SQL_MODE	varchar(8192)	NO			
unknown's avatar
unknown committed
43 44 45 46 47 48 49 50 51 52 53 54 55 56 57
STARTS	datetime	YES		NULL	
ENDS	datetime	YES		NULL	
STATUS	varchar(18)	NO			
ON_COMPLETION	varchar(12)	NO			
CREATED	datetime	NO		0000-00-00 00:00:00	
LAST_ALTERED	datetime	NO		0000-00-00 00:00:00	
LAST_EXECUTED	datetime	YES		NULL	
EVENT_COMMENT	varchar(64)	NO			
ORIGINATOR	bigint(10)	NO		0	
CHARACTER_SET_CLIENT	varchar(32)	NO			
COLLATION_CONNECTION	varchar(32)	NO			
DATABASE_COLLATION	varchar(32)	NO			
SHOW CREATE TABLE information_schema.EVENTS;
Table	Create Table
EVENTS	CREATE TEMPORARY TABLE `EVENTS` (
58
  `EVENT_CATALOG` varchar(64) NOT NULL DEFAULT '',
unknown's avatar
unknown committed
59 60 61 62 63 64 65 66 67 68
  `EVENT_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `EVENT_NAME` varchar(64) NOT NULL DEFAULT '',
  `DEFINER` varchar(77) NOT NULL DEFAULT '',
  `TIME_ZONE` varchar(64) NOT NULL DEFAULT '',
  `EVENT_BODY` varchar(8) NOT NULL DEFAULT '',
  `EVENT_DEFINITION` longtext NOT NULL,
  `EVENT_TYPE` varchar(9) NOT NULL DEFAULT '',
  `EXECUTE_AT` datetime DEFAULT NULL,
  `INTERVAL_VALUE` varchar(256) DEFAULT NULL,
  `INTERVAL_FIELD` varchar(18) DEFAULT NULL,
69
  `SQL_MODE` varchar(8192) NOT NULL DEFAULT '',
unknown's avatar
unknown committed
70 71 72 73 74 75 76 77 78 79 80 81 82 83 84
  `STARTS` datetime DEFAULT NULL,
  `ENDS` datetime DEFAULT NULL,
  `STATUS` varchar(18) NOT NULL DEFAULT '',
  `ON_COMPLETION` varchar(12) NOT NULL DEFAULT '',
  `CREATED` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `LAST_ALTERED` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `LAST_EXECUTED` datetime DEFAULT NULL,
  `EVENT_COMMENT` varchar(64) NOT NULL DEFAULT '',
  `ORIGINATOR` bigint(10) NOT NULL DEFAULT '0',
  `CHARACTER_SET_CLIENT` varchar(32) NOT NULL DEFAULT '',
  `COLLATION_CONNECTION` varchar(32) NOT NULL DEFAULT '',
  `DATABASE_COLLATION` varchar(32) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8
SHOW COLUMNS FROM information_schema.EVENTS;
Field	Type	Null	Key	Default	Extra
85
EVENT_CATALOG	varchar(64)	NO			
unknown's avatar
unknown committed
86 87 88 89 90 91 92 93 94 95
EVENT_SCHEMA	varchar(64)	NO			
EVENT_NAME	varchar(64)	NO			
DEFINER	varchar(77)	NO			
TIME_ZONE	varchar(64)	NO			
EVENT_BODY	varchar(8)	NO			
EVENT_DEFINITION	longtext	NO		NULL	
EVENT_TYPE	varchar(9)	NO			
EXECUTE_AT	datetime	YES		NULL	
INTERVAL_VALUE	varchar(256)	YES		NULL	
INTERVAL_FIELD	varchar(18)	YES		NULL	
96
SQL_MODE	varchar(8192)	NO			
unknown's avatar
unknown committed
97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148
STARTS	datetime	YES		NULL	
ENDS	datetime	YES		NULL	
STATUS	varchar(18)	NO			
ON_COMPLETION	varchar(12)	NO			
CREATED	datetime	NO		0000-00-00 00:00:00	
LAST_ALTERED	datetime	NO		0000-00-00 00:00:00	
LAST_EXECUTED	datetime	YES		NULL	
EVENT_COMMENT	varchar(64)	NO			
ORIGINATOR	bigint(10)	NO		0	
CHARACTER_SET_CLIENT	varchar(32)	NO			
COLLATION_CONNECTION	varchar(32)	NO			
DATABASE_COLLATION	varchar(32)	NO			
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');
event_catalog	event_name	event_body	event_type	event_type	status	on_completion
########################################################################
# Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
#           DDL on INFORMATION_SCHEMA tables are not supported
########################################################################
DROP DATABASE IF EXISTS db_datadict;
CREATE DATABASE db_datadict;
CREATE TABLE db_datadict.t1 (f1 BIGINT)
ENGINE = <engine_type>;
INSERT INTO information_schema.events
SELECT * FROM information_schema.events;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
UPDATE information_schema.events SET event_name = '1234567'
WHERE table_name = 't1';
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DELETE FROM information_schema.events WHERE event_catalog IS NULL;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
TRUNCATE information_schema.events;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
CREATE INDEX my_idx_on_events ON information_schema.events(event_name);
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.events DROP PRIMARY KEY;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.events ADD f1 INT;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DROP TABLE information_schema.events;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.events RENAME db_datadict.events;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.events RENAME information_schema.xevents;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DROP DATABASE db_datadict;