1 files changed, 84 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb/t/stat_tables.test b/mysql-test/suite/innodb/t/stat_tables.test
new file mode 100644
index 00000000..ad421587
--- /dev/null
+++ b/mysql-test/suite/innodb/t/stat_tables.test
@@ -0,0 +1,84 @@
+source include/have_innodb.inc;
+
+#
+# MDEV-20354 All but last insert ignored in InnoDB tables when table locked
+#
+rename table mysql.table_stats to mysql.table_stats_save;
+flush tables;
+set use_stat_tables= PREFERABLY;
+create table t1 (a int) engine=InnoDB;
+start transaction;
+insert t1 values (1);
+insert t1 values (2);
+commit;
+select * from t1;
+drop table t1;
+rename table mysql.table_stats_save to mysql.table_stats;
+flush tables;
+
+--echo #
+--echo # MDEV-26753 Assertion state == TRX_STATE_PREPARED ||... failed
+--echo #
+CREATE TABLE t1(a INT PRIMARY KEY) ENGINE=InnoDB;
+XA START 'test';
+SELECT COUNT(*)>0 FROM mysql.innodb_index_stats LOCK IN SHARE MODE;
+INSERT INTO t1 VALUES (1),(2);
+UPDATE mysql.innodb_table_stats SET last_update=NULL WHERE table_name='t1';
+XA END 'test';
+XA ROLLBACK 'test';
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-30483 After upgrade to 10.6 from Mysql 5.7 seeing "InnoDB: Column last_update in table mysql.innodb_table_stats is BINARY(4) NOT NULL but should be INT UNSIGNED NOT NULL"
+--echo #
+
+# The following tests demonstrate that these columns:
+# - innodb_table_stats.last_update
+# - innodb_index_stats.last_update
+# have sane values close to NOW(), rather than any garbage,
+# with all TIMESTAMP formats.
+
+--echo #
+--echo # Testing a non-default format: Field_timestamp0 - UINT4 based
+--echo #
+
+SET @@global.mysql56_temporal_format=0;
+ALTER TABLE mysql.innodb_table_stats MODIFY last_update TIMESTAMP NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp();
+ALTER TABLE mysql.innodb_index_stats MODIFY last_update TIMESTAMP NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp();
+SHOW COLUMNS FROM mysql.innodb_table_stats LIKE 'last_update';
+SHOW COLUMNS FROM mysql.innodb_index_stats LIKE 'last_update';
+CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB STATS_PERSISTENT=1;
+
+SELECT TIMESTAMPDIFF(DAY,last_update,now())<=1 FROM mysql.innodb_table_stats
+WHERE database_name='test' AND table_name='t1';
+SELECT TIMESTAMPDIFF(DAY,last_update,now())<=1 FROM mysql.innodb_index_stats
+WHERE database_name='test' AND table_name='t1' AND stat_name='size';
+DROP TABLE t1;
+
+--echo #
+--echo # Now as the table t1 is dropped, expect no statistics
+--echo #
+
+SELECT * FROM mysql.innodb_table_stats
+WHERE database_name='test' AND table_name='t1';
+SELECT * FROM mysql.innodb_index_stats
+WHERE database_name='test' AND table_name='t1' AND stat_name='size';
+
+--echo #
+--echo # Testing with the default format: Field_timestampf - BINARY(4) based with the UNSIGNED_FLAG
+--echo #
+
+SET @@global.mysql56_temporal_format=1;
+ALTER TABLE mysql.innodb_table_stats MODIFY last_update TIMESTAMP NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp();
+ALTER TABLE mysql.innodb_index_stats MODIFY last_update TIMESTAMP NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp();
+SHOW COLUMNS FROM mysql.innodb_table_stats LIKE 'last_update';
+SHOW COLUMNS FROM mysql.innodb_index_stats LIKE 'last_update';
+CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB STATS_PERSISTENT=1;
+SELECT TIMESTAMPDIFF(DAY,last_update,now())<=1 FROM mysql.innodb_table_stats
+WHERE database_name='test' AND table_name='t1';
+SELECT TIMESTAMPDIFF(DAY,last_update,now())<=1 FROM mysql.innodb_index_stats
+WHERE database_name='test' AND table_name='t1' AND stat_name='size';
+DROP TABLE t1;
+
+
+--echo # End of 10.6 tests