| 1 |
################################################################################ |
| 2 |
# |
| 3 |
# Verifies that queries in a REPEATABLE READ transaction is indeed repeatable. |
| 4 |
# Wrong results are shown as a result set based on one or more specially crafted |
| 5 |
# queries. Normally these result sets should be empty. |
| 6 |
# |
| 7 |
# We want to verify that reads (SELECTs) are indeed repeatable during a |
| 8 |
# REPEATABLE READ transaction. |
| 9 |
# |
| 10 |
# Generally, queries which should yield the same results at one moment in time |
| 11 |
# should also yield the same results later in the same transaction. In some |
| 12 |
# configurations, however, phantom reads are allowed (may e.g. depend on |
| 13 |
# settings such as falcon_consistent_read). |
| 14 |
# |
| 15 |
# The check will fail if rows are changed or missing when comparing later |
| 16 |
# queries to earlier ones. |
| 17 |
# TODO: Phantom reads. |
| 18 |
# Note: There is a separate test looking for `is_uncommitted` = 1. |
| 19 |
# |
| 20 |
# Assumptions: |
| 21 |
# - we are in a REPEATABLE READ transaction with autocommit OFF. |
| 22 |
# - queries include all columns of table (t1) (we SELECT columns by name) |
| 23 |
# |
| 24 |
# Requires/using the following variables: |
| 25 |
# $query_count - the number of queries to compare. |
| 26 |
# Will also be used to deduce the name of the temp table in |
| 27 |
# which the query results should be stored (see |
| 28 |
# record_query_all_columns.inc). |
| 29 |
# |
| 30 |
################################################################################ |
| 31 |
|
| 32 |
# Show results of next queries. Empty results is OK. Non-empty means failure. |
| 33 |
--enable_result_log |
| 34 |
|
| 35 |
# The mysqltest language is unfortunaltely not very flexible, but we try our |
| 36 |
# best to compare query results this way: |
| 37 |
# - For each query, compare with previous query |
| 38 |
# - this requires that at least 2 queries have been stored |
| 39 |
# - Number of queries should be stored as $query_count |
| 40 |
# - Results should be stored in temp tables with names ending with the query |
| 41 |
# number, and with prefix "tmp". |
| 42 |
# - E.g. compare "tmp2" with "tmp1", "tmp3" with "tmp2", "tmp4" with "tmp3" etc. |
| 43 |
# - Fail the test once we detect changed or missing or invalid extra rows in |
| 44 |
# latter query. |
| 45 |
# ????? |
| 46 |
# - Problem is that if one of the queries deadlocked or timed out, we may not |
| 47 |
# have enough result sets to compare, so output will vary depending on this. |
| 48 |
# Still we need the output from these checks to see which rows are missing or |
| 49 |
# changed. |
| 50 |
# So, if we don't have enough queries we fake "correct output" to make mysqltest |
| 51 |
# happy. |
| 52 |
# |
| 53 |
# Unfortunately, we need to utilize SQL and spend client-server roundtrips |
| 54 |
# in order to do some computations that the mysqltest language does not handle. |
| 55 |
# We try to use mysqltest variables instead where possible, as this should be |
| 56 |
# less expensive in terms of CPU usage and time spenditure. |
| 57 |
|
| 58 |
# |
| 59 |
# First, check that we have at least two query results stored. |
| 60 |
# We need at least 2 to be able to compare. |
| 61 |
# Some results may not have been stored due to locking errors (see record_query_all_columns.inc), so |
| 62 |
# we cannot assume that we always have at least 2 query results stored. |
| 63 |
# If less than 2 query results are stored, return to calling test/script. |
| 64 |
# |
| 65 |
if (`SELECT IF($query_count > 1, 1, 0)`) |
| 66 |
{ |
| 67 |
|
| 68 |
--echo *************************************************************************** |
| 69 |
--echo * Checking REPEATABLE READ by comparing result sets from same transaction |
| 70 |
--echo *************************************************************************** |
| 71 |
|
| 72 |
--echo *** Query log disabled. See include files used by test for query details. |
| 73 |
--disable_query_log |
| 74 |
|
| 75 |
let $queryA= 1; |
| 76 |
let $queryB= 2; |
| 77 |
let $more_queries= $query_count; |
| 78 |
|
| 79 |
# We start out by comparing the first 2 queries, so the while loop should run |
| 80 |
# $query_count - 1 times. (If we have 3 queries, compare 1 with 2, 2 and 3). |
| 81 |
--dec $more_queries |
| 82 |
|
| 83 |
while ($more_queries) |
| 84 |
{ |
| 85 |
# We still have one or more queries that have not been compared to the |
| 86 |
# previous query. |
| 87 |
# Compare queryB ("current query") with queryA ("previous query") |
| 88 |
|
| 89 |
#--source suite/stress_tx_rr/include/compare_queries_with_pk.inc |
| 90 |
|
| 91 |
let $tableA= tmp$queryA; |
| 92 |
let $tableB= tmp$queryB; |
| 93 |
|
| 94 |
--echo *** Comparing query $queryA (A) with query $queryB (B): |
| 95 |
|
| 96 |
# |
| 97 |
# In the following queries, 'SELECT * ...' could have been used instead of |
| 98 |
# 'SELECT tmp1.pk AS ...' etc., but the latter makes it easier to compare the first |
| 99 |
# result set to the second in test/diff output. |
| 100 |
|
| 101 |
|
| 102 |
|
| 103 |
########################### |
| 104 |
# Detect extra rows: |
| 105 |
# Allow phantoms in some configurations: |
| 106 |
# - InnoDB default settings |
| 107 |
# - Falcon's falcon_consistent_read=0 (non-default setting) |
| 108 |
# (TODO: What about PBXT?) |
| 109 |
# |
| 110 |
########################### |
| 111 |
# TODO: Execute a query against tmp1 and tmp2 which selects new rows (rows |
| 112 |
# present in tmp2 that are not present in tmp1) that are of the uncommitted |
| 113 |
# variety (field `is_uncommitted` = 1). |
| 114 |
# E.g. something like: |
| 115 |
# SELECT ... |
| 116 |
# FROM tmp2 LEFT JOIN tmp1 |
| 117 |
# ON tmp1.`pk` = tmp2.`pk` |
| 118 |
# WHERE tmp1.`int1` IS NULL |
| 119 |
# OR tmp1.`int1_key` IS NULL |
| 120 |
# OR tmp1.`int1_unique` IS NULL |
| 121 |
# OR tmp1.`int2` IS NULL |
| 122 |
# OR tmp1.`int2_key` IS NULL |
| 123 |
# OR tmp1.`int2_unique` IS NULL |
| 124 |
# AND tmp2.`is_uncommitted` = 1; |
| 125 |
|
| 126 |
|
| 127 |
--echo ########################### |
| 128 |
--echo # Detect missing rows: |
| 129 |
--echo ########################### |
| 130 |
|
| 131 |
|
| 132 |
eval SELECT $tableA.pk AS 'A.pk', |
| 133 |
$tableB.pk AS 'B.pk', |
| 134 |
$tableA.id AS 'A.id', |
| 135 |
$tableB.id AS 'B.id', |
| 136 |
$tableA.`int1` AS 'A.int1', |
| 137 |
$tableB.`int1` AS 'B.int1', |
| 138 |
$tableA.`int1_key` AS 'A.int1_key', |
| 139 |
$tableB.`int1_key` AS 'B.int1_key', |
| 140 |
$tableA.`int1_unique` AS 'A.int1_unique', |
| 141 |
$tableB.`int1_unique` AS 'B.int1_unique', |
| 142 |
$tableA.`int2` AS 'A.int2', |
| 143 |
$tableB.`int2` AS 'B.int2', |
| 144 |
$tableA.`int2_key` AS 'A.int2_key', |
| 145 |
$tableB.`int2_key` AS 'B.int2_key', |
| 146 |
$tableA.`int2_unique` AS 'A.int2_unique', |
| 147 |
$tableB.`int2_unique` AS 'B.int2_unique', |
| 148 |
$tableA.`for_update` AS 'A.for_update', |
| 149 |
$tableB.`for_update` AS 'B.for_update', |
| 150 |
$tableA.timestamp AS 'A.timestamp', |
| 151 |
$tableB.timestamp AS 'B.timestamp', |
| 152 |
$tableA.`connection_id` AS 'A.connection_id', |
| 153 |
$tableB.`connection_id` AS 'B.connection_id', |
| 154 |
$tableA.`thread_id` AS 'A.thread_id', |
| 155 |
$tableB.`thread_id` AS 'B.thread_id', |
| 156 |
$tableA.`is_uncommitted` AS 'A.is_uncommitted', |
| 157 |
$tableB.`is_uncommitted` AS 'B.is_uncommitted', |
| 158 |
$tableA.`is_consistent` AS 'A.is_consistent', |
| 159 |
$tableB.`is_consistent` AS 'B.is_consistent' |
| 160 |
FROM $tableA LEFT JOIN $tableB |
| 161 |
ON $tableA.`pk` = $tableB.`pk` |
| 162 |
WHERE $tableB.`pk` IS NULL; |
| 163 |
|
| 164 |
# |
| 165 |
# OR $tableB.`int1_key` IS NULL |
| 166 |
# OR $tableB.`int1_unique` IS NULL |
| 167 |
# OR $tableB.`int2` IS NULL |
| 168 |
# OR $tableB.`int2_key` IS NULL |
| 169 |
# OR $tableB.`int2_unique` IS NULL; |
| 170 |
|
| 171 |
--echo |
| 172 |
--echo ########################### |
| 173 |
--echo # Detect changed rows: |
| 174 |
--echo ########################### |
| 175 |
|
| 176 |
eval SELECT $tableA.pk AS 'A.pk', |
| 177 |
$tableB.pk AS 'B.pk', |
| 178 |
$tableA.id AS 'A.id', |
| 179 |
$tableB.id AS 'B.id', |
| 180 |
$tableA.`int1` AS 'A.int1', |
| 181 |
$tableB.`int1` AS 'B.int1', |
| 182 |
$tableA.`int1_key` AS 'A.int1_key', |
| 183 |
$tableB.`int1_key` AS 'B.int1_key', |
| 184 |
$tableA.`int1_unique` AS 'A.int1_unique', |
| 185 |
$tableB.`int1_unique` AS 'B.int1_unique', |
| 186 |
$tableA.`int2` AS 'A.int2', |
| 187 |
$tableB.`int2` AS 'B.int2', |
| 188 |
$tableA.`int2_key` AS 'A.int2_key', |
| 189 |
$tableB.`int2_key` AS 'B.int2_key', |
| 190 |
$tableA.`int2_unique` AS 'A.int2_unique', |
| 191 |
$tableB.`int2_unique` AS 'B.int2_unique', |
| 192 |
$tableA.`for_update` AS 'A.for_update', |
| 193 |
$tableB.`for_update` AS 'B.for_update', |
| 194 |
$tableA.timestamp AS 'A.timestamp', |
| 195 |
$tableB.timestamp AS 'B.timestamp', |
| 196 |
$tableA.`connection_id` AS 'A.connection_id', |
| 197 |
$tableB.`connection_id` AS 'B.connection_id', |
| 198 |
$tableA.`thread_id` AS 'A.thread_id', |
| 199 |
$tableB.`thread_id` AS 'B.thread_id', |
| 200 |
$tableA.`is_uncommitted` AS 'A.is_uncommitted', |
| 201 |
$tableB.`is_uncommitted` AS 'B.is_uncommitted', |
| 202 |
$tableA.`is_consistent` AS 'A.is_consistent', |
| 203 |
$tableB.`is_consistent` AS 'B.is_consistent' |
| 204 |
FROM $tableB INNER JOIN $tableA |
| 205 |
ON $tableB.`pk` = $tableA.`pk` |
| 206 |
WHERE $tableB.`int1` <> $tableA.`int1` |
| 207 |
OR $tableB.`int1_key` <> $tableA.`int1_key` |
| 208 |
OR $tableB.`int1_unique` <> $tableA.`int1_unique` |
| 209 |
OR $tableB.`int2` <> $tableA.`int2` |
| 210 |
OR $tableB.`int2_key` <> $tableA.`int2_key` |
| 211 |
OR $tableB.`int2_unique` <> $tableA.`int2_unique`; |
| 212 |
|
| 213 |
--dec $more_queries |
| 214 |
--inc $queryA |
| 215 |
--inc $queryB |
| 216 |
|
| 217 |
} |
| 218 |
--enable_query_log |
| 219 |
} |
| 220 |
|
| 221 |
|
| 222 |
## Cleanup is skipped because temporary tables and prepared statements will |
| 223 |
## be cleaned up automatically by the server when this session ends, and we |
| 224 |
## want to have as few client-server roundtrips as possible (thus avoid |
| 225 |
## unnecessary SQL statement executions). |