show_slave_status_nolock.patch

   1 # name       : show_slave_status_nolock.patch
   2 # introduced : 12
   3 # maintainer : Oleg
   4 #
   5 #!!! notice !!!
   6 # Any small change to this file in the main branch
   7 # should be done or reviewed by the maintainer!
   8 --- /dev/null
   9 +++ b/patch_info/show_slave_status_nolock.patch
  10 @@ -0,0 +1,6 @@
  11 +File=show_slave_status_nolock.patch
  12 +Name= SHOW SLAVE STATUS NOLOCK
  13 +Version=1.0
  14 +Author=Percona <info@percona.com>
  15 +License=GPL
  16 +Comment= Implement SHOW SLAVE STATUS without lock (STOP SLAVE lock the same mutex what lock SHOW SLAVE STATUS)
  17 --- a/sql/lex.h
  18 +++ b/sql/lex.h
  19 @@ -378,6 +378,7 @@
  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)},
  27 --- a/sql/mysqld.cc
  28 +++ b/sql/mysqld.cc
  29 @@ -3121,6 +3121,7 @@
  30    {"show_relaylog_events", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_RELAYLOG_EVENTS]), 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_status",    (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_TABLE_STATUS]), SHOW_LONG_STATUS},
  37 --- a/sql/sql_lex.h
  38 +++ b/sql/sql_lex.h
  39 @@ -190,6 +190,8 @@
  40    SQLCOM_SHOW_PROFILE, SQLCOM_SHOW_PROFILES,
  41    SQLCOM_SIGNAL, SQLCOM_RESIGNAL,
  42    SQLCOM_SHOW_RELAYLOG_EVENTS,
  43 +  /* SHOW SLAVE STATUS NOLOCK */
  44 +  SQLCOM_SHOW_SLAVE_NOLOCK_STAT,
  45    /*
  46      When a command is added here, be sure it's also added in mysqld.cc
  47      in "struct show_var_st status_vars[]= {" ...
  48 --- a/sql/sql_parse.cc
  49 +++ b/sql/sql_parse.cc
  50 @@ -335,6 +335,7 @@
  51    sql_command_flags[SQLCOM_SHOW_CREATE]=  CF_STATUS_COMMAND;
  52    sql_command_flags[SQLCOM_SHOW_MASTER_STAT]= CF_STATUS_COMMAND;
  53    sql_command_flags[SQLCOM_SHOW_SLAVE_STAT]=  CF_STATUS_COMMAND;
  54 +  sql_command_flags[SQLCOM_SHOW_SLAVE_NOLOCK_STAT]=  CF_STATUS_COMMAND;
  55    sql_command_flags[SQLCOM_SHOW_CREATE_PROC]= CF_STATUS_COMMAND;
  56    sql_command_flags[SQLCOM_SHOW_CREATE_FUNC]= CF_STATUS_COMMAND;
  57    sql_command_flags[SQLCOM_SHOW_CREATE_TRIGGER]=  CF_STATUS_COMMAND;
  58 @@ -2359,12 +2360,16 @@
  59      mysql_mutex_unlock(&LOCK_active_mi);
  60      break;
  61    }
  62 +  case SQLCOM_SHOW_SLAVE_NOLOCK_STAT:
  63    case SQLCOM_SHOW_SLAVE_STAT:
  64    {
  65      /* Accept one of two privileges */
  66      if (check_global_access(thd, SUPER_ACL | REPL_CLIENT_ACL))
  67        goto error;
  68 -    mysql_mutex_lock(&LOCK_active_mi);
  69 +    if(SQLCOM_SHOW_SLAVE_NOLOCK_STAT != lex->sql_command)
  70 +    {
  71 +      mysql_mutex_lock(&LOCK_active_mi);
  72 +    }
  73      if (active_mi != NULL)
  74      {
  75        res = show_master_info(thd, active_mi);
  76 @@ -2375,7 +2380,10 @@
  77                     WARN_NO_MASTER_INFO, ER(WARN_NO_MASTER_INFO));
  78        my_ok(thd);
  79      }
  80 -    mysql_mutex_unlock(&LOCK_active_mi);
  81 +    if(SQLCOM_SHOW_SLAVE_NOLOCK_STAT != lex->sql_command)
  82 +    {
  83 +      mysql_mutex_unlock(&LOCK_active_mi);
  84 +    }
  85      break;
  86    }
  87    case SQLCOM_SHOW_MASTER_STAT:
  88 --- a/sql/sql_yacc.yy
  89 +++ b/sql/sql_yacc.yy
  90 @@ -1293,6 +1293,7 @@
  91  %token  STARTS_SYM
  92  %token  START_SYM                     /* SQL-2003-R */
  93  %token  STATUS_SYM
  94 +%token  NOLOCK_SYM                    /* SHOW SLAVE STATUS NOLOCK */
  95  %token  STDDEV_SAMP_SYM               /* SQL-2003-N */
  96  %token  STD_SYM
  97  %token  STOP_SYM
  98 @@ -11086,6 +11087,10 @@
  99            {
 100              Lex->sql_command = SQLCOM_SHOW_SLAVE_STAT;
 101            }
 102 +        | SLAVE STATUS_SYM NOLOCK_SYM
 103 +          {
 104 +           Lex->sql_command = SQLCOM_SHOW_SLAVE_NOLOCK_STAT; //SQLCOM_SHOW_SLAVE_NOLOCK_STAT;
 105 +          }
 106          | QUERY_RESPONSE_TIME_SYM wild_and_where
 107           {
 108  #ifdef HAVE_RESPONSE_TIME_DISTRIBUTION
 109 --- /dev/null
 110 +++ b/mysql-test/r/percona_show_slave_status_nolock.result
 111 @@ -0,0 +1,21 @@
 112 +include/master-slave.inc
 113 +[connection master]
 114 +DROP TABLE IF EXISTS t;
 115 +CREATE TABLE t(id INT);
 116 +INSERT INTO t SELECT SLEEP(10);
 117 +STOP SLAVE;
 118 +Warnings:
 119 +Note   1592    Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave.
 120 +master count(*)
 121 +master 1
 122 +slave  count(*)
 123 +slave  0
 124 +SHOW SLAVE STATUS NOLOCK;
 125 +include/wait_for_slave_to_stop.inc
 126 +START SLAVE;
 127 +include/wait_for_slave_to_start.inc
 128 +slave  count(*)
 129 +slave  1
 130 +DROP TABLE t;
 131 +STOP SLAVE;
 132 +include/wait_for_slave_to_stop.inc
 133 --- /dev/null
 134 +++ b/mysql-test/t/percona_show_slave_status_nolock.test
 135 @@ -0,0 +1,53 @@
 136 +--source include/master-slave.inc
 137 +--source include/have_binlog_format_statement.inc
 138 +--disable_query_log
 139 +call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave. Statement:");
 140 +call mtr.add_suppression("Slave SQL.*Request to stop slave SQL Thread received while applying a group that has non-transactional changes; waiting for completion of the group");
 141 +--enable_query_log
 142 +connection master;
 143 +  --disable_warnings
 144 +  DROP TABLE IF EXISTS t;
 145 +  --enable_warnings
 146 +  CREATE TABLE t(id INT);
 147 +  sync_slave_with_master;
 148 +
 149 +connection master;
 150 +  send INSERT INTO t SELECT SLEEP(10);
 151 +
 152 +connection slave;
 153 +  sleep 15;
 154 +  send STOP SLAVE;
 155 +
 156 +connection master;
 157 +  reap;
 158 +
 159 +  --disable_query_log
 160 +  select "master",count(*) from t;
 161 +  --enable_query_log
 162 +
 163 +connection slave1;
 164 +  --disable_query_log
 165 +  select "slave",count(*) from t;
 166 +  --enable_query_log
 167 +
 168 +  --disable_result_log
 169 +  SHOW SLAVE STATUS NOLOCK;
 170 +  --enable_result_log
 171 +
 172 +connection slave;
 173 +  reap;
 174 +
 175 +  --source include/wait_for_slave_to_stop.inc
 176 +  START SLAVE;
 177 +  --source include/wait_for_slave_to_start.inc
 178 +
 179 +  --disable_query_log
 180 +  select "slave",count(*) from t;
 181 +  --enable_query_log
 182 +
 183 +connection master;
 184 +  DROP TABLE t;
 185 +sync_slave_with_master;
 186 +STOP SLAVE;
 187 +--source include/wait_for_slave_to_stop.inc
 188 +
 189 \ No newline at end of file