1drop table if exists t1; 2Warnings: 3Note 1051 Unknown table 'test.t1' 4drop view if exists view_t1; 5Warnings: 6Note 4092 Unknown VIEW: 'test.view_t1' 7SET sql_mode=ONLY_FULL_GROUP_BY; 8CREATE TABLE t1 ( 9pk INT, 10f0 INT, f1 INT, f2 INT, f3 INT, f4 INT, 11f5 INT, f6 INT, f7 INT, f8 INT, f9 INT, 12PRIMARY KEY (pk) 13); 14CREATE VIEW view_t1 AS SELECT * FROM t1; 15CREATE PROCEDURE s1() 16SELECT * FROM ( 17INFORMATION_SCHEMA.`INNODB_BUFFER_PAGE_LRU` AS table1 18LEFT JOIN test.view_t1 AS table2 19ON ( table2.`f6` = table1.FREE_PAGE_CLOCK) 20) 21ORDER BY table1.NUMBER_RECORDS 22LIMIT 0 23; 24CALL s1; 25POOL_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 26CALL s1; 27POOL_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 28drop table t1; 29drop view view_t1; 30drop procedure s1; 31CREATE TABLE A ( 32pk INTEGER AUTO_INCREMENT, 33col_int_key INTEGER, 34col_varchar_key VARCHAR(1), 35PRIMARY KEY (pk) 36) ENGINE=MyISAM; 37CREATE VIEW view_A AS SELECT * FROM A; 38CREATE TABLE C ( 39pk INTEGER AUTO_INCREMENT, 40col_int_nokey INTEGER, 41col_int_key INTEGER, 42col_date_key DATE, 43col_date_nokey DATE, 44col_time_key TIME, 45col_time_nokey TIME, 46col_datetime_key DATETIME, 47col_datetime_nokey DATETIME, 48col_varchar_key VARCHAR(1), 49col_varchar_nokey VARCHAR(1), 50PRIMARY KEY (pk) 51) ENGINE=MyISAM; 52CREATE VIEW view_C AS SELECT * FROM C; 53CREATE TABLE AA ( 54pk INTEGER AUTO_INCREMENT, 55col_int_nokey INTEGER, 56col_int_key INTEGER, 57col_date_key DATE, 58col_date_nokey DATE, 59col_time_key TIME, 60col_time_nokey TIME, 61col_datetime_key DATETIME, 62col_datetime_nokey DATETIME, 63col_varchar_key VARCHAR(1), 64col_varchar_nokey VARCHAR(1), 65PRIMARY KEY (pk), 66KEY (col_varchar_key, col_int_key) 67) ENGINE=MyISAM; 68CREATE VIEW view_AA AS SELECT * FROM AA; 69CREATE TABLE BB ( 70pk INTEGER AUTO_INCREMENT, 71col_int_key INTEGER, 72col_varchar_key VARCHAR(1), 73col_varchar_nokey VARCHAR(1), 74PRIMARY KEY (pk), 75KEY (col_varchar_key, col_int_key) 76) ENGINE=MyISAM; 77CREATE VIEW view_BB AS SELECT * FROM BB; 78CREATE TABLE DD ( 79pk INTEGER AUTO_INCREMENT, 80col_int_key INTEGER, 81col_date_key DATE, 82col_time_key TIME, 83col_datetime_key DATETIME, 84col_varchar_key VARCHAR(1), 85PRIMARY KEY (pk), 86KEY (col_varchar_key, col_int_key) 87) ENGINE=MyISAM; 88CREATE VIEW view_DD AS SELECT * FROM DD; 89CREATE TRIGGER k BEFORE INSERT ON `DD` FOR EACH ROW INSERT INTO `view_BB` SELECT * FROM `view_A` LIMIT 0 ; 90CREATE TRIGGER r BEFORE INSERT ON `A` FOR EACH ROW INSERT INTO `view_AA` SELECT * FROM `view_C` LIMIT 0 ; 91ALTER TABLE `DD` DROP PRIMARY KEY; 92ERROR 42000: Incorrect table definition; there can be only one auto column and it must be defined as a key 93INSERT INTO `view_A` ( `pk` ) VALUES (NULL); 94INSERT INTO `DD` ( `pk` ) VALUES (NULL); 95INSERT INTO `A` ( `pk` ) VALUES (NULL); 96INSERT INTO `view_DD` ( `pk` ) VALUES (NULL); 97drop trigger r; 98drop trigger k; 99drop view view_A,view_AA,view_C,view_BB,view_DD; 100drop table A,C,AA,BB,DD; 101CREATE TABLE A ( 102i INT, 103i1 INT, 104i2 INT, 105d1 DATE, 106d2 DATE, 107col_time_nokey1 TIME, 108col_time_nokey2 TIME, 109col_datetime_nokey1 DATETIME, 110col_datetime_nokey2 DATETIME, 111col_varchar_nokey1 VARCHAR(1), 112col_varchar_nokey2 VARCHAR(1) 113) ENGINE=MyISAM; 114CREATE VIEW view_A AS SELECT * FROM A; 115CREATE TABLE B ( 116col_varchar_nokey VARCHAR(1) 117) ENGINE=MyISAM; 118CREATE TABLE AA ( 119i INT, 120i1 INT, 121i2 INT, 122d1 DATE, 123d2 DATE, 124col_time_nokey1 TIME, 125col_time_nokey2 TIME, 126col_datetime_nokey1 DATETIME, 127col_datetime_nokey2 DATETIME, 128col_varchar_nokey1 VARCHAR(1), 129col_varchar_nokey2 VARCHAR(1) 130) ENGINE=MyISAM; 131CREATE VIEW view_AA AS SELECT * FROM AA; 132CREATE TABLE DD ( 133i INT, 134i1 INT, 135i2 INT, 136d1 DATE, 137d2 DATE, 138col_time_nokey1 TIME, 139col_time_nokey2 TIME, 140col_datetime_nokey1 DATETIME, 141col_datetime_nokey2 DATETIME, 142col_varchar_nokey1 VARCHAR(1), 143col_varchar_nokey2 VARCHAR(1) 144) ENGINE=MyISAM; 145CREATE VIEW view_DD AS SELECT * FROM DD; 146CREATE TRIGGER tr1 BEFORE INSERT ON `AA` FOR EACH ROW INSERT INTO `view_A` SELECT * FROM `view_AA` LIMIT 0 ; 147CREATE TRIGGER tr2 BEFORE INSERT ON `B` FOR EACH ROW INSERT INTO `D` SELECT * FROM `A` LIMIT 0 ; 148INSERT INTO `view_AA` ( `i` ) VALUES (1); 149INSERT INTO `AA` ( `i` ) VALUES (2); 150DELETE FROM `B`; 151INSERT INTO `view_DD` ( `i` ) VALUES (1); 152INSERT INTO `view_AA` ( `i` ) VALUES (3); 153drop trigger tr1; 154drop trigger tr2; 155drop view view_A, view_AA,view_DD; 156drop table A,B,AA,DD; 157