diff options
| -rw-r--r-- | mysql-test/extra/rpl_tests/rpl_loadfile.test | 36 | ||||
| -rw-r--r-- | mysql-test/suite/rpl/r/rpl_loadfile.result | 18 | ||||
| -rw-r--r-- | mysql-test/suite/rpl/r/rpl_stm_loadfile.result | 231 | ||||
| -rw-r--r-- | mysql-test/suite/rpl/t/rpl_loadfile.test | 126 | ||||
| -rw-r--r-- | mysql-test/suite/rpl/t/rpl_stm_loadfile.test | 20 | ||||
| -rw-r--r-- | sql/item_create.cc | 1 |
6 files changed, 400 insertions, 32 deletions
diff --git a/mysql-test/extra/rpl_tests/rpl_loadfile.test b/mysql-test/extra/rpl_tests/rpl_loadfile.test new file mode 100644 index 00000000000..85620b58a97 --- /dev/null +++ b/mysql-test/extra/rpl_tests/rpl_loadfile.test @@ -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; diff --git a/mysql-test/suite/rpl/r/rpl_loadfile.result b/mysql-test/suite/rpl/r/rpl_loadfile.result index 7a5a7bc50c9..63710326086 100644 --- a/mysql-test/suite/rpl/r/rpl_loadfile.result +++ b/mysql-test/suite/rpl/r/rpl_loadfile.result @@ -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; diff --git a/mysql-test/suite/rpl/r/rpl_stm_loadfile.result b/mysql-test/suite/rpl/r/rpl_stm_loadfile.result new file mode 100644 index 00000000000..d18befe6e4c --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_stm_loadfile.result @@ -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; diff --git a/mysql-test/suite/rpl/t/rpl_loadfile.test b/mysql-test/suite/rpl/t/rpl_loadfile.test index 5aa4da41fe5..d12d43224df 100644 --- a/mysql-test/suite/rpl/t/rpl_loadfile.test +++ b/mysql-test/suite/rpl/t/rpl_loadfile.test @@ -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; diff --git a/mysql-test/suite/rpl/t/rpl_stm_loadfile.test b/mysql-test/suite/rpl/t/rpl_stm_loadfile.test new file mode 100644 index 00000000000..3db8385a2f2 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_stm_loadfile.test @@ -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 + diff --git a/sql/item_create.cc b/sql/item_create.cc index 349c47816ad..bf359b10caa 100644 --- a/sql/item_create.cc +++ b/sql/item_create.cc @@ -3791,6 +3791,7 @@ Create_func_load_file Create_func_load_file::s_singleton; 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); } |