1# include file to test index and table statstics for specific storage engine 2# requires includer set the default strorage engine for the session 3 4# Bug 602047 (wrong rows_read value) 5 6FLUSH INDEX_STATISTICS; 7FLUSH TABLE_STATISTICS; 8 9SET @userstat_old= @@userstat; 10SET GLOBAL userstat=ON; 11 12CREATE TABLE t1 (id int(10), a INT, PRIMARY KEY (id)); 13INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); 14INSERT INTO t1 VALUES (6, 6), (7, 7), (8, 8), (9, 9), (10, 10); 15SELECT MAX(a) FROM t1; 16SELECT ROWS_READ FROM INFORMATION_SCHEMA.TABLE_STATISTICS WHERE TABLE_NAME='t1'; 17SELECT ROWS_READ FROM INFORMATION_SCHEMA.INDEX_STATISTICS WHERE TABLE_NAME='t1'; 18 19# Test that FLUSH clears one table but not another 20 21FLUSH TABLE_STATISTICS; 22 23SELECT ROWS_READ FROM INFORMATION_SCHEMA.TABLE_STATISTICS WHERE TABLE_NAME='t1'; 24SELECT ROWS_READ FROM INFORMATION_SCHEMA.INDEX_STATISTICS WHERE TABLE_NAME='t1'; 25 26# Test that FLUSH clears both tables now 27 28FLUSH INDEX_STATISTICS; 29 30SELECT ROWS_READ FROM INFORMATION_SCHEMA.INDEX_STATISTICS WHERE TABLE_NAME='t1'; 31 32# Test that stats are collected after the FLUSH again 33 34SELECT MAX(a) FROM t1; 35SELECT ROWS_READ FROM INFORMATION_SCHEMA.TABLE_STATISTICS WHERE TABLE_NAME='t1'; 36SELECT ROWS_READ FROM INFORMATION_SCHEMA.INDEX_STATISTICS WHERE TABLE_NAME='t1'; 37 38DROP TABLE t1; 39 40# Bug 1183625 (handler::update_global_table_stats crash). 41 42CREATE TABLE t2 (c1 INT UNSIGNED) ENGINE=InnoDB; 43 44ALTER TABLE t2 MODIFY c1 FLOAT; 45 46SELECT * FROM INFORMATION_SCHEMA.TABLE_STATISTICS WHERE TABLE_NAME='t2'; 47 48DROP TABLE t2; 49 50SET GLOBAL userstat= @userstat_old; 51