Debian Codegraph @

mysql-test/suite/innodb/t/stat_tables.test

raw
1 source include/have_innodb.inc;
2
3 --disable_query_log
4 call mtr.add_suppression("InnoDB: Transaction was aborted due to ");
5 --enable_query_log
6
7 #
8 # MDEV-20354 All but last insert ignored in InnoDB tables when table locked
9 #
10 rename table mysql.table_stats to mysql.table_stats_save;
11 flush tables;
12 set use_stat_tables= PREFERABLY;
13 create table t1 (a int) engine=InnoDB;
14 start transaction;
15 insert t1 values (1);
16 insert t1 values (2);
17 commit;
18 select * from t1;
19 drop table t1;
20 rename table mysql.table_stats_save to mysql.table_stats;
21 flush tables;
22
23 --echo #
24 --echo # MDEV-26753 Assertion state == TRX_STATE_PREPARED ||... failed
25 --echo #
26 CREATE TABLE t1(a INT PRIMARY KEY) ENGINE=InnoDB;
27 XA START 'test';
28 SELECT COUNT(*)>0 FROM mysql.innodb_index_stats LOCK IN SHARE MODE;
29 INSERT INTO t1 VALUES (1),(2);
30 UPDATE mysql.innodb_table_stats SET last_update=NULL WHERE table_name='t1';
31 XA END 'test';
32 XA ROLLBACK 'test';
33 DROP TABLE t1;
34
35 --echo #
36 --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"
37 --echo #
38
39 # The following tests demonstrate that these columns:
40 # - innodb_table_stats.last_update
41 # - innodb_index_stats.last_update
42 # have sane values close to NOW(), rather than any garbage,
43 # with all TIMESTAMP formats.
44
45 --echo #
46 --echo # Testing a non-default format: Field_timestamp0 - UINT4 based
47 --echo #
48
49 SET @@global.mysql56_temporal_format=0;
50 ALTER TABLE mysql.innodb_table_stats MODIFY last_update TIMESTAMP NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp();
51 ALTER TABLE mysql.innodb_index_stats MODIFY last_update TIMESTAMP NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp();
52 SHOW COLUMNS FROM mysql.innodb_table_stats LIKE 'last_update';
53 SHOW COLUMNS FROM mysql.innodb_index_stats LIKE 'last_update';
54 CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB STATS_PERSISTENT=1;
55
56 SELECT TIMESTAMPDIFF(DAY,last_update,now())<=1 FROM mysql.innodb_table_stats
57 WHERE database_name='test' AND table_name='t1';
58 SELECT TIMESTAMPDIFF(DAY,last_update,now())<=1 FROM mysql.innodb_index_stats
59 WHERE database_name='test' AND table_name='t1' AND stat_name='size';
60 DROP TABLE t1;
61
62 --echo #
63 --echo # Now as the table t1 is dropped, expect no statistics
64 --echo #
65
66 SELECT * FROM mysql.innodb_table_stats
67 WHERE database_name='test' AND table_name='t1';
68 SELECT * FROM mysql.innodb_index_stats
69 WHERE database_name='test' AND table_name='t1' AND stat_name='size';
70
71 --echo #
72 --echo # Testing with the default format: Field_timestampf - BINARY(4) based with the UNSIGNED_FLAG
73 --echo #
74
75 SET @@global.mysql56_temporal_format=1;
76 ALTER TABLE mysql.innodb_table_stats MODIFY last_update TIMESTAMP NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp();
77 ALTER TABLE mysql.innodb_index_stats MODIFY last_update TIMESTAMP NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp();
78 SHOW COLUMNS FROM mysql.innodb_table_stats LIKE 'last_update';
79 SHOW COLUMNS FROM mysql.innodb_index_stats LIKE 'last_update';
80 CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB STATS_PERSISTENT=1;
81 SELECT TIMESTAMPDIFF(DAY,last_update,now())<=1 FROM mysql.innodb_table_stats
82 WHERE database_name='test' AND table_name='t1';
83 SELECT TIMESTAMPDIFF(DAY,last_update,now())<=1 FROM mysql.innodb_index_stats
84 WHERE database_name='test' AND table_name='t1' AND stat_name='size';
85 DROP TABLE t1;
86
87 --echo #
88 --echo # MDEV-34207: ALTER TABLE...STATS_PERSISTENT=0 fails to drop statistics
89 --echo #
90 CREATE TABLE t1 (c1 INT) ENGINE=InnoDB STATS_PERSISTENT 1;
91 ALTER TABLE t1 STATS_PERSISTENT 0;
92 DROP TABLE t1;
93
94 SET @save_persistent=@@GLOBAL.innodb_stats_persistent;
95 SET GLOBAL innodb_stats_persistent=1;
96
97 CREATE TABLE t2 (c1 INT) ENGINE=InnoDB;
98 RENAME TABLE t2 TO t1;
99
100 DROP TABLE t1;
101 SET GLOBAL innodb_stats_persistent=0;
102
103 CREATE TABLE t2 (c1 INT) ENGINE=InnoDB STATS_PERSISTENT 1;
104 RENAME TABLE t2 TO t1;
105
106 SET GLOBAL innodb_stats_persistent=@save_persistent;
107 DROP TABLE t1;
108
109 CREATE TABLE t1 (c1 INT) ENGINE=InnoDB STATS_PERSISTENT 1;
110 DROP TABLE t1;
111
112 --echo # End of 10.6 tests
113
114 --echo #
115 --echo # MDEV-36373 Warning: ... persistent statistics storage is corrupted
116 --echo #
117 CREATE TABLE t1 (c INT) ENGINE=InnoDB;
118 SET STATEMENT transaction_read_only=1 FOR ANALYZE TABLE t1;
119 DROP TABLE t1;
120
121 --echo # End of 10.11 tests
122
123 --echo #
124 --echo # MDEV-38832 Assertion `!commit_lsn' failed in void trx_t::free()
125 --echo #
126 CREATE DATABASE test_1;
127 CREATE TABLE test_1.t1(f1 INT NOT NULL)Engine=InnoDB;
128 SET @old_stats=@@global.innodb_stats_persistent;
129 SET @flush_log_at_commit=@@global.innodb_flush_log_at_trx_commit;
130 SET GLOBAL innodb_stats_persistent=OFF;
131 SET GLOBAL innodb_flush_log_at_trx_commit=0;
132 DROP TABLE test_1.t1;
133 DROP DATABASE test_1;
134 SET GLOBAL innodb_stats_persistent=@old_stats;
135 SET GLOBAL innodb_flush_log_at_trx_commit=@flush_log_at_commit;