TRUNCATE TABLE test_innodb_stats; SELECT 'dummy INSERT, the table should be empty'; dummy INSERT, the table should be empty dummy INSERT, the table should be empty ANALYZE TABLE test_innodb_stats; Table Op Msg_type Msg_text test.test_innodb_stats analyze status OK SELECT stat_name, stat_value, sample_size, stat_description FROM mysql.innodb_index_stats WHERE database_name = DATABASE() AND table_name = 'test_innodb_stats' AND index_name = 'a_key' AND stat_name IN ('n_diff_pfx01', 'n_diff_pfx02', 'n_leaf_pages', 'size') ORDER BY stat_name; stat_name n_diff_pfx01 stat_value 0 sample_size 1 stat_description a stat_name n_diff_pfx02 stat_value 0 sample_size 1 stat_description a,DB_ROW_ID stat_name n_leaf_pages stat_value 1 sample_size NULL stat_description Number of leaf pages in the index stat_name size stat_value 1 sample_size NULL stat_description Number of pages in the index FLUSH TABLE test_innodb_stats; SELECT * FROM information_schema.statistics WHERE table_name = 'test_innodb_stats'; TABLE_CATALOG def TABLE_SCHEMA test TABLE_NAME test_innodb_stats NON_UNIQUE 1 INDEX_SCHEMA test INDEX_NAME a_key SEQ_IN_INDEX 1 COLUMN_NAME a COLLATION A CARDINALITY 0 SUB_PART NULL PACKED NULL NULLABLE YES INDEX_TYPE BTREE COMMENT INDEX_COMMENT IGNORED NO TRUNCATE TABLE test_innodb_stats; INSERT INTO test_innodb_stats (a) VALUES (1); ANALYZE TABLE test_innodb_stats; Table Op Msg_type Msg_text test.test_innodb_stats analyze status OK SELECT stat_name, stat_value, sample_size, stat_description FROM mysql.innodb_index_stats WHERE database_name = DATABASE() AND table_name = 'test_innodb_stats' AND index_name = 'a_key' AND stat_name IN ('n_diff_pfx01', 'n_diff_pfx02', 'n_leaf_pages', 'size') ORDER BY stat_name; stat_name n_diff_pfx01 stat_value 1 sample_size 1 stat_description a stat_name n_diff_pfx02 stat_value 1 sample_size 1 stat_description a,DB_ROW_ID stat_name n_leaf_pages stat_value 1 sample_size NULL stat_description Number of leaf pages in the index stat_name size stat_value 1 sample_size NULL stat_description Number of pages in the index FLUSH TABLE test_innodb_stats; SELECT * FROM information_schema.statistics WHERE table_name = 'test_innodb_stats'; TABLE_CATALOG def TABLE_SCHEMA test TABLE_NAME test_innodb_stats NON_UNIQUE 1 INDEX_SCHEMA test INDEX_NAME a_key SEQ_IN_INDEX 1 COLUMN_NAME a COLLATION A CARDINALITY 1 SUB_PART NULL PACKED NULL NULLABLE YES INDEX_TYPE BTREE COMMENT INDEX_COMMENT IGNORED NO TRUNCATE TABLE test_innodb_stats; INSERT INTO test_innodb_stats (a) VALUES (1), (1); ANALYZE TABLE test_innodb_stats; Table Op Msg_type Msg_text test.test_innodb_stats analyze status OK SELECT stat_name, stat_value, sample_size, stat_description FROM mysql.innodb_index_stats WHERE database_name = DATABASE() AND table_name = 'test_innodb_stats' AND index_name = 'a_key' AND stat_name IN ('n_diff_pfx01', 'n_diff_pfx02', 'n_leaf_pages', 'size') ORDER BY stat_name; stat_name n_diff_pfx01 stat_value 1 sample_size 1 stat_description a stat_name n_diff_pfx02 stat_value 2 sample_size 1 stat_description a,DB_ROW_ID stat_name n_leaf_pages stat_value 1 sample_size NULL stat_description Number of leaf pages in the index stat_name size stat_value 1 sample_size NULL stat_description Number of pages in the index FLUSH TABLE test_innodb_stats; SELECT * FROM information_schema.statistics WHERE table_name = 'test_innodb_stats'; TABLE_CATALOG def TABLE_SCHEMA test TABLE_NAME test_innodb_stats NON_UNIQUE 1 INDEX_SCHEMA test INDEX_NAME a_key SEQ_IN_INDEX 1 COLUMN_NAME a COLLATION A CARDINALITY 1 SUB_PART NULL PACKED NULL NULLABLE YES INDEX_TYPE BTREE COMMENT INDEX_COMMENT IGNORED NO TRUNCATE TABLE test_innodb_stats; INSERT INTO test_innodb_stats (a) VALUES (1), (1), (1); ANALYZE TABLE test_innodb_stats; Table Op Msg_type Msg_text test.test_innodb_stats analyze status OK SELECT stat_name, stat_value, sample_size, stat_description FROM mysql.innodb_index_stats WHERE database_name = DATABASE() AND table_name = 'test_innodb_stats' AND index_name = 'a_key' AND stat_name IN ('n_diff_pfx01', 'n_diff_pfx02', 'n_leaf_pages', 'size') ORDER BY stat_name; stat_name n_diff_pfx01 stat_value 1 sample_size 1 stat_description a stat_name n_diff_pfx02 stat_value 3 sample_size 1 stat_description a,DB_ROW_ID stat_name n_leaf_pages stat_value 1 sample_size NULL stat_description Number of leaf pages in the index stat_name size stat_value 1 sample_size NULL stat_description Number of pages in the index FLUSH TABLE test_innodb_stats; SELECT * FROM information_schema.statistics WHERE table_name = 'test_innodb_stats'; TABLE_CATALOG def TABLE_SCHEMA test TABLE_NAME test_innodb_stats NON_UNIQUE 1 INDEX_SCHEMA test INDEX_NAME a_key SEQ_IN_INDEX 1 COLUMN_NAME a COLLATION A CARDINALITY 1 SUB_PART NULL PACKED NULL NULLABLE YES INDEX_TYPE BTREE COMMENT INDEX_COMMENT IGNORED NO TRUNCATE TABLE test_innodb_stats; INSERT INTO test_innodb_stats (a) VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1); ANALYZE TABLE test_innodb_stats; Table Op Msg_type Msg_text test.test_innodb_stats analyze status OK SELECT stat_name, stat_value, sample_size, stat_description FROM mysql.innodb_index_stats WHERE database_name = DATABASE() AND table_name = 'test_innodb_stats' AND index_name = 'a_key' AND stat_name IN ('n_diff_pfx01', 'n_diff_pfx02', 'n_leaf_pages', 'size') ORDER BY stat_name; stat_name n_diff_pfx01 stat_value 1 sample_size 1 stat_description a stat_name n_diff_pfx02 stat_value 10 sample_size 1 stat_description a,DB_ROW_ID stat_name n_leaf_pages stat_value 1 sample_size NULL stat_description Number of leaf pages in the index stat_name size stat_value 1 sample_size NULL stat_description Number of pages in the index FLUSH TABLE test_innodb_stats; SELECT * FROM information_schema.statistics WHERE table_name = 'test_innodb_stats'; TABLE_CATALOG def TABLE_SCHEMA test TABLE_NAME test_innodb_stats NON_UNIQUE 1 INDEX_SCHEMA test INDEX_NAME a_key SEQ_IN_INDEX 1 COLUMN_NAME a COLLATION A CARDINALITY 1 SUB_PART NULL PACKED NULL NULLABLE YES INDEX_TYPE BTREE COMMENT INDEX_COMMENT IGNORED NO TRUNCATE TABLE test_innodb_stats; INSERT INTO test_innodb_stats (a) VALUES (1), (2); ANALYZE TABLE test_innodb_stats; Table Op Msg_type Msg_text test.test_innodb_stats analyze status OK SELECT stat_name, stat_value, sample_size, stat_description FROM mysql.innodb_index_stats WHERE database_name = DATABASE() AND table_name = 'test_innodb_stats' AND index_name = 'a_key' AND stat_name IN ('n_diff_pfx01', 'n_diff_pfx02', 'n_leaf_pages', 'size') ORDER BY stat_name; stat_name n_diff_pfx01 stat_value 2 sample_size 1 stat_description a stat_name n_diff_pfx02 stat_value 2 sample_size 1 stat_description a,DB_ROW_ID stat_name n_leaf_pages stat_value 1 sample_size NULL stat_description Number of leaf pages in the index stat_name size stat_value 1 sample_size NULL stat_description Number of pages in the index FLUSH TABLE test_innodb_stats; SELECT * FROM information_schema.statistics WHERE table_name = 'test_innodb_stats'; TABLE_CATALOG def TABLE_SCHEMA test TABLE_NAME test_innodb_stats NON_UNIQUE 1 INDEX_SCHEMA test INDEX_NAME a_key SEQ_IN_INDEX 1 COLUMN_NAME a COLLATION A CARDINALITY 2 SUB_PART NULL PACKED NULL NULLABLE YES INDEX_TYPE BTREE COMMENT INDEX_COMMENT IGNORED NO TRUNCATE TABLE test_innodb_stats; INSERT INTO test_innodb_stats (a) VALUES (1), (1), (2); ANALYZE TABLE test_innodb_stats; Table Op Msg_type Msg_text test.test_innodb_stats analyze status OK SELECT stat_name, stat_value, sample_size, stat_description FROM mysql.innodb_index_stats WHERE database_name = DATABASE() AND table_name = 'test_innodb_stats' AND index_name = 'a_key' AND stat_name IN ('n_diff_pfx01', 'n_diff_pfx02', 'n_leaf_pages', 'size') ORDER BY stat_name; stat_name n_diff_pfx01 stat_value 2 sample_size 1 stat_description a stat_name n_diff_pfx02 stat_value 3 sample_size 1 stat_description a,DB_ROW_ID stat_name n_leaf_pages stat_value 1 sample_size NULL stat_description Number of leaf pages in the index stat_name size stat_value 1 sample_size NULL stat_description Number of pages in the index FLUSH TABLE test_innodb_stats; SELECT * FROM information_schema.statistics WHERE table_name = 'test_innodb_stats'; TABLE_CATALOG def TABLE_SCHEMA test TABLE_NAME test_innodb_stats NON_UNIQUE 1 INDEX_SCHEMA test INDEX_NAME a_key SEQ_IN_INDEX 1 COLUMN_NAME a COLLATION A CARDINALITY 3 SUB_PART NULL PACKED NULL NULLABLE YES INDEX_TYPE BTREE COMMENT INDEX_COMMENT IGNORED NO TRUNCATE TABLE test_innodb_stats; INSERT INTO test_innodb_stats (a) VALUES (1), (2), (3); ANALYZE TABLE test_innodb_stats; Table Op Msg_type Msg_text test.test_innodb_stats analyze status OK SELECT stat_name, stat_value, sample_size, stat_description FROM mysql.innodb_index_stats WHERE database_name = DATABASE() AND table_name = 'test_innodb_stats' AND index_name = 'a_key' AND stat_name IN ('n_diff_pfx01', 'n_diff_pfx02', 'n_leaf_pages', 'size') ORDER BY stat_name; stat_name n_diff_pfx01 stat_value 3 sample_size 1 stat_description a stat_name n_diff_pfx02 stat_value 3 sample_size 1 stat_description a,DB_ROW_ID stat_name n_leaf_pages stat_value 1 sample_size NULL stat_description Number of leaf pages in the index stat_name size stat_value 1 sample_size NULL stat_description Number of pages in the index FLUSH TABLE test_innodb_stats; SELECT * FROM information_schema.statistics WHERE table_name = 'test_innodb_stats'; TABLE_CATALOG def TABLE_SCHEMA test TABLE_NAME test_innodb_stats NON_UNIQUE 1 INDEX_SCHEMA test INDEX_NAME a_key SEQ_IN_INDEX 1 COLUMN_NAME a COLLATION A CARDINALITY 3 SUB_PART NULL PACKED NULL NULLABLE YES INDEX_TYPE BTREE COMMENT INDEX_COMMENT IGNORED NO TRUNCATE TABLE test_innodb_stats; INSERT INTO test_innodb_stats (a) VALUES (1), (1), (2), (3), (3); ANALYZE TABLE test_innodb_stats; Table Op Msg_type Msg_text test.test_innodb_stats analyze status OK SELECT stat_name, stat_value, sample_size, stat_description FROM mysql.innodb_index_stats WHERE database_name = DATABASE() AND table_name = 'test_innodb_stats' AND index_name = 'a_key' AND stat_name IN ('n_diff_pfx01', 'n_diff_pfx02', 'n_leaf_pages', 'size') ORDER BY stat_name; stat_name n_diff_pfx01 stat_value 3 sample_size 1 stat_description a stat_name n_diff_pfx02 stat_value 5 sample_size 1 stat_description a,DB_ROW_ID stat_name n_leaf_pages stat_value 1 sample_size NULL stat_description Number of leaf pages in the index stat_name size stat_value 1 sample_size NULL stat_description Number of pages in the index FLUSH TABLE test_innodb_stats; SELECT * FROM information_schema.statistics WHERE table_name = 'test_innodb_stats'; TABLE_CATALOG def TABLE_SCHEMA test TABLE_NAME test_innodb_stats NON_UNIQUE 1 INDEX_SCHEMA test INDEX_NAME a_key SEQ_IN_INDEX 1 COLUMN_NAME a COLLATION A CARDINALITY 5 SUB_PART NULL PACKED NULL NULLABLE YES INDEX_TYPE BTREE COMMENT INDEX_COMMENT IGNORED NO TRUNCATE TABLE test_innodb_stats; INSERT INTO test_innodb_stats (a) VALUES (1), (2), (3), (4), (5), (1), (2), (3), (4), (5); ANALYZE TABLE test_innodb_stats; Table Op Msg_type Msg_text test.test_innodb_stats analyze status OK SELECT stat_name, stat_value, sample_size, stat_description FROM mysql.innodb_index_stats WHERE database_name = DATABASE() AND table_name = 'test_innodb_stats' AND index_name = 'a_key' AND stat_name IN ('n_diff_pfx01', 'n_diff_pfx02', 'n_leaf_pages', 'size') ORDER BY stat_name; stat_name n_diff_pfx01 stat_value 5 sample_size 1 stat_description a stat_name n_diff_pfx02 stat_value 10 sample_size 1 stat_description a,DB_ROW_ID stat_name n_leaf_pages stat_value 1 sample_size NULL stat_description Number of leaf pages in the index stat_name size stat_value 1 sample_size NULL stat_description Number of pages in the index FLUSH TABLE test_innodb_stats; SELECT * FROM information_schema.statistics WHERE table_name = 'test_innodb_stats'; TABLE_CATALOG def TABLE_SCHEMA test TABLE_NAME test_innodb_stats NON_UNIQUE 1 INDEX_SCHEMA test INDEX_NAME a_key SEQ_IN_INDEX 1 COLUMN_NAME a COLLATION A CARDINALITY 5 SUB_PART NULL PACKED NULL NULLABLE YES INDEX_TYPE BTREE COMMENT INDEX_COMMENT IGNORED NO # # MDEV-31740 InnoDB statistics column length validation failed # call mtr.add_suppression("InnoDB: Unexpected length of mysql\\.innodb_table_stats\\.last_update"); ALTER TABLE mysql.innodb_table_stats MODIFY LAST_UPDATE DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP(); CREATE TABLE t (a INT KEY)Engine=InnoDB STATS_PERSISTENT=1; FOUND 1 /InnoDB: Unexpected length of mysql\.innodb_table_stats\.last_update/ in mysqld.1.err ALTER TABLE mysql.innodb_table_stats MODIFY last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP(); CREATE TABLE t2(a INT KEY)ENGINE=InnoDB STATS_PERSISTENT= 1; SELECT table_name FROM mysql.innodb_table_stats; table_name t2 DROP TABLE t, t2;