1 # name : show_slave_status_nolock.patch
6 # Any small change to this file in the main branch
7 # should be done or reviewed by the maintainer!
9 +++ b/patch_info/show_slave_status_nolock.patch
11 +File=show_slave_status_nolock.patch
12 +Name= SHOW SLAVE STATUS NOLOCK
14 +Author=Percona <info@percona.com>
16 +Comment= Implement SHOW SLAVE STATUS without lock (STOP SLAVE lock the same mutex what lock SHOW SLAVE STATUS)
20 { "NONE", SYM(NONE_SYM)},
21 { "NOT", SYM(NOT_SYM)},
22 { "NO_WRITE_TO_BINLOG", SYM(NO_WRITE_TO_BINLOG)},
23 + { "NOLOCK", SYM(NOLOCK_SYM)},
24 { "NULL", SYM(NULL_SYM)},
25 { "NUMERIC", SYM(NUMERIC_SYM)},
26 { "NVARCHAR", SYM(NVARCHAR_SYM)},
30 {"show_profiles", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_PROFILES]), SHOW_LONG_STATUS},
31 {"show_slave_hosts", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_SLAVE_HOSTS]), SHOW_LONG_STATUS},
32 {"show_slave_status", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_SLAVE_STAT]), SHOW_LONG_STATUS},
33 + {"show_slave_status_nolock", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_SLAVE_NOLOCK_STAT]), SHOW_LONG_STATUS},
34 {"show_status", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_STATUS]), SHOW_LONG_STATUS},
35 {"show_storage_engines", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_STORAGE_ENGINES]), SHOW_LONG_STATUS},
36 {"show_table_statistics",(char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_TABLE_STATS]), SHOW_LONG_STATUS},
40 SQLCOM_ALTER_DB_UPGRADE, SQLCOM_SHOW_TEMPORARY_TABLES,
41 SQLCOM_SHOW_PROFILE, SQLCOM_SHOW_PROFILES,
44 + /* SHOW SLAVE STATUS NOLOCK */
45 + SQLCOM_SHOW_SLAVE_NOLOCK_STAT,
47 When a command is added here, be sure it's also added in mysqld.cc
48 in "struct show_var_st status_vars[]= {" ...
49 --- a/sql/sql_parse.cc
50 +++ b/sql/sql_parse.cc
52 sql_command_flags[SQLCOM_SHOW_CREATE]= CF_STATUS_COMMAND;
53 sql_command_flags[SQLCOM_SHOW_MASTER_STAT]= CF_STATUS_COMMAND;
54 sql_command_flags[SQLCOM_SHOW_SLAVE_STAT]= CF_STATUS_COMMAND;
55 + sql_command_flags[SQLCOM_SHOW_SLAVE_NOLOCK_STAT]= CF_STATUS_COMMAND;
56 sql_command_flags[SQLCOM_SHOW_CREATE_PROC]= CF_STATUS_COMMAND;
57 sql_command_flags[SQLCOM_SHOW_CREATE_FUNC]= CF_STATUS_COMMAND;
58 sql_command_flags[SQLCOM_SHOW_CREATE_TRIGGER]= CF_STATUS_COMMAND;
59 @@ -2596,12 +2597,17 @@
60 pthread_mutex_unlock(&LOCK_active_mi);
63 + case SQLCOM_SHOW_SLAVE_NOLOCK_STAT:
64 case SQLCOM_SHOW_SLAVE_STAT:
66 /* Accept one of two privileges */
67 if (check_global_access(thd, SUPER_ACL | REPL_CLIENT_ACL))
69 - pthread_mutex_lock(&LOCK_active_mi);
70 + bool do_lock=SQLCOM_SHOW_SLAVE_NOLOCK_STAT != lex->sql_command;
73 + pthread_mutex_lock(&LOCK_active_mi);
75 if (active_mi != NULL)
77 res = show_master_info(thd, active_mi);
78 @@ -2612,7 +2618,19 @@
79 WARN_NO_MASTER_INFO, ER(WARN_NO_MASTER_INFO));
82 - pthread_mutex_unlock(&LOCK_active_mi);
85 + pthread_mutex_unlock(&LOCK_active_mi);
87 + DBUG_EXECUTE_IF("after_show_slave_status",
91 + "signal signal.after_show_slave_status";
92 + DBUG_ASSERT(opt_debug_sync_timeout > 0);
93 + DBUG_ASSERT(!debug_sync_set_action(current_thd,
94 + STRING_WITH_LEN(act)));
98 case SQLCOM_SHOW_MASTER_STAT:
100 +++ b/sql/sql_yacc.yy
101 @@ -1175,6 +1175,7 @@
103 %token START_SYM /* SQL-2003-R */
105 +%token NOLOCK_SYM /* SHOW SLAVE STATUS NOLOCK */
106 %token STDDEV_SAMP_SYM /* SQL-2003-N */
109 @@ -10387,6 +10388,11 @@
111 Lex->sql_command = SQLCOM_SHOW_SLAVE_STAT;
113 + /* SHOW SLAVE STATUS NOLOCK */
114 + | SLAVE STATUS_SYM NOLOCK_SYM
116 + Lex->sql_command = SQLCOM_SHOW_SLAVE_NOLOCK_STAT; //SQLCOM_SHOW_SLAVE_NOLOCK_STAT;
118 | CLIENT_STATS_SYM wild_and_where
122 +++ b/mysql-test/t/percona_show_slave_status_nolock.test
124 +--source include/master-slave.inc
125 +--source include/have_debug_sync.inc
126 +--source include/have_binlog_format_statement.inc
128 +--let $rpl_connection_name=slave_lock
129 +--let $rpl_server_number=2
130 +--source include/rpl_connect.inc
132 +--let $rpl_connection_name=slave_nolock
133 +--let $rpl_server_number=2
134 +--source include/rpl_connect.inc
136 +--let $show_statement= SHOW PROCESSLIST
142 +DROP TABLE IF EXISTS t;
144 +CREATE TABLE t(id INT);
145 +sync_slave_with_master;
149 +SET DEBUG_SYNC='RESET';
150 +SET GLOBAL DEBUG="+d,after_mysql_insert";
151 +SET GLOBAL DEBUG="+d,after_show_slave_status";
155 +INSERT INTO t VALUES(0);
159 +--let $condition= 'INSERT INTO t VALUES(0)'
160 +--source include/wait_show_condition.inc
162 +--echo check 'SHOW SLAVE STATUS' and 'SHOW SLAVE STATUS NOLOCK' - both should work fine
163 +--source include/percona_show_slave_status_nolock.inc
167 +INSERT INTO t VALUES(1);
171 +--let $condition= 'INSERT INTO t VALUES(1)'
172 +--source include/wait_show_condition.inc
174 +--let $rpl_connection_name=slave_stop
175 +--let $rpl_server_number=2
176 +--source include/rpl_connect.inc
178 +connection slave_stop;
184 +--let $condition= 'STOP SLAVE'
185 +--source include/wait_show_condition.inc
187 +--echo check 'SHOW SLAVE STATUS' and 'SHOW SLAVE STATUS NOLOCK' - just NOLOCK version should works fine
188 +--source include/percona_show_slave_status_nolock.inc
191 +connection slave_stop;
194 +--source include/wait_for_slave_to_stop.inc
196 +--source include/wait_for_slave_to_start.inc
200 +SET DEBUG_SYNC='RESET';
204 +SET GLOBAL DEBUG='';
205 +SET DEBUG_SYNC='RESET';
209 +sync_slave_with_master;
211 +--source include/rpl_end.inc
213 +++ b/mysql-test/r/percona_show_slave_status_nolock.result
215 +include/master-slave.inc
217 +include/rpl_connect.inc [creating slave_lock]
218 +include/rpl_connect.inc [creating slave_nolock]
220 +DROP TABLE IF EXISTS t;
221 +CREATE TABLE t(id INT);
223 +SET DEBUG_SYNC='RESET';
224 +SET GLOBAL DEBUG="+d,after_mysql_insert";
225 +SET GLOBAL DEBUG="+d,after_show_slave_status";
227 +INSERT INTO t VALUES(0);
229 +check 'SHOW SLAVE STATUS' and 'SHOW SLAVE STATUS NOLOCK' - both should work fine
233 +SET DEBUG_SYNC='now WAIT_FOR signal.after_show_slave_status TIMEOUT 1';
234 +SIGNAL after SHOW SLAVE STATUS is 'signal.after_show_slave_status'
236 +SET DEBUG_SYNC='now SIGNAL signal.empty';
238 +SHOW SLAVE STATUS NOLOCK;
239 +SET DEBUG_SYNC='now WAIT_FOR signal.after_show_slave_status TIMEOUT 1';
240 +# should be 'signal.after_show_slave_status'
241 +SIGNAL after SHOW SLAVE STATUS NOLOCK is 'signal.after_show_slave_status'
243 +SET DEBUG_SYNC='now SIGNAL signal.continue';
245 +SET DEBUG_SYNC='now SIGNAL signal.empty';
248 +INSERT INTO t VALUES(1);
250 +include/rpl_connect.inc [creating slave_stop]
254 +check 'SHOW SLAVE STATUS' and 'SHOW SLAVE STATUS NOLOCK' - just NOLOCK version should works fine
258 +SET DEBUG_SYNC='now WAIT_FOR signal.after_show_slave_status TIMEOUT 1';
259 +SIGNAL after SHOW SLAVE STATUS is 'signal.empty'
261 +SET DEBUG_SYNC='now SIGNAL signal.empty';
263 +SHOW SLAVE STATUS NOLOCK;
264 +SET DEBUG_SYNC='now WAIT_FOR signal.after_show_slave_status TIMEOUT 1';
265 +# should be 'signal.after_show_slave_status'
266 +SIGNAL after SHOW SLAVE STATUS NOLOCK is 'signal.after_show_slave_status'
268 +SET DEBUG_SYNC='now SIGNAL signal.continue';
270 +SET DEBUG_SYNC='now SIGNAL signal.empty';
273 +include/wait_for_slave_to_stop.inc
275 +include/wait_for_slave_to_start.inc
277 +SET DEBUG_SYNC='RESET';
279 +SET GLOBAL DEBUG='';
280 +SET DEBUG_SYNC='RESET';
284 +++ b/mysql-test/include/percona_show_slave_status_nolock.inc
287 +--disable_result_log
288 +connection slave_lock;
290 +send SHOW SLAVE STATUS;
293 +--let $condition= 'SHOW SLAVE STATUS'
294 +--source include/wait_show_condition.inc
297 +SET DEBUG_SYNC='now WAIT_FOR signal.after_show_slave_status TIMEOUT 1';
300 +--let current=`SELECT SUBSTR(Variable_value FROM 22) FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE Variable_name = 'DEBUG_SYNC'`
301 +--echo SIGNAL after SHOW SLAVE STATUS is $current
305 +SET DEBUG_SYNC='now SIGNAL signal.empty';
307 +connection slave_nolock;
308 +--echo [slave_nolock]
309 +send SHOW SLAVE STATUS NOLOCK;
312 +--let $condition= 'SHOW SLAVE STATUS NOLOCK'
313 +--source include/wait_show_condition.inc
316 +SET DEBUG_SYNC='now WAIT_FOR signal.after_show_slave_status TIMEOUT 1';
319 +--echo # should be 'signal.after_show_slave_status'
320 +--let current=`SELECT SUBSTR(Variable_value FROM 22) FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE Variable_name = 'DEBUG_SYNC'`
321 +--echo SIGNAL after SHOW SLAVE STATUS NOLOCK is $current
325 +SET DEBUG_SYNC='now SIGNAL signal.continue';
327 +connection slave_lock;
328 +--disable_result_log
332 +connection slave_nolock;
333 +--disable_result_log
339 +SET DEBUG_SYNC='now SIGNAL signal.empty';
344 @@ -1641,6 +1641,7 @@
348 + bool do_lock=SQLCOM_SHOW_SLAVE_NOLOCK_STAT != thd->lex->sql_command;
349 DBUG_PRINT("info",("host is set: '%s'", mi->host));
350 String *packet= &thd->packet;
351 protocol->prepare_for_resend();
352 @@ -1649,9 +1650,15 @@
353 slave_running can be accessed without run_lock but not other
354 non-volotile members like mi->io_thd, which is guarded by the mutex.
356 - pthread_mutex_lock(&mi->run_lock);
359 + pthread_mutex_lock(&mi->run_lock);
361 protocol->store(mi->io_thd ? mi->io_thd->proc_info : "", &my_charset_bin);
362 - pthread_mutex_unlock(&mi->run_lock);
365 + pthread_mutex_unlock(&mi->run_lock);
368 pthread_mutex_lock(&mi->data_lock);
369 pthread_mutex_lock(&mi->rli.data_lock);