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
######## suite/innodb/t/innodb-wl6445-2 ##########
# #
# Testcase for worklog WL#6445: InnoDB should be able to work with #
# read-only tables
# All sub-test in this file focus on changinf file permission and #
# restarting server in read only. It verifies necessary operations #
# are blocked #
# #
# #
# Creation: #
# 2011-09-06 Implemented this test as part of WL#6445 #
# #
######################################################################
# Don't test this under valgrind, memory leaks will occur due restart
--source include/not_valgrind.inc
# Not supported in embedded
--source include/not_embedded.inc
-- source include/have_innodb.inc
# *nix specific command to remove write permission
# wanted to use perl to save original permission of file and restore back after
# chnage but could not find way to do.(with perl we could run test on windows too)
-- source include/not_windows.inc
let MYSQLD_DATADIR =`SELECT @@datadir`;
let $innodb_file_per_table = `SELECT @@innodb_file_per_table`;
let $innodb_file_format = `SELECT @@innodb_file_format`;
let $innodb_large_prefix_orig = `select @@innodb_large_prefix`;
SET GLOBAL innodb_file_per_table = 1;
SELECT @@innodb_file_per_table;
SET GLOBAL innodb_file_format = `Barracuda`;
SELECT @@innodb_file_format;
set global innodb_large_prefix=1;
SELECT @@innodb_large_prefix;
let $MYSQLD_DATADIR = `SELECT @@datadir`;
--disable_warnings
DROP DATABASE IF EXISTS testdb_wl6445;
--enable_warnings
CREATE DATABASE testdb_wl6445;
#------------------------------------------------------------------------------
# Testcase covers
# a) Create table/data ,
# b) remove write permission of ibdata , ib_logfile0
# c) restart server in --innodb-read-only mode and verfiy DDL/DML/DCL in read only mode
#------------------------------------------------------------------------------
--echo case # 1
SET GLOBAL innodb_file_per_table = 1;
SET GLOBAL innodb_file_format = `Barracuda`;
USE testdb_wl6445;
CREATE TABLE t1 ( i int PRIMARY KEY , j blob) ENGINE = InnoDB;
INSERT INTO t1 VALUES (1,repeat('a',200)),(2,repeat('b',200)),(3,repeat('c',200));
SELECT i,LEFT(j,20) FROM t1 ORDER BY i;
# remove write permissions
--exec chmod a-w $MYSQLD_DATADIR/ibdata1
--exec chmod a-w $MYSQLD_DATADIR/testdb_wl6445/t1.ibd
--exec chmod a-w $MYSQLD_DATADIR/ib_logfile0
#
--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
--shutdown_server 10
--source include/wait_until_disconnected.inc
# Do something while server is down
--enable_reconnect
# Write file to make mysql-test-run.pl start up the server again
--exec echo "restart: --innodb-read-only " > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
--source include/wait_until_connected_again.inc
USE testdb_wl6445;
SELECT i FROM t1 ORDER BY i;
--ERROR ER_CANT_LOCK
INSERT INTO t1 VALUES (11,repeat('a',200)),(12,repeat('b',200)),(13,repeat('c',200));
SELECT i,LEFT(j,20) FROM t1 ORDER BY i;
--ERROR ER_CANT_LOCK
INSERT INTO t1 VALUES (11,repeat('a',200)),(12,repeat('b',200)),(13,repeat('c',200));
SELECT i,LEFT(j,20) FROM t1 ORDER BY i;
--ERROR ER_INNODB_READ_ONLY
CREATE TABLE t2 ( i int , j blob) ENGINE = Innodb;
--ERROR ER_CANT_LOCK
UPDATE t1 SET i = i+1;
# Fix in next revision - known ( no data returned)
# SHOW ENGINE INNODB STATUS;
FLUSH STATUS;
FLUSH LOGS;
FLUSH TABLES t1;
FLUSH TABLES WITH READ LOCK;
UNLOCK TABLES;
#------------------------------------------------------------------------------
#clenaup
#------------------------------------------------------------------------------
#
--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
--shutdown_server 10
--source include/wait_until_disconnected.inc
# Do something while server is down
--exec chmod 0644 $MYSQLD_DATADIR/ibdata1
--exec chmod 0644 $MYSQLD_DATADIR/ib_logfile0
--exec chmod 0660 $MYSQLD_DATADIR/testdb_wl6445/t1.ibd
--enable_reconnect
# Write file to make mysql-test-run.pl start up the server again
--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
--source include/wait_until_connected_again.inc
DROP DATABASE IF EXISTS testdb_wl6445;
#------------------------------------------------------------------------------
# Testcase covers
# a) Create table/data ,
# b) remove write permission of ibdata , ib_logfile0 when server is running
# c) restart server in --innodb-read-only mode and verfiy DDL/DML/DCL in read only mode
#------------------------------------------------------------------------------
--echo case # 2
--disable_warnings
DROP DATABASE IF EXISTS testdb_wl6445;
--enable_warnings
CREATE DATABASE testdb_wl6445;
USE testdb_wl6445;
CREATE TABLE t1 ( i int PRIMARY KEY , j blob) ENGINE = InnoDB;
INSERT INTO t1 VALUES (1,repeat('a',200)),(2,repeat('b',200)),(3,repeat('c',200));
SELECT i,LEFT(j,20) FROM t1 ORDER BY i;
# remove write permissions
--exec chmod a-w $MYSQLD_DATADIR/ibdata1
--exec chmod a-w $MYSQLD_DATADIR/testdb_wl6445/t1.ibd
--exec chmod a-w $MYSQLD_DATADIR/ib_logfile0
#
# check dml/ddl after removing write permission
CREATE TABLE t2 ( i int PRIMARY KEY , j blob) ENGINE = InnoDB;
INSERT INTO t2 VALUES (1,repeat('a',200)),(2,repeat('b',200)),(3,repeat('c',200));
SELECT i,LEFT(j,20) FROM t2 ORDER BY i;
SELECT i,LEFT(j,20) FROM t1 ORDER BY i;
UPDATE t2 SET i = i + 10;
SELECT i,LEFT(j,20) FROM t2 ORDER BY i;
DELETE FROM t2;
SELECT i,LEFT(j,20) FROM t2 ORDER BY i;
--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
--shutdown_server 10
--source include/wait_until_disconnected.inc
# Do something while server is down
--enable_reconnect
# Write file to make mysql-test-run.pl start up the server again
--exec echo "restart: --innodb-read-only " > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
--source include/wait_until_connected_again.inc
USE testdb_wl6445;
SELECT i FROM t1 ORDER BY i;
SELECT i FROM t2 ORDER BY i;
--ERROR ER_CANT_LOCK
INSERT INTO t1 VALUES (11,repeat('a',200)),(12,repeat('b',200)),(13,repeat('c',200));
SELECT i,LEFT(j,20) FROM t1 ORDER BY i;
--ERROR ER_CANT_LOCK
INSERT INTO t1 VALUES (11,repeat('a',200)),(12,repeat('b',200)),(13,repeat('c',200));
SELECT i,LEFT(j,20) FROM t1 ORDER BY i;
--ERROR ER_TABLE_EXISTS_ERROR
CREATE TABLE t2 ( i int , j blob) ENGINE = Innodb;
--ERROR ER_INNODB_READ_ONLY
CREATE TABLE t3 ( i int , j blob) ENGINE = Innodb;
--ERROR ER_CANT_LOCK
UPDATE t1 SET i = i+1;
# Fix in next revision - known ( no data returned)
# SHOW ENGINE INNODB STATUS;
FLUSH STATUS;
FLUSH LOGS;
FLUSH TABLES t1,t2;
FLUSH TABLES WITH READ LOCK;
UNLOCK TABLES;
#------------------------------------------------------------------------------
# Testcase covers
# a) Create table/data ,
# b) remove write permission of ibdata , ib_logfile0
# c) try to restart server without --innodb-read-only mode
#------------------------------------------------------------------------------
# Note : write permission is already removed in previous case so we just
# start server without --innodb-read-only option
--echo case # 3
# We let our server restart attempts write to the file $error_log.
let $error_log= $MYSQLTEST_VARDIR/log/my_restart.err;
--error 0,1
--remove_file $error_log
# $error_log has to be processed by include/search_pattern_in_file.inc which
# contains Perl code requiring that the environment variable SEARCH_FILE points
# to this file.
let SEARCH_FILE= $error_log;
--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
--shutdown_server 10
--source include/wait_until_disconnected.inc
--echo # Try to restart the server without --innodb-read-only after removing
--echo # write permissions of system tablespace. Server should not start.
--echo # This confirms server is not automatically started in read-only mode.
#----------------------------------------------------------------------------------
# Detailed explanations of what happens are placed nearby the checks.
--error 1
--exec $MYSQLD_CMD --loose-console > $error_log 2>&1
# We get depending on the platform either "./ibdata1" or ".\ibdata1".
let SEARCH_PATTERN=InnoDB: The system tablespace must be writable;
--source include/search_pattern_in_file.inc
#------------------------------------------------------------------------------
#clenaup
#------------------------------------------------------------------------------
#
# Do something while server is down
--exec chmod 0644 $MYSQLD_DATADIR/ibdata1
--exec chmod 0644 $MYSQLD_DATADIR/ib_logfile0
--exec chmod 0660 $MYSQLD_DATADIR/testdb_wl6445/t1.ibd
--error 0,1
--remove_file $error_log
--enable_reconnect
# Write file to make mysql-test-run.pl start up the server again
--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
--source include/wait_until_connected_again.inc
USE testdb_wl6445;
DROP DATABASE testdb_wl6445;
eval SET GLOBAL INNODB_FILE_FORMAT=$innodb_file_format;
eval SET GLOBAL INNODB_FILE_PER_TABLE=$innodb_file_per_table;
eval SET GLOBAL innodb_large_prefix = $innodb_large_prefix_orig;