Debian Codegraph @

mysql-test/suite/ndbcluster/table_stats.test

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