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
|
################################################################################
#
# Verifies that queries in a REPEATABLE READ transaction is indeed repeatable.
# Wrong results are shown as a result set based on one or more specially crafted
# queries. Normally these result sets should be empty.
#
# We want to verify that reads (SELECTs) are indeed repeatable during a
# REPEATABLE READ transaction.
#
# Generally, queries which should yield the same results at one moment in time
# should also yield the same results later in the same transaction. In some
# configurations, however, phantom reads are allowed (may e.g. depend on
# settings such as falcon_consistent_read).
#
# The check will fail if rows are changed or missing when comparing later
# queries to earlier ones.
# TODO: Phantom reads.
# Note: There is a separate test looking for `is_uncommitted` = 1.
#
# Assumptions:
# - we are in a REPEATABLE READ transaction with autocommit OFF.
# - queries include all columns of table (t1) (we SELECT columns by name)
#
# Requires/using the following variables:
# $query_count - the number of queries to compare.
# Will also be used to deduce the name of the temp table in
# which the query results should be stored (see
# record_query_all_columns.inc).
#
################################################################################
# Show results of next queries. Empty results is OK. Non-empty means failure.
--enable_result_log
# The mysqltest language is unfortunaltely not very flexible, but we try our
# best to compare query results this way:
# - For each query, compare with previous query
# - this requires that at least 2 queries have been stored
# - Number of queries should be stored as $query_count
# - Results should be stored in temp tables with names ending with the query
# number, and with prefix "tmp".
# - E.g. compare "tmp2" with "tmp1", "tmp3" with "tmp2", "tmp4" with "tmp3" etc.
# - Fail the test once we detect changed or missing or invalid extra rows in
# latter query.
# ?????
# - Problem is that if one of the queries deadlocked or timed out, we may not
# have enough result sets to compare, so output will vary depending on this.
# Still we need the output from these checks to see which rows are missing or
# changed.
# So, if we don't have enough queries we fake "correct output" to make mysqltest
# happy.
#
# Unfortunately, we need to utilize SQL and spend client-server roundtrips
# in order to do some computations that the mysqltest language does not handle.
# We try to use mysqltest variables instead where possible, as this should be
# less expensive in terms of CPU usage and time spenditure.
#
# First, check that we have at least two query results stored.
# We need at least 2 to be able to compare.
# Some results may not have been stored due to locking errors (see record_query_all_columns.inc), so
# we cannot assume that we always have at least 2 query results stored.
# If less than 2 query results are stored, return to calling test/script.
#
if (`SELECT IF($query_count > 1, 1, 0)`)
{
--echo ***************************************************************************
--echo * Checking REPEATABLE READ by comparing result sets from same transaction
--echo ***************************************************************************
--echo *** Query log disabled. See include files used by test for query details.
--disable_query_log
let $queryA= 1;
let $queryB= 2;
let $more_queries= $query_count;
# We start out by comparing the first 2 queries, so the while loop should run
# $query_count - 1 times. (If we have 3 queries, compare 1 with 2, 2 and 3).
--dec $more_queries
while ($more_queries)
{
# We still have one or more queries that have not been compared to the
# previous query.
# Compare queryB ("current query") with queryA ("previous query")
#--source suite/stress_tx_rr/include/compare_queries_with_pk.inc
let $tableA= tmp$queryA;
let $tableB= tmp$queryB;
--echo *** Comparing query $queryA (A) with query $queryB (B):
#
# In the following queries, 'SELECT * ...' could have been used instead of
# 'SELECT tmp1.pk AS ...' etc., but the latter makes it easier to compare the first
# result set to the second in test/diff output.
###########################
# Detect extra rows:
# Allow phantoms in some configurations:
# - InnoDB default settings
# - Falcon's falcon_consistent_read=0 (non-default setting)
# (TODO: What about PBXT?)
#
###########################
# TODO: Execute a query against tmp1 and tmp2 which selects new rows (rows
# present in tmp2 that are not present in tmp1) that are of the uncommitted
# variety (field `is_uncommitted` = 1).
# E.g. something like:
# SELECT ...
# FROM tmp2 LEFT JOIN tmp1
# ON tmp1.`pk` = tmp2.`pk`
# WHERE tmp1.`int1` IS NULL
# OR tmp1.`int1_key` IS NULL
# OR tmp1.`int1_unique` IS NULL
# OR tmp1.`int2` IS NULL
# OR tmp1.`int2_key` IS NULL
# OR tmp1.`int2_unique` IS NULL
# AND tmp2.`is_uncommitted` = 1;
--echo ###########################
--echo # Detect missing rows:
--echo ###########################
eval SELECT $tableA.pk AS 'A.pk',
$tableB.pk AS 'B.pk',
$tableA.id AS 'A.id',
$tableB.id AS 'B.id',
$tableA.`int1` AS 'A.int1',
$tableB.`int1` AS 'B.int1',
$tableA.`int1_key` AS 'A.int1_key',
$tableB.`int1_key` AS 'B.int1_key',
$tableA.`int1_unique` AS 'A.int1_unique',
$tableB.`int1_unique` AS 'B.int1_unique',
$tableA.`int2` AS 'A.int2',
$tableB.`int2` AS 'B.int2',
$tableA.`int2_key` AS 'A.int2_key',
$tableB.`int2_key` AS 'B.int2_key',
$tableA.`int2_unique` AS 'A.int2_unique',
$tableB.`int2_unique` AS 'B.int2_unique',
$tableA.`for_update` AS 'A.for_update',
$tableB.`for_update` AS 'B.for_update',
$tableA.timestamp AS 'A.timestamp',
$tableB.timestamp AS 'B.timestamp',
$tableA.`connection_id` AS 'A.connection_id',
$tableB.`connection_id` AS 'B.connection_id',
$tableA.`thread_id` AS 'A.thread_id',
$tableB.`thread_id` AS 'B.thread_id',
$tableA.`is_uncommitted` AS 'A.is_uncommitted',
$tableB.`is_uncommitted` AS 'B.is_uncommitted',
$tableA.`is_consistent` AS 'A.is_consistent',
$tableB.`is_consistent` AS 'B.is_consistent'
FROM $tableA LEFT JOIN $tableB
ON $tableA.`pk` = $tableB.`pk`
WHERE $tableB.`pk` IS NULL;
#
# OR $tableB.`int1_key` IS NULL
# OR $tableB.`int1_unique` IS NULL
# OR $tableB.`int2` IS NULL
# OR $tableB.`int2_key` IS NULL
# OR $tableB.`int2_unique` IS NULL;
--echo
--echo ###########################
--echo # Detect changed rows:
--echo ###########################
eval SELECT $tableA.pk AS 'A.pk',
$tableB.pk AS 'B.pk',
$tableA.id AS 'A.id',
$tableB.id AS 'B.id',
$tableA.`int1` AS 'A.int1',
$tableB.`int1` AS 'B.int1',
$tableA.`int1_key` AS 'A.int1_key',
$tableB.`int1_key` AS 'B.int1_key',
$tableA.`int1_unique` AS 'A.int1_unique',
$tableB.`int1_unique` AS 'B.int1_unique',
$tableA.`int2` AS 'A.int2',
$tableB.`int2` AS 'B.int2',
$tableA.`int2_key` AS 'A.int2_key',
$tableB.`int2_key` AS 'B.int2_key',
$tableA.`int2_unique` AS 'A.int2_unique',
$tableB.`int2_unique` AS 'B.int2_unique',
$tableA.`for_update` AS 'A.for_update',
$tableB.`for_update` AS 'B.for_update',
$tableA.timestamp AS 'A.timestamp',
$tableB.timestamp AS 'B.timestamp',
$tableA.`connection_id` AS 'A.connection_id',
$tableB.`connection_id` AS 'B.connection_id',
$tableA.`thread_id` AS 'A.thread_id',
$tableB.`thread_id` AS 'B.thread_id',
$tableA.`is_uncommitted` AS 'A.is_uncommitted',
$tableB.`is_uncommitted` AS 'B.is_uncommitted',
$tableA.`is_consistent` AS 'A.is_consistent',
$tableB.`is_consistent` AS 'B.is_consistent'
FROM $tableB INNER JOIN $tableA
ON $tableB.`pk` = $tableA.`pk`
WHERE $tableB.`int1` <> $tableA.`int1`
OR $tableB.`int1_key` <> $tableA.`int1_key`
OR $tableB.`int1_unique` <> $tableA.`int1_unique`
OR $tableB.`int2` <> $tableA.`int2`
OR $tableB.`int2_key` <> $tableA.`int2_key`
OR $tableB.`int2_unique` <> $tableA.`int2_unique`;
--dec $more_queries
--inc $queryA
--inc $queryB
}
--enable_query_log
}
## Cleanup is skipped because temporary tables and prepared statements will
## be cleaned up automatically by the server when this session ends, and we
## want to have as few client-server roundtrips as possible (thus avoid
## unnecessary SQL statement executions).
|
