Debian Codegraph @

mysql-test/suite/ndbcluster/table_stats.result

raw
1 # ###################################################################
2 # Show that local table stats are correctly reflecting number of
3 # records in table at failure to insert, both with and without trans
4 # - in particular this shows how the "uncomitted rows" value in Thd_ndb
5 # is handled
6 CREATE TABLE t1 (
7 a int PRIMARY KEY,
8 b varchar(255)
9 ) ENGINE = NDB;
10 INSERT INTO t1 VALUES (1, "this is in the way");
11 SELECT COUNT(*) as "count should be 1" FROM t1;
12 count should be 1
13 1
14 INSERT INTO t1 VALUES (1, "duplicate key");
15 ERROR 23000: Duplicate entry '1' for key 't1.PRIMARY'
16 SELECT COUNT(*) as "count should still be 1" FROM t1;
17 count should still be 1
18 1
19 INSERT INTO t1 VALUES
20 (2, "val2"), (3, "val3"), (4, "val4"),
21 (1, "duplicate key, 3 uncommitted");
22 ERROR 23000: Duplicate entry '1' for key 't1.PRIMARY'
23 SELECT COUNT(*) as "count should still be 1" FROM t1;
24 count should still be 1
25 1
26 BEGIN;
27 INSERT INTO t1 VALUES (1, "duplicate key, inside transaction");
28 ERROR 23000: Duplicate entry '1' for key 't1.PRIMARY'
29 SELECT COUNT(*) as "count should still be 1" FROM t1;
30 count should still be 1
31 1
32 COMMIT;
33 SELECT COUNT(*) as "count should still be 1" FROM t1;
34 count should still be 1
35 1
36 BEGIN;
37 INSERT INTO t1 VALUES
38 (2, "val2"), (3, "val3"), (4, "val4");
39 SELECT COUNT(*) as "count should be 4" FROM t1;
40 count should be 4
41 4
42 INSERT INTO t1 VALUES (1, "duplicate key, inside transaction, 3 uncommitted");
43 ERROR 23000: Duplicate entry '1' for key 't1.PRIMARY'
44 SELECT COUNT(*) as "count should still be 1, whole trans aborted" FROM t1;
45 count should still be 1, whole trans aborted
46 1
47 COMMIT;
48 SELECT COUNT(*) as "count should still be 1" FROM t1;
49 count should still be 1
50 1
51 BEGIN;
52 INSERT INTO t1 VALUES (1, "duplicate key, inside transaction");
53 ERROR 23000: Duplicate entry '1' for key 't1.PRIMARY'
54 SELECT COUNT(*) as "count should still be 1" FROM t1;
55 count should still be 1
56 1
57 ROLLBACK;
58 SELECT COUNT(*) as "count should still be 1" FROM t1;
59 count should still be 1
60 1
61 BEGIN;
62 INSERT INTO t1 VALUES
63 (2, "val2"), (3, "val3"), (4, "val4");
64 SELECT COUNT(*) as "count should be 4" FROM t1;
65 count should be 4
66 4
67 INSERT INTO t1 VALUES (1, "duplicate key, inside transaction, 3 uncommitted");
68 ERROR 23000: Duplicate entry '1' for key 't1.PRIMARY'
69 SELECT COUNT(*) as "count should still be 1, whole trans aborted" FROM t1;
70 count should still be 1, whole trans aborted
71 1
72 ROLLBACK;
73 SELECT COUNT(*) as "count should still be 1" FROM t1;
74 count should still be 1
75 1
76 CREATE TABLE t2 (
77 a int PRIMARY KEY,
78 b varchar(255)
79 ) ENGINE = NDB;
80 INSERT INTO t2 VALUES
81 (10, "val10"), (12, "val12"), (13, "val13"), (14, "val14"), (15, "val15");
82 BEGIN;
83 INSERT INTO t2 VALUES (1, "val1"), (2, "val2"), (3, "val3"), (4, "val4");
84 SELECT COUNT(*) as "count should be 9" FROM t2;
85 count should be 9
86 9
87 INSERT INTO t1 VALUES (1, "duplicate key, inside transaction");
88 ERROR 23000: Duplicate entry '1' for key 't1.PRIMARY'
89 SELECT COUNT(*) as "count should still be 1" FROM t1;
90 count should still be 1
91 1
92 SELECT COUNT(*) as "count should be 5, trans aborted" FROM t2;
93 count should be 5, trans aborted
94 5
95 COMMIT;
96 SELECT COUNT(*) as "count should still be 1" FROM t1;
97 count should still be 1
98 1
99 SELECT COUNT(*) as "count should be 5" FROM t2;
100 count should be 5
101 5
102 DROP TABLE t2;
103 DROP TABLE t1;
104 # ###################################################################
105 # Test I_S and SHOW commands that present table stats
106 #
107 # Test with 0, 1, 2 and 10 rows in the table since special case exists
108 # for masking out 0 and 1 row for other queries.
109 #
110 # NOTE! This test shows that only SHOW INDEX and SHOW TABLE STATUS
111 # bypass the "records = min(records, 2)" while all the I_S queries
112 # never show less than 2 rows.
113 #
114 # Turn off "cached statistics" in INFORMATION_SCHEMA to always fetch
115 # latest stats from storage engine
116 set @save_information_schema_stats_expiry = @@information_schema_stats_expiry;
117 set @@information_schema_stats_expiry=0;
118 #
119 # Create table with only one primary key index
120 CREATE TABLE t1 (
121 a int PRIMARY KEY,
122 b varchar(255),
123 c int NOT NULL
124 ) ENGINE = NDB;
125 #### TEST LOOP 0 ####
126 SELECT COUNT(*) as "rows in table" FROM t1;
127 rows in table
128 0
129 # SELECT .. FROM INFORMATION_SCHEMA.TABLES
130 SELECT TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES
131 WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1';
132 TABLE_ROWS
133 2
134 # SHOW TABLE STATUS
135 Rows: 0
136 # SELECT ... FROM INFORMATION_SCHEMA.STATISTICS
137 SELECT CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS
138 WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1';
139 CARDINALITY
140 2
141 # SHOW INDEX (aka. SHOW INDEXES or SHOW KEYS)
142 Cardinality: 0
143 INSERT INTO t1 VALUES (0, "Row 0", 37+0);
144 #### TEST LOOP 1 ####
145 SELECT COUNT(*) as "rows in table" FROM t1;
146 rows in table
147 1
148 # SELECT .. FROM INFORMATION_SCHEMA.TABLES
149 SELECT TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES
150 WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1';
151 TABLE_ROWS
152 2
153 # SHOW TABLE STATUS
154 Rows: 1
155 # SELECT ... FROM INFORMATION_SCHEMA.STATISTICS
156 SELECT CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS
157 WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1';
158 CARDINALITY
159 2
160 # SHOW INDEX (aka. SHOW INDEXES or SHOW KEYS)
161 Cardinality: 1
162 INSERT INTO t1 VALUES (1, "Row 1", 37+1);
163 #### TEST LOOP 2 ####
164 SELECT COUNT(*) as "rows in table" FROM t1;
165 rows in table
166 2
167 # SELECT .. FROM INFORMATION_SCHEMA.TABLES
168 SELECT TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES
169 WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1';
170 TABLE_ROWS
171 2
172 # SHOW TABLE STATUS
173 Rows: 2
174 # SELECT ... FROM INFORMATION_SCHEMA.STATISTICS
175 SELECT CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS
176 WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1';
177 CARDINALITY
178 2
179 # SHOW INDEX (aka. SHOW INDEXES or SHOW KEYS)
180 Cardinality: 2
181 INSERT INTO t1 VALUES (2, "Row 2", 37+2);
182 #### TEST LOOP 3 ####
183 SELECT COUNT(*) as "rows in table" FROM t1;
184 rows in table
185 3
186 # SELECT .. FROM INFORMATION_SCHEMA.TABLES
187 SELECT TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES
188 WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1';
189 TABLE_ROWS
190 3
191 # SHOW TABLE STATUS
192 Rows: 3
193 # SELECT ... FROM INFORMATION_SCHEMA.STATISTICS
194 SELECT CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS
195 WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1';
196 CARDINALITY
197 3
198 # SHOW INDEX (aka. SHOW INDEXES or SHOW KEYS)
199 Cardinality: 3
200 INSERT INTO t1 VALUES (3, "Row 3", 37+3);
201 INSERT INTO t1 VALUES
202 (14, "extra row 4", 44), (15, "extra row 5", 45), (16, "extra row 6", 46),
203 (17, "extra row 7", 47), (18, "extra row 8", 48), (19, "extra row 9", 49);
204 #### TEST LOOP 4 ####
205 SELECT COUNT(*) as "rows in table" FROM t1;
206 rows in table
207 10
208 # SELECT .. FROM INFORMATION_SCHEMA.TABLES
209 SELECT TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES
210 WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1';
211 TABLE_ROWS
212 10
213 # SHOW TABLE STATUS
214 Rows: 10
215 # SELECT ... FROM INFORMATION_SCHEMA.STATISTICS
216 SELECT CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS
217 WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1';
218 CARDINALITY
219 10
220 # SHOW INDEX (aka. SHOW INDEXES or SHOW KEYS)
221 Cardinality: 10
222 INSERT INTO t1 VALUES (4, "Row 4", 37+4);
223 DROP TABLE t1;
224 set @@information_schema_stats_expiry = @save_information_schema_stats_expiry;
225 # ###################################################################
226 #
227 # Test table stats with several tables updated in transaction.
228 # - when transaction is committed the "cached table stats" for all
229 # tables registered in the transaction will be updated with number
230 # of rows changed by transaction, nothing is updated during rollback.
231 # - This test exercise the logic for maintaining "cached table stats"
232 # by using 32 different tables in same transaction, however
233 # it's not possible to check the value from SQL level
234 #
235 # Create tables and load data
236 # Run transaction with update of all tables, commit
237 # Run transaction with update of all tables, rollback
238 # Drop tables