Debian Codegraph @

mysql-test/suite/engines/rr_trx/include/check_repeatable_read_all_columns.inc

raw
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 #
108 ###########################
109 # TODO: Execute a query against tmp1 and tmp2 which selects new rows (rows
110 # present in tmp2 that are not present in tmp1) that are of the uncommitted
111 # variety (field `is_uncommitted` = 1).
112 # E.g. something like:
113 # SELECT ...
114 # FROM tmp2 LEFT JOIN tmp1
115 # ON tmp1.`pk` = tmp2.`pk`
116 # WHERE tmp1.`int1` IS NULL
117 # OR tmp1.`int1_key` IS NULL
118 # OR tmp1.`int1_unique` IS NULL
119 # OR tmp1.`int2` IS NULL
120 # OR tmp1.`int2_key` IS NULL
121 # OR tmp1.`int2_unique` IS NULL
122 # AND tmp2.`is_uncommitted` = 1;
123
124
125 --echo ###########################
126 --echo # Detect missing rows:
127 --echo ###########################
128
129
130 eval SELECT $tableA.pk AS 'A.pk',
131 $tableB.pk AS 'B.pk',
132 $tableA.id AS 'A.id',
133 $tableB.id AS 'B.id',
134 $tableA.`int1` AS 'A.int1',
135 $tableB.`int1` AS 'B.int1',
136 $tableA.`int1_key` AS 'A.int1_key',
137 $tableB.`int1_key` AS 'B.int1_key',
138 $tableA.`int1_unique` AS 'A.int1_unique',
139 $tableB.`int1_unique` AS 'B.int1_unique',
140 $tableA.`int2` AS 'A.int2',
141 $tableB.`int2` AS 'B.int2',
142 $tableA.`int2_key` AS 'A.int2_key',
143 $tableB.`int2_key` AS 'B.int2_key',
144 $tableA.`int2_unique` AS 'A.int2_unique',
145 $tableB.`int2_unique` AS 'B.int2_unique',
146 $tableA.`for_update` AS 'A.for_update',
147 $tableB.`for_update` AS 'B.for_update',
148 $tableA.timestamp AS 'A.timestamp',
149 $tableB.timestamp AS 'B.timestamp',
150 $tableA.`connection_id` AS 'A.connection_id',
151 $tableB.`connection_id` AS 'B.connection_id',
152 $tableA.`thread_id` AS 'A.thread_id',
153 $tableB.`thread_id` AS 'B.thread_id',
154 $tableA.`is_uncommitted` AS 'A.is_uncommitted',
155 $tableB.`is_uncommitted` AS 'B.is_uncommitted',
156 $tableA.`is_consistent` AS 'A.is_consistent',
157 $tableB.`is_consistent` AS 'B.is_consistent'
158 FROM $tableA LEFT JOIN $tableB
159 ON $tableA.`pk` = $tableB.`pk`
160 WHERE $tableB.`pk` IS NULL;
161
162 #
163 # OR $tableB.`int1_key` IS NULL
164 # OR $tableB.`int1_unique` IS NULL
165 # OR $tableB.`int2` IS NULL
166 # OR $tableB.`int2_key` IS NULL
167 # OR $tableB.`int2_unique` IS NULL;
168
169 --echo
170 --echo ###########################
171 --echo # Detect changed rows:
172 --echo ###########################
173
174 eval SELECT $tableA.pk AS 'A.pk',
175 $tableB.pk AS 'B.pk',
176 $tableA.id AS 'A.id',
177 $tableB.id AS 'B.id',
178 $tableA.`int1` AS 'A.int1',
179 $tableB.`int1` AS 'B.int1',
180 $tableA.`int1_key` AS 'A.int1_key',
181 $tableB.`int1_key` AS 'B.int1_key',
182 $tableA.`int1_unique` AS 'A.int1_unique',
183 $tableB.`int1_unique` AS 'B.int1_unique',
184 $tableA.`int2` AS 'A.int2',
185 $tableB.`int2` AS 'B.int2',
186 $tableA.`int2_key` AS 'A.int2_key',
187 $tableB.`int2_key` AS 'B.int2_key',
188 $tableA.`int2_unique` AS 'A.int2_unique',
189 $tableB.`int2_unique` AS 'B.int2_unique',
190 $tableA.`for_update` AS 'A.for_update',
191 $tableB.`for_update` AS 'B.for_update',
192 $tableA.timestamp AS 'A.timestamp',
193 $tableB.timestamp AS 'B.timestamp',
194 $tableA.`connection_id` AS 'A.connection_id',
195 $tableB.`connection_id` AS 'B.connection_id',
196 $tableA.`thread_id` AS 'A.thread_id',
197 $tableB.`thread_id` AS 'B.thread_id',
198 $tableA.`is_uncommitted` AS 'A.is_uncommitted',
199 $tableB.`is_uncommitted` AS 'B.is_uncommitted',
200 $tableA.`is_consistent` AS 'A.is_consistent',
201 $tableB.`is_consistent` AS 'B.is_consistent'
202 FROM $tableB INNER JOIN $tableA
203 ON $tableB.`pk` = $tableA.`pk`
204 WHERE $tableB.`int1` <> $tableA.`int1`
205 OR $tableB.`int1_key` <> $tableA.`int1_key`
206 OR $tableB.`int1_unique` <> $tableA.`int1_unique`
207 OR $tableB.`int2` <> $tableA.`int2`
208 OR $tableB.`int2_key` <> $tableA.`int2_key`
209 OR $tableB.`int2_unique` <> $tableA.`int2_unique`;
210
211 --dec $more_queries
212 --inc $queryA
213 --inc $queryB
214
215 }
216 --enable_query_log
217 }
218
219
220 ## Cleanup is skipped because temporary tables and prepared statements will
221 ## be cleaned up automatically by the server when this session ends, and we
222 ## want to have as few client-server roundtrips as possible (thus avoid
223 ## unnecessary SQL statement executions).