List:Commits« Previous MessageNext Message »
From:Luis Soares Date:February 17 2009 12:49am
Subject:bzr commit into mysql-5.1-bugteam branch (luis.soares:2801) Bug#39701
View as plain text  
#At file:///home/lsoares/Workspace/mysql-server/bugfix/39701/5.1-bt/ based on revid:alexey.kopytov@stripped

 2801 Luis Soares	2009-02-17
      BUG#39701: Mixed binlog format does not switch to row mode on LOAD_FILE
      
      MIXED mode binlog_format should switch to ROW mode whenever there is a risk of breaking 
      replication. However, LOAD_FILE, which depends on a file on the same host, does not trigger 
      a switch to ROW based mode. This leads to scenarios on which the slave replicates the 
      statement with 'load_file' and it will try to load the file from local file system. Given that 
      the file is mostly likely inexistent in slave filesystem the operation will not succeed 
      (probably returning NULL), causing master and slave(s) to diverge.
      
      This patch addresses this bug by marking the load_file function as unsafe. When in mixed mode 
      logging, this will make the procedure that decides on which logging format to use to be aware of
      the load_file function and decide accordingly.
      added:
        mysql-test/suite/rpl/r/rpl_mix_load_file.result
        mysql-test/suite/rpl/t/rpl_mix_load_file.test
      modified:
        sql/item_create.cc

per-file messages:
  mysql-test/suite/rpl/t/rpl_mix_load_file.test
    Simple test to assert that the patch fixes this bug.
  sql/item_create.cc
    Added the set_stmt_unsafe call to lex.
=== added file 'mysql-test/suite/rpl/r/rpl_mix_load_file.result'
--- a/mysql-test/suite/rpl/r/rpl_mix_load_file.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/r/rpl_mix_load_file.result	2009-02-17 00:49:33 +0000
@@ -0,0 +1,29 @@
+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;
+SELECT repeat('x',20) INTO OUTFILE '/home/lsoares/Workspace/mysql-server/bugfix/39701/5.1-bt/mysql-test/var/tmp/bug_39701.data';
+DROP TABLE IF EXISTS t1;
+Warnings:
+Note	1051	Unknown table 't1'
+CREATE TABLE t1 (t text);
+INSERT INTO t1 VALUES (load_file('/home/lsoares/Workspace/mysql-server/bugfix/39701/5.1-bt/mysql-test/var/tmp/bug_39701.data'));;
+INSERT INTO t1 VALUES ('1');
+SET BINLOG_FORMAT=STATEMENT;
+INSERT INTO t1 VALUES (LOAD_FILE('$MYSQL_TMP_DIR/inexistent'));
+Warnings:
+Warning	1592	Statement is not safe to log in statement format.
+show binlog events from <binlog_start>;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	#	Query	#	#	use `test`; DROP TABLE IF EXISTS t1
+master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE t1 (t text)
+master-bin.000001	#	Query	#	#	use `test`; BEGIN
+master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
+master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Query	#	#	use `test`; COMMIT
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES ('1')
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (LOAD_FILE('$MYSQL_TMP_DIR/inexistent'))
+Comparing tables master:test.t1 and slave:test.t1
+DROP TABLE t1;

=== added file 'mysql-test/suite/rpl/t/rpl_mix_load_file.test'
--- a/mysql-test/suite/rpl/t/rpl_mix_load_file.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/t/rpl_mix_load_file.test	2009-02-17 00:49:33 +0000
@@ -0,0 +1,62 @@
+#  BUG#39701: Mixed binlog format does not switch to row mode on LOAD_FILE
+#
+#  DESCRIPTION
+#
+#    Problem: when using load_file string function and mixed binlogging format
+#             there was no switch to row based binlogging format. This leads
+#             to scenarios on which the slave replicates the statement and it
+#             will try to load the file from local file system, which in most
+#             likely it will not exist.
+#
+#    Solution:
+#             Marking this function as unsafe for statement format, makes the
+#             statement using it to be logged in row based format. As such, data 
+#             replicated from the master, becomes the content of the loaded file.
+#             Consequently, the slave receives the necessary data to complete
+#             the load_file instruction correctly.
+#
+#  IMPLEMENTATION
+#
+#    The test is implemented as follows:
+#
+#      On Master,
+#       i) write to file the desired content.
+#      ii) create table, and load into it the file contents
+#     iii) execute load_file and safe statement (wrt to mixed mode binlogging)
+#      iv) show binlogevents to assert that tere are rows for load_file and
+#          statement for safe statement from iii)
+#       v) force format to statement and check that the statement is still
+#          logged, but this time with a warning.
+#      vi) remove the file used in load_file
+#
+#      On Slave,
+#     vii) check that it has replicated the correct values from master (should 
+#          load_file not switch to row mode, then slave differs from master, 
+#          because the file was removed in vi) and the load_file returns NULL
+#          when file does not exist) 
+#
+
+--source include/master-slave.inc
+--source include/have_binlog_format_mixed.inc
+
+let $file= $MYSQL_TMP_DIR/bug_39701.data;
+--eval SELECT repeat('x',20) INTO OUTFILE '$file'
+
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (t text);
+--eval INSERT INTO t1 VALUES (load_file('$file'));
+INSERT INTO t1 VALUES ('1');
+
+SET BINLOG_FORMAT=STATEMENT;
+INSERT INTO t1 VALUES (LOAD_FILE('$MYSQL_TMP_DIR/inexistent'));
+--source include/show_binlog_events.inc
+
+remove_file $file;
+sync_slave_with_master;
+
+let $diff_table_1=master:test.t1;
+let $diff_table_2=slave:test.t1;
+--source include/diff_tables.inc
+
+DROP TABLE t1;
+sync_slave_with_master;

=== modified file 'sql/item_create.cc'
--- a/sql/item_create.cc	2008-04-11 03:27:24 +0000
+++ b/sql/item_create.cc	2009-02-17 00:49:33 +0000
@@ -3791,6 +3791,7 @@ Create_func_load_file Create_func_load_f
 Item*
 Create_func_load_file::create(THD *thd, Item *arg1)
 {
+  thd->lex->set_stmt_unsafe();
   thd->lex->uncacheable(UNCACHEABLE_SIDEEFFECT);
   return new (thd->mem_root) Item_load_file(arg1);
 }

Thread
bzr commit into mysql-5.1-bugteam branch (luis.soares:2801) Bug#39701Luis Soares17 Feb
  • Re: bzr commit into mysql-5.1-bugteam branch (luis.soares:2801)Bug#39701Andrei Elkin19 Feb
  • Re: bzr commit into mysql-5.1-bugteam branch (luis.soares:2801)Bug#39701Mats Kindahl20 Feb