bzr commit into mysql-5.1-bugteam branch (Li-Bing.Song:3111) Bug#43579

[Li-Bing.Song]

#At file:///home/anders/work/bzrwork/September/Bug43579/mysql-5.1-bugteam/ based on revid:joro@stripped

 3111 Li-Bing.Song@stripped	2009-09-16
      BUG#43579 mysql_upgrade tries to alter log tables on replicated database 
      
      All statements executed by mysql_upgrade are binlogged and then are replicated to slave.
      This will result in some errors. The report of this bug has demonstrated some examples.
      
      Master and slave should be upgraded singly. All statements executed by
      mysql_upgrade will not be binlogged. It can be done by that msql_upgrade
      assigns 0 to session.sql_log_bin before any statements are executed

    added:
      mysql-test/suite/rpl/r/rpl_mysql_upgrade.result
      mysql-test/suite/rpl/t/rpl_mysql_upgrade.test
    modified:
      client/mysql_upgrade.c
      client/mysqlcheck.c
=== modified file 'client/mysql_upgrade.c'
--- a/client/mysql_upgrade.c	2009-08-28 16:21:54 +0000
+++ b/client/mysql_upgrade.c	2009-09-16 00:31:45 +0000
@@ -448,6 +448,8 @@ static int run_query(const char *query, 
   int ret;
   File fd;
   char query_file_path[FN_REFLEN];
+  const uchar * sql_log_bin= "SET SQL_LOG_BIN=0;";
+
   DBUG_ENTER("run_query");
   DBUG_PRINT("enter", ("query: %s", query));
   if ((fd= create_temp_file(query_file_path, opt_tmpdir,
@@ -455,6 +457,14 @@ static int run_query(const char *query, 
                             MYF(MY_WME))) < 0)
     die("Failed to create temporary file for defaults");
 
+  if (my_write(fd, sql_log_bin, strlen(sql_log_bin),
+               MYF(MY_FNABP | MY_WME)))
+  {
+    my_close(fd, MYF(0));
+    my_delete(query_file_path, MYF(0));
+    die("Failed to write to '%s'", query_file_path);
+  }
+
   if (my_write(fd, (uchar*) query, strlen(query),
                MYF(MY_FNABP | MY_WME)))
   {
@@ -648,6 +658,7 @@ static int run_mysqlcheck_upgrade(void)
                   "--check-upgrade",
                   "--all-databases",
                   "--auto-repair",
+                  "--skip-write-binlog",
                   NULL);
 }
 
@@ -662,6 +673,7 @@ static int run_mysqlcheck_fixnames(void)
                   "--all-databases",
                   "--fix-db-names",
                   "--fix-table-names",
+                  "--skip-write-binlog",
                   NULL);
 }
 

=== modified file 'client/mysqlcheck.c'
--- a/client/mysqlcheck.c	2009-07-14 17:08:38 +0000
+++ b/client/mysqlcheck.c	2009-09-16 00:31:45 +0000
@@ -652,6 +652,18 @@ static int use_db(char *database)
   return 0;
 } /* use_db */
 
+static int disable_binlog()
+{
+  char qbuf[100 + NAME_LEN*4];
+  sprintf(qbuf, "SET SQL_LOG_BIN=0");
+  if (mysql_query(sock, qbuf))
+  {
+    fprintf(stderr, "Failed to %s\n", qbuf);
+    fprintf(stderr, "Error: %s\n", mysql_error(sock));
+    return 1;
+  }
+  return 0;
+}
 
 static int handle_request_for_tables(char *tables, uint length)
 {
@@ -844,6 +856,11 @@ int main(int argc, char **argv)
   if (dbConnect(current_host, current_user, opt_password))
     exit(EX_MYSQLERR);
 
+  if (!opt_write_binlog)
+  {
+    disable_binlog();
+  }
+
   if (opt_auto_repair &&
       my_init_dynamic_array(&tables4repair, sizeof(char)*(NAME_LEN*2+2),16,64))
   {

=== added file 'mysql-test/suite/rpl/r/rpl_mysql_upgrade.result'
--- a/mysql-test/suite/rpl/r/rpl_mysql_upgrade.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/r/rpl_mysql_upgrade.result	2009-09-16 00:31:45 +0000
@@ -0,0 +1,18 @@
+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;
+DROP DATABASE IF EXISTS `#mysql50#mysqltest-1`;
+CREATE DATABASE `#mysql50#mysqltest-1`;
+show binlog events from <binlog_start>;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	#	Query	#	#	DROP DATABASE IF EXISTS `#mysql50#mysqltest-1`
+master-bin.000001	#	Query	#	#	CREATE DATABASE `#mysql50#mysqltest-1`
+show binlog events from <binlog_start>;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	#	Query	#	#	DROP DATABASE IF EXISTS `#mysql50#mysqltest-1`
+master-bin.000001	#	Query	#	#	CREATE DATABASE `#mysql50#mysqltest-1`
+DROP DATABASE `mysqltest-1`;
+DROP DATABASE `#mysql50#mysqltest-1`;

=== added file 'mysql-test/suite/rpl/t/rpl_mysql_upgrade.test'
--- a/mysql-test/suite/rpl/t/rpl_mysql_upgrade.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/t/rpl_mysql_upgrade.test	2009-09-16 00:31:45 +0000
@@ -0,0 +1,31 @@
+##########################################################################
+# BUG#43579 Upgrade statemens are binlogged.
+# Master and slave should be upgraded singly. All statements executed by
+# mysql_upgrade will not be binlogged. It can be done by that msql_upgrade
+# assigns 0 to session.sql_log_bin before any statements are executed.
+# ##########################################################################
+--source include/master-slave.inc
+
+# Only run test if "mysql_upgrade" is found
+--require r/have_mysql_upgrade.result
+--disable_query_log
+select LENGTH("$MYSQL_UPGRADE")>0 as have_mysql_upgrade;
+--enable_query_log
+
+connection master;
+--disable_warnings
+DROP DATABASE IF EXISTS `#mysql50#mysqltest-1`;
+CREATE DATABASE `#mysql50#mysqltest-1`;
+--enable_warnings
+sync_slave_with_master;
+
+connection master;
+source include/show_binlog_events.inc;
+#With '--force' option, mysql_upgrade always executes all sql statements for upgrading.
+--exec $MYSQL_UPGRADE --skip-verbose --force --user=root > $MYSQLTEST_VARDIR/log/mysql_upgrade.log 2>&1
+source include/show_binlog_events.inc;
+
+DROP DATABASE `mysqltest-1`;
+connection slave;
+DROP DATABASE `#mysql50#mysqltest-1`;
+--source include/master-slave-end.inc


Attachment: [text/bzr-bundle] bzr/[e-mail]