2
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
5
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
8
DROP VIEW IF EXISTS v1,v11;
9
DROP TABLE IF EXISTS t1,t2,t3,t11,t12,t13;
10
DROP PROCEDURE IF EXISTS p1;
11
DROP PROCEDURE IF EXISTS p11;
12
DROP FUNCTION IF EXISTS f1;
13
DROP FUNCTION IF EXISTS f2;
14
DROP EVENT IF EXISTS e1;
15
DROP EVENT IF EXISTS e11;
17
CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, c VARCHAR(64)) ENGINE=myisam;
18
INSERT INTO t1 VALUES (1,1,'1');
19
INSERT INTO t1 VALUES (2,2,UUID());
20
CREATE TABLE t2 (a INT UNIQUE, b INT, c VARCHAR(64)) ENGINE=myisam;
21
INSERT INTO t2 VALUES (1,1,'1');
22
INSERT INTO t2 VALUES (2,2,UUID());
23
CREATE TABLE t11 (a INT NOT NULL PRIMARY KEY, b INT, c VARCHAR(64)) ENGINE=innodb;
24
INSERT INTO t11 VALUES (1,1,'1');
25
INSERT INTO t11 VALUES (2,2,UUID());
26
CREATE TABLE t12 (a INT UNIQUE, b INT, c VARCHAR(64)) ENGINE=innodb;
27
INSERT INTO t12 VALUES (1,1,'1');
28
INSERT INTO t12 VALUES (2,2,UUID());
30
CREATE VIEW v1 AS SELECT * FROM t1;
31
CREATE VIEW v11 AS SELECT * FROM t11;
32
CREATE TRIGGER t1_tr1 BEFORE INSERT ON t1 FOR EACH ROW
34
INSERT INTO t2 VALUES (NEW.a, NEW.b, NEW.c);
35
INSERT INTO t3 VALUES (NEW.a, NEW.b, NEW.c);
37
CREATE TRIGGER t1_tr2 BEFORE UPDATE ON t1 FOR EACH ROW
42
CREATE TRIGGER t11_tr1 BEFORE INSERT ON t11 FOR EACH ROW
44
INSERT INTO t12 VALUES (NEW.a, NEW.b, NEW.c);
45
INSERT INTO t13 VALUES (NEW.a, NEW.b, NEW.c);
47
CREATE TRIGGER t11_tr2 BEFORE UPDATE ON t11 FOR EACH ROW
49
UPDATE t12 SET c = '';
50
UPDATE t13 SET c = '';
52
CREATE EVENT e1 ON SCHEDULE EVERY 1 SECOND DISABLE DO
54
ALTER EVENT e1 DISABLE;
57
CREATE EVENT e11 ON SCHEDULE EVERY 1 SECOND DISABLE DO
59
ALTER EVENT e11 DISABLE;
62
CREATE FUNCTION f1 (x INT) RETURNS VARCHAR(64)
69
CREATE FUNCTION f2 (x INT) RETURNS VARCHAR(64)
73
CREATE PROCEDURE p1 (IN x INT, IN y VARCHAR(64))
75
INSERT IGNORE INTO t1 VALUES (x,x,y);
77
CREATE PROCEDURE p11 (IN x INT, IN y VARCHAR(64))
79
INSERT IGNORE INTO t11 VALUES (x,x,y);
82
CREATE TABLE t3 SELECT * FROM v1;
83
INSERT INTO t1 VALUES (3,3,'');
84
UPDATE t1 SET c='2' WHERE a = 1;
85
INSERT INTO t1 VALUES(4,4,f1(4));
86
INSERT INTO t1 VALUES (100,100,'');
88
INSERT INTO t1 VALUES (101,101,'');
89
INSERT INTO t1 VALUES(6,6,f1(6));
90
INSERT INTO t1 VALUES (102,102,'');
91
INSERT INTO t1 VALUES(7,7,f2(7));
92
INSERT INTO t1 VALUES (103,103,'');
94
CREATE TABLE t13 SELECT * FROM v11;
95
INSERT INTO t11 VALUES (3,3,'');
96
UPDATE t11 SET c='2' WHERE a = 1;
97
INSERT INTO t11 VALUES(4,4,f1(4));
98
INSERT INTO t11 VALUES (100,100,'');
100
INSERT INTO t11 VALUES (101,101,'');
101
INSERT INTO t11 VALUES(6,6,f1(6));
102
INSERT INTO t11 VALUES (102,102,'');
103
INSERT INTO t11 VALUES(7,7,f2(7));
104
INSERT INTO t11 VALUES (103,103,'');
106
SET GLOBAL EVENT_SCHEDULER = on;
107
ALTER EVENT e1 ENABLE;
108
ALTER EVENT e11 ENABLE;
109
SET GLOBAL EVENT_SCHEDULER = off;
111
SHOW TABLES LIKE 't%';
119
SELECT table_name FROM information_schema.views WHERE table_schema='test';
123
SELECT trigger_name, event_manipulation, event_object_table FROM information_schema.triggers WHERE trigger_schema='test';
124
trigger_name event_manipulation event_object_table
129
SELECT routine_type, routine_name FROM information_schema.routines WHERE routine_schema='test';
130
routine_type routine_name
135
SELECT event_name, status FROM information_schema.events WHERE event_schema='test';
140
SELECT COUNT(*) FROM t1;
143
SELECT a,b FROM t1 ORDER BY a;
157
SELECT COUNT(*) FROM t2;
160
SELECT a,b FROM t2 ORDER BY a;
174
SELECT COUNT(*) FROM t3;
177
SELECT a,b FROM t3 ORDER BY a;
191
SELECT a,b FROM v1 ORDER BY a;
205
SELECT COUNT(*) FROM t11;
208
SELECT a,b FROM t11 ORDER BY a;
222
SELECT COUNT(*) FROM t12;
225
SELECT a,b FROM t12 ORDER BY a;
239
SELECT COUNT(*) FROM t13;
242
SELECT a,b FROM t13 ORDER BY a;
256
SELECT a,b FROM v11 ORDER BY a;
271
SHOW TABLES LIKE 't%';
279
SELECT table_name FROM information_schema.views WHERE table_schema='test';
283
SELECT trigger_name, event_manipulation, event_object_table FROM information_schema.triggers WHERE trigger_schema='test';
284
trigger_name event_manipulation event_object_table
289
SELECT routine_type, routine_name FROM information_schema.routines WHERE routine_schema='test';
290
routine_type routine_name
295
SELECT event_name, status FROM information_schema.events WHERE event_schema='test';
297
e1 SLAVESIDE_DISABLED
298
e11 SLAVESIDE_DISABLED
300
SELECT COUNT(*) FROM t1;
303
SELECT a,b FROM t1 ORDER BY a;
317
SELECT COUNT(*) FROM t2;
320
SELECT a,b FROM t2 ORDER BY a;
334
SELECT COUNT(*) FROM t3;
337
SELECT a,b FROM t3 ORDER BY a;
351
SELECT a,b FROM v1 ORDER BY a;
365
SELECT COUNT(*) FROM t11;
368
SELECT a,b FROM t11 ORDER BY a;
382
SELECT COUNT(*) FROM t12;
385
SELECT a,b FROM t12 ORDER BY a;
399
SELECT COUNT(*) FROM t13;
402
SELECT a,b FROM t13 ORDER BY a;
416
SELECT a,b FROM v11 ORDER BY a;
437
ALTER TABLE t3 ORDER BY a;
438
ALTER TABLE t13 ORDER BY a;
442
DROP VIEW IF EXISTS v1,v11;
443
DROP TABLE IF EXISTS t1,t2,t3,t11,t12,t13;
444
DROP PROCEDURE IF EXISTS p1;
445
DROP PROCEDURE IF EXISTS p11;
446
DROP FUNCTION IF EXISTS f1;
447
DROP FUNCTION IF EXISTS f2;
448
DROP EVENT IF EXISTS e1;
449
DROP EVENT IF EXISTS e11;