Debian Codegraph @

mysql-test/suite/innodb/r/stat_tables.result

raw
1 rename table mysql.table_stats to mysql.table_stats_save;
2 flush tables;
3 set use_stat_tables= PREFERABLY;
4 create table t1 (a int) engine=InnoDB;
5 start transaction;
6 insert t1 values (1);
7 insert t1 values (2);
8 commit;
9 select * from t1;
10 a
11 1
12 2
13 drop table t1;
14 rename table mysql.table_stats_save to mysql.table_stats;
15 flush tables;
16 #
17 # MDEV-26753 Assertion state == TRX_STATE_PREPARED ||... failed
18 #
19 CREATE TABLE t1(a INT PRIMARY KEY) ENGINE=InnoDB;
20 XA START 'test';
21 SELECT COUNT(*)>0 FROM mysql.innodb_index_stats LOCK IN SHARE MODE;
22 COUNT(*)>0
23 1
24 INSERT INTO t1 VALUES (1),(2);
25 UPDATE mysql.innodb_table_stats SET last_update=NULL WHERE table_name='t1';
26 XA END 'test';
27 XA ROLLBACK 'test';
28 DROP TABLE t1;
29 #
30 # 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"
31 #
32 #
33 # Testing a non-default format: Field_timestamp0 - UINT4 based
34 #
35 SET @@global.mysql56_temporal_format=0;
36 ALTER TABLE mysql.innodb_table_stats MODIFY last_update TIMESTAMP NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp();
37 ALTER TABLE mysql.innodb_index_stats MODIFY last_update TIMESTAMP NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp();
38 SHOW COLUMNS FROM mysql.innodb_table_stats LIKE 'last_update';
39 Field Type Null Key Default Extra
40 last_update timestamp /* mariadb-5.3 */ NO current_timestamp() on update current_timestamp()
41 SHOW COLUMNS FROM mysql.innodb_index_stats LIKE 'last_update';
42 Field Type Null Key Default Extra
43 last_update timestamp /* mariadb-5.3 */ NO current_timestamp() on update current_timestamp()
44 CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB STATS_PERSISTENT=1;
45 SELECT TIMESTAMPDIFF(DAY,last_update,now())<=1 FROM mysql.innodb_table_stats
46 WHERE database_name='test' AND table_name='t1';
47 TIMESTAMPDIFF(DAY,last_update,now())<=1
48 1
49 SELECT TIMESTAMPDIFF(DAY,last_update,now())<=1 FROM mysql.innodb_index_stats
50 WHERE database_name='test' AND table_name='t1' AND stat_name='size';
51 TIMESTAMPDIFF(DAY,last_update,now())<=1
52 1
53 DROP TABLE t1;
54 #
55 # Now as the table t1 is dropped, expect no statistics
56 #
57 SELECT * FROM mysql.innodb_table_stats
58 WHERE database_name='test' AND table_name='t1';
59 database_name table_name last_update n_rows clustered_index_size sum_of_other_index_sizes
60 SELECT * FROM mysql.innodb_index_stats
61 WHERE database_name='test' AND table_name='t1' AND stat_name='size';
62 database_name table_name index_name last_update stat_name stat_value sample_size stat_description
63 #
64 # Testing with the default format: Field_timestampf - BINARY(4) based with the UNSIGNED_FLAG
65 #
66 SET @@global.mysql56_temporal_format=1;
67 ALTER TABLE mysql.innodb_table_stats MODIFY last_update TIMESTAMP NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp();
68 ALTER TABLE mysql.innodb_index_stats MODIFY last_update TIMESTAMP NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp();
69 SHOW COLUMNS FROM mysql.innodb_table_stats LIKE 'last_update';
70 Field Type Null Key Default Extra
71 last_update timestamp NO current_timestamp() on update current_timestamp()
72 SHOW COLUMNS FROM mysql.innodb_index_stats LIKE 'last_update';
73 Field Type Null Key Default Extra
74 last_update timestamp NO current_timestamp() on update current_timestamp()
75 CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB STATS_PERSISTENT=1;
76 SELECT TIMESTAMPDIFF(DAY,last_update,now())<=1 FROM mysql.innodb_table_stats
77 WHERE database_name='test' AND table_name='t1';
78 TIMESTAMPDIFF(DAY,last_update,now())<=1
79 1
80 SELECT TIMESTAMPDIFF(DAY,last_update,now())<=1 FROM mysql.innodb_index_stats
81 WHERE database_name='test' AND table_name='t1' AND stat_name='size';
82 TIMESTAMPDIFF(DAY,last_update,now())<=1
83 1
84 DROP TABLE t1;
85 #
86 # MDEV-34207: ALTER TABLE...STATS_PERSISTENT=0 fails to drop statistics
87 #
88 CREATE TABLE t1 (c1 INT) ENGINE=InnoDB STATS_PERSISTENT 1;
89 ALTER TABLE t1 STATS_PERSISTENT 0;
90 DROP TABLE t1;
91 SET @save_persistent=@@GLOBAL.innodb_stats_persistent;
92 SET GLOBAL innodb_stats_persistent=1;
93 CREATE TABLE t2 (c1 INT) ENGINE=InnoDB;
94 RENAME TABLE t2 TO t1;
95 DROP TABLE t1;
96 SET GLOBAL innodb_stats_persistent=0;
97 CREATE TABLE t2 (c1 INT) ENGINE=InnoDB STATS_PERSISTENT 1;
98 RENAME TABLE t2 TO t1;
99 SET GLOBAL innodb_stats_persistent=@save_persistent;
100 DROP TABLE t1;
101 CREATE TABLE t1 (c1 INT) ENGINE=InnoDB STATS_PERSISTENT 1;
102 DROP TABLE t1;
103 # End of 10.6 tests
104 #
105 # MDEV-36373 Warning: ... persistent statistics storage is corrupted
106 #
107 CREATE TABLE t1 (c INT) ENGINE=InnoDB;
108 SET STATEMENT transaction_read_only=1 FOR ANALYZE TABLE t1;
109 Table Op Msg_type Msg_text
110 test.t1 analyze status Engine-independent statistics collected
111 test.t1 analyze status OK
112 DROP TABLE t1;
113 # End of 10.11 tests
114 #
115 # MDEV-38832 Assertion `!commit_lsn' failed in void trx_t::free()
116 #
117 CREATE DATABASE test_1;
118 CREATE TABLE test_1.t1(f1 INT NOT NULL)Engine=InnoDB;
119 SET @old_stats=@@global.innodb_stats_persistent;
120 SET @flush_log_at_commit=@@global.innodb_flush_log_at_trx_commit;
121 SET GLOBAL innodb_stats_persistent=OFF;
122 SET GLOBAL innodb_flush_log_at_trx_commit=0;
123 DROP TABLE test_1.t1;
124 DROP DATABASE test_1;
125 SET GLOBAL innodb_stats_persistent=@old_stats;
126 SET GLOBAL innodb_flush_log_at_trx_commit=@flush_log_at_commit;