List:Internals« Previous MessageNext Message »
From:guilhem Date:April 20 2005 11:35pm
Subject:bk commit into 5.0 tree (gbichot:1.1862) BUG#2610
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 repository of gbichot. When gbichot does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.1862 05/04/20 23:35:15 gbichot@stripped +16 -0
  Approximative fixes for BUG#2610,2611,9100 i.e. WL#2146 binlogging/replication of
routines (stored procs and functions).
  Approximative, because it's using our binlogging way (what we call "query"-level) and
this is not as good as record-level binlog (5.1) would be. It imposes several
  limitations to routines, and has caveats (which I'll document, and for which the server
will try to issue errors but that is not always possible).
  Reason I don't propagate caller info to the binlog as planned is that on master and
slave
  users may be different; even with that some caveats would remain.
  FYI range, func_sapdb, timezone_grant and view fail in ps-protocol. I will wait a few
days before push, for review.

  mysql-test/t/rpl_sp.test
    1.1 05/04/20 23:35:09 gbichot@stripped +232 -0

  mysql-test/t/rpl_sp-slave.opt
    1.1 05/04/20 23:35:09 gbichot@stripped +2 -0

  mysql-test/t/rpl_sp-master.opt
    1.1 05/04/20 23:35:09 gbichot@stripped +2 -0

  mysql-test/t/rpl_sp.test
    1.0 05/04/20 23:35:09 gbichot@stripped +0 -0
    BitKeeper file /nfstmp1/guilhem/mysql-5.0-4ita/mysql-test/t/rpl_sp.test

  mysql-test/t/rpl_sp-slave.opt
    1.0 05/04/20 23:35:09 gbichot@stripped +0 -0
    BitKeeper file /nfstmp1/guilhem/mysql-5.0-4ita/mysql-test/t/rpl_sp-slave.opt

  mysql-test/t/rpl_sp-master.opt
    1.0 05/04/20 23:35:09 gbichot@stripped +0 -0
    BitKeeper file /nfstmp1/guilhem/mysql-5.0-4ita/mysql-test/t/rpl_sp-master.opt

  mysql-test/r/rpl_sp.result
    1.1 05/04/20 23:35:08 gbichot@stripped +234 -0

  sql/sql_parse.cc
    1.442 05/04/20 23:35:08 gbichot@stripped +70 -4
    Binlogging of CALL (and not of the substatements of the SP).
    If SP returns error, we don't binlog it (see comment); we push warning in this case.
    Binlogging of ALTER|DROP PROCEDURE|FUNCTION with safety messages.

  sql/sql_acl.cc
    1.148 05/04/20 23:35:08 gbichot@stripped +5 -5
    No thd==0 in tables_ok().

  sql/sp.cc
    1.74 05/04/20 23:35:08 gbichot@stripped +46 -1
    If binlog is on: errors if one wants to create a non-deterministic update routine
    (repeatability problem - note that the test is not perfect for functions) or does not
have SUPER (because routines can easily
    be made to destroy slave's data with just CREATE ROUTINE and EXECUTE priv on master).
    --log-bin-trust-routine-creators removes these errors.
    Binlogging of CREATE PROCEDURE|FUNCTION.

  sql/slave.cc
    1.245 05/04/20 23:35:08 gbichot@stripped +24 -2
    If in a routine, replication table inclusion/exclusion rules always answer
"replicate!" (see comment in code).

  sql/set_var.cc
    1.106 05/04/20 23:35:08 gbichot@stripped +5 -0
    --log-bin-trust-routine-creators

  mysql-test/r/rpl_sp.result
    1.0 05/04/20 23:35:08 gbichot@stripped +0 -0
    BitKeeper file /nfstmp1/guilhem/mysql-5.0-4ita/mysql-test/r/rpl_sp.result

  sql/mysqld.cc
    1.457 05/04/20 23:35:07 gbichot@stripped +12 -1
    --log-bin-trust-routine-creators

  sql/mysql_priv.h
    1.289 05/04/20 23:35:07 gbichot@stripped +1 -1
    --log-bin-trust-routine-creators

  sql/log_event.h
    1.110 05/04/20 23:35:07 gbichot@stripped +2 -3
    comment.

  sql/log_event.cc
    1.175 05/04/20 23:35:07 gbichot@stripped +39 -23
    Just making functions which can be re-used when we binlog more strings
    in status_vars in Query_log_event (e.g. one day "user", "host").

  sql/item_func.cc
    1.181 05/04/20 23:35:07 gbichot@stripped +17 -0
    Don't binlog the substatements when executing a function. If the function
    is declared to modify data and does not complete, warning "broken binlog".
    Note that SELECT myfunc() will not be binlogged even if myfunc() updates data (will be
documented);
    but INSERT INTO t VALUES(myfunc()) will be binlogged (what decides is if the caller
    gets binlogged; the function changes nothing to binlogging).

  mysql-test/valgrind.supp
    1.2 05/04/20 23:35:07 gbichot@stripped +20 -0
    Some suppressions for Valgrind (useful on my machine Suse 9.1);
    this is just adding to the already existing suppressions of pthread and dl.

  mysql-test/mysql-test-run.sh
    1.262 05/04/20 23:35:07 gbichot@stripped +3 -0
    By default the tests are run with --log-bin-trust-routine-creators;
    indeed the repeatability of binlog is necessary only in dedicated rpl_sp.test.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	gbichot
# Host:	quadita2.mysql.com
# Root:	/nfstmp1/guilhem/mysql-5.0-4ita

--- 1.261/mysql-test/mysql-test-run.sh	2005-04-11 10:22:26 +02:00
+++ 1.262/mysql-test/mysql-test-run.sh	2005-04-20 23:35:07 +02:00
@@ -1181,6 +1181,7 @@
           --language=$LANGUAGE \
           --innodb_data_file_path=ibdata1:128M:autoextend \
 	  --open-files-limit=1024 \
+          --log-bin-trust-routine-creators \
 	   $MASTER_40_ARGS \
            $SMALL_SERVER \
            $EXTRA_MASTER_OPT $EXTRA_MASTER_MYSQLD_OPT \
@@ -1201,6 +1202,7 @@
           --tmpdir=$MYSQL_TMP_DIR \
           --language=$LANGUAGE \
           --innodb_data_file_path=ibdata1:128M:autoextend \
+          --log-bin-trust-routine-creators \
 	   $MASTER_40_ARGS \
            $SMALL_SERVER \
            $EXTRA_MASTER_OPT $EXTRA_MASTER_MYSQLD_OPT \
@@ -1333,6 +1335,7 @@
           --report-port=$slave_port \
           --master-retry-count=10 \
           -O slave_net_timeout=10 \
+          --log-bin-trust-routine-creators \
            $SMALL_SERVER \
            $EXTRA_SLAVE_OPT $EXTRA_SLAVE_MYSQLD_OPT"
   CUR_MYERR=$slave_err

--- 1.180/sql/item_func.cc	2005-03-28 11:15:07 +02:00
+++ 1.181/sql/item_func.cc	2005-04-20 23:35:07 +02:00
@@ -4563,7 +4563,24 @@
   }
 #endif
 
+  /*
+    Like for SPs, we don't binlog the substatements. If the statement which
+    called this function is an update statement, it will be binlogged; but if
+    it's not (e.g. SELECT myfunc()) it won't be binlogged (documented known
+    problem).
+  */
+  tmp_disable_binlog(thd); /* don't binlog the substatements */
   res= m_sp->execute_function(thd, args, arg_count, itp);
+  reenable_binlog(thd);
+  if (res && mysql_bin_log.is_open() &&
+      (m_sp->m_chistics->daccess == SP_CONTAINS_SQL ||
+       m_sp->m_chistics->daccess == SP_MODIFIES_SQL_DATA))
+    push_warning(thd, MYSQL_ERROR::WARN_LEVEL_WARN,
+                 ER_UNKNOWN_ERROR,
+                 "A routine failed and is declared to modify data and "
+                 "binary logging is enabled; if non-transactional "
+                 "tables were updated, the binary log will miss their "
+                 "changes");
 
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
   sp_restore_security_context(thd, m_sp, &save_ctx);

--- 1.174/sql/log_event.cc	2005-03-30 15:01:28 +02:00
+++ 1.175/sql/log_event.cc	2005-04-20 23:35:07 +02:00
@@ -955,6 +955,18 @@
 
 #ifndef MYSQL_CLIENT
 
+/* Utility function for the next method */
+static void write_str_with_code_and_len(char **dst, const char *src,
+                                        int len, uint code)
+{
+  DBUG_ASSERT(src);
+  *((*dst)++)= code;
+  *((*dst)++)= (uchar) len;
+  bmove(*dst, src, len);
+  (*dst)+= len;
+}
+
+
 /*
   Query_log_event::write()
 
@@ -1039,12 +1051,10 @@
     int8store(start, (ulonglong)sql_mode);
     start+= 8;
   }
-  if (catalog_len) // i.e. "catalog inited" (false for 4.0 events)
+  if (catalog_len) // i.e. this var is inited (false for 4.0 events)
   {
-    *start++= Q_CATALOG_NZ_CODE;
-    *start++= (uchar) catalog_len;
-    bmove(start, catalog, catalog_len);
-    start+= catalog_len;
+    write_str_with_code_and_len((char **)(&start),
+                                catalog, catalog_len, Q_CATALOG_NZ_CODE);
     /*
       In 5.0.x where x<4 masters we used to store the end zero here. This was
       a waste of one byte so we don't do it in x>=4 masters. We change code to
@@ -1176,6 +1186,25 @@
 #endif /* MYSQL_CLIENT */
 
 
+/* 2 utility functions for the next method */
+
+static void get_str_len_and_pointer(const char **dst, const char **src, uint *len)
+{
+  if ((*len= **src))
+    *dst= *src + 1;                          // Will be copied later
+  (*src)+= *len+1;
+}
+
+
+static void copy_str_and_move(char **dst, const char **src, uint len)
+{
+  memcpy(*dst, *src, len);
+  *src= *dst;
+  (*dst)+= len;
+  *(*dst)++= 0;
+}
+
+
 /*
   Query_log_event::Query_log_event()
   This is used by the SQL slave thread to prepare the event before execution.
@@ -1264,9 +1293,7 @@
       break;
     }
     case Q_CATALOG_NZ_CODE:
-      if ((catalog_len= *pos))
-        catalog= (char*) pos+1;                 // Will be copied later
-      pos+= catalog_len+1;
+      get_str_len_and_pointer(&catalog, (const char **)(&pos), &catalog_len);
       break;
     case Q_AUTO_INCREMENT:
       auto_increment_increment= uint2korr(pos);
@@ -1282,9 +1309,7 @@
     }
     case Q_TIME_ZONE_CODE:
     {
-      if ((time_zone_len= *pos))
-        time_zone_str= (char *)(pos+1);
-      pos+= time_zone_len+1;
+      get_str_len_and_pointer(&time_zone_str, (const char **)(&pos),
&time_zone_len);
       break;
     }
     case Q_CATALOG_CODE: /* for 5.0.x where 0<=x<=3 masters */
@@ -1308,12 +1333,7 @@
   if (catalog_len)                                  // If catalog is given
   {
     if (likely(catalog_nz)) // true except if event comes from 5.0.0|1|2|3.
-    {
-      memcpy(start, catalog, catalog_len);
-      catalog= start;
-      start+= catalog_len;
-      *start++= 0;
-    }
+      copy_str_and_move(&start, &catalog, catalog_len);
     else
     {
       memcpy(start, catalog, catalog_len+1); // copy end 0
@@ -1322,12 +1342,8 @@
     }
   }
   if (time_zone_len)
-  {
-    memcpy(start, time_zone_str, time_zone_len);
-    time_zone_str= start;
-    start+= time_zone_len;
-    *start++= 0;
-  }
+    copy_str_and_move(&start, &time_zone_str, time_zone_len);
+
   /* A 2nd variable part; this is common to all versions */ 
   memcpy((char*) start, end, data_len);          // Copy db and query
   start[data_len]= '\0';              // End query with \0 (For safetly)

--- 1.109/sql/log_event.h	2005-03-30 22:41:38 +02:00
+++ 1.110/sql/log_event.h	2005-04-20 23:35:07 +02:00
@@ -234,13 +234,12 @@
 /* these are codes, not offsets; not more than 256 values (1 byte). */
 #define Q_FLAGS2_CODE           0
 #define Q_SQL_MODE_CODE         1
-#ifndef TO_BE_DELETED
 /*
   Q_CATALOG_CODE is catalog with end zero stored; it is used only by MySQL
-  5.0.x where 0<=x<=3.
+  5.0.x where 0<=x<=3. We have to keep it to be able to replicate these
+  old masters.
 */
 #define Q_CATALOG_CODE          2
-#endif
 #define Q_AUTO_INCREMENT	3
 #define Q_CHARSET_CODE          4
 #define Q_TIME_ZONE_CODE        5

--- 1.288/sql/mysql_priv.h	2005-04-07 18:24:08 +02:00
+++ 1.289/sql/mysql_priv.h	2005-04-20 23:35:07 +02:00
@@ -1087,7 +1087,7 @@
 extern my_bool opt_enable_named_pipe, opt_sync_frm, opt_allow_suspicious_udfs;
 extern my_bool opt_secure_auth;
 extern my_bool sp_automatic_privileges;
-extern my_bool opt_old_style_user_limits;
+extern my_bool opt_old_style_user_limits, trust_routine_creators;
 extern uint opt_crash_binlog_innodb;
 extern char *shared_memory_base_name, *mysqld_unix_port;
 extern bool opt_enable_shared_memory;

--- 1.456/sql/mysqld.cc	2005-04-16 03:35:46 +02:00
+++ 1.457/sql/mysqld.cc	2005-04-20 23:35:07 +02:00
@@ -303,7 +303,7 @@
 my_bool lower_case_file_system= 0;
 my_bool opt_large_pages= 0;
 uint    opt_large_page_size= 0;
-my_bool opt_old_style_user_limits= 0;
+my_bool opt_old_style_user_limits= 0, trust_routine_creators= 0;
 /*
   True if there is at least one per-hour limit for some user, so we should
   check them before each query (and possibly reset counters when hour is
@@ -4170,6 +4170,7 @@
   OPT_INNODB_FAST_SHUTDOWN,
   OPT_INNODB_FILE_PER_TABLE, OPT_CRASH_BINLOG_INNODB,
   OPT_INNODB_LOCKS_UNSAFE_FOR_BINLOG,
+  OPT_LOG_BIN_TRUST_ROUTINE_CREATORS,
   OPT_SAFE_SHOW_DB, OPT_INNODB_SAFE_BINLOG,
   OPT_INNODB, OPT_ISAM,
   OPT_ENGINE_CONDITION_PUSHDOWN,
@@ -4590,6 +4591,16 @@
    "File that holds the names for last binary log files.",
    (gptr*) &opt_binlog_index_name, (gptr*) &opt_binlog_index_name, 0, GET_STR,
    REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
+  /*
+    This option starts with "log-bin" to emphasize that it is specific of
+    binary logging. Hopefully in 5.1 nobody will need it anymore, when we have
+    row-level binlog.
+  */
+  {"log-bin-trust-routine-creators", OPT_LOG_BIN_TRUST_ROUTINE_CREATORS,
+   "If equal to 0 (the default), then when --log-bin is used, creation of "
+   "routines is allowed only to users having the SUPER privilege.",
+   (gptr*) &trust_routine_creators, (gptr*) &trust_routine_creators, 0,
+   GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
   {"log-error", OPT_ERROR_LOG_FILE, "Error log file.",
    (gptr*) &log_error_file_ptr, (gptr*) &log_error_file_ptr, 0, GET_STR,
    OPT_ARG, 0, 0, 0, 0, 0, 0},

--- 1.244/sql/slave.cc	2005-03-24 14:32:30 +01:00
+++ 1.245/sql/slave.cc	2005-04-20 23:35:08 +02:00
@@ -852,7 +852,7 @@
 
   SYNOPSIS
     tables_ok()
-    thd             thread (SQL slave thread normally)
+    thd             thread (SQL slave thread normally). Mustn't be null.
     tables          list of tables to check
 
   NOTES
@@ -885,6 +885,23 @@
   bool some_tables_updating= 0;
   DBUG_ENTER("tables_ok");
 
+  /*
+    In routine, can't reliably pick and choose substatements, so always
+    replicate.
+    We can't reliably know if one substatement should be executed or not:
+    consider the case of this substatement: a SELECT on a non-replicated
+    constant table; if we don't execute it maybe it was going to fill a
+    variable which was going to be used by the next substatement to update
+    a replicated table? If we execute it maybe the constant non-replicated
+    table does not exist (and so we'll fail) while there was no need to
+    execute this as this SELECT does not influence replicated tables in the
+    rest of the routine? In other words: users are used to replicate-*-table
+    specifying how to handle updates to tables, these options don't say
+    anything about reads to tables; we can't guess.
+  */
+  if (thd->spcont)
+    DBUG_RETURN(1);
+
   for (; tables; tables= tables->next_global)
   {
     char hash_key[2*NAME_LEN+2];
@@ -2791,12 +2808,17 @@
   DBUG_ENTER("init_slave_thread");
   thd->system_thread = (thd_type == SLAVE_THD_SQL) ?
     SYSTEM_THREAD_SLAVE_SQL : SYSTEM_THREAD_SLAVE_IO; 
+  /*
+    The two next lines are needed for replication of SP (CREATE PROCEDURE
+    needs a valid user to store in mysql.proc).
+  */
+  thd->priv_user= (char *) "";
+  thd->priv_host[0]= '\0';
   thd->host_or_ip= "";
   thd->client_capabilities = 0;
   my_net_init(&thd->net, 0);
   thd->net.read_timeout = slave_net_timeout;
   thd->master_access= ~0;
-  thd->priv_user = 0;
   thd->slave_thread = 1;
   set_slave_thread_options(thd);
   /* 

--- 1.147/sql/sql_acl.cc	2005-03-29 11:58:30 +02:00
+++ 1.148/sql/sql_acl.cc	2005-04-20 23:35:08 +02:00
@@ -1515,7 +1515,7 @@
     */
     tables.updating= 1;
     /* Thanks to bzero, tables.next==0 */
-    if (!tables_ok(0, &tables))
+    if (!tables_ok(thd, &tables))
       DBUG_RETURN(0);
   }
 #endif
@@ -2699,7 +2699,7 @@
       account in tests.
     */
     tables[0].updating= tables[1].updating= tables[2].updating= 1;
-    if (!tables_ok(0, tables))
+    if (!tables_ok(thd, tables))
       DBUG_RETURN(FALSE);
   }
 #endif
@@ -2904,7 +2904,7 @@
       account in tests.
     */
     tables[0].updating= tables[1].updating= 1;
-    if (!tables_ok(0, tables))
+    if (!tables_ok(thd, tables))
       DBUG_RETURN(FALSE);
   }
 #endif
@@ -3035,7 +3035,7 @@
       account in tests.
     */
     tables[0].updating= tables[1].updating= 1;
-    if (!tables_ok(0, tables))
+    if (!tables_ok(thd, tables))
       DBUG_RETURN(FALSE);
   }
 #endif
@@ -4245,7 +4245,7 @@
     */
     tables[0].updating=tables[1].updating=tables[2].updating=
       tables[3].updating=tables[4].updating=1;
-    if (!tables_ok(0, tables))
+    if (!tables_ok(thd, tables))
       DBUG_RETURN(1);
     tables[0].updating=tables[1].updating=tables[2].updating=
       tables[3].updating=tables[4].updating=0;;

--- 1.441/sql/sql_parse.cc	2005-04-14 11:56:08 +02:00
+++ 1.442/sql/sql_parse.cc	2005-04-20 23:35:08 +02:00
@@ -4091,7 +4091,46 @@
 	thd->variables.select_limit= HA_POS_ERROR;
 
 	thd->row_count_func= 0;
+        tmp_disable_binlog(thd); /* don't binlog the substatements */
 	res= sp->execute_procedure(thd, &lex->value_list);
+        reenable_binlog(thd);
+
+        /*
+          We write CALL to binlog; on the opposite we didn't write the
+          substatements. That choice is necessary because the substatements
+          may use local vars.
+          Binlogging should happen when all tables are locked. They are locked
+          just above, and unlocked by close_thread_tables(). All tables which
+          are to be updated are locked like with a table-level write lock, and
+          this also applies to InnoDB (I tested - note that it reduces
+          InnoDB's concurrency as we don't use row-level locks). So binlogging
+          below is safe.
+          Note the limitation: if the SP returned an error, but still did some
+          updates, we do NOT binlog it. This is because otherwise "permission
+          denied", "table does not exist" etc would stop the slave quite
+          often. There is no easy way to know if the SP updated something
+          (even no_trans_update is not suitable, as it may be a transactional
+          autocommit update which happened, and no_trans_update covers only
+          INSERT/UPDATE/LOAD).
+        */
+        if (mysql_bin_log.is_open() &&
+            (sp->m_chistics->daccess == SP_CONTAINS_SQL ||
+             sp->m_chistics->daccess == SP_MODIFIES_SQL_DATA))
+        {
+          if (res)
+            push_warning(thd, MYSQL_ERROR::WARN_LEVEL_WARN,
+                         ER_UNKNOWN_ERROR,
+                         "A routine failed and is declared to modify data "
+                         "and binary logging is enabled; if "
+                         "non-transactional tables were updated, the binary "
+                         "log will miss their changes");
+          else
+          {
+            thd->clear_error();
+            Query_log_event qinfo(thd, thd->query, thd->query_length, 0, FALSE);
+            mysql_bin_log.write(&qinfo);
+          }
+        }
 
 	/*
           If warnings have been cleared, we have to clear total_warn_count
@@ -4147,14 +4186,34 @@
 				  sp->m_name.str, 0))
 	  goto error;
 	memcpy(&lex->sp_chistics, &chistics, sizeof(lex->sp_chistics));
-	if (lex->sql_command == SQLCOM_ALTER_PROCEDURE)
-	  result= sp_update_procedure(thd, lex->spname, &lex->sp_chistics);
-	else
-	  result= sp_update_function(thd, lex->spname, &lex->sp_chistics);
+        if (!trust_routine_creators &&  mysql_bin_log.is_open() &&
+            !sp->m_chistics->detistic &&
+            (chistics.daccess == SP_CONTAINS_SQL ||
+             chistics.daccess == SP_MODIFIES_SQL_DATA))
+        {
+          my_message(ER_UNKNOWN_ERROR,
+                     "This routine is declared to be "
+                     "non-deterministic and to modify data and binary "
+                     "logging is enabled", MYF(0));
+          result= SP_INTERNAL_ERROR;
+        }
+        else
+        {
+          if (lex->sql_command == SQLCOM_ALTER_PROCEDURE)
+            result= sp_update_procedure(thd, lex->spname, &lex->sp_chistics);
+          else
+            result= sp_update_function(thd, lex->spname, &lex->sp_chistics);
+        }
       }
       switch (result)
       {
       case SP_OK:
+        if (mysql_bin_log.is_open())
+        {
+          thd->clear_error();
+          Query_log_event qinfo(thd, thd->query, thd->query_length, 0, FALSE);
+          mysql_bin_log.write(&qinfo);
+        }
 	send_ok(thd);
 	break;
       case SP_KEY_NOT_FOUND:
@@ -4231,6 +4290,12 @@
       switch (result)
       {
       case SP_OK:
+        if (mysql_bin_log.is_open())
+        {
+          thd->clear_error();
+          Query_log_event qinfo(thd, thd->query, thd->query_length, 0, FALSE);
+          mysql_bin_log.write(&qinfo);
+        }
 	send_ok(thd);
 	break;
       case SP_KEY_NOT_FOUND:
@@ -4489,6 +4554,7 @@
     break;
   }
   thd->proc_info="query end";
+  /* Two binlog-related cleanups: */
   if (thd->one_shot_set)
   {
     /*
--- New file ---
+++ mysql-test/r/rpl_sp.result	05/04/20 23:35:08
stop slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
create database if not exists mysqltest1;
use mysqltest1;
create table t1 (a varchar(100));
use mysqltest1;
drop procedure if exists foo;
drop procedure if exists foo2;
drop procedure if exists foo3;
drop procedure if exists foo4;
drop procedure if exists bar;
drop function if exists fn1;
create procedure foo()
begin
declare b int;
set b = 8;
insert into t1 values (b);
insert into t1 values (unix_timestamp());
end|
ERROR HY000: This routine is declared to be non-deterministic and to modify data and
binary logging is enabled
show binlog events from 98|
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	1	#	create database if not exists mysqltest1
master-bin.000001	#	Query	1	#	use `mysqltest1`; create table t1 (a varchar(100))
create procedure foo() deterministic
begin
declare b int;
set b = 8;
insert into t1 values (b);
insert into t1 values (unix_timestamp());
end|
select * from mysql.proc where name='foo' and db='mysqltest1';
db	name	type	specific_name	language	sql_data_access	is_deterministic	security_type	param_list	returns	body	definer	created	modified	sql_mode	comment
mysqltest1	foo	PROCEDURE	foo	SQL	CONTAINS_SQL	YES	DEFINER			begin
declare b int;
set b = 8;
insert into t1 values (b);
insert into t1 values (unix_timestamp());
end	root@localhost	#	#		
select * from mysql.proc where name='foo' and db='mysqltest1';
db	name	type	specific_name	language	sql_data_access	is_deterministic	security_type	param_list	returns	body	definer	created	modified	sql_mode	comment
mysqltest1	foo	PROCEDURE	foo	SQL	CONTAINS_SQL	YES	DEFINER			begin
declare b int;
set b = 8;
insert into t1 values (b);
insert into t1 values (unix_timestamp());
end	@	#	#		
set timestamp=1000000000;
call foo();
show binlog events from 308;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	1	#	use `mysqltest1`; create procedure foo() deterministic
begin
declare b int;
set b = 8;
insert into t1 values (b);
insert into t1 values (unix_timestamp());
end
master-bin.000001	#	Query	1	#	use `mysqltest1`; call foo()
select * from t1;
a
8
1000000000
select * from t1;
a
8
1000000000
delete from t1;
create procedure foo2()
not deterministic
reads sql data
select * from mysqltest1.t1;
call foo2();
a
show binlog events from 605;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	1	#	use `mysqltest1`; delete from t1
master-bin.000001	#	Query	1	#	use `mysqltest1`; create procedure foo2()
not deterministic
reads sql data
select * from mysqltest1.t1
alter procedure foo2 contains sql;
ERROR HY000: This routine is declared to be non-deterministic and to modify data and
binary logging is enabled
drop table t1;
create table t1 (a int);
create table t2 like t1;
create procedure foo3()
deterministic
insert into t1 values (15);
grant CREATE ROUTINE, EXECUTE on mysqltest1.* to "zedjzlcsjhd"@127.0.0.1;
grant SELECT on mysqltest1.t1 to "zedjzlcsjhd"@127.0.0.1;
grant SELECT, INSERT on mysqltest1.t2 to "zedjzlcsjhd"@127.0.0.1;
create procedure foo4()
deterministic
insert into t1 values (10);
ERROR HY000: You do not have SUPER privilege and binary logging is enabled (you *might*
want to use the less safe log_bin_trust_routine_creators variable)
set global log_bin_trust_routine_creators=1;
create procedure foo4()
deterministic
begin
insert into t2 values(3);
insert into t1 values (5);
end|
call foo4();
ERROR 42000: INSERT command denied to user 'zedjzlcsjhd'@'localhost' for table 't1'
show warnings;
Level	Code	Message
Warning	1105	A routine failed and is declared to modify data and binary logging is
enabled; if non-transactional tables were updated, the binary log will miss their changes
call foo3();
show warnings;
Level	Code	Message
call foo4();
ERROR 42000: INSERT command denied to user 'zedjzlcsjhd'@'localhost' for table 't1'
show warnings;
Level	Code	Message
Warning	1105	A routine failed and is declared to modify data and binary logging is
enabled; if non-transactional tables were updated, the binary log will miss their changes
alter procedure foo4 sql security invoker;
call foo4();
show warnings;
Level	Code	Message
show binlog events from 841;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	1	#	use `mysqltest1`; drop table t1
master-bin.000001	#	Query	1	#	use `mysqltest1`; create table t1 (a int)
master-bin.000001	#	Query	1	#	use `mysqltest1`; create table t2 like t1
master-bin.000001	#	Query	1	#	use `mysqltest1`; create procedure foo3()
deterministic
insert into t1 values (15)
master-bin.000001	#	Query	1	#	use `mysqltest1`; grant CREATE ROUTINE, EXECUTE on
mysqltest1.* to "zedjzlcsjhd"@127.0.0.1
master-bin.000001	#	Query	1	#	use `mysqltest1`; grant SELECT on mysqltest1.t1 to
"zedjzlcsjhd"@127.0.0.1
master-bin.000001	#	Query	1	#	use `mysqltest1`; grant SELECT, INSERT on mysqltest1.t2 to
"zedjzlcsjhd"@127.0.0.1
master-bin.000001	#	Query	1	#	use `mysqltest1`; create procedure foo4()
deterministic
begin
insert into t2 values(3);
insert into t1 values (5);
end
master-bin.000001	#	Query	1	#	use `mysqltest1`; call foo3()
master-bin.000001	#	Query	1	#	use `mysqltest1`; alter procedure foo4 sql security invoker
master-bin.000001	#	Query	1	#	use `mysqltest1`; call foo4()
select * from t1;
a
15
5
select * from t2;
a
3
3
3
select * from t1;
a
15
5
select * from t2;
a
3
select if(compte<>3,"this is broken but documented","this unexpectedly works?") from
(select count(*) as compte from t2) as aggreg;
if(compte<>3,"this is broken but documented","this unexpectedly works?")
this is broken but documented
select * from mysql.proc where name="foo4" and db='mysqltest1';
db	name	type	specific_name	language	sql_data_access	is_deterministic	security_type	param_list	returns	body	definer	created	modified	sql_mode	comment
mysqltest1	foo4	PROCEDURE	foo4	SQL	CONTAINS_SQL	YES	INVOKER			begin
insert into t2 values(3);
insert into t1 values (5);
end	@	#	#		
drop procedure foo4;
select * from mysql.proc where name="foo4" and db='mysqltest1';
db	name	type	specific_name	language	sql_data_access	is_deterministic	security_type	param_list	returns	body	definer	created	modified	sql_mode	comment
select * from mysql.proc where name="foo4" and db='mysqltest1';
db	name	type	specific_name	language	sql_data_access	is_deterministic	security_type	param_list	returns	body	definer	created	modified	sql_mode	comment
drop procedure foo;
drop procedure foo2;
drop procedure foo3;
create function fn1(x int)
returns int
deterministic
begin
insert into t1 values (x);
return x+2;
end|
delete t1,t2 from t1,t2;
select fn1(20);
fn1(20)
22
insert into t2 values(fn1(21));
select * from t1;
a
21
20
select * from t2;
a
23
select * from t1;
a
21
select if(compte<>1,"this is broken but documented","this unexpectedly works?") from
(select count(*) as compte from t1 where a=20) as aggreg;
if(compte<>1,"this is broken but documented","this unexpectedly works?")
this is broken but documented
select * from t2;
a
23
drop function fn1;
create function fn1()
returns int
deterministic
begin
return unix_timestamp();
end|
delete from t1;
set timestamp=1000000000;
insert into t1 values(fn1());
select * from mysql.proc where db='mysqltest1';
db	name	type	specific_name	language	sql_data_access	is_deterministic	security_type	param_list	returns	body	definer	created	modified	sql_mode	comment
mysqltest1	fn1	FUNCTION	fn1	SQL	CONTAINS_SQL	YES	DEFINER		int(11)	begin
return unix_timestamp();
end	root@localhost	#	#		
select * from t1;
a
1000000000
use mysqltest1;
select * from t1;
a
1000000000
select * from mysql.proc where db='mysqltest1';
db	name	type	specific_name	language	sql_data_access	is_deterministic	security_type	param_list	returns	body	definer	created	modified	sql_mode	comment
mysqltest1	fn1	FUNCTION	fn1	SQL	CONTAINS_SQL	YES	DEFINER		int(11)	begin
return unix_timestamp();
end	@	#	#		
drop function fn1;
drop database mysqltest1;

--- New file ---
+++ mysql-test/t/rpl_sp-master.opt	05/04/20 23:35:09
--log_bin_trust_routine_creators=0


--- New file ---
+++ mysql-test/t/rpl_sp-slave.opt	05/04/20 23:35:09
--log_bin_trust_routine_creators=0


--- New file ---
+++ mysql-test/t/rpl_sp.test	05/04/20 23:35:09
# Test of replication of stored procedures (WL#2146 for MySQL 5.0)

source include/master-slave.inc;

# First let's test replication of current_user() (that's a related thing)
# we need a db != test, where we don't have automatic grants
create database if not exists mysqltest1;
use mysqltest1;
create table t1 (a varchar(100));
sync_slave_with_master;
use mysqltest1;

# ********************** PART 1 : STORED PROCEDURES ***************

# Does the same proc as on master get inserted into mysql.proc ?
# (same definer, same properties...)

connection master;
# cleanup
--disable_warnings
drop procedure if exists foo;
drop procedure if exists foo2;
drop procedure if exists foo3;
drop procedure if exists foo4;
drop procedure if exists bar;
drop function if exists fn1;
--enable_warnings

delimiter |;
--error 1105; # not deterministic
create procedure foo()
begin
  declare b int;
  set b = 8;
  insert into t1 values (b);
  insert into t1 values (unix_timestamp());
end|

--replace_column 2 # 5 #
show binlog events from 98| # check that not there

create procedure foo() deterministic
begin
  declare b int;
  set b = 8;
  insert into t1 values (b);
  insert into t1 values (unix_timestamp());
end|
delimiter ;|

# we replace columns having times
# (even with fixed timestamp displayed time may changed based on TZ)
--replace_result localhost.localdomain localhost 127.0.0.1 localhost
--replace_column 13 # 14 #
select * from mysql.proc where name='foo' and db='mysqltest1';
sync_slave_with_master;
--replace_result localhost.localdomain localhost 127.0.0.1 localhost
--replace_column 13 # 14 #
select * from mysql.proc where name='foo' and db='mysqltest1';

# Now when we call it, does the CALL() get into binlog,
# or the substatements?
connection master;
# see if timestamp used in SP on slave is same as on master
set timestamp=1000000000;
call foo();
--replace_column 2 # 5 #
show binlog events from 308;
select * from t1;
sync_slave_with_master;
select * from t1;

# Now a SP which is supposed to not update tables (CALL should not be
# binlogged) as it's "read sql data", so should not give error even if
# non-deterministic.

connection master;
delete from t1;
create procedure foo2()
  not deterministic
  reads sql data
  select * from mysqltest1.t1;
call foo2();
# verify CALL is not in binlog
--replace_column 2 # 5 #
show binlog events from 605;

--error 1105;
alter procedure foo2 contains sql;

# SP with definer's right

drop table t1;
create table t1 (a int);
create table t2 like t1;

create procedure foo3()
  deterministic
  insert into t1 values (15);

# let's create a non-privileged user
grant CREATE ROUTINE, EXECUTE on mysqltest1.* to "zedjzlcsjhd"@127.0.0.1;
grant SELECT on mysqltest1.t1 to "zedjzlcsjhd"@127.0.0.1;
grant SELECT, INSERT on mysqltest1.t2 to "zedjzlcsjhd"@127.0.0.1;

connect (con1,127.0.0.1,zedjzlcsjhd,,mysqltest1,$MASTER_MYPORT,);
connection con1;

--error 1105; # only full-global-privs user can create a routine
create procedure foo4()
  deterministic
  insert into t1 values (10);

connection master;
set global log_bin_trust_routine_creators=1;
connection con1;

delimiter |;
create procedure foo4()
  deterministic
  begin
  insert into t2 values(3);
  insert into t1 values (5);
  end|

delimiter ;|

--replace_result localhost.localdomain localhost 127.0.0.1 localhost
--error 1142;
call foo4(); # invoker has no INSERT grant on table => failure
show warnings;

connection master;
call foo3(); # success (definer == root)
show warnings;

--replace_result localhost.localdomain localhost 127.0.0.1 localhost
--error 1142;
call foo4(); # definer's rights => failure
show warnings;

# we test replication of ALTER PROCEDURE
alter procedure foo4 sql security invoker;
call foo4(); # invoker's rights => success
show warnings;

# Check that only successful CALLs are in binlog
--replace_column 2 # 5 #
show binlog events from 841;

# Note that half-failed CALLs are not in binlog, which is a known
# bug. If we compare t2 on master and slave we see they differ:

select * from t1;
select * from t2;
sync_slave_with_master;
select * from t1;
select * from t2;
select if(compte<>3,"this is broken but documented","this unexpectedly works?") from
(select count(*) as compte from t2) as aggreg;

# Test of DROP PROCEDURE

--replace_result localhost.localdomain localhost 127.0.0.1 localhost
--replace_column 13 # 14 #
select * from mysql.proc where name="foo4" and db='mysqltest1';
connection master;
drop procedure foo4;
select * from mysql.proc where name="foo4" and db='mysqltest1';
sync_slave_with_master;
select * from mysql.proc where name="foo4" and db='mysqltest1';

# ********************** PART 2 : FUNCTIONS ***************

connection master;
drop procedure foo;
drop procedure foo2;
drop procedure foo3;

delimiter |;
create function fn1(x int)
       returns int
       deterministic
begin
       insert into t1 values (x);
       return x+2;
end|

delimiter ;|
delete t1,t2 from t1,t2;
select fn1(20);
insert into t2 values(fn1(21));
select * from t1;
select * from t2;
sync_slave_with_master;
select * from t1;
select if(compte<>1,"this is broken but documented","this unexpectedly works?") from
(select count(*) as compte from t1 where a=20) as aggreg;
select * from t2;

connection master;
delimiter |;

drop function fn1;

create function fn1()
       returns int
       deterministic
begin
       return unix_timestamp();
end|
delimiter ;|
delete from t1;
set timestamp=1000000000;
insert into t1 values(fn1()); 

--replace_result localhost.localdomain localhost 127.0.0.1 localhost
--replace_column 13 # 14 #
select * from mysql.proc where db='mysqltest1';
select * from t1;

sync_slave_with_master;
use mysqltest1;
select * from t1;
--replace_result localhost.localdomain localhost 127.0.0.1 localhost
--replace_column 13 # 14 #
select * from mysql.proc where db='mysqltest1';


# Clean up
connection master;
drop function fn1;
drop database mysqltest1;
sync_slave_with_master;


--- 1.105/sql/set_var.cc	2005-04-16 03:23:45 +02:00
+++ 1.106/sql/set_var.cc	2005-04-20 23:35:08 +02:00
@@ -203,6 +203,9 @@
 							      param_age_threshold));
 sys_var_bool_ptr	sys_local_infile("local_infile",
 					 &opt_local_infile);
+sys_var_bool_ptr       
+sys_trust_routine_creators("log_bin_trust_routine_creators",
+                           &trust_routine_creators);
 sys_var_thd_ulong	sys_log_warnings("log_warnings", &SV::log_warnings);
 sys_var_thd_ulong	sys_long_query_time("long_query_time",
 					     &SV::long_query_time);
@@ -703,6 +706,7 @@
   &sys_innodb_thread_sleep_delay,
   &sys_innodb_thread_concurrency,
 #endif  
+  &sys_trust_routine_creators,
   &sys_engine_condition_pushdown,
 #ifdef HAVE_NDBCLUSTER_DB
   &sys_ndb_autoincrement_prefetch_sz,
@@ -842,6 +846,7 @@
 #endif
   {"log",                     (char*) &opt_log,                     SHOW_BOOL},
   {"log_bin",                 (char*) &opt_bin_log,                 SHOW_BOOL},
+  {sys_trust_routine_creators.name,(char*) &sys_trust_routine_creators, SHOW_SYS},
   {"log_error",               (char*) log_error_file,               SHOW_CHAR},
 #ifdef HAVE_REPLICATION
   {"log_slave_updates",       (char*) &opt_log_slave_updates,       SHOW_MY_BOOL},

--- 1.1/mysql-test/valgrind.supp	2005-04-06 16:22:16 +02:00
+++ 1.2/mysql-test/valgrind.supp	2005-04-20 23:35:07 +02:00
@@ -25,12 +25,32 @@
 }
 
 {
+   pthread allocate_dtv memory loss second
+   Memcheck:Leak
+   fun:calloc
+   fun:allocate_dtv
+   fun:_dl_allocate_tls
+   fun:pthread_create*
+}
+
+{
    pthread memalign memory loss
    Memcheck:Leak
    fun:memalign
    fun:_dl_allocate_tls_storage
    fun:__GI__dl_allocate_tls
    fun:pthread_create
+}
+
+{
+   pthread strstr uninit
+   Memcheck:Cond
+   fun:strstr
+   obj:/lib/tls/libpthread.so.*
+   obj:/lib/tls/libpthread.so.*
+   fun:call_init
+   fun:_dl_init
+   obj:/lib/ld-*.so
 }
 
 {

--- 1.73/sql/sp.cc	2005-04-06 23:40:25 +02:00
+++ 1.74/sql/sp.cc	2005-04-20 23:35:08 +02:00
@@ -58,6 +58,9 @@
 
 bool mysql_proc_table_exists= 1;
 
+/* Tells what SP_DEFAULT_ACCESS should be mapped to */
+#define SP_DEFAULT_ACCESS_MAPPING SP_CONTAINS_SQL
+
 /* *opened=true means we opened ourselves */
 static int
 db_find_routine_aux(THD *thd, int type, sp_name *name,
@@ -189,7 +192,7 @@
     chistics.daccess= SP_MODIFIES_SQL_DATA;
     break;
   default:
-    chistics.daccess= SP_CONTAINS_SQL;
+    chistics.daccess= SP_DEFAULT_ACCESS_MAPPING;
   }
 
   if ((ptr= get_field(thd->mem_root,
@@ -425,9 +428,51 @@
 	store(sp->m_chistics->comment.str, sp->m_chistics->comment.length,
 	      system_charset_info);
 
+    if (!trust_routine_creators && mysql_bin_log.is_open())
+    {
+      if (!sp->m_chistics->detistic)
+      {
+	/*
+	  Note that for a _function_ this test is not enough; one could use
+	  a non-deterministic read-only function in an update statement.
+	*/
+	enum enum_sp_data_access access=
+	  (sp->m_chistics->daccess == SP_DEFAULT_ACCESS) ?
+	  SP_DEFAULT_ACCESS_MAPPING : sp->m_chistics->daccess;
+	if (access == SP_CONTAINS_SQL ||
+	    access == SP_MODIFIES_SQL_DATA)
+	{
+	  my_message(ER_UNKNOWN_ERROR,
+		     "This routine is declared to be "
+		     "non-deterministic and to modify data and binary "
+		     "logging is enabled", MYF(0));
+	  ret= SP_INTERNAL_ERROR;
+	  goto done;
+	}
+      }
+      if (!(thd->master_access & SUPER_ACL))
+      {
+	my_message(ER_UNKNOWN_ERROR,
+		   "You do not have SUPER privilege and binary logging "
+		   "is enabled (you *might* want to use the less safe "
+		   "log_bin_trust_routine_creators variable)",
+		   MYF(0));
+	ret= SP_INTERNAL_ERROR;
+	goto done;
+      }
+    }
+
     ret= SP_OK;
     if (table->file->write_row(table->record[0]))
       ret= SP_WRITE_ROW_FAILED;
+    else if (mysql_bin_log.is_open())
+    {
+      thd->clear_error();
+      /* Such a statement can always go directly to binlog, no trans cache */
+      Query_log_event qinfo(thd, thd->query, thd->query_length, 0, FALSE);
+      mysql_bin_log.write(&qinfo);
+    }
+
   }
 
 done:
Thread
• bk commit into 5.0 tree (gbichot:1.1862) BUG#2610guilhem20 Apr