From: Li-Bing.Song Date: September 21 2009 2:15pm Subject: bzr commit into mysql-5.1-bugteam branch (Li-Bing.Song:3111) Bug#43579 List-Archive: http://lists.mysql.com/commits/83956 X-Bug: 43579 Message-Id: <200909211416.n8LEGCbU031649@anders-laptop> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============0581408572==" --===============0581408572== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #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 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 --===============0581408572== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/li-bing.song@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: li-bing.song@stripped # target_branch: file:///home/anders/work/bzrwork/September/Bug43579\ # /mysql-5.1-bugteam/ # testament_sha1: 61f29f9d88829b9b3b8b04b80a234f245f95d82a # timestamp: 2009-09-21 22:15:12 +0800 # base_revision_id: joro@stripped # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWX0d6F0ABdJfgFATf/f////n /+T////+YA2OfSHO3Td3UYoVRtNpMbA0rIFBrStYjRtltW4aNSmk2k9Tyj1NME0ZMmnoGoMQYQyG CA0aaaAJJBDQEwCnqSaGaZNEYTIDJ6RtQAaBk02UOMmCaGQyMjJoaANBkYQDQaNMhiGgAkSQyRPK ZoqfkTap7Ij1R+knkeqaeU9Q9TQNDQANHqHqDjJgmhkMjIyaGgDQZGEA0GjTIYhoAJJAQBExo1M1 MmFNJ6TaCNDUZM1HlNomTQemoZGpCoiT/5UXuM2IxmNqXv0KaTVDoBiksLbY9fwei0EpU0i37PXL wT52obfDaMm8KgrxG2DNtrWfGvcglj7Pv0Pw5Jd81O3NLt5F7QmqN6WcL6c9DvaxV0cpcGSpCKmA zBZr9RYWOacGg7XwgUPk/CD6g1MP3u0xG6tHBopyQZZQgRjSQ03gQkjeEtRB+fRxePUG/ycB6iP0 tRgy67vhpZe5y8wrzUMBtNo0FVEfyP9wMJ3VBBzPXFt74Py2Uxg0efZDjvdCmaeouIvKmFW3GDoc iDyVY6NZexwztv9q2MzD/tNSgShR+IRP3p3jxzHBe8KKl+Ay3UG/gu9wfCJvwuzp5MGJok8HEEL0 x0ARPJOUyJY4ghhuhaPU2IW9B2LVVKRW9yh0n5mtizVjlOptSzuDYWupRgrAkxccpC6e2hDFcwXX DFEp0YMSvWJOucen3U/650KrPJSPRdBXkjAlaEEYR3mAvRhKDAwOqjio09gD6WFoqnYKrlYVYOUw u1uja44rSbD4tZbVSi3wpdKEs3b4vHv3821hISwiFNTd0lWyYJsS7fgFJ7g7BkewVoqzbww2222d nHVIjlztIA/R62c897tJ86cEEwybx7595E7ig4bNJ5SJyhv3aSzmSn9LDzX/C+w6hwHrpKN9DSOq 0Ga01HzD7+TNHaC6yxHyx8QioIRYFcXROECCXAS4ChIsyWyTMQPAHdRsH1DhKB8xP47iZJToy7mx 0hBlnon6GlJQKLVaFw1iebhGQZMQl02t6790mMqs0bKU5bWMdmthA5VMBESeOXPcl536I3vR9THt pYJhdsqFzslEBlZlYocPeyyurfOYo6SaVU7ImtbSE4bafA4O4FhAlQUESJI/UkRN9JJDq6ys+JQi KYEMgiMJWB0LBV3ZpE2KiJWc8jac1VxTAT+9yvLLJKAcT6odRccewm8S1uYZHjbZr676pvpr/GTU s9NxmwshLGEDcSqLKAl0X2j5WT3FlVJaRMCOMpG1JEfSYkXlItANjuomTIlNYNbi6t0a3bECeoST YmhSAZMFxOV5GddmpWTlw5XjojPppLiYD5EiBgbCoM50ePpoJmfqlToMy0Yl5OUSovjOsit8RmC0 rtGMNqqpUSmznnmKzU6iwpLlzmhYU3VNm/bQ2jqReVoQxPFxqUx+ktM8+yUE84wwlVr1alxaSnIk xROULDiFoLPd5eHfmPd2TkusLQ2ZjQ4jEuti8V5YUhTq4ic0dygjRxm5RuTGiwtH7CovHs4rPIPk wly3FIMrBiwuLzgXkRzd5usqufZi4gNw1HmQQhUUOxl23bvUhgYxHEzOewpmBoKyBEuKiQ9w3KBI yxpNeBfK++9+g0FxGxplG6aso89BAgWki8nVJEsMEtjPCYpTGnXnUacolbcqIl7sjSAlNnq/GgzD WL6WcQduZGg+FbWscSkz3Wk0jUlFUkiAmOdcK7GnqecC4oBLgTlKZK0EMAZM3UUioLzEFhbZkIwJ nATLA5tnoOJdgUhexVnKhhVpQeK+8CVvBfT/G0cGhrWkjAlXumDbYyLG9EOl7pDjrM7Eb2XBX7ds GMkS1yvY2mVjIxN2IZA22wOw9ipl7heXfKkEuPXOEZonJedKYS+8C35z29fcA9gewD0lAWWLjVwz AzMm+AFSGhlR3THEAIl0BPrrPuAdbIl7CI0yHL00D7v3OpcHkTo9x/1kP2NoKqwKJjUMOopgUZjq bV1C9UA/tj1+1fo86dxzNsv4sc/jC7G8UP5DB0RRxHPNYqgq3HZ+w2nEW0rCAH0HZ5m1hwvTDBIL S777hygBmBhgwlneQCLu2nHUbzQDYAx22nOB5Su5VTwyIiHuR7qA8y+3rl6B1CfvplSGmwJFaP2J BA2DCxuIHZfXUTm0H1DJpCYVBVCXaGgWs7QNZI+s9J8YXC7hi4AiR6/oPQWzH2jo9I8eGRmwzOMy 8x4GiEqmq8gSHgfIX6CQlT7vdgvyBrM4s/v6i6Qe3FDkwFbFiehhL2FMIfgN5PGlSgiMbiwsZlSB WMbO0RQJi3ybyJ6ff7jqO04nEmWGAPGMj39moxA7hlyDYcDzI/mJMDIZCij8LEj8hLdpllyH49Kq 2D7mOinzEF56yG5wYjsDZ+J1k3kCBwMDYEjsLy1TIlBQUGk1IsMd5SW12Bqk5JjkGo8BrZ+8eqDb UiOsEYziBgWmbmxI6BoLWr00iE214zqRCJYuU0GQ5YDjIqgbh5QluKUpj2GqWgS3FxmNBWZFZE4c jARuZJMmCjFneMxNnxpFRKfmyK/zPEOicRp07zmeQZnjyJvaNzGdRI2JWJkHJ51xWlEwnM7MAsmQ GKhkU0MyGZKDAyGMTfqGLSnuO2UR3cpUg474YtiLY98Wt6JxkjAcKIEhOHjEBh5YZlxfNocu5AJc AgBNWmUGUr057NBzIbNyvmTYsSxMITKEh1teSvnQVo6lUA5BBMipBSxVqclFgm7IwkdpZwDJBTmL xmLjTmLSSE6CkEFEHPEqc7CJpGBOikRj2SOxHYuRE0G/EyHHgRcROJE7h4rvETLxf4RoOs6IYcJj xdDHtxl1laTg8h3B2nh1m6jJLtrchMAM5knADI3HQhwrESOKGWkKEazVQchJSP0zw5iV65TiwXMw 14Jklq89XhZmfmS7R8tZvSFJEvHTbmZ+hMvXFdqQec+q4Sr71z848gn327xLbeSJEzBWnIcpizGB 08vgd6D6Bkl3GkLTNZ4xmWsNDwqAz5j1TEsBkhS0nh8tENVyQyYYRjYl4tHGYmkXh7XdwL5iJeJF J7tZMDzx4noPV0Mpo5WGNp6jvmaj1HlgYnEtA2UCW0caDtAzpaDw6xL5MTVgHpYXTJlqOsgUyUy/ bWL0CbGIVYJh4lhRHIMTmRbJOIDAMFbOOpxTzCO8A9BtSZEVsLPZUDnBbsd7AOUZIpZKCGRKw8GJ IPJWZpHFQh4M2qqpbagRyJary8zBUpkhbN5WBR34HOzaJYFZRXyE1xko/RyKbJeo8v5/t7BRgwuf MzmIYMBGECdH4MzlXuZ5DzFA3a6x5GDk0EgcsewJQeCiWieHk+sqeilwSeAzprtCREQPiwlFDyZd cc1SQvVMPPcls1ekrQTuSY02gGuW9T1nUNK5aRrO4SCvT0O4ETGPXcKgZCM4/3SEpAubjsLWF7j1 lYdR4BxIynCgBwLgn+Mgqsnh+QxnmlU7fVV4qk1mXlHKAicj5dIhTh2E5HP4vEl3y/CkXAOYSWoD 6gjUUBhPyGRbaNVWJc255jZchcYoEr83gwOOqs5dAHpMyeOLjsJRJ6hxMYTMLISTgMt9SjDYmB4Z N848K3iVYfJ5iT2b7dsHo2sn77arjWcRIzRINenl45Q/WjvKQ162V/oGZYbbl9zODS75EMzbJSbA fiM5LApSIa53EsICJAYd+7n54D3jQHPRwHEyshjTctfc4rXHZAWoUVaVMMkUnnsAkjepnym0zg9Q ZmCI9cMwlzIjxKS3CyZmhsQRJPH/IMkg1GsY60mZkG7LRuKgBiROknmEJgKIaGBJeVIJpzxTGcwV aNjX4jDkSvc5zMpFVTzGogCqZksyngC42FDZGp5YV1Ib7hDFgRSofIrBwlnJYjkUMgJ7kUAwyRNF gwjLyhGuqSQ4Q1purqGIrSzPM5IqNAMMrNJ0ZFYFrCEGNOBwAxq3GQlRiQ4Dcgl0Lu6clfwmSDzo L97cx8MCBiuD1pe5IIZCFaD3qvrMtwlcB9apCsQbFLP14gmEqzoiYj2aRPgWK8IHhNjKEOWoHj0j lPe5y4ww9AwkZus2q5jGcWHlRKUU0lpTkDUG09JqxigFoxDEqrTUMFS1kKVJ8wlxwKKYW17DSG/c oBikGf/i7kinChIPo70LoA== --===============0581408572==--