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
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
|
# -----------------------------------------------------------------------
# Create a base table, an SQL statement file, and a non-super user with a connection.
# -----------------------------------------------------------------------
create table t_alter(i int);
insert into t_alter values(1);
create user nonsuper@localhost;
grant CREATE, SELECT, DROP on *.* to nonsuper@localhost;
connect con_nonsuper, localhost, nonsuper,, test;
connect con_super, localhost, root,, test;
# -----------------------------------------------------------------------
# Set read-only.
# -----------------------------------------------------------------------
#
# Server side DDL execution succeeds with read_only = 1.
#
# restart: --init-file=INIT_SQL --read-only=1 --log-error=MYSQLD_LOG
select * from t_create;
@@innodb_read_only @@transaction_read_only @@read_only @@super_read_only
0 0 1 0
drop table t_create;
show create table t_alter;
Table Create Table
t_alter CREATE TABLE `t_alter` (
`i` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='new comment'
alter table t_alter comment = '';
select @@innodb_read_only, @@transaction_read_only, @@read_only, @@super_read_only;
@@innodb_read_only @@transaction_read_only @@read_only @@super_read_only
0 0 1 0
#
# A non-super user cannot create or alter tables with read_only = 1.
#
connection con_nonsuper;
create table t_create (a int);
ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
alter table t_alter comment = 'new comment';
ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
#
# A super user can create or alter tables with read_only = 1.
#
connection con_super;
create table t_create (a int);
drop table t_create;
alter table t_alter comment = 'new comment';
alter table t_alter comment = '';
# -----------------------------------------------------------------------
# Set super-read-only.
# -----------------------------------------------------------------------
#
# Server side DDL execution succeeds with super_read_only = 1 because we
# set opt_super_read_only after the init-file is processed.
#
# restart: --init-file=INIT_SQL --super-read-only=1 --log-error=MYSQLD_LOG
set @@global.super_read_only = 0;
select * from t_create;
@@innodb_read_only @@transaction_read_only @@read_only @@super_read_only
0 0 1 1
drop table t_create;
show create table t_alter;
Table Create Table
t_alter CREATE TABLE `t_alter` (
`i` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='new comment'
alter table t_alter comment = '';
set @@global.super_read_only = 1;
select @@innodb_read_only, @@transaction_read_only, @@read_only, @@super_read_only;
@@innodb_read_only @@transaction_read_only @@read_only @@super_read_only
0 0 1 1
#
# A non-super user cannot create or alter tables with super_read_only = 1.
#
connection con_nonsuper;
create table t_create (a int);
ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
alter table t_alter comment = 'new comment';
ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
#
# A super user cannot create or alter tables with super_read_only = 1.
#
connection con_super;
create table t_create (a int);
ERROR HY000: The MySQL server is running with the --super-read-only option so it cannot execute this statement
alter table t_alter comment = 'new comment';
ERROR HY000: The MySQL server is running with the --super-read-only option so it cannot execute this statement
# -----------------------------------------------------------------------
# Set transaction-read-only.
# -----------------------------------------------------------------------
#
# Server side DDL execution succeeds with transaction_read_only = 1 because
# the THDs that execute statements have transaction_read_only explicitly
# turned off.
#
# restart: --init-file=INIT_SQL --transaction-read-only=1 --log-error=MYSQLD_LOG
set @@session.transaction_read_only = 0;
select * from t_create;
@@innodb_read_only @@transaction_read_only @@read_only @@super_read_only
0 0 0 0
drop table t_create;
show create table t_alter;
Table Create Table
t_alter CREATE TABLE `t_alter` (
`i` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='new comment'
alter table t_alter comment = '';
set @@session.transaction_read_only = 1;
select @@innodb_read_only, @@transaction_read_only, @@read_only, @@super_read_only;
@@innodb_read_only @@transaction_read_only @@read_only @@super_read_only
0 1 0 0
#
# A non-super user cannot create or alter tables with transaction_read_only = 1.
#
connection con_nonsuper;
create table t_create (a int);
ERROR 25006: Cannot execute statement in a READ ONLY transaction.
alter table t_alter comment = 'new comment';
ERROR 25006: Cannot execute statement in a READ ONLY transaction.
#
# A super user cannot create or alter tables with transaction_read_only = 1.
#
connection con_super;
create table t_create (a int);
ERROR 25006: Cannot execute statement in a READ ONLY transaction.
alter table t_alter comment = 'new comment';
ERROR 25006: Cannot execute statement in a READ ONLY transaction.
# -----------------------------------------------------------------------
# Set innodb-read-only.
# -----------------------------------------------------------------------
#
# Server side DDL execution fails with innodb_read_only = 1.
#
# restart: --init-file=INIT_SQL --innodb-read-only=1 --log-error=MYSQLD_LOG
Pattern "InnoDB read-only mode" found
select * from t_create;
ERROR 42S02: Table 'test.t_create' doesn't exist
show create table t_alter;
Table Create Table
t_alter CREATE TABLE `t_alter` (
`i` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
select @@innodb_read_only, @@transaction_read_only, @@read_only, @@super_read_only;
@@innodb_read_only @@transaction_read_only @@read_only @@super_read_only
1 0 0 0
#
# A non-super user cannot create or alter tables with innodb_read_only = 1.
#
connection con_nonsuper;
create table t_create (a int);
ERROR HY000: Running in read-only mode
alter table t_alter comment = 'new comment';
ERROR HY000: Running in read-only mode
#
# A super user cannot create or alter tables with innodb_read_only = 1.
#
connection con_super;
create table t_create (a int);
ERROR HY000: Running in read-only mode
alter table t_alter comment = 'new comment';
ERROR HY000: Running in read-only mode
# -----------------------------------------------------------------------
# Upgrade from 5.7.22.
# -----------------------------------------------------------------------
#
# Prepare data directory
#
#
# Upgrade with read-only = 1 succeeds.
#
# restart: --datadir=MYSQLD_DATADIR --read-only=1 --log-error=MYSQLD_LOG
select @@innodb_read_only, @@transaction_read_only, @@read_only, @@super_read_only;
@@innodb_read_only @@transaction_read_only @@read_only @@super_read_only
0 0 1 0
#
# Upgrade with super-read-only = 1 succeeds.
#
# restart: --datadir=MYSQLD_DATADIR --super-read-only=1 --log-error=MYSQLD_LOG
select @@innodb_read_only, @@transaction_read_only, @@read_only, @@super_read_only;
@@innodb_read_only @@transaction_read_only @@read_only @@super_read_only
0 0 1 1
#
# Upgrade with transaction-read-only = 1 succeeds.
#
# restart: --datadir=MYSQLD_DATADIR --transaction-read-only=1 --log-error=MYSQLD_LOG
select @@innodb_read_only, @@transaction_read_only, @@read_only, @@super_read_only;
@@innodb_read_only @@transaction_read_only @@read_only @@super_read_only
0 1 0 0
#
# Upgrade with innodb-read-only = 1 fails.
#
Pattern "Database upgrade cannot be accomplished in read-only mode" found
# -----------------------------------------------------------------------
# Upgrade from 8.0.15.
# -----------------------------------------------------------------------
#
# Prepare data directory
#
#
# Upgrade with read-only = 1 succeeds.
#
# restart: --datadir=MYSQLD_DATADIR --read-only=1 --log-error=MYSQLD_LOG
select @@innodb_read_only, @@transaction_read_only, @@read_only, @@super_read_only;
@@innodb_read_only @@transaction_read_only @@read_only @@super_read_only
0 0 1 0
#
# Upgrade with super-read-only = 1 succeeds.
#
# restart: --datadir=MYSQLD_DATADIR --super-read-only=1 --log-error=MYSQLD_LOG
select @@innodb_read_only, @@transaction_read_only, @@read_only, @@super_read_only;
@@innodb_read_only @@transaction_read_only @@read_only @@super_read_only
0 0 1 1
#
# Upgrade with transaction-read-only = 1 succeeds.
#
# restart: --datadir=MYSQLD_DATADIR --transaction-read-only=1 --log-error=MYSQLD_LOG
select @@innodb_read_only, @@transaction_read_only, @@read_only, @@super_read_only;
@@innodb_read_only @@transaction_read_only @@read_only @@super_read_only
0 1 0 0
#
# Upgrade with innodb-read-only = 1 fails.
#
Pattern "Cannot upgrade format \(v3\) of redo log files in read-only mode" found
# -----------------------------------------------------------------------
# Initialize.
# -----------------------------------------------------------------------
#
# Initialize with read-only = 1 succeeds.
#
#
# Initialize with super-read-only = 1 succeeds.
#
#
# Initialize with transaction-read-only = 1 succeeds.
#
#
# Initialize with innodb-read-only = 1 fails.
#
Pattern "--innodb-read-only is set" found
# -----------------------------------------------------------------------
# Cleanup.
# -----------------------------------------------------------------------
connection default;
disconnect con_nonsuper;
disconnect con_super;
# restart:
drop table t_alter;
drop user nonsuper@localhost;
|
