Debian Codegraph @

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

raw
1 #
2 # Bug 23333990 PERSISTENT INDEX STATISTICS UPDATE BEFORE
3 # TRANSACTION IS COMMITTED
4 #
5 "Test 1:- Uncommited delete test"
6 CREATE TABLE t1 (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
7 val INT UNSIGNED NOT NULL,
8 INDEX (val)) ENGINE=INNODB
9 STATS_PERSISTENT=1,STATS_AUTO_RECALC=1;
10 INSERT INTO t1 (val) VALUES (4);
11 INSERT INTO t1 (val) SELECT VAL from t1;
12 INSERT INTO t1 (val) SELECT VAL from t1;
13 INSERT INTO t1 (val) SELECT VAL from t1;
14 INSERT INTO t1 (val) SELECT VAL from t1;
15 INSERT INTO t1 (val) SELECT VAL from t1;
16 INSERT INTO t1 (val) SELECT VAL from t1;
17 INSERT INTO t1 (val) SELECT VAL from t1;
18 INSERT INTO t1 (val) SELECT VAL from t1;
19 INSERT INTO t1 (val) SELECT VAL from t1;
20 INSERT INTO t1 (val) SELECT VAL from t1;
21 INSERT INTO t1 (val) SELECT VAL from t1;
22 INSERT INTO t1 (val) SELECT VAL from t1;
23 INSERT INTO t1 (val) SELECT VAL from t1;
24 INSERT INTO t1 (val) SELECT VAL from t1;
25 INSERT INTO t1 (val) SELECT VAL from t1;
26 INSERT INTO t1 (val) SELECT VAL from t1;
27 INSERT INTO t1 (val) SELECT VAL from t1;
28 INSERT INTO t1 (val) SELECT VAL from t1;
29 SELECT COUNT(*) FROM t1;
30 COUNT(*)
31 262144
32 Connection 1
33 START TRANSACTION;
34 DELETE FROM t1;
35 SELECT COUNT(*) FROM t1;
36 Connection Default
37 analyze table t1;
38 Table Op Msg_type Msg_text
39 test.t1 analyze status OK
40 Test correctly estimates the number of rows as > 20000
41 even when in other uncommitted transaction
42 all rows have been deleted.
43 Connection 1
44 COUNT(*)
45 0
46 commit;
47 Connection deafult
48 Test 2:- Insert and rollback test
49 CREATE TABLE t2 (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
50 val INT UNSIGNED NOT NULL,
51 INDEX (val)) ENGINE=INNODB
52 STATS_PERSISTENT=1,STATS_AUTO_RECALC=1;
53 Connection 1
54 START TRANSACTION;
55 INSERT INTO t2 (val) VALUES (4);
56 INSERT INTO t2 (val) SELECT VAL from t2;
57 INSERT INTO t2 (val) SELECT VAL from t2;
58 INSERT INTO t2 (val) SELECT VAL from t2;
59 INSERT INTO t2 (val) SELECT VAL from t2;
60 INSERT INTO t2 (val) SELECT VAL from t2;
61 INSERT INTO t2 (val) SELECT VAL from t2;
62 INSERT INTO t2 (val) SELECT VAL from t2;
63 INSERT INTO t2 (val) SELECT VAL from t2;
64 INSERT INTO t2 (val) SELECT VAL from t2;
65 INSERT INTO t2 (val) SELECT VAL from t2;
66 INSERT INTO t2 (val) SELECT VAL from t2;
67 INSERT INTO t2 (val) SELECT VAL from t2;
68 INSERT INTO t2 (val) SELECT VAL from t2;
69 INSERT INTO t2 (val) SELECT VAL from t2;
70 INSERT INTO t2 (val) SELECT VAL from t2;
71 INSERT INTO t2 (val) SELECT VAL from t2;
72 INSERT INTO t2 (val) SELECT VAL from t2;
73 INSERT INTO t2 (val) SELECT VAL from t2;
74 SELECT COUNT(*) FROM t2;
75 Connection default
76 select count(*) from t2;
77 count(*)
78 0
79 analyze table t2;
80 Table Op Msg_type Msg_text
81 test.t2 analyze status OK
82 Test correctly estimates the number of rows as > 20000
83 even when in other uncommitted transaction
84 many rows are inserted.
85 Connection 1
86 COUNT(*)
87 262144
88 Rollback the insert
89 rollback;
90 Connection default
91 Test correctly estimates the number of rows as 1
92 after rollback.
93 DROP TABLE t1,t2;