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