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
|
# ==== Purpose ====
#
# The purpose of this script is to test the behaviour of `SELECT` based
# statements that acquire table write-locks on regards to `GTID_NEXT` and
# `GTID_EXECUTED` state.
#
# ==== Requirements ====
#
# R1. When `GTID_NEXT` = `UNDEFINED`, executing a select statement which
# acquires write-locks will make the stament fail as a non-innocent
# statement, that is, it is required for the `GTID_NEXT` to be set to
# `AUTOMATIC` or `UUID:number`.
#
# R2. Despite the behaviour imposed by *R1*, executing a select statement that
# acquires write-locks will not trigger a change in `GTID_NEXT`,
# `GTID_OWNED` or `GTID_EXECUTED`.
#
# ==== Implementation ====
#
# TC.1 `GTID_NEXT` = `UNDEFINED` + Query statement acquiring wr-lock
# ------------------------------------------------------------------
# 1) SET `GTID_NEXT` to a manual value.
# 2) Execute an implicitly commiting statement.
# 3) Execute `SELECT ... FOR UPDATE`, expecting an error -
# `ER_GTID_NEXT_TYPE_UNDEFINED_GTID`
#
# TC.2 `GTID_NEXT` = `ASSIGNED` + Query statement acquiring wr-lock
# -----------------------------------------------------------------
# 1) SET `GTID_NEXT` to a manual value.
# 2) Execute `SELECT ... FOR UPDATE`.
# 3) Assert that `GTID_NEXT`, `GTID_OWNED` and `GTID_EXECUTED` remain unaltered.
# 4) Execute `SET GTID_NEXT = "AUTOMATIC"` expecting an error -
# `ER_CANT_SET_GTID_NEXT_WHEN_OWNING_GTID`
# 5) Commit in order to release `GTID_NEXT`.
#
# TC.3 `GTID_NEXT` = `AUTOMATIC` + Query statement acquiring wr-lock
# ------------------------------------------------------------------
# 1) SET `GTID_NEXT` to "AUTOMATIC".
# 2) Execute an implicitly commiting statement.
# 3) Execute `SELECT ... FOR UPDATE`.
# 4) Assert that `GITD_OWNED` and `GTID_EXECUTED` remain unaltered.
#
# ==== References ====
#
# binlog_gtid.binlog_gtid_select_taking_write_locks
#
# binlog_nogtid.binlog_nogtid_select_taking_write_locks
#
# BUG#27903848 [MYSQL 8.0 GA DEBUG BUILD] ASSERTION `GTID_NEXT_TYPE ==
# ANONYMOUS_GTID' FAILED.
#
CREATE TABLE t1(c1 INT) ENGINE=InnoDB;
CREATE TABLE t2(c1 INT) ENGINE=MEMORY;
--echo # TC.1 `GTID_NEXT` = `UNDEFINED` + Query statement acquiring wr-lock
--echo # ------------------------------------------------------------------
# 1) SET `GTID_NEXT` to a manual value.
SET GTID_NEXT='aaaaaaaa-aaaa-aaaa-aaaa-bbbbbbbbbbbb:1';
# 2) Execute an implicitly commiting statement.
CREATE TABLE t3(c1 CHAR CHARACTER SET utf16) ENGINE=InnoDB;
# 3) Execute `SELECT ... FOR UPDATE`, expecting an error -
# `ER_GTID_NEXT_TYPE_UNDEFINED_GTID`
--error ER_GTID_NEXT_TYPE_UNDEFINED_GTID
SELECT * FROM t1, t2, t3 FOR UPDATE OF t1, t2, t3 SKIP LOCKED;
--echo # TC.2 `GTID_NEXT` = `ASSIGNED` + Query statement acquiring wr-lock
--echo # -----------------------------------------------------------------
# 1) SET `GTID_NEXT` to a manual value.
SET GTID_NEXT='aaaaaaaa-aaaa-aaaa-aaaa-bbbbbbbbbbbb:2';
# 2) Execute `SELECT ... FOR UPDATE`.
--let $gtid_executed_before = `SELECT @@GTID_EXECUTED`
--let $gtid_owned_before = `SELECT @@GTID_OWNED`
SELECT * FROM t1, t3 FOR UPDATE OF t1, t3 SKIP LOCKED;
# 3) Assert that `GTID_NEXT`, `GTID_OWNED` and `GTID_EXECUTED` remain unaltered.
--let $assert_text = No change in `GTID_EXECUTED`
--let $assert_cond = "[SELECT @@GTID_EXECUTED]" = "$gtid_executed_before"
--source include/assert.inc
--let $assert_text = No change in `GTID_OWNED`
--let $assert_cond = "[SELECT @@GTID_OWNED]" = "$gtid_owned_before"
--source include/assert.inc
--let $assert_text = No change in `GTID_NEXT`
--let $assert_cond = "[SELECT @@GTID_NEXT]" = "aaaaaaaa-aaaa-aaaa-aaaa-bbbbbbbbbbbb:2"
--source include/assert.inc
# 4) Execute `SET GTID_NEXT = "AUTOMATIC"` expecting an error -
# `ER_CANT_SET_GTID_NEXT_WHEN_OWNING_GTID`
--error ER_CANT_SET_GTID_NEXT_WHEN_OWNING_GTID
SET GTID_NEXT='AUTOMATIC';
# 5) Commit in order to release `GTID_NEXT`.
COMMIT;
--echo # TC.3 `GTID_NEXT` = `AUTOMATIC` + Query statement acquiring wr-lock
--echo # ------------------------------------------------------------------
# 1) SET `GTID_NEXT` to "AUTOMATIC".
SET GTID_NEXT='AUTOMATIC';
# 2) Execute an implicitly commiting statement.
CREATE TABLE t4(c1 FLOAT) ENGINE=InnoDB;
# 3) Execute `SELECT ... FOR UPDATE`.
--let $gtid_executed_before = `SELECT @@GTID_EXECUTED`
--let $gtid_owned_before = `SELECT @@GTID_OWNED`
SELECT * FROM t1, t3, t4 FOR UPDATE OF t1, t3, t4 SKIP LOCKED;
# 4) Assert that `GTID_OWNED` and `GTID_EXECUTED` remain unaltered.
--let $assert_text = No change in `GTID_EXECUTED`
--let $assert_cond = "[SELECT @@GTID_EXECUTED]" = "$gtid_executed_before"
--source include/assert.inc
--let $assert_text = No change in `GTID_OWNED`
--let $assert_cond = "[SELECT @@GTID_OWNED]" = "$gtid_owned_before"
--source include/assert.inc
# Clean up
DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t3;
DROP TABLE t4;
RESET MASTER;
|
