#At file:///home/anders/work/bzrwork/September/Bug43579/mysql-5.1-bugteam/ based on revid:joro@stripped
3111 Li-Bing.Song@stripped 2009-09-21
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 separately. 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/include/have_mysql_upgrade.inc
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-21 14:14:58 +0000
@@ -54,6 +54,8 @@ static char **defaults_argv;
static my_bool not_used; /* Can't use GET_BOOL without a value pointer */
+static my_bool opt_write_binlog;
+
#include <help_start.h>
static struct my_option my_long_options[]=
@@ -124,6 +126,10 @@ static struct my_option my_long_options[
{"verbose", 'v', "Display more output about the process",
(uchar**) &opt_verbose, (uchar**) &opt_verbose, 0,
GET_BOOL, NO_ARG, 1, 0, 0, 0, 0, 0},
+ {"write-binlog", OPT_WRITE_BINLOG,
+ "All commands including mysqlcheck are binlogged. Disabled by default.",
+ (uchar**) &opt_write_binlog, (uchar**) &opt_write_binlog, 0, GET_BOOL, NO_ARG,
+ 0, 0, 0, 0, 0, 0},
{0, 0, 0, 0, 0, 0, GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0}
};
@@ -448,6 +454,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 +463,22 @@ static int run_query(const char *query,
MYF(MY_WME))) < 0)
die("Failed to create temporary file for defaults");
+ /*
+ Master and slave should be upgraded separately. All statements executed
+ by mysql_upgrade will not be binlogged.
+ 'SET SQL_LOG_BIN=0' is executed before any other statements.
+ */
+ if (!opt_write_binlog)
+ {
+ 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)))
{
@@ -640,6 +664,11 @@ static void print_conn_args(const char *
static int run_mysqlcheck_upgrade(void)
{
+ const char *write_binlog= "--write-binlog";
+ const char *skip_write_binlog= "--skip-write-binlog";
+ const char *opt;
+ opt= opt_write_binlog ? write_binlog : skip_write_binlog;
+
print_conn_args("mysqlcheck");
return run_tool(mysqlcheck_path,
NULL, /* Send output from mysqlcheck directly to screen */
@@ -648,12 +677,18 @@ static int run_mysqlcheck_upgrade(void)
"--check-upgrade",
"--all-databases",
"--auto-repair",
+ opt,
NULL);
}
static int run_mysqlcheck_fixnames(void)
{
+ const char *write_binlog= "--write-binlog";
+ const char *skip_write_binlog= "--skip-write-binlog";
+ const char *opt;
+ opt= opt_write_binlog ? write_binlog : skip_write_binlog;
+
print_conn_args("mysqlcheck");
return run_tool(mysqlcheck_path,
NULL, /* Send output from mysqlcheck directly to screen */
@@ -662,6 +697,7 @@ static int run_mysqlcheck_fixnames(void)
"--all-databases",
"--fix-db-names",
"--fix-table-names",
+ opt,
NULL);
}
=== modified file 'client/mysqlcheck.c'
--- a/client/mysqlcheck.c 2009-07-14 17:08:38 +0000
+++ b/client/mysqlcheck.c 2009-09-21 14:14:58 +0000
@@ -652,6 +652,17 @@ static int use_db(char *database)
return 0;
} /* use_db */
+static int disable_binlog()
+{
+ const char *stmt= "SET SQL_LOG_BIN=0";
+ if (mysql_query(sock, stmt))
+ {
+ fprintf(stderr, "Failed to %s\n", stmt);
+ fprintf(stderr, "Error: %s\n", mysql_error(sock));
+ return 1;
+ }
+ return 0;
+}
static int handle_request_for_tables(char *tables, uint length)
{
@@ -844,6 +855,14 @@ int main(int argc, char **argv)
if (dbConnect(current_host, current_user, opt_password))
exit(EX_MYSQLERR);
+ if (!opt_write_binlog)
+ {
+ if (disable_binlog()) {
+ first_error= 1;
+ goto end;
+ }
+ }
+
if (opt_auto_repair &&
my_init_dynamic_array(&tables4repair, sizeof(char)*(NAME_LEN*2+2),16,64))
{
=== added file 'mysql-test/include/have_mysql_upgrade.inc'
--- a/mysql-test/include/have_mysql_upgrade.inc 1970-01-01 00:00:00 +0000
+++ b/mysql-test/include/have_mysql_upgrade.inc 2009-09-21 14:14:58 +0000
@@ -0,0 +1,4 @@
+--require r/have_mysql_upgrade.result
+--disable_query_log
+select LENGTH("$MYSQL_UPGRADE")>0 as have_mysql_upgrade;
+--enable_query_log
=== 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-21 14:14:58 +0000
@@ -0,0 +1,12 @@
+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`;
+Master file is not changed
+Master position is not changed
+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-21 14:14:58 +0000
@@ -0,0 +1,43 @@
+#############################################################################
+# BUG#43579 mysql_upgrade tries to alter log tables on replicated database
+# Master and slave should be upgraded separately. 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
+--source include/have_mysql_upgrade.inc
+
+connection master;
+--disable_warnings
+DROP DATABASE IF EXISTS `#mysql50#mysqltest-1`;
+CREATE DATABASE `#mysql50#mysqltest-1`;
+--enable_warnings
+sync_slave_with_master;
+
+connection master;
+let $before_file= query_get_value(SHOW MASTER STATUS, File, 1);
+let $before_position= query_get_value(SHOW MASTER STATUS, Position, 1);
+
+#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
+sync_slave_with_master;
+
+connection master;
+let $after_file= query_get_value(SHOW MASTER STATUS, File, 1);
+let $after_position= query_get_value(SHOW MASTER STATUS, Position, 1);
+
+if (`SELECT '$before_file'='$after_file'`)
+{
+ echo Master file is not changed;
+}
+if (`SELECT '$before_position'='$after_position'`)
+{
+ echo Master position is not changed;
+}
+
+DROP DATABASE `mysqltest-1`;
+connection slave;
+DROP DATABASE `#mysql50#mysqltest-1`;
+--source include/master-slave-end.inc
Attachment: [text/bzr-bundle] bzr/li-bing.song@sun.com-20090921141458-yzakbmlgusn7vktz.bundle
