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
|
RESET MASTER;
call mtr.add_suppression("Did not write failed .* into binary log while storing table level and column level grants in the privilege tables");
CREATE TABLE t1(a INT, b INT);
CREATE USER u1@h;
#
# Verify that u1 is added into mysql.user.
#
SELECT user FROM mysql.user where user='u1';
user
u1
GRANT SELECT(a), SELECT(c) ON t1 TO u1@h;
ERROR 42S22: Unknown column 'c' in 't1'
#
# The SELECT privilege on column a is not granted to u1@h,
# which means that GRANT can not be failed partially when
# granting an available privilege and a non-available
# privilege to an existent user.
#
SELECT user, column_name, column_priv FROM mysql.columns_priv;
user column_name column_priv
#
# Verify that the failed GRANT statement does not generate
# a gtid and save the gtid into @@GLOBAL.GTID_EXECUTED.
#
include/assert.inc [Committed gtid MASTER_UUID:3 into @@GLOBAL.GTID_EXECUTED]
#
# Verify that the failed GRANT statement is not binlogged
#
include/show_binlog_events.inc
GRANT SELECT(a) ON t1 TO u1@h;
#
# Verify that the grant info is added into mysql.columns_priv.
#
SELECT user, column_name, column_priv FROM mysql.columns_priv;
user column_name column_priv
u1 a Select
REVOKE SELECT(a), SELECT(b) ON t1 FROM u1@h;
ERROR 42000: There is no such grant defined for user 'u1' on host 'h' on table 't1'
#
# The SELECT privilege is removed on column a from user u1,
# which means that REVOKE statement can be failed partially
# when revoking multiple privileges from a user.
#
SELECT user, column_name, column_priv FROM mysql.columns_priv;
user column_name column_priv
u1 a Select
#
# Verify that the partially failed REVOKE statement does not generate
# a gtid master_uuid:5 and save the gtid into @@GLOBAL.GTID_EXECUTED.
#
include/assert.inc [Committed gtid MASTER_UUID:4 into @@GLOBAL.GTID_EXECUTED]
#
# Verify that the partially failed REVOKE statement is not binlogged
#
include/show_binlog_events.inc
DROP USER u1@h, u2@h;
ERROR HY000: Operation DROP USER failed for 'u2'@'h'
#
# u1@h should not have been dropped.
#
SELECT user FROM mysql.user where user='u1';
user
u1
#
# Verify that the failed DROP USER statement does not generate
# a gtid and save it into @@GLOBAL.GTID_EXECUTED.
#
include/assert.inc [Does not generate MASTER_UUID:5 and commit it into @@GLOBAL.GTID_EXECUTED]
#
# Verify failed DROP USER statement is not binlogged
#
include/show_binlog_events.inc
RENAME TABLE t1 TO t2, t3 TO t4;
ERROR 42S02: Table 'test.t3' doesn't exist
#
# The table t1 was not renamed, which means RENAME TABLE
# can not be failed partially.
#
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int DEFAULT NULL,
`b` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
#
# Verify that the failed RENAME TABLE statement does not generate
# a new GTID MASTER_UUID:7 and save it into @@GLOBAL.GTID_EXECUTED.
#
include/assert.inc [Does not generate MASTER_UUID:6 and commit it into @@GLOBAL.GTID_EXECUTED]
#
# Verify that the failed RENAME TABLE statement is not binlogged
#
include/show_binlog_events.inc
#
# The OPTIMIZE TABLE statement can be failed partially when optimizing
# multiple tables, which contain a non-existent table.
#
OPTIMIZE TABLE t1, t_non_existent;
Table Op Msg_type Msg_text
test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
test.t1 optimize status OK
test.t_non_existent optimize Error Table 'test.t_non_existent' doesn't exist
test.t_non_existent optimize status Operation failed
#
# When optimizing a non-existent table, verify that the OPTIMIZE TABLE
# statement generates a gtid and save it into @@GLOBAL.GTID_EXECUTED.
#
include/assert.inc [Committed gtid MASTER_UUID:7 into @@GLOBAL.GTID_EXECUTED]
#
# When optimizing a non-existent table, verify that the OPTIMIZE TABLE
# statement is binlogged.
#
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
binlog.000001 # Query # # use `test`; OPTIMIZE TABLE t1, t_non_existent
#
# The ANALYZE TABLE statement can be failed partially when analyzing
# multiple tables, which contain a non-existent table.
#
ANALYZE TABLE t1, t_non_existent;
Table Op Msg_type Msg_text
test.t1 analyze status OK
test.t_non_existent analyze Error Table 'test.t_non_existent' doesn't exist
test.t_non_existent analyze status Operation failed
#
# When analyzing a non-existent table, verify that the ANALYZE TABLE
# statement generates a gtid and save it into @@GLOBAL.GTID_EXECUTED.
#
include/assert.inc [Committed gtid MASTER_UUID:8 into @@GLOBAL.GTID_EXECUTED]
#
# When analyzing a non-existent table, verify that the ANALYZE TABLE
# statement is binlogged.
#
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
binlog.000001 # Query # # use `test`; ANALYZE TABLE t1, t_non_existent
#
# The REPAIR TABLE statement can be failed partially when repairing
# multiple tables, which contain a non-existent table.
#
REPAIR TABLE t1, t_non_existent;
Table Op Msg_type Msg_text
test.t1 repair note The storage engine for the table doesn't support repair
test.t_non_existent repair Error Table 'test.t_non_existent' doesn't exist
test.t_non_existent repair status Operation failed
#
# When repairing a non-existent table, verify that the REPAIR TABLE
# statement generates a gtid and save it into @@GLOBAL.GTID_EXECUTED.
#
include/assert.inc [Committed gtid MASTER_UUID:9 into @@GLOBAL.GTID_EXECUTED]
#
# When repairing a non-existent table, verify that the REPAIR TABLE
# statement is binlogged.
#
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
binlog.000001 # Query # # use `test`; REPAIR TABLE t1, t_non_existent
#
# The CHECKSUM TABLE statement can be failed partially when checksuming
# multiple tables, which contain a non-existent table.
#
CHECKSUM TABLE t1, t_non_existent;
Table Checksum
test.t1 0
test.t_non_existent NULL
Warnings:
Error 1146 Table 'test.t_non_existent' doesn't exist
#
# When checksuming a non-existent table, verify that the CHECKSUM TABLE
# statement generates a gtid and save it into @@GLOBAL.GTID_EXECUTED.
#
include/assert.inc [Does not generate MASTER_UUID:10 and commit it into @@GLOBAL.GTID_EXECUTED]
#
# When checksuming a non-existent table, verify that the CHECKSUM TABLE
# statement is not binlogged.
#
include/show_binlog_events.inc
DROP TABLE t1;
DROP USER u1@h;
|
