Debian Codegraph @

mysql-test/suite/innodb/t/innodb_read_only-1.test

raw
1 ######## suite/innodb/t/innodb-wl6445-1 ##########
2 # #
3 # Testcase for worklog WL#6445: InnoDB should be able to work with #
4 # read-only tables
5 # All sub-test in this file focus on restarting server in read only #
6 # and verify necessary operations blocked #
7 # operations #
8 # #
9 # #
10 # Creation: #
11 # 2011-09-06 Implemented this test as part of WL#6445 #
12 # #
13 ######################################################################
14
15 # This test takes long time, so only run it with the --big-test mtr-flag.
16 --source include/big_test.inc
17
18 let MYSQLD_DATADIR =`SELECT @@datadir`;
19 let $innodb_file_per_table = `SELECT @@innodb_file_per_table`;
20 let $innodb_flush_log_at_trx_commit_orig =`select @@innodb_flush_log_at_trx_commit`;
21
22 SET GLOBAL innodb_file_per_table = 1;
23 SELECT @@innodb_file_per_table;
24
25 let $MYSQLD_DATADIR = `SELECT @@datadir`;
26 let $data_index_directory = DATA DIRECTORY='$MYSQL_TMP_DIR/alt_dir';
27
28 CREATE DATABASE testdb_wl6445;
29
30
31 #------------------------------------------------------------------------------
32 # Testcase 1 covers
33 # a) Create table/data , restart server in readonly mode
34 # b) verify DDL/DML/DCL in read only mode
35 # c) statements blocked for non root and root user from differet sessions.
36 #------------------------------------------------------------------------------
37 --echo case # 1
38 USE testdb_wl6445;
39 CREATE TABLE t1 ( i int PRIMARY KEY , j blob) ENGINE = InnoDB;
40
41 INSERT INTO t1 VALUES (1,repeat('a',200)),(2,repeat('b',200)),(3,repeat('c',200));
42 SELECT i,LEFT(j,20) FROM t1 ORDER BY i;
43
44 # Create user with minimun access
45 CREATE USER 'test1'@'localhost' IDENTIFIED BY '123';
46 GRANT ALL ON testdb_wl6445.* TO 'test1'@'localhost';
47 # Create user with root access
48 CREATE USER 'test2'@'localhost' IDENTIFIED BY '123';
49 GRANT ALL ON *.* TO 'test2'@'localhost';
50
51 # check when datadir and index dir are specified
52 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
53 EVAL CREATE TABLE otherlocation (id int PRIMARY KEY)
54 ENGINE=InnoDB , $data_index_directory;
55 INSERT INTO otherlocation VALUES (1),(2),(3);
56 SELECT * FROM otherlocation ORDER BY id;
57
58 #SHOW ENGINE INNODB STATUS;
59
60 #
61
62 --source include/restart_innodb_read_only.inc
63
64 SET GLOBAL innodb_file_per_table = 1;
65
66 USE testdb_wl6445;
67 SELECT i FROM t1 ORDER BY i;
68 --ERROR ER_CANT_LOCK
69 INSERT INTO t1 VALUES (11,repeat('a',200)),(12,repeat('b',200)),(13,repeat('c',200));
70 SELECT i,LEFT(j,20) FROM t1 ORDER BY i;
71
72 # User-manipulation statements are disallowed since ACL tables use InnoDB.
73 --error ER_ACL_OPERATION_FAILED
74 CREATE USER 'test3'@'localhost' IDENTIFIED BY '123';
75
76 --echo # connection con_test1_user try to modify
77 --connect (con_test1_user,'localhost','test1','123',)
78 SELECT user();
79 USE testdb_wl6445;
80 --ERROR ER_CANT_LOCK
81 INSERT INTO t1 VALUES (11,repeat('a',200)),(12,repeat('b',200)),(13,repeat('c',200));
82 SELECT i,LEFT(j,20) FROM t1 ORDER BY i;
83 --ERROR ER_READ_ONLY_MODE
84 CREATE TABLE t2 ( i int ,j blob) ENGINE = Innodb;
85 --ERROR ER_CANT_LOCK
86 UPDATE t1 SET i = i+1;
87
88 --echo # disconnect con_test1_user
89 --disconnect con_test1_user
90
91
92 --echo # connection con_test2_user
93 --connect (con_test2_user,'localhost','test2','123',)
94 SELECT user();
95 USE testdb_wl6445;
96 --ERROR ER_CANT_LOCK
97 INSERT INTO t1 VALUES (11,repeat('a',200)),(12,repeat('b',200)),(13,repeat('c',200));
98 SELECT i,LEFT(j,20) FROM t1 ORDER BY i;
99 --ERROR ER_READ_ONLY_MODE
100 CREATE TABLE t2 ( i int , j blob) ENGINE = Innodb;
101 --ERROR ER_CANT_LOCK
102 UPDATE t1 SET i = i+1;
103 # Fix in next revision - known ( no data returned)
104 #SHOW ENGINE INNODB STATUS;
105 FLUSH STATUS;
106 FLUSH LOGS;
107 FLUSH TABLES t1;
108 FLUSH TABLES WITH READ LOCK;
109 UNLOCK TABLES;
110
111
112 --echo # disconnect con_test2_user
113 --disconnect con_test2_user
114
115
116 --echo # connection default
117 --connection default
118 USE testdb_wl6445;
119 --ERROR ER_CANT_LOCK
120 INSERT INTO t1 VALUES (11,repeat('a',200)),(12,repeat('b',200)),(13,repeat('c',200));
121 SELECT i,LEFT(j,20) FROM t1 ORDER BY i;
122 --ERROR ER_READ_ONLY_MODE
123 CREATE TABLE t2 ( i int , j blob) ENGINE = Innodb;
124 --ERROR ER_CANT_LOCK
125 UPDATE t1 SET i = i+1;
126
127 # check with table having data and index directory specified
128 --ERROR ER_CANT_LOCK
129 INSERT INTO otherlocation VALUES (1),(2),(3);
130 SELECT * FROM otherlocation ORDER BY id;
131
132 # Fix in next revision - known ( no data returned)
133 # SHOW ENGINE INNODB STATUS;
134 FLUSH STATUS;
135 FLUSH LOGS;
136 FLUSH TABLES t1;
137 FLUSH TABLES WITH READ LOCK;
138 UNLOCK TABLES;
139
140
141 #------------------------------------------------------------------------------
142 # clean-up
143 #------------------------------------------------------------------------------
144 #
145
146 --source include/restart_mysqld.inc
147 DROP USER 'test1'@'localhost';
148 DROP USER 'test2'@'localhost';
149 DROP DATABASE testdb_wl6445;
150
151
152 #------------------------------------------------------------------------------
153 # Testcase 2 covers
154 # a) Create table/data , perform transaction , restart server in readonly mode
155 # b) verfiy DDL/DML/DCL in read only mode
156 #------------------------------------------------------------------------------
157 --echo case # 2
158 CREATE DATABASE testdb_wl6445;
159
160 SET GLOBAL innodb_file_per_table = 1;
161 USE testdb_wl6445;
162 CREATE TABLE t1 ( i int PRIMARY KEY , j blob) ENGINE = InnoDB;
163 INSERT INTO t1 VALUES (1,repeat('a',4000)),(2,repeat('b',4000)),(3,repeat('c',4000));
164 SELECT i,LEFT(j,20) FROM t1 ORDER BY i;
165
166
167 START TRANSACTION;
168 let $counter= 50;
169 --disable_query_log
170 while ($counter>3) {
171 EVAL INSERT INTO testdb_wl6445.t1 VALUES ($counter,repeat(CONCAT('a',$counter),2000));
172 --dec $counter
173 }
174 --enable_query_log
175 SAVEPOINT A;
176 let $counter= 100;
177 --disable_query_log
178 while ($counter>50) {
179 EVAL INSERT INTO testdb_wl6445.t1 VALUES ($counter,repeat(CONCAT('a',$counter),2000));
180 --dec $counter
181 }
182 --enable_query_log
183 SAVEPOINT B;
184 ROLLBACK TO A;
185 --echo "---commit first 50 records "
186 COMMIT;
187
188
189 SELECT COUNT(*) FROM testdb_wl6445.t1;
190 #SHOW ENGINE INNODB STATUS;
191
192 #
193
194 --source include/restart_innodb_read_only.inc
195
196 USE testdb_wl6445;
197 --ERROR ER_CANT_LOCK
198 INSERT INTO t1 VALUES (211,repeat('a',200)),(212,repeat('b',200)),(213,repeat('c',200));
199 SELECT i,LEFT(j,20) FROM t1 WHERE i%10=0 ORDER BY i;
200 --ERROR ER_READ_ONLY_MODE
201 CREATE TABLE t2 ( i int , j blob) ENGINE = Innodb;
202 --ERROR ER_CANT_LOCK
203 UPDATE t1 SET i = i+1;
204
205 SELECT i,LEFT(j,20) FROM t1 WHERE i%10=0 ORDER BY i;
206
207 # Check with transaction in read-only mode with innodb_flush_log_at_trx_commit=0
208 START TRANSACTION;
209 SET GLOBAL innodb_flush_log_at_trx_commit = 0;
210 let $counter= 200;
211 --disable_query_log
212 while ($counter>150) {
213 --ERROR ER_CANT_LOCK
214 EVAL INSERT INTO testdb_wl6445.t1 VALUES ($counter,repeat(CONCAT('a',$counter),2000));
215 --ERROR ER_CANT_LOCK
216 UPDATE testdb_wl6445.t1 SET i = i + 1;
217 --ERROR ER_CANT_LOCK
218 DELETE FROM testdb_wl6445.t1;
219 --dec $counter
220 }
221 --enable_query_log
222 SAVEPOINT A;
223 let $counter= 250;
224 --disable_query_log
225 while ($counter>200) {
226 --ERROR ER_CANT_LOCK
227 EVAL INSERT INTO testdb_wl6445.t1 VALUES ($counter,repeat(CONCAT('a',$counter),2000));
228 --ERROR ER_CANT_LOCK
229 UPDATE testdb_wl6445.t1 SET i = i + 1;
230 --ERROR ER_CANT_LOCK
231 DELETE FROM testdb_wl6445.t1;
232 --dec $counter
233 }
234 --enable_query_log
235 SAVEPOINT B;
236 ROLLBACK TO A;
237 --echo "---commit first 50 records with innodb_flush_log_at_trx_commit = 0 --"
238 COMMIT;
239
240 SELECT i,LEFT(j,20) FROM t1 WHERE i%10=0 ORDER BY i;
241
242 # Check with transaction in read-only mode with innodb_flush_log_at_trx_commit=1
243 START TRANSACTION;
244 SET GLOBAL innodb_flush_log_at_trx_commit = 1;
245 let $counter= 200;
246 --disable_query_log
247 while ($counter>150) {
248 --ERROR ER_CANT_LOCK
249 EVAL INSERT INTO testdb_wl6445.t1 VALUES ($counter,repeat(CONCAT('a',$counter),2000));
250 --ERROR ER_CANT_LOCK
251 UPDATE testdb_wl6445.t1 SET i = i + 1;
252 --ERROR ER_CANT_LOCK
253 DELETE FROM testdb_wl6445.t1;
254 --dec $counter
255 }
256 --enable_query_log
257 SAVEPOINT A;
258 let $counter= 250;
259 --disable_query_log
260 while ($counter>200) {
261 --ERROR ER_CANT_LOCK
262 EVAL INSERT INTO testdb_wl6445.t1 VALUES ($counter,repeat(CONCAT('a',$counter),2000));
263 --ERROR ER_CANT_LOCK
264 UPDATE testdb_wl6445.t1 SET i = i + 1;
265 --ERROR ER_CANT_LOCK
266 DELETE FROM testdb_wl6445.t1;
267 --dec $counter
268 }
269 --enable_query_log
270 SAVEPOINT B;
271 ROLLBACK TO A;
272 --echo "---commit first 50 records with innodb_flush_log_at_trx_commit = 1 --"
273 COMMIT;
274
275 SELECT i,LEFT(j,20) FROM t1 WHERE i%10=0 ORDER BY i;
276
277 # Check with transaction in read-only mode with innodb_flush_log_at_trx_commit=2
278 START TRANSACTION;
279 SET GLOBAL innodb_flush_log_at_trx_commit = 2;
280 let $counter= 200;
281 --disable_query_log
282 while ($counter>150) {
283 --ERROR ER_CANT_LOCK
284 EVAL INSERT INTO testdb_wl6445.t1 VALUES ($counter,repeat(CONCAT('a',$counter),2000));
285 --ERROR ER_CANT_LOCK
286 UPDATE testdb_wl6445.t1 SET i = i + 1;
287 --ERROR ER_CANT_LOCK
288 DELETE FROM testdb_wl6445.t1;
289 --dec $counter
290 }
291 --enable_query_log
292 SAVEPOINT A;
293 let $counter= 250;
294 --disable_query_log
295 while ($counter>200) {
296 --ERROR ER_CANT_LOCK
297 EVAL INSERT INTO testdb_wl6445.t1 VALUES ($counter,repeat(CONCAT('a',$counter),2000));
298 --ERROR ER_CANT_LOCK
299 UPDATE testdb_wl6445.t1 SET i = i + 1;
300 --ERROR ER_CANT_LOCK
301 DELETE FROM testdb_wl6445.t1;
302 --dec $counter
303 }
304 --enable_query_log
305 SAVEPOINT B;
306 ROLLBACK TO A;
307 --echo "---commit first 50 records with innodb_flush_log_at_trx_commit = 2 --"
308 COMMIT;
309
310
311 # Fix in next revision - known ( no data returned)
312 # SHOW ENGINE INNODB STATUS;
313 FLUSH STATUS;
314 FLUSH LOGS;
315 FLUSH TABLES t1;
316 FLUSH TABLES WITH READ LOCK;
317 UNLOCK TABLES;
318
319 #------------------------------------------------------------------------------
320 # clean-up
321 #------------------------------------------------------------------------------
322 #
323 --source include/restart_mysqld.inc
324 DROP DATABASE testdb_wl6445;
325
326 #------------------------------------------------------------------------------
327 # Testcase 3 covers
328 # a) Create table/data with trigger & procedure, partitioned table
329 # b) restart server in readonly mode
330 # c) verfiy behavior with trigger,procedure,partitioned and temp table
331 #------------------------------------------------------------------------------
332 --echo case # 3
333 CREATE DATABASE testdb_wl6445;
334 USE testdb_wl6445;
335 CREATE TABLE t1 ( i int PRIMARY KEY , j blob) ENGINE = InnoDB;
336 CREATE TABLE t2 ( t2_i int PRIMARY KEY , t2_j blob) ENGINE = InnoDB;
337 CREATE TABLE t3 ( i int PRIMARY KEY , j VARCHAR(20)) ENGINE = InnoDB;
338
339 # create partitioned table
340 CREATE TABLE t4 (val INT)
341 PARTITION BY LIST(val)(
342 PARTITION mypart_odd VALUES IN (1,3,5),
343 PARTITION MyPart_even VALUES IN (2,4,6));
344 INSERT INTO testdb_wl6445.t4 VALUES (1),(2),(3),(4),(5),(6);
345 SELECT * FROM testdb_wl6445.t4 ORDER BY val;
346
347 DELIMITER //;
348 CREATE TRIGGER TRIGGER_1 BEFORE INSERT ON testdb_wl6445.t1 FOR EACH ROW BEGIN
349 INSERT INTO testdb_wl6445.t2 SET t2_i = NEW.i , t2_j = NEW.j;
350 END;//
351 CREATE PROCEDURE proc1 (OUT param1 INT)
352 BEGIN
353 SELECT COUNT(*) INTO param1 FROM testdb_wl6445.t1;
354 END;//
355 CREATE PROCEDURE proc2 (IN param1 INT,IN param2 VARCHAR(20))
356 BEGIN
357 INSERT INTO testdb_wl6445.t3 VALUES (param1,param2);
358 END;//
359 DELIMITER ;//
360
361 INSERT INTO t1 VALUES (1,repeat('a',200)),(2,repeat('b',200)),(3,repeat('c',200));
362 SELECT i,LEFT(j,20) FROM t1 ORDER BY i;
363 SELECT t2_i,LEFT(t2_j,20) FROM t2 ORDER BY t2_i;
364 CALL proc1(@a);
365 SELECT @a;
366 CALL proc2(1,'test1');
367 SELECT * FROM t3;
368
369 #SHOW ENGINE INNODB STATUS;
370
371 #
372
373 --source include/restart_innodb_read_only.inc
374
375 SET GLOBAL innodb_file_per_table = 1;
376 USE testdb_wl6445;
377 --ERROR ER_CANT_LOCK
378 INSERT INTO t1 VALUES (11,repeat('a',200)),(12,repeat('b',200)),(13,repeat('c',200));
379 SELECT i,LEFT(j,20) FROM t1 ORDER BY i;
380 SELECT t2_i,LEFT(t2_j,20) FROM t2 ORDER BY t2_i;
381 CALL proc1(@a);
382 SELECT @a;
383 --ERROR ER_CANT_LOCK
384 CALL proc2(2,'test2');
385 SELECT * FROM t3;
386
387 # try to CREATE temp table
388 --ERROR ER_INNODB_READ_ONLY
389 CREATE TEMPORARY TABLE temp_1 ( i INT ) ENGINE = Innodb;
390
391 # Try to insert with partitioned table
392 --ERROR ER_CANT_LOCK
393 INSERT INTO testdb_wl6445.t4 VALUES (1),(2),(3),(4),(5),(6);
394 SELECT * FROM testdb_wl6445.t4 ORDER BY val;
395
396 FLUSH STATUS;
397 FLUSH LOGS;
398 FLUSH TABLES t1,t2,t3;
399 FLUSH TABLES WITH READ LOCK;
400 UNLOCK TABLES;
401
402
403 #------------------------------------------------------------------------------
404 # clean-up
405 #------------------------------------------------------------------------------
406 #
407
408 --source include/restart_mysqld.inc
409 DROP DATABASE testdb_wl6445;
410
411 #------------------------------------------------------------------------------
412 # Testcase 4 covers
413 # a) Create table/data restart server in readonly mode
414 # b) check the effect of server variables impacting change of RO
415 # ( they will be ignored)
416 #------------------------------------------------------------------------------
417 --echo case # 4
418 CREATE DATABASE testdb_wl6445;
419 USE testdb_wl6445;
420 CREATE TABLE t1 ( i int PRIMARY KEY , j VARCHAR(300), FULLTEXT KEY (j)) ENGINE = InnoDB;
421 CREATE INDEX idx1 ON testdb_wl6445.t1(i);
422 INSERT INTO t1 VALUES (1,repeat('a',200)),(2,repeat('b',200)),(3,repeat('c',200));
423 INSERT INTO t1 VALUES (4,'mysql database'),(5,'mysql database innodb support'),(6,'innodb engine');
424 SELECT i,LEFT(j,20) FROM t1 ORDER BY i;
425
426 #FTS Query
427 SELECT * FROM t1 WHERE MATCH (j) AGAINST ('mysql (+database -innodb)' IN BOOLEAN MODE) ;
428
429 # store initial values
430 let $innodb_max_purge_lag_orig = `SELECT @@innodb_max_purge_lag`;
431 let $innodb_max_purge_lag_delay_orig = `SELECT @@innodb_max_purge_lag_delay`;
432 let $innodb_purge_batch_size_orig = `select @@innodb_purge_batch_size`;
433 let $innodb_purge_threads_orig = `SELECT @@innodb_purge_threads`;
434 let $relay_log_purge_orig = `select @@relay_log_purge`;
435 let $innodb_buffer_pool_size_orig = `select @@innodb_buffer_pool_size`;
436 let $innodb_change_buffer_max_size_orig = `select @@innodb_change_buffer_max_size`;
437 let $innodb_change_buffering_orig = `select @@innodb_change_buffering`;
438 let $innodb_print_all_deadlocks_orig = `select @@innodb_print_all_deadlocks`;
439
440
441 let $extra_restart_parameters = --innodb_purge_threads=5 --innodb_buffer_pool_size=16M;
442 --source include/restart_innodb_read_only.inc
443 let $extra_restart_parameters = ;
444
445 SET GLOBAL innodb_max_purge_lag = 10;
446 SET GLOBAL innodb_max_purge_lag_delay = 10;
447 SET GLOBAL innodb_purge_batch_size = 600;
448 # purge thread is read only variable
449 #SET GLOBAL innodb_purge_threads = 5;
450 SET GLOBAL relay_log_purge = 0;
451 SET GLOBAL innodb_change_buffer_max_size = 30;
452 SET GLOBAL innodb_change_buffering = 'changes';
453 SET GLOBAL innodb_print_all_deadlocks = 'ON';
454
455
456 USE testdb_wl6445;
457 --ERROR ER_CANT_LOCK
458 INSERT INTO t1 VALUES (1,repeat('a',200)),(2,repeat('b',200)),(3,repeat('c',200));
459 --ERROR ER_CANT_LOCK
460 UPDATE t1 SET i = i + 1;
461 --ERROR ER_CANT_LOCK
462 DELETE FROM t1 ;
463
464
465 SELECT @@innodb_max_purge_lag,@@innodb_max_purge_lag_delay,@@innodb_purge_batch_size,
466 @@innodb_purge_threads,@@relay_log_purge,@@innodb_buffer_pool_size,
467 @@innodb_change_buffer_max_size,@@innodb_change_buffering,@@innodb_print_all_deadlocks;
468
469 SELECT i,LEFT(j,20) FROM t1 ORDER BY i;
470
471 #FTS Query
472 SELECT * FROM t1 WHERE MATCH (j) AGAINST ('mysql (+database -innodb)' IN BOOLEAN MODE) ;
473 SELECT * FROM t1 WHERE MATCH (j) AGAINST ('innodb') ;
474
475 # restore initial values
476 --disable_query_log
477 eval SET GLOBAL innodb_max_purge_lag=$innodb_max_purge_lag_orig;
478 eval SET GLOBAL innodb_max_purge_lag_delay=$innodb_max_purge_lag_delay_orig;
479 eval SET GLOBAL innodb_purge_batch_size = $innodb_purge_batch_size_orig;
480 #eval SET GLOBAL innodb_purge_threads = $innodb_purge_threads_orig;
481 eval SET GLOBAL relay_log_purge = $relay_log_purge_orig;
482 eval SET GLOBAL innodb_change_buffer_max_size = $innodb_change_buffer_max_size_orig;
483 eval SET GLOBAL innodb_change_buffering = $innodb_change_buffering_orig;
484 eval SET GLOBAL innodb_print_all_deadlocks = $innodb_print_all_deadlocks_orig;
485 --enable_query_log
486
487
488 #------------------------------------------------------------------------------
489 # clean-up
490 #------------------------------------------------------------------------------
491 #
492
493 --source include/restart_mysqld.inc
494 DROP DATABASE testdb_wl6445;
495
496 #------------------------------------------------------------------------------
497 # Testcase 5 covers
498 # a) Create table/data restart server in readonly mode
499 # b) check starting with both --read-only and --innodb-read-only
500 # c) check starting with only --read-only
501 #------------------------------------------------------------------------------
502 --echo case # 5
503 CREATE DATABASE testdb_wl6445;
504 USE testdb_wl6445;
505 CREATE TABLE t1 ( i int PRIMARY KEY , j VARCHAR(300), FULLTEXT KEY (j)) ENGINE = InnoDB;
506 CREATE INDEX idx1 ON testdb_wl6445.t1(i);
507 INSERT INTO t1 VALUES (1,repeat('a',200)),(2,repeat('b',200)),(3,repeat('c',200));
508 INSERT INTO t1 VALUES (4,'mysql database'),(5,'mysql database innodb support'),(6,'innodb engine');
509 #FTS Query
510 SELECT * FROM t1 WHERE MATCH (j) AGAINST ('mysql (+database -innodb)' IN BOOLEAN MODE) ;
511 SELECT * FROM t1 WHERE MATCH (j) AGAINST ('innodb') ;
512
513 let $extra_restart_parameters = --read-only;
514 --source include/restart_innodb_read_only.inc
515 let $extra_restart_parameters = ;
516 USE testdb_wl6445;
517 show tables;
518 select * from t1 where i = 4;
519 # check if root user able to modify
520 --ERROR ER_CANT_LOCK
521 INSERT INTO t1 VALUES (11,repeat('a',200)),(12,repeat('b',200)),(13,repeat('c',200));
522 --ERROR ER_CANT_LOCK
523 UPDATE t1 SET i = i + 20;
524 --ERROR ER_CANT_LOCK
525 DELETE FROM t1;
526 --ERROR ER_READ_ONLY_MODE
527 CREATE TABLE t2 ( i INT ) ENGINE = Innodb;
528
529 # User-manipulation statements are disallowed since ACL tables use InnoDB.
530 --error ER_ACL_OPERATION_FAILED
531 CREATE USER 'test5'@'localhost' IDENTIFIED BY '123';
532
533 let $restart_parameters = restart: --read-only;
534 --source include/restart_mysqld.inc
535 let $restart_parameters = restart;
536 USE testdb_wl6445;
537 # check if root user able to do DML/DDL
538 INSERT INTO t1 VALUES (11,repeat('a',200)),(12,repeat('b',200)),(13,repeat('c',200));
539 SELECT i,LEFT(j,20) FROM t1 ORDER BY i;
540 UPDATE t1 SET i = i + 20;
541 SELECT i,LEFT(j,20) FROM t1 ORDER BY i;
542 DELETE FROM t1;
543 SELECT i,LEFT(j,20) FROM t1 ORDER BY i;
544 CREATE TABLE t2 ( i INT ) ENGINE = Innodb;
545 CREATE USER 'test5_2'@'localhost' IDENTIFIED BY '123';
546 GRANT ALL ON testdb_wl6445.* TO 'test5_2'@'localhost';
547
548
549 #------------------------------------------------------------------------------
550 # clean-up
551 #------------------------------------------------------------------------------
552 #
553
554 --source include/restart_mysqld.inc
555 DROP USER 'test5_2'@'localhost';
556 DROP DATABASE testdb_wl6445;
557
558 #------------------------------------------------------------------------------
559 # Testcase 6 covers
560 # a) Create table/data restart server in readonly mode
561 # b) check mysqldump/mysqlimport work
562 #------------------------------------------------------------------------------
563 --echo case # 6
564 CREATE DATABASE testdb_wl6445;
565 USE testdb_wl6445;
566 CREATE TABLE t1 ( i int PRIMARY KEY ) ENGINE = InnoDB;
567 CREATE INDEX idx1 ON testdb_wl6445.t1(i);
568 INSERT INTO t1 VALUES (1),(2),(3);
569 SELECT i FROM t1 ORDER BY i;
570
571 --echo # Creating a temp sql file to be loaded.
572 --write_file $MYSQLTEST_VARDIR/tmp/t1.sql
573 11
574 12
575 13
576 EOF
577
578
579 --source include/restart_innodb_read_only.inc
580
581 USE testdb_wl6445;
582 # try mysql import
583 --replace_regex /.*Error//i
584 --error 1
585 --exec $MYSQL_IMPORT -uroot testdb_wl6445 $MYSQLTEST_VARDIR/tmp/t1.sql 2>&1
586 # only 3 records get in select as import shoudl fail due to --innodb-read-only
587 SELECT i FROM t1 ORDER BY i;
588
589
590 # mysqldump works
591 --exec $MYSQL_DUMP --skip-comments --databases testdb_wl6445 > $MYSQLTEST_VARDIR/tmp/testdb_wl6445_dump.txt
592
593 --error 0,1
594 --remove_file $MYSQLTEST_VARDIR/tmp/testdb_wl6445_dump.txt
595 --error 0,1
596 --remove_file $MYSQLTEST_VARDIR/tmp/t1.sql
597
598 #------------------------------------------------------------------------------
599 # clean-up
600 #------------------------------------------------------------------------------
601 --source include/restart_mysqld.inc
602 DROP DATABASE testdb_wl6445;
603 --rmdir $MYSQL_TMP_DIR/alt_dir/testdb_wl6445
604 --rmdir $MYSQL_TMP_DIR/alt_dir
605 --disable_query_log
606 eval SET GLOBAL INNODB_FILE_PER_TABLE=$innodb_file_per_table;
607 eval SET GLOBAL innodb_flush_log_at_trx_commit = $innodb_flush_log_at_trx_commit_orig;
608 call mtr.add_suppression("deleting orphaned .ibd file");
609 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Cannot open datafile for read-only:");
610 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Operating system error number 2 in a file operation.");
611 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* The error means the system cannot find the path specified.");
612 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.");
613 --enable_query_log
614
615
616 #------------------------------------------------------------------------------
617 # Testcase 7 covers
618 # a) Create/Load table in normal mode such that index level > 1.
619 # b) Restart server in read-only mode and try to check validity of table/data.
620 #------------------------------------------------------------------------------
621 --echo case # 7 (restart server in read-only mode and perform check table)
622 use test;
623 #
624 create table t1 (
625 i int,
626 f float,
627 c char(100),
628 b blob
629 ) engine = innodb;
630 #
631 delimiter |;
632 CREATE PROCEDURE populate_t1()
633 BEGIN
634 DECLARE i INT DEFAULT 1;
635 while (i <= 100) DO
636 insert into t1 values (
637 i, 2.323,
638 'adfaqfafafafafsasfafsa',
639 'badfadfsaeerer234234asdfdsafdasr2354234324234234');
640 SET i = i + 1;
641 END WHILE;
642 END|
643 delimiter ;|
644 begin;
645 call populate_t1();
646 commit;
647 check table t1 extended;
648 #
649 #
650 --source include/restart_innodb_read_only.inc
651 #
652 check table t1 extended;
653
654 #------------------------------------------------------------------------------
655 # clean-up
656 #------------------------------------------------------------------------------
657 let $restart_parameters = restart;
658 --source include/restart_mysqld.inc
659 check table t1 extended;
660 drop table t1;
661 drop procedure populate_t1;