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