List:Commits« Previous MessageNext Message »
From:Luis Soares Date:March 6 2009 3:15pm
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-03-06
      BUG#39701: Mixed binlog format does not switch to row mode on
      LOAD_FILE
            
      LOAD_FILE is not safe to replicate in STATEMENT mode, because it
      depends on a file (which is loaded on master and may not exist in
      slave(s)). 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 may not exist in the slave filesystem
      the operation will not succeed (probably returning NULL), causing
      master and slave(s) to diverge. However, when using MIXED mode
      replication, this can be made to work, if the statement including
      LOAD_FILE is marked as unsafe, triggering a switch to ROW mode,
      meaning that the contents of the file are written to binlog as row
      events. Consequently, the contents from the file in the master will
      reach the slave via the binlog.
           
      This patch addresses this bug by marking the load_file function as
      unsafe. When in mixed mode and when LOAD_FILE is issued, there will be
      a switch to row mode. Furthermore, when in statement mode, the
      LOAD_FILE will raise a warning that the statement is unsafe in that
      mode.
     @ mysql-test/extra/rpl_tests/rpl_loadfile.test
        Extra file that is "sourced" on both rpl_loadfile and rpl_stm_loadfile
        test files.
     @ mysql-test/suite/rpl/r/rpl_loadfile.result
        Updated with the results from the test case added to this file.
     @ mysql-test/suite/rpl/r/rpl_stm_loadfile.result
        Result file for rpl_loadfile test split with the warnings in statement
        mode.
     @ mysql-test/suite/rpl/t/rpl_loadfile.test
        After splitting the original rpl_loadfile file, this one is only 
        required to be executed in mixed or row format.
        Appended the test for 39701 to this file.
     @ mysql-test/suite/rpl/t/rpl_stm_loadfile.test
        Split the original rpl_loadfile test because load_file now raises
        a warning when in statement mode. The goal of this split is 
        two-fold: i) make the test case more resilient; ii) assert that 
        warnings are indeed raised when in statement mode.
     @ sql/item_create.cc
        Added the set_stmt_unsafe call to lex.

    added:
      mysql-test/extra/rpl_tests/rpl_loadfile.test
      mysql-test/suite/rpl/r/rpl_stm_loadfile.result
      mysql-test/suite/rpl/t/rpl_stm_loadfile.test
    modified:
      mysql-test/suite/rpl/r/rpl_loadfile.result
      mysql-test/suite/rpl/t/rpl_loadfile.test
      sql/item_create.cc
=== added file 'mysql-test/extra/rpl_tests/rpl_loadfile.test'
--- a/mysql-test/extra/rpl_tests/rpl_loadfile.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/extra/rpl_tests/rpl_loadfile.test	2009-03-06 15:15:15 +0000
@@ -0,0 +1,36 @@
+# Begin clean up test section
+--disable_warnings
+connection master;
+DROP PROCEDURE IF EXISTS test.p1;
+DROP TABLE IF EXISTS test.t1;
+--enable_warnings
+
+# Section 1 test 
+
+CREATE TABLE test.t1 (a INT, blob_column LONGBLOB, PRIMARY KEY(a));
+INSERT INTO test.t1  VALUES(1,'test');
+UPDATE test.t1 SET blob_column=LOAD_FILE('../../std_data/words2.dat') WHERE a=1;
+delimiter |;
+create procedure test.p1()
+begin
+  INSERT INTO test.t1  VALUES(2,'test');
+  UPDATE test.t1 SET blob_column=LOAD_FILE('../../std_data/words2.dat') WHERE a=2;
+end|
+delimiter ;|
+
+CALL test.p1();
+SELECT * FROM test.t1 ORDER BY blob_column;
+save_master_pos;
+sync_slave_with_master;
+connection slave;
+# Need to allow some time when NDB engine is used for
+# the injector thread to have time to populate binlog
+let $wait_condition= SELECT INSTR(blob_column,'aberration') > 0 FROM test.t1 WHERE a = 2;
+--source include/wait_condition.inc
+SELECT * FROM test.t1 ORDER BY blob_column;
+
+# Cleanup
+connection master;
+DROP PROCEDURE IF EXISTS test.p1;
+DROP TABLE test.t1;
+sync_slave_with_master;

=== modified file 'mysql-test/suite/rpl/r/rpl_loadfile.result'
--- a/mysql-test/suite/rpl/r/rpl_loadfile.result	2007-12-12 17:19:24 +0000
+++ b/mysql-test/suite/rpl/r/rpl_loadfile.result	2009-03-06 15:15:15 +0000
@@ -225,3 +225,21 @@ aberration
 
 DROP PROCEDURE IF EXISTS test.p1;
 DROP TABLE test.t1;
+**** Resetting master and slave ****
+include/stop_slave.inc
+RESET SLAVE;
+RESET MASTER;
+include/start_slave.inc
+SELECT repeat('x',20) INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug_39701.data';
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (t text);
+CREATE PROCEDURE p(file TEXT) 
+BEGIN
+INSERT INTO t1 VALUES (LOAD_FILE(file));
+END|
+include/stop_slave.inc
+CALL p('MYSQLTEST_VARDIR/tmp/bug_39701.data');
+include/start_slave.inc
+Comparing tables master:test.t1 and slave:test.t1
+DROP TABLE t1;
+DROP PROCEDURE p;

=== added file 'mysql-test/suite/rpl/r/rpl_stm_loadfile.result'
--- a/mysql-test/suite/rpl/r/rpl_stm_loadfile.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/r/rpl_stm_loadfile.result	2009-03-06 15:15:15 +0000
@@ -0,0 +1,231 @@
+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 PROCEDURE IF EXISTS test.p1;
+DROP TABLE IF EXISTS test.t1;
+CREATE TABLE test.t1 (a INT, blob_column LONGBLOB, PRIMARY KEY(a));
+INSERT INTO test.t1  VALUES(1,'test');
+UPDATE test.t1 SET blob_column=LOAD_FILE('../../std_data/words2.dat') WHERE a=1;
+Warnings:
+Warning	1592	Statement is not safe to log in statement format.
+create procedure test.p1()
+begin
+INSERT INTO test.t1  VALUES(2,'test');
+UPDATE test.t1 SET blob_column=LOAD_FILE('../../std_data/words2.dat') WHERE a=2;
+end|
+CALL test.p1();
+Warnings:
+Warning	1592	Statement is not safe to log in statement format.
+SELECT * FROM test.t1 ORDER BY blob_column;
+a	blob_column
+1	abase
+abased
+abasement
+abasements
+abases
+abash
+abashed
+abashes
+abashing
+abasing
+abate
+abated
+abatement
+abatements
+abater
+abates
+abating
+Abba
+abbe
+abbey
+abbeys
+abbot
+abbots
+Abbott
+abbreviate
+abbreviated
+abbreviates
+abbreviating
+abbreviation
+abbreviations
+Abby
+abdomen
+abdomens
+abdominal
+abduct
+abducted
+abduction
+abductions
+abductor
+abductors
+abducts
+Abe
+abed
+Abel
+Abelian
+Abelson
+Aberdeen
+Abernathy
+aberrant
+aberration
+
+2	abase
+abased
+abasement
+abasements
+abases
+abash
+abashed
+abashes
+abashing
+abasing
+abate
+abated
+abatement
+abatements
+abater
+abates
+abating
+Abba
+abbe
+abbey
+abbeys
+abbot
+abbots
+Abbott
+abbreviate
+abbreviated
+abbreviates
+abbreviating
+abbreviation
+abbreviations
+Abby
+abdomen
+abdomens
+abdominal
+abduct
+abducted
+abduction
+abductions
+abductor
+abductors
+abducts
+Abe
+abed
+Abel
+Abelian
+Abelson
+Aberdeen
+Abernathy
+aberrant
+aberration
+
+SELECT * FROM test.t1 ORDER BY blob_column;
+a	blob_column
+1	abase
+abased
+abasement
+abasements
+abases
+abash
+abashed
+abashes
+abashing
+abasing
+abate
+abated
+abatement
+abatements
+abater
+abates
+abating
+Abba
+abbe
+abbey
+abbeys
+abbot
+abbots
+Abbott
+abbreviate
+abbreviated
+abbreviates
+abbreviating
+abbreviation
+abbreviations
+Abby
+abdomen
+abdomens
+abdominal
+abduct
+abducted
+abduction
+abductions
+abductor
+abductors
+abducts
+Abe
+abed
+Abel
+Abelian
+Abelson
+Aberdeen
+Abernathy
+aberrant
+aberration
+
+2	abase
+abased
+abasement
+abasements
+abases
+abash
+abashed
+abashes
+abashing
+abasing
+abate
+abated
+abatement
+abatements
+abater
+abates
+abating
+Abba
+abbe
+abbey
+abbeys
+abbot
+abbots
+Abbott
+abbreviate
+abbreviated
+abbreviates
+abbreviating
+abbreviation
+abbreviations
+Abby
+abdomen
+abdomens
+abdominal
+abduct
+abducted
+abduction
+abductions
+abductor
+abductors
+abducts
+Abe
+abed
+Abel
+Abelian
+Abelson
+Aberdeen
+Abernathy
+aberrant
+aberration
+
+DROP PROCEDURE IF EXISTS test.p1;
+DROP TABLE test.t1;

=== modified file 'mysql-test/suite/rpl/t/rpl_loadfile.test'
--- a/mysql-test/suite/rpl/t/rpl_loadfile.test	2007-12-12 17:19:24 +0000
+++ b/mysql-test/suite/rpl/t/rpl_loadfile.test	2009-03-06 15:15:15 +0000
@@ -11,43 +11,105 @@
 
 # Includes
 -- source include/master-slave.inc
+-- source include/have_binlog_format_mixed_or_row.inc
 
+-- source extra/rpl_tests/rpl_loadfile.test
+
+#  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 stored procedure with load_file
+#     iii) stop slave
+#     iii) execute load_file 
+#      iv) remove file
+#
+#      On Slave,
+#       v) start slave
+#      vi) sync it with master so that it gets the updates from binlog (which 
+#          should have bin logged in row format). 
+#
+#          If the the binlog format does not change to row, then the assertion
+#          done in the following step fails. This happens because tables differ 
+#          since the file does not exist anymore, meaning that when slave 
+#          attempts to execute LOAD_FILE statement it inserts NULL on table 
+#          instead of the same contents that the master loaded when it executed 
+#          the procedure (which was executed when file existed).
+#
+#     vii) assert that the contents of master and slave 
+#          table are the same
 
-# Begin clean up test section
---disable_warnings
 connection master;
-DROP PROCEDURE IF EXISTS test.p1;
-DROP TABLE IF EXISTS test.t1;
---enable_warnings
-
-# Section 1 test 
-
-CREATE TABLE test.t1 (a INT, blob_column LONGBLOB, PRIMARY KEY(a));
-INSERT INTO test.t1  VALUES(1,'test');
-UPDATE test.t1 SET blob_column=LOAD_FILE('../../std_data/words2.dat') WHERE a=1;
-delimiter |;
-create procedure test.p1()
-begin
-  INSERT INTO test.t1  VALUES(2,'test');
-  UPDATE test.t1 SET blob_column=LOAD_FILE('../../std_data/words2.dat') WHERE a=2;
-end|
-delimiter ;|
-
-CALL test.p1();
-SELECT * FROM test.t1 ORDER BY blob_column;
-save_master_pos;
-sync_slave_with_master;
+source include/reset_master_and_slave.inc;
+
+connection master;
+let $file= $MYSQLTEST_VARDIR/tmp/bug_39701.data;
+
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+--eval SELECT repeat('x',20) INTO OUTFILE '$file'
+
+disable_warnings;
+DROP TABLE IF EXISTS t1;
+enable_warnings;
+
+CREATE TABLE t1 (t text);
+DELIMITER |;
+CREATE PROCEDURE p(file TEXT) 
+  BEGIN
+    INSERT INTO t1 VALUES (LOAD_FILE(file));
+  END|
+DELIMITER ;|
+
+# stop slave before issuing the load_file on master
 connection slave;
-# Need to allow some time when NDB engine is used for
-# the injector thread to have time to populate binlog
-let $wait_condition= SELECT INSTR(blob_column,'aberration') > 0 FROM test.t1 WHERE a = 2;
---source include/wait_condition.inc
-SELECT * FROM test.t1 ORDER BY blob_column;
+source include/stop_slave.inc;
+
+connection master;
+
+# test: check that logging falls back to rbr.
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+--eval CALL p('$file')
+
+# test: remove the file from the filesystem and assert that slave still 
+#       gets the loaded file
+remove_file $file;
+
+# now that the file is removed it is safe (regarding what we want to test) 
+# to start slave
+connection slave;
+source include/start_slave.inc;
 
-# Cleanup
 connection master;
-DROP PROCEDURE IF EXISTS test.p1;
-DROP TABLE test.t1;
 sync_slave_with_master;
 
-# End of 5.0 test case
+# assertion: assert that the slave got the updates even
+#            if the file was removed before the slave started,
+#            meaning that contents were indeed transfered
+#            through binlog (in row format)
+let $diff_table_1=master:test.t1;
+let $diff_table_2=slave:test.t1;
+source include/diff_tables.inc;
+
+# CLEAN UP
+DROP TABLE t1;
+DROP PROCEDURE p;
+sync_slave_with_master;

=== added file 'mysql-test/suite/rpl/t/rpl_stm_loadfile.test'
--- a/mysql-test/suite/rpl/t/rpl_stm_loadfile.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/t/rpl_stm_loadfile.test	2009-03-06 15:15:15 +0000
@@ -0,0 +1,20 @@
+#############################################################################
+# Original Author: JBM                                                      #
+# Original Date: Aug/18/2005                                                #
+#############################################################################
+# TEST: To test the LOAD_FILE() in rbr                                      #
+#############################################################################
+# Change Author: JBM
+# Change Date: 2006-01-16
+# Change: Added Order by for NDB
+# Change: Split the original test file. This one forces STATEMENT only because
+#         when in STATEMENT mode, the load_file will issue a warning, whereas
+#         in RBR or MIXED mode it does not (by lsoares).
+##########
+
+# Includes
+-- source include/master-slave.inc
+-- source include/have_binlog_format_statement.inc
+
+-- source extra/rpl_tests/rpl_loadfile.test
+

=== modified file 'sql/item_create.cc'
--- a/sql/item_create.cc	2008-04-11 03:27:24 +0000
+++ b/sql/item_create.cc	2009-03-06 15:15:15 +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);
 }


Attachment: [text/bzr-bundle] bzr/luis.soares@sun.com-20090306151515-5n0e3hisdx69ei4i.bundle
Thread
bzr commit into mysql-5.1-bugteam branch (luis.soares:2801) Bug#39701Luis Soares6 Mar
  • Re: bzr commit into mysql-5.1-bugteam branch (luis.soares:2801)Bug#39701Mats Kindahl12 Mar