| 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 |