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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
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
149
150
151
152
153
154
155
156
|
drop table if exists t1;
Warnings:
Note 1051 Unknown table 'test.t1'
drop view if exists view_t1;
Warnings:
Note 1051 Unknown table 'test.view_t1'
SET sql_mode=ONLY_FULL_GROUP_BY;
CREATE TABLE t1 (
pk INT,
f0 INT, f1 INT, f2 INT, f3 INT, f4 INT,
f5 INT, f6 INT, f7 INT, f8 INT, f9 INT,
PRIMARY KEY (pk)
);
CREATE VIEW view_t1 AS SELECT * FROM t1;
CREATE PROCEDURE s1()
SELECT * FROM (
INFORMATION_SCHEMA.`INNODB_BUFFER_PAGE_LRU` AS table1
LEFT JOIN test.view_t1 AS table2
ON ( table2.`f6` = table1.FREE_PAGE_CLOCK)
)
ORDER BY table1.NUMBER_RECORDS
LIMIT 0
;
CALL s1;
POOL_ID LRU_POSITION SPACE PAGE_NUMBER PAGE_TYPE FLUSH_TYPE FIX_COUNT IS_HASHED NEWEST_MODIFICATION OLDEST_MODIFICATION ACCESS_TIME TABLE_NAME INDEX_NAME NUMBER_RECORDS DATA_SIZE COMPRESSED_SIZE COMPRESSED IO_FIX IS_OLD FREE_PAGE_CLOCK pk f0 f1 f2 f3 f4 f5 f6 f7 f8 f9
CALL s1;
POOL_ID LRU_POSITION SPACE PAGE_NUMBER PAGE_TYPE FLUSH_TYPE FIX_COUNT IS_HASHED NEWEST_MODIFICATION OLDEST_MODIFICATION ACCESS_TIME TABLE_NAME INDEX_NAME NUMBER_RECORDS DATA_SIZE COMPRESSED_SIZE COMPRESSED IO_FIX IS_OLD FREE_PAGE_CLOCK pk f0 f1 f2 f3 f4 f5 f6 f7 f8 f9
drop table t1;
drop view view_t1;
drop procedure s1;
CREATE TABLE A (
pk INTEGER AUTO_INCREMENT,
col_int_key INTEGER,
col_varchar_key VARCHAR(1),
PRIMARY KEY (pk)
) ENGINE=MyISAM;
CREATE VIEW view_A AS SELECT * FROM A;
CREATE TABLE C (
pk INTEGER AUTO_INCREMENT,
col_int_nokey INTEGER,
col_int_key INTEGER,
col_date_key DATE,
col_date_nokey DATE,
col_time_key TIME,
col_time_nokey TIME,
col_datetime_key DATETIME,
col_datetime_nokey DATETIME,
col_varchar_key VARCHAR(1),
col_varchar_nokey VARCHAR(1),
PRIMARY KEY (pk)
) ENGINE=MyISAM;
CREATE VIEW view_C AS SELECT * FROM C;
CREATE TABLE AA (
pk INTEGER AUTO_INCREMENT,
col_int_nokey INTEGER,
col_int_key INTEGER,
col_date_key DATE,
col_date_nokey DATE,
col_time_key TIME,
col_time_nokey TIME,
col_datetime_key DATETIME,
col_datetime_nokey DATETIME,
col_varchar_key VARCHAR(1),
col_varchar_nokey VARCHAR(1),
PRIMARY KEY (pk),
KEY (col_varchar_key, col_int_key)
) ENGINE=MyISAM;
CREATE VIEW view_AA AS SELECT * FROM AA;
CREATE TABLE BB (
pk INTEGER AUTO_INCREMENT,
col_int_key INTEGER,
col_varchar_key VARCHAR(1),
col_varchar_nokey VARCHAR(1),
PRIMARY KEY (pk),
KEY (col_varchar_key, col_int_key)
) ENGINE=MyISAM;
CREATE VIEW view_BB AS SELECT * FROM BB;
CREATE TABLE DD (
pk INTEGER AUTO_INCREMENT,
col_int_key INTEGER,
col_date_key DATE,
col_time_key TIME,
col_datetime_key DATETIME,
col_varchar_key VARCHAR(1),
PRIMARY KEY (pk),
KEY (col_varchar_key, col_int_key)
) ENGINE=MyISAM;
CREATE VIEW view_DD AS SELECT * FROM DD;
CREATE TRIGGER k BEFORE INSERT ON `DD` FOR EACH ROW INSERT INTO `view_BB` SELECT * FROM `view_A` LIMIT 0 ;
CREATE TRIGGER r BEFORE INSERT ON `A` FOR EACH ROW INSERT INTO `view_AA` SELECT * FROM `view_C` LIMIT 0 ;
ALTER TABLE `DD` DROP PRIMARY KEY;
ERROR 42000: Incorrect table definition; there can be only one auto column and it must be defined as a key
INSERT INTO `view_A` ( `pk` ) VALUES (NULL);
INSERT INTO `DD` ( `pk` ) VALUES (NULL);
INSERT INTO `A` ( `pk` ) VALUES (NULL);
INSERT INTO `view_DD` ( `pk` ) VALUES (NULL);
drop trigger r;
drop trigger k;
drop view view_A,view_AA,view_C,view_BB,view_DD;
drop table A,C,AA,BB,DD;
CREATE TABLE A (
i INT,
i1 INT,
i2 INT,
d1 DATE,
d2 DATE,
col_time_nokey1 TIME,
col_time_nokey2 TIME,
col_datetime_nokey1 DATETIME,
col_datetime_nokey2 DATETIME,
col_varchar_nokey1 VARCHAR(1),
col_varchar_nokey2 VARCHAR(1)
) ENGINE=MyISAM;
CREATE VIEW view_A AS SELECT * FROM A;
CREATE TABLE B (
col_varchar_nokey VARCHAR(1)
) ENGINE=MyISAM;
CREATE TABLE AA (
i INT,
i1 INT,
i2 INT,
d1 DATE,
d2 DATE,
col_time_nokey1 TIME,
col_time_nokey2 TIME,
col_datetime_nokey1 DATETIME,
col_datetime_nokey2 DATETIME,
col_varchar_nokey1 VARCHAR(1),
col_varchar_nokey2 VARCHAR(1)
) ENGINE=MyISAM;
CREATE VIEW view_AA AS SELECT * FROM AA;
CREATE TABLE DD (
i INT,
i1 INT,
i2 INT,
d1 DATE,
d2 DATE,
col_time_nokey1 TIME,
col_time_nokey2 TIME,
col_datetime_nokey1 DATETIME,
col_datetime_nokey2 DATETIME,
col_varchar_nokey1 VARCHAR(1),
col_varchar_nokey2 VARCHAR(1)
) ENGINE=MyISAM;
CREATE VIEW view_DD AS SELECT * FROM DD;
CREATE TRIGGER tr1 BEFORE INSERT ON `AA` FOR EACH ROW INSERT INTO `view_A` SELECT * FROM `view_AA` LIMIT 0 ;
CREATE TRIGGER tr2 BEFORE INSERT ON `B` FOR EACH ROW INSERT INTO `D` SELECT * FROM `A` LIMIT 0 ;
INSERT INTO `view_AA` ( `i` ) VALUES (1);
INSERT INTO `AA` ( `i` ) VALUES (2);
DELETE FROM `B`;
INSERT INTO `view_DD` ( `i` ) VALUES (1);
INSERT INTO `view_AA` ( `i` ) VALUES (3);
drop trigger tr1;
drop trigger tr2;
drop view view_A, view_AA,view_DD;
drop table A,B,AA,DD;
|
