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
|
# Test Enable and Disable redo logging
--disable_query_log
call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* InnoDB redo logging is disabled. All data could be lost in case of a server crash");
call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* InnoDB redo logging is enabled. Data is now safe and can be recovered in case of a server crash.");
--enable_query_log
--echo #1. Check Redo logging syntax and status
SHOW STATUS LIKE 'Innodb_redo_log_enabled';
--echo
SELECT * FROM performance_schema.global_status WHERE variable_name like 'Innodb_redo_log_enabled';
--echo
ALTER INSTANCE DISABLE INNODB REDO_LOG;
--echo
SHOW STATUS LIKE 'Innodb_redo_log_enabled';
--echo
SELECT * FROM performance_schema.global_status WHERE variable_name like 'Innodb_redo_log_enabled';
--echo
ALTER INSTANCE ENABLE INNODB REDO_LOG;
--echo
SHOW STATUS LIKE 'Innodb_redo_log_enabled';
--echo
SELECT * FROM performance_schema.global_status WHERE variable_name like 'Innodb_redo_log_enabled';
--echo
--echo #2. Check privilege
CREATE USER 'user_redo'@'localhost' IDENTIFIED BY '123';
--echo
GRANT SELECT ON performance_schema.* to 'user_redo'@'localhost';
--echo
--connect (con1,'localhost','user_redo','123',)
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
ALTER INSTANCE DISABLE INNODB REDO_LOG;
--echo
SELECT * FROM performance_schema.global_status WHERE variable_name like 'Innodb_redo_log_enabled';
--echo
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
ALTER INSTANCE ENABLE INNODB REDO_LOG;
--echo
--connection default
GRANT INNODB_REDO_LOG_ENABLE ON *.* TO 'user_redo'@'localhost';
--echo
--connection con1
ALTER INSTANCE DISABLE INNODB REDO_LOG;
--echo
SELECT * FROM performance_schema.global_status WHERE variable_name like 'Innodb_redo_log_enabled';
--echo
ALTER INSTANCE ENABLE INNODB REDO_LOG;
--echo
SELECT * FROM performance_schema.global_status WHERE variable_name like 'Innodb_redo_log_enabled';
--echo
--connection default
--disconnect con1
DROP USER 'user_redo'@'localhost';
--echo
--echo #3. Check basic DDL and DML when redo logging is disabled
ALTER INSTANCE DISABLE INNODB REDO_LOG;
SELECT * FROM performance_schema.global_status WHERE variable_name like 'Innodb_redo_log_enabled';
--let check_dd = 0
--let NO_EXT_PATH = 1
--let $check_option = CREATE
--source suite/innodb/include/partition_upgrade_check.inc
ALTER INSTANCE ENABLE INNODB REDO_LOG;
SELECT * FROM performance_schema.global_status WHERE variable_name like 'Innodb_redo_log_enabled';
--let $check_option = DML
--source suite/innodb/include/partition_upgrade_check.inc
ALTER INSTANCE DISABLE INNODB REDO_LOG;
SELECT * FROM performance_schema.global_status WHERE variable_name like 'Innodb_redo_log_enabled';
--let $check_option = DROP
--source suite/innodb/include/partition_upgrade_check.inc
--let $check_option = CREATE
--source suite/innodb/include/partition_upgrade_check.inc
--let $check_option = DML
--source suite/innodb/include/partition_upgrade_check.inc
--echo #3A. Check shutdown and restart
let SEARCH_FILE = $MYSQLTEST_VARDIR/log/my_restart.err;
--echo # Restart the server with --log-error
--replace_result $SEARCH_FILE SEARCH_FILE
--let $restart_parameters = restart: --log-error=$SEARCH_FILE --no-console
--source include/restart_mysqld.inc
SELECT * FROM performance_schema.global_status WHERE variable_name like 'Innodb_redo_log_enabled';
--let $check_option = DROP
--source suite/innodb/include/partition_upgrade_check.inc
--let $check_option = CREATE
--source suite/innodb/include/partition_upgrade_check.inc
--let $check_option = DML
--source suite/innodb/include/partition_upgrade_check.inc
--let $check_option = DDL
--source suite/innodb/include/partition_upgrade_check.inc
--echo #3B. Check shutdown and restart with innodb_fast_shutdown = 2
SET GLOBAL innodb_fast_shutdown = 2;
--let $restart_parameters = restart:
--source include/restart_mysqld.inc
--let SEARCH_PATTERN=\\[Warning\\] .*MY-\\d+.* InnoDB cannot do cold shutdown 'innodb_fast_shutdown = 2' and is forcing 'innodb_fast_shutdown = 1' as redo logging is disabled. InnoDB would flush all dirty pages to ensure physical data consistency.
--source include/search_pattern.inc
--remove_file $SEARCH_FILE
SELECT * FROM performance_schema.global_status WHERE variable_name like 'Innodb_redo_log_enabled';
--echo #3C. Check shutdown and restart with innodb-read-only
--source include/add_mtr_suppression_for_innodb_readonly.inc
--let $restart_parameters = restart: --innodb-read-only
--source include/restart_mysqld.inc
SELECT * FROM performance_schema.global_status WHERE variable_name like 'Innodb_redo_log_enabled';
--let $restart_parameters = restart:
--source include/restart_mysqld.inc
# Avoid printing tables names in capital letters for case insensitive platforms
--let $check_option = CHECK
--source suite/innodb/include/partition_upgrade_check.inc
--let $check_option = DROP
--source suite/innodb/include/partition_upgrade_check.inc
ALTER INSTANCE ENABLE INNODB REDO_LOG;
SELECT * FROM performance_schema.global_status WHERE variable_name like 'Innodb_redo_log_enabled';
|
