Debian Codegraph @

mysql-test/suite/perfschema/t/transaction.test

raw
1 ############# suite/perfschema/t/transaction.test ####################
2 # #
3 # Test processing of transaction events by the Performance Schema, #
4 # including explicit/implicit transactions, access modes, isolation #
5 # levels, statement counts and state transitions. #
6 # #
7 # #
8 ######################################################################
9
10 --source include/have_myisam.inc
11 --source include/force_myisam_default.inc
12 # Test requires: sp-protocol/ps-protocol/view-protocol/cursor-protocol disabled
13 --source include/no_protocol.inc
14 --source include/have_log_bin.inc
15 --disable_query_log
16 # Disable events from GTID background thread
17 UPDATE performance_schema.threads
18 SET instrumented='NO' WHERE name like '%clone_gtid_thread%';
19 --source ../include/transaction_setup.inc
20 --enable_query_log
21
22 --echo
23 --echo ========================================================================
24 --echo STEP 1 - SETUP
25 --echo ========================================================================
26
27 --echo
28 --echo # Control thread
29 --echo
30 --connection default
31 SET SESSION AUTOCOMMIT= 1;
32 eval $get_thread_id;
33 let $default_thread_id= `SELECT @my_thread_id`;
34
35 --echo
36 --echo # Connection 1
37 --echo
38 connect(con1, localhost, root,,);
39 --disable_query_log
40 eval $get_thread_id;
41 let $con1_thread_id= `SELECT @my_thread_id`;
42 --enable_query_log
43 SET SESSION AUTOCOMMIT= 0;
44 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE;
45
46 --disable_testcase BUG#0000
47 --echo
48 --echo # Connection 2
49 --echo
50 connect(con2, localhost, root,,);
51 --disable_query_log
52 eval $get_thread_id;
53 let $con2_thread_id= `SELECT @my_thread_id`;
54 --enable_query_log
55 SET SESSION AUTOCOMMIT= 0;
56 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE;
57 --enable_testcase
58
59 --connection default
60 --disable_query_log
61 eval SET @con1_thread_id= $con1_thread_id;
62 #eval SET @con2_thread_id= $con2_thread_id;
63 --enable_query_log
64
65 --echo
66 --echo # Create test tables, one transactional and one non-transactional
67 --echo
68 --connection default
69
70 --disable_warnings
71 DROP TABLE IF EXISTS t1;
72 DROP TABLE IF EXISTS nt1;
73 --enable_warnings
74 CREATE TABLE t1 (s1 int, s2 varchar(64)) ENGINE=INNODB;
75 CREATE TABLE nt1 (s1 int, s2 varchar(64)) ENGINE=MYISAM;
76
77 --echo
78 --echo # Get tables loaded in data-dictionary cache
79 --echo
80 --disable_query_log
81 --disable_result_log
82 SELECT * FROM t1;
83 SELECT * FROM nt1;
84 --enable_result_log
85 --enable_query_log
86
87 --echo
88 --echo # Disable all events from the control thread
89 --echo
90 --disable_query_log
91 UPDATE performance_schema.threads
92 SET instrumented='NO' WHERE processlist_id = CONNECTION_ID();
93 --enable_query_log
94
95 --echo
96 --echo # Clear transaction tables
97 --echo
98 CALL clear_transaction_tables();
99
100 --echo
101 --echo ========================================================================
102 --echo # STEP 2 - BASIC TRANSACTION
103 --echo ========================================================================
104 --echo
105 --connection con1
106 SELECT @@global.transaction_isolation;
107 SELECT @@global.autocommit;
108 SELECT @@transaction_isolation;
109 SELECT @@autocommit;
110
111 --echo
112 --echo # STEP 2.1 - START/COMMIT
113 --echo
114 START TRANSACTION;
115 INSERT INTO t1 VALUES (101, 'COMMITTED');
116 COMMIT;
117
118 --echo
119 --echo # STEP 2.2 - ROLLBACK
120 --echo
121 START TRANSACTION;
122 INSERT INTO t1 VALUES (102, 'ROLLED BACK');
123 ROLLBACK;
124
125 --echo
126 --echo ## Expect 1 committed and 1 rolled back transaction
127 --connection default
128 CALL transaction_verifier(1, @con1_thread_id, 'transaction', 'COMMITTED', 0, '', '', '', '', 'READ WRITE', 'REPEATABLE READ', 'NO', 0, 0, 0, 1);
129 CALL transaction_verifier(1, @con1_thread_id, 'transaction', 'ROLLED BACK', 0, '', '', '', '', 'READ WRITE', 'REPEATABLE READ', 'NO', 0, 0, 0, 1);
130 CALL clear_transaction_tables();
131
132 --echo
133 --echo # STEP 2.3 - COMMIT AND CHAIN
134 --echo
135 --connection con1
136 START TRANSACTION;
137 INSERT INTO t1 VALUES (103, 'COMMIT AND CHAIN');
138 COMMIT AND CHAIN;
139 INSERT INTO t1 VALUES (104, 'COMMIT AND CHAIN');
140 COMMIT;
141
142 --echo
143 --echo ## Expect 2 committed transactions
144 --connection default
145 CALL transaction_verifier(1, @con1_thread_id, 'transaction', 'COMMITTED', 0, '', '', '', '', 'READ WRITE', 'REPEATABLE READ', 'NO', 0, 0, 0, 2);
146 CALL clear_transaction_tables();
147
148 --echo
149 --echo ========================================================================
150 --echo # STEP 3 - ISOLATION LEVEL
151 --echo ========================================================================
152 --echo
153 --echo connection con1
154 --connection con1
155 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
156 START TRANSACTION;
157 INSERT INTO t1 VALUES (301, 'SERIALIZABLE');
158 COMMIT;
159
160 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
161 START TRANSACTION;
162 INSERT INTO t1 VALUES (302, 'REPEATABLE READ');
163 COMMIT;
164
165 ## NOTE - InnoDB requires binlog_format = 'ROW' for READ COMMITTED/UNCOMMITTED
166 SELECT @@binlog_format INTO @binlog_save;
167 SET SESSION BINLOG_FORMAT='ROW';
168 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
169 START TRANSACTION;
170 INSERT INTO t1 VALUES (303, 'READ COMMITTED');
171 COMMIT;
172
173 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
174 START TRANSACTION;
175 INSERT INTO t1 VALUES (304, 'READ UNCOMMITTED');
176 COMMIT;
177
178 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
179 SET binlog_format= @binlog_save;
180
181 --echo
182 --connection default
183 CALL transaction_verifier(1, @con1_thread_id, 'transaction', 'COMMITTED', 0, '', '', '', '', 'READ WRITE', 'SERIALIZABLE', 'NO', 0, 0, 0, 1);
184 CALL transaction_verifier(1, @con1_thread_id, 'transaction', 'COMMITTED', 0, '', '', '', '', 'READ WRITE', 'REPEATABLE READ', 'NO', 0, 0, 0, 1);
185 CALL transaction_verifier(1, @con1_thread_id, 'transaction', 'COMMITTED', 0, '', '', '', '', 'READ WRITE', 'READ COMMITTED', 'NO', 0, 0, 0, 1);
186 CALL transaction_verifier(1, @con1_thread_id, 'transaction', 'COMMITTED', 0, '', '', '', '', 'READ WRITE', 'READ UNCOMMITTED', 'NO', 0, 0, 0, 1);
187 CALL clear_transaction_tables();
188
189 --echo
190 --echo ========================================================================
191 --echo # STEP 4 - ACCESS MODE
192 --echo ========================================================================
193 --echo #
194 --echo # STEP 4.1 - READ ONLY, TIMING ENABLED
195 --echo #
196 --echo
197 --echo connection con1
198 --connection con1
199 SET SESSION TRANSACTION READ WRITE;
200 START TRANSACTION;
201 INSERT INTO t1 VALUES (410, 'READ ONLY');
202 INSERT INTO t1 VALUES (411, 'READ ONLY');
203 INSERT INTO t1 VALUES (412, 'READ ONLY');
204 INSERT INTO t1 VALUES (413, 'READ ONLY');
205 COMMIT;
206
207 SET SESSION TRANSACTION READ ONLY;
208 START TRANSACTION;
209 SELECT * FROM t1 ORDER BY s1;
210 COMMIT;
211
212 --echo
213 --echo ## Expect 1 read only, committed transaction in events_transactions_history
214 --connection default
215 CALL transaction_verifier(1, @con1_thread_id, 'transaction', 'COMMITTED', 0, '', '', '', '', 'READ ONLY', 'REPEATABLE READ', 'NO', 0, 0, 0, 1);
216 CALL clear_transaction_tables();
217
218 --echo
219 --echo #
220 --echo # STEP 4.2 - READ ONLY, TIMING DISABLED
221 --echo #
222 --echo
223 --echo ## Disable timing stats for 'transaction'
224 UPDATE performance_schema.setup_instruments SET timed = 'NO' WHERE name = 'transaction';
225 --echo
226 TRUNCATE performance_schema.events_transactions_summary_global_by_event_name;
227
228 --echo connection con1
229 --connection con1
230 START TRANSACTION;
231 SELECT * FROM t1 ORDER BY s1;
232 COMMIT;
233 --echo
234 SET SESSION TRANSACTION READ WRITE;
235
236 --connection default
237 --echo
238 --echo ## Expect 1 event, 0 stats
239 SELECT * FROM performance_schema.events_transactions_summary_global_by_event_name;
240 --echo
241 --echo ## Restore setup_instruments
242 UPDATE performance_schema.setup_instruments SET enabled = 'YES' WHERE name = 'transaction';
243
244 --echo
245 --echo ========================================================================
246 --echo # STEP 5 - IMPLICIT START
247 --echo ========================================================================
248 --echo # When AUTOCOMMIT is disabled, the first statement following a committed
249 --echo # transaction marks the start of a new transaction. Subsequent statements will
250 --echo # be part of the transaction until it is committed.
251 --echo
252 --connection con1
253 SET SESSION AUTOCOMMIT = 0;
254 INSERT INTO t1 VALUES (501, 'IMPLICIT START');
255
256 --echo
257 --echo ## Expect 1 active transaction in events_transactions_current
258 --connection default
259 CALL transaction_verifier(0, @con1_thread_id, 'transaction', 'ACTIVE', 0, '', '', '', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
260
261 --connection con1
262 INSERT INTO t1 VALUES (502, 'IMPLICIT START');
263 COMMIT;
264
265 --echo
266 --echo ## Expect one committed transaction in events_transactions_current and history
267 --connection default
268 CALL transaction_verifier(0, @con1_thread_id, 'transaction', 'COMMITTED', 0, '', '', '', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
269 CALL transaction_verifier(1, @con1_thread_id, 'transaction', 'COMMITTED', 0, '', '', '', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
270 CALL clear_transaction_tables();
271
272 --echo
273 --echo ========================================================================
274 --echo # STEP 6 - IMPLICIT COMMIT (DDL, ETC)
275 --echo ========================================================================
276 --echo # Transactions are implicitly ended by DDL statements, locking statements
277 --echo # and server administration commands.
278 --echo
279 --connection con1
280 SET SESSION AUTOCOMMIT = 0;
281 INSERT INTO t1 VALUES (601, 'IMPLICIT COMMIT');
282
283 --echo
284 --echo ## Expect one active transaction in events_transactions_current, zero events in history
285 --connection default
286 CALL transaction_verifier(0, @con1_thread_id, 'transaction', 'ACTIVE', 0, '', '', '', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
287 CALL transaction_verifier(1, @con1_thread_id, 'transaction', '', 0, '', '', '', '', '', '', 'NO', 0, 0, 0, 0);
288
289 --connection con1
290 INSERT INTO t1 VALUES (602, 'IMPLICIT COMMIT');
291
292 --echo ## Issue a DDL statement to force a commmit
293 --echo ## This DDL needs to fail early, to avoid starting its own transaction
294 --echo ## which will disturb the state.
295 --error ER_KEY_COLUMN_DOES_NOT_EXITS
296 ALTER TABLE t1 ADD KEY (s3);
297
298 --echo
299 --echo ## Expect 0 active transactions, 1 committed transaction
300 --connection default
301 CALL transaction_verifier(0, @con1_thread_id, 'transaction', 'ACTIVE', 0, '', '', '', '', 'READ WRITE', '', 'NO', 0, 0, 0, 0);
302 CALL transaction_verifier(0, @con1_thread_id, 'transaction', 'COMMITTED', 0, '', '', '', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
303 CALL transaction_verifier(1, @con1_thread_id, 'transaction', 'COMMITTED', 0, '', '', '', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
304 CALL clear_transaction_tables();
305
306 --echo
307 --echo ========================================================================
308 --echo STEP 7 - XA TRANSACTIONS
309 --echo ========================================================================
310 --echo #
311 --echo # STEP 7.1 - XA START
312 --echo #
313 --connection con1
314 XA START 'XA_CON1_GTRID_001', 'XA_CON1_BQUAL_001', 1234567890;
315
316 --echo
317 --echo ## Expect 1 active XA transaction, state ACTIVE
318 --connection default
319 CALL transaction_verifier(0, @con1_thread_id, 'transaction', 'ACTIVE', 1234567890, 'XA_CON1_GTRID_001', 'XA_CON1_BQUAL_001', 'ACTIVE', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
320
321 --echo
322 --echo # STEP 7.2 - XA END
323 --echo
324 --connection con1
325 INSERT INTO t1 VALUES (701, 'XA');
326 XA END 'XA_CON1_GTRID_001', 'XA_CON1_BQUAL_001', 1234567890;
327
328 --echo
329 --echo ## Expect 1 active XA transaction, state IDLE
330 --connection default
331 CALL transaction_verifier(0, @con1_thread_id, 'transaction', 'ACTIVE', 1234567890, 'XA_CON1_GTRID_001', 'XA_CON1_BQUAL_001', 'IDLE', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
332
333 --echo
334 --echo #
335 --echo # STEP 7.3 - XA PREPARE
336 --echo #
337 --connection con1
338 XA PREPARE 'XA_CON1_GTRID_001', 'XA_CON1_BQUAL_001', 1234567890;
339
340 --echo
341 --echo ## Expect 1 active XA transaction, state PREPARED
342 --connection default
343 CALL transaction_verifier(0, @con1_thread_id, 'transaction', 'ACTIVE', 1234567890, 'XA_CON1_GTRID_001', 'XA_CON1_BQUAL_001', 'PREPARED', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
344
345 --echo
346 --echo #
347 --echo # STEP 7.4 - XA COMMIT
348 --echo #
349 --connection con1
350 XA COMMIT 'XA_CON1_GTRID_001', 'XA_CON1_BQUAL_001', 1234567890;
351
352 --echo
353 --echo ## Expect 1 committed XA transaction, state COMMITTED in current and history
354 --connection default
355 CALL transaction_verifier(0, @con1_thread_id, 'transaction', 'COMMITTED', 1234567890, 'XA_CON1_GTRID_001', 'XA_CON1_BQUAL_001', 'COMMITTED', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
356 CALL transaction_verifier(1, @con1_thread_id, 'transaction', 'COMMITTED', 1234567890, 'XA_CON1_GTRID_001', 'XA_CON1_BQUAL_001', 'COMMITTED', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
357
358 CALL clear_transaction_tables();
359
360 --echo
361 --echo #
362 --echo # STEP 7.5 - XA ROLLBACK
363 --echo #
364 --connection con1
365 XA START 'XA_CON1_002';
366 INSERT INTO t1 VALUES (702, 'XA');
367 XA END 'XA_CON1_002';
368 XA PREPARE 'XA_CON1_002';
369
370 --echo
371 --echo ## Expect 1 active XA transaction, state PREPARED
372 --connection default
373 CALL transaction_verifier(0, @con1_thread_id, 'transaction', 'ACTIVE', 0, 'XA_CON1_002', '', 'PREPARED', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
374
375 --connection con1
376 XA ROLLBACK 'XA_CON1_002';
377
378 --echo
379 --echo ## Expect 1 XA transaction, state ROLLBACK ONLY in current and history
380 --connection default
381 CALL transaction_verifier(0, @con1_thread_id, 'transaction', 'ROLLED BACK', 0, 'XA_CON1_002', '', 'ROLLBACK ONLY', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
382 CALL transaction_verifier(1, @con1_thread_id, 'transaction', 'ROLLED BACK', 0, 'XA_CON1_002', '', 'ROLLBACK ONLY', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
383 CALL clear_transaction_tables();
384 SELECT * FROM performance_schema.events_transactions_current ORDER BY event_id;
385
386 --echo #
387 --echo # STEP 7.6 - XA TRANSACTION - LONG GTRID AND BQUAL
388 --echo #
389 --connection con1
390 XA START 'GTRID_6789012345678901234567890123456789012345678901234567890123','BQUAL_6789012345678901234567890123456789012345678901234567890123',1234567890;
391 INSERT INTO t1 VALUES (703, 'XA LONG');
392 XA END 'GTRID_6789012345678901234567890123456789012345678901234567890123','BQUAL_6789012345678901234567890123456789012345678901234567890123',1234567890;
393 XA PREPARE 'GTRID_6789012345678901234567890123456789012345678901234567890123','BQUAL_6789012345678901234567890123456789012345678901234567890123',1234567890;
394 XA COMMIT 'GTRID_6789012345678901234567890123456789012345678901234567890123','BQUAL_6789012345678901234567890123456789012345678901234567890123',1234567890;
395
396 --echo
397 --echo ## Expect 1 committed XA transaction, state COMMITTED in current and history
398 --connection default
399 CALL transaction_verifier(0, @con1_thread_id, 'transaction', 'COMMITTED', 1234567890, 'GTRID_6789012345678901234567890123456789012345678901234567890123', 'BQUAL_6789012345678901234567890123456789012345678901234567890123', 'COMMITTED', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
400 CALL transaction_verifier(1, @con1_thread_id, 'transaction', 'COMMITTED', 1234567890, 'GTRID_6789012345678901234567890123456789012345678901234567890123', 'BQUAL_6789012345678901234567890123456789012345678901234567890123', 'COMMITTED', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
401 CALL clear_transaction_tables();
402
403 --echo #
404 --echo # STEP 7.7 - XA TRANSACTION - LONG GTRID AND BINARY BQUAL
405 --echo #
406 --connection con1
407 XA START 'GTRID_6789012345678901234567890123456789012345678901234567890123',0x425155414C5FA5A53839303132333435363738393031323334353637383930313233343536373839303132333435363738393031323334353637383930313233,1234567890;
408 INSERT INTO t1 VALUES (704, 'XA LONG/BINARY');
409 XA END 'GTRID_6789012345678901234567890123456789012345678901234567890123',0x425155414C5FA5A53839303132333435363738393031323334353637383930313233343536373839303132333435363738393031323334353637383930313233,1234567890;
410 XA PREPARE 'GTRID_6789012345678901234567890123456789012345678901234567890123',0x425155414C5FA5A53839303132333435363738393031323334353637383930313233343536373839303132333435363738393031323334353637383930313233,1234567890;
411 XA COMMIT 'GTRID_6789012345678901234567890123456789012345678901234567890123',0x425155414C5FA5A53839303132333435363738393031323334353637383930313233343536373839303132333435363738393031323334353637383930313233,1234567890;
412 --echo
413 --echo ## Expect 1 committed XA transaction, state COMMITTED in current and history
414 --connection default
415 CALL transaction_verifier(0, @con1_thread_id, 'transaction', 'COMMITTED', 1234567890, 'GTRID_6789012345678901234567890123456789012345678901234567890123', '0x425155414C5FA5A53839303132333435363738393031323334353637383930313233343536373839303132333435363738393031323334353637383930313233', 'COMMITTED', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
416 CALL transaction_verifier(1, @con1_thread_id, 'transaction', 'COMMITTED', 1234567890, 'GTRID_6789012345678901234567890123456789012345678901234567890123', '0x425155414C5FA5A53839303132333435363738393031323334353637383930313233343536373839303132333435363738393031323334353637383930313233', 'COMMITTED', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
417 CALL clear_transaction_tables();
418
419 --echo
420 --echo ========================================================================
421 --echo # STEP 8 - TRANSACTIONAL AND NON-TRANSACTIONAL TABLES
422 --echo ========================================================================
423 --echo
424
425 ## Statements that work with non-transactional engines have no effect on the
426 ## transaction state of the connection. For implicit transactions,
427 ## the transaction event begins with the first statement that uses a
428 ## transactional engine. This means that statements operating exclusively on
429 ## non-transactional tables will be ignored, even following START TRANSACTION.
430
431 --connection con1
432 SET SESSION AUTOCOMMIT = 0;
433 SELECT COUNT(*) FROM performance_schema.events_transactions_current ORDER BY event_id;
434
435 --echo
436 --echo #
437 --echo # STEP 8.1 - UPDATE NON-TRANSACTIONAL TABLE
438 --echo #
439 INSERT INTO nt1 VALUES (801, 'NON-TRANSACTIONAL');
440
441 --echo
442 --echo ## Expect 0 transactions in events_transactions_current
443 --connection default
444 CALL transaction_verifier(0, @con1_thread_id, 'transaction', '', 0, '', '', '', '', '', '', '', 0, 0, 0, 0);
445
446 --connection con1
447 COMMIT;
448
449 --echo
450 --echo ## Expect 0 transactions in events_transactions_history
451 --connection default
452 CALL transaction_verifier(1, @con1_thread_id, 'transaction', '', 0, '', '', '', '', '', '', '', 0, 0, 0, 0);
453
454 --echo
455 --echo #
456 --echo # STEP 8.2 - UPDATE TRANSACTIONAL AND NON-TRANSACTIONAL TABLES
457 --echo #
458 --echo
459 --echo ## First non-transactional...
460 --echo
461 --connection con1
462 INSERT INTO nt1 VALUES (802, 'NON-TRANSACTIONAL');
463
464 --echo
465 --echo ## Expect 0 transactions in events_transactions_current
466 --connection default
467 CALL transaction_verifier(0, @con1_thread_id, 'transaction', '', 0, '', '', '', '', '', '', '', 0, 0, 0, 0);
468
469 --echo
470 --echo ## Now transactional. Transaction should be started.
471 --connection con1
472 INSERT INTO t1 VALUES (802, 'TRANSACTIONAL');
473
474 --echo
475 --echo ## Expect 1 transaction in events_transactions_current
476 --connection default
477 CALL transaction_verifier(0, @con1_thread_id, 'transaction', 'ACTIVE', 0, '', '', '', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
478
479 ## Commit
480 --echo
481 --connection con1
482 COMMIT;
483
484 --echo
485 --echo ## Expect 1 committed transaction in events_transactions_current and history
486 --connection default
487 CALL transaction_verifier(0, @con1_thread_id, 'transaction', 'COMMITTED', 0, '', '', '', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
488 CALL transaction_verifier(1, @con1_thread_id, 'transaction', 'COMMITTED', 0, '', '', '', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
489 CALL clear_transaction_tables();
490
491 --echo
492 --echo ========================================================================
493 --echo # STEP 9 - SAVEPOINTS
494 --echo ========================================================================
495 --echo #
496 --echo # STEP 9.1 - SAVEPOINT 1
497 --echo #
498 --connection con1
499 START TRANSACTION;
500 INSERT INTO t1 VALUES (901, 'SAVEPOINT');
501 SAVEPOINT SVP001;
502
503 --echo
504 --echo ## Expect 1 active transaction with 1 savepoint
505 --connection default
506 CALL transaction_verifier(0, @con1_thread_id, 'transaction', 'ACTIVE', 0, '', '', '', '', 'READ WRITE', '', 'NO', 1, 0, 0, 1);
507
508 --echo
509 --echo #
510 --echo # STEP 9.2 - SAVEPOINTS 2 and 3
511 --echo #
512 --connection con1
513 INSERT INTO t1 VALUES (902, 'SAVEPOINT');
514 SAVEPOINT SVP002;
515 INSERT INTO t1 VALUES (903, 'SAVEPOINT');
516 SAVEPOINT SVP003;
517 INSERT INTO t1 VALUES (904, 'SAVEPOINT');
518 SELECT COUNT(*) FROM t1 WHERE s1 > 900;
519
520 --echo
521 --echo #
522 --echo # STEP 9.3 - ROLLBACK TO SAVEPOINT 2
523 --echo #
524 --connection con1
525 ROLLBACK TO SVP002;
526
527 --echo
528 --echo ## Expect 1 active transaction with 3 savepoints, 1 rollback to savepoint
529 --connection default
530 CALL transaction_verifier(0, @con1_thread_id, 'transaction', 'ACTIVE', 0, '', '', '', '', 'READ WRITE', '', 'NO', 3, 1, 0, 1);
531
532 --echo
533 --echo #
534 --echo # STEP 9.4 - RELEASE SAVEPOINT 1
535 --echo #
536 --connection con1
537 RELEASE SAVEPOINT SVP001;
538
539 --echo
540 --echo ## Expect 1 active transaction with 3 savepoints, 1 rollback to savepoint, 1 release savepoint
541 --connection default
542 CALL transaction_verifier(0, @con1_thread_id, 'transaction', 'ACTIVE', 0, '', '', '', '', 'READ WRITE', '', 'NO', 3, 1, 1, 1);
543
544 --echo #
545 --echo # STEP 9.5 - COMMIT
546 --echo #
547 --connection con1
548 COMMIT;
549
550 --echo
551 --echo ## Expect 1 committed transaction with 3 savepoints, 1 rollback to savepoint, 1 release savepoint
552 --connection default
553 CALL transaction_verifier(1, @con1_thread_id, 'transaction', 'COMMITTED', 0, '', '', '', '', 'READ WRITE', '', 'NO', 3, 1, 1, 1);
554 CALL clear_transaction_tables();
555
556 --echo
557 --echo ========================================================================
558 --echo # STEP 10 - GTIDs
559 --echo ========================================================================
560 --echo GTIDs are tested in transaction_gtid.test.
561
562 --echo
563 --echo ========================================================================
564 --echo # STEP 11 - MISCELLANY
565 --echo ========================================================================
566 --echo #
567 --echo # STEP 11.1 - TRUNCATE DURING ACTIVE TRANSACTION
568 --echo #
569 --echo
570 --echo # Verify that truncating events_transactions_current during an active transaction
571 --echo # does not leave an orphaned transaction event, and that the row index to
572 --echo # events_transactions_history is reset to 0.
573 --echo
574 --connection con1
575 START TRANSACTION;
576 INSERT INTO t1 VALUES (1110, 'INSERT 1110');
577 --connection default
578 TRUNCATE performance_schema.events_transactions_current;
579 --connection con1
580 --echo
581 COMMIT;
582 --echo
583 START TRANSACTION;
584 INSERT INTO t1 VALUES (1111, 'INSERT 1111');
585 COMMIT;
586
587 --echo
588 --echo ## Expect 1 transaction for connection 1
589 --connection default
590 CALL transaction_verifier(1, @con1_thread_id, 'transaction', 'COMMITTED', 0, '', '', '', '', '', '', '', 0, 0, 0, 1);
591 CALL clear_transaction_tables();
592
593 --echo
594 --echo #
595 --echo # STEP 11.2 - DISABLE THREAD INSTRUMENTATION
596 --echo #
597 --connection default
598 UPDATE performance_schema.setup_consumers
599 SET enabled = 'NO'
600 WHERE name = 'thread_instrumentation';
601 --echo
602 TRUNCATE performance_schema.events_transactions_summary_global_by_event_name;
603
604 --connection con1
605 --echo
606 START TRANSACTION;
607 INSERT INTO t1 VALUES (1120, 'INSERT 1120');
608 COMMIT;
609
610 --connection default
611 --echo
612 --echo ## Expect 1 event with non-zero summary stats
613 --replace_column 3 sum_timer_wait 4 min_timer_wait 5 avg_timer_wait
614 SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT, MIN_TIMER_WAIT, AVG_TIMER_WAIT, COUNT_READ_WRITE
615 FROM performance_schema.events_transactions_summary_global_by_event_name
616 WHERE count_star = 1 and sum_timer_wait != 0;
617
618 --echo
619 --echo ## Disable timing stats for 'transaction'
620 UPDATE performance_schema.setup_instruments SET timed = 'NO' WHERE name = 'transaction';
621 --echo
622 TRUNCATE performance_schema.events_transactions_summary_global_by_event_name;
623
624 --connection con1
625 --echo
626 START TRANSACTION;
627 INSERT INTO t1 VALUES (1121, 'INSERT 1121');
628 COMMIT;
629
630 --connection default
631 --echo
632 --echo ## Expect 1 event, 0 stats
633 SELECT * FROM performance_schema.events_transactions_summary_global_by_event_name;
634
635 --echo
636 --echo ## Restore setup_consumers and setup_instruments
637 UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name = 'thread_instrumentation';
638 UPDATE performance_schema.setup_instruments SET enabled = 'YES' WHERE name = 'transaction';
639 --echo
640 DELETE FROM t1;
641 CALL clear_history();
642
643 --echo
644 --echo #
645 --echo # STEP 11.3 - STATEMENT ROLLBACK - AUTOCOMMIT OFF - BINLOG FORMAT 'STATEMENT'
646 --echo #
647 --connection con1
648 SET SESSION binlog_format = STATEMENT;
649 SET SESSION AUTOCOMMIT = 0;
650
651 # A transaction with a rolled back statement should not show as rolled back.
652 #
653 # Force a statement rollback by attempting to update a transactional table
654 # and a non-replicatable table with binlog_format = STATEMENT.
655
656 --echo
657 START TRANSACTION;
658 INSERT INTO t1 VALUES (1130, 'INSERT 1130');
659 --echo
660 --echo ## Expect binlog statement mode error
661 --error 0, ER_BINLOG_STMT_MODE_AND_NO_REPL_TABLES
662 UPDATE t1, performance_schema.setup_instruments pfs
663 SET t1.s1 = 1, pfs.timed = 'NO';
664 --echo
665 COMMIT;
666 --echo
667 SET SESSION AUTOCOMMIT = 1;
668
669 --echo
670 --echo ## Expect 1 committed transaction
671 --connection default
672 CALL transaction_verifier(1, @con1_thread_id, 'transaction', 'COMMITTED', 0, '', '', '', '', '', '', 'NO', 0, 0, 0, 1);
673 --echo
674 DELETE FROM t1;
675 CALL clear_history();
676
677 --echo
678 --echo #
679 --echo # STEP 11.4 - STATEMENT ROLLBACK - AUTOCOMMIT ON - BINLOG FORMAT 'STATEMENT'
680 --echo #
681 --connection con1
682 SET SESSION binlog_format = STATEMENT;
683 SET SESSION AUTOCOMMIT = 1;
684
685 # A rolled back autocommit statement should be recorded as a rolled back transaction
686 #
687 # Force a statement rollback by attempting to update a transactional table
688 # and a non-replicatable table with binlog_format = STATEMENT.
689 --echo
690 --echo ## Expect binlog statement mode error
691 --error 0, ER_BINLOG_STMT_MODE_AND_NO_REPL_TABLES
692 UPDATE t1, performance_schema.setup_instruments pfs
693 SET t1.s1 = 1, pfs.timed = 'NO';
694
695 --echo
696 --echo ## Expect 1 rolled back transaction
697 --connection default
698 CALL transaction_verifier(1, @con1_thread_id, 'transaction', 'ROLLED BACK', 0, '', '', '', '', '', '', 'YES', 0, 0, 0, 1);
699 CALL clear_history();
700
701 --disable_testcase BUG#0000
702 # TODO: Add wait timer
703 --echo
704 --echo #
705 --echo # STEP 11.5 - DROPPED CONNECTION DURING TRANSACTION
706 --echo #
707 --connection con2
708 START TRANSACTION;
709 INSERT INTO t1 VALUES (1150, 'DROP CONNECTION');
710 --echo
711 --echo ## Expect 1 active transaction for connection 2
712 --connection default
713 CALL transaction_verifier(0, @con2_thread_id, 'transaction', 'ACTIVE', 0, '', '', '', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
714
715 --echo
716 --echo ## Drop connection
717 --connection con2
718 --disconnect con2
719 --source include/wait_until_disconnected.inc
720 --connection default
721
722 --echo
723 --echo ## Expect 0 transactions for connection 2
724 CALL transaction_verifier(0, @con2_thread_id, '', '', 0, '', '', '', '', '', '', '', 0, 0, 0, 0);
725 CALL transaction_verifier(1, @con2_thread_id, '', '', 0, '', '', '', '', '', '', '', 0, 0, 0, 0);
726 CALL clear_transaction_tables();
727
728 --enable_testcase
729
730 --echo
731 --echo ========================================================================
732 --echo # CLEAN UP
733 --echo ========================================================================
734 --echo
735
736 --disconnect con1
737 ##--disconnect con2
738
739 --connection default
740 DROP TABLE t1;
741 DROP TABLE nt1;
742
743 --source ../include/transaction_cleanup.inc