1#############################################################################
2# Original Author: JBM                                                      #
3# Original Date: Aug/18/2005                                                #
4#############################################################################
5# TEST: To test the LOAD_FILE() in rbr                                      #
6#############################################################################
7# Change Author: JBM
8# Change Date: 2006-01-16
9# Change: Added Order by for NDB
10##########
11
12# Includes
13--source include/not_group_replication_plugin.inc
14-- source include/have_binlog_format_mixed_or_row.inc
15-- source include/master-slave.inc
16
17-- source extra/rpl_tests/rpl_loadfile.test
18
19#  BUG#39701: Mixed binlog format does not switch to row mode on LOAD_FILE
20#
21#  DESCRIPTION
22#
23#    Problem: when using load_file string function and mixed binlogging format
24#             there was no switch to row based binlogging format. This leads
25#             to scenarios on which the slave replicates the statement and it
26#             will try to load the file from local file system, which in most
27#             likely it will not exist.
28#
29#    Solution:
30#             Marking this function as unsafe for statement format, makes the
31#             statement using it to be logged in row based format. As such, data
32#             replicated from the master, becomes the content of the loaded file.
33#             Consequently, the slave receives the necessary data to complete
34#             the load_file instruction correctly.
35#
36#  IMPLEMENTATION
37#
38#    The test is implemented as follows:
39#
40#      On Master,
41#       i) write to file the desired content.
42#      ii) create table and stored procedure with load_file
43#     iii) stop slave
44#     iii) execute load_file
45#      iv) remove file
46#
47#      On Slave,
48#       v) start slave
49#      vi) sync it with master so that it gets the updates from binlog (which
50#          should have bin logged in row format).
51#
52#          If the the binlog format does not change to row, then the assertion
53#          done in the following step fails. This happens because tables differ
54#          since the file does not exist anymore, meaning that when slave
55#          attempts to execute LOAD_FILE statement it inserts NULL on table
56#          instead of the same contents that the master loaded when it executed
57#          the procedure (which was executed when file existed).
58#
59#     vii) assert that the contents of master and slave
60#          table are the same
61
62--source include/rpl_reset.inc
63
64connection master;
65let $file= $MYSQLTEST_VARDIR/tmp/bug_39701.data;
66
67--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
68--eval SELECT repeat('x',20) INTO OUTFILE '$file'
69
70disable_warnings;
71DROP TABLE IF EXISTS t1;
72enable_warnings;
73
74CREATE TABLE t1 (t text);
75DELIMITER |;
76CREATE PROCEDURE p(file varchar(4096))
77  BEGIN
78    INSERT INTO t1 VALUES (LOAD_FILE(file));
79  END|
80DELIMITER ;|
81
82# stop slave before issuing the load_file on master
83connection slave;
84source include/stop_slave.inc;
85
86connection master;
87
88# test: check that logging falls back to rbr.
89--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
90--eval CALL p('$file')
91
92# test: remove the file from the filesystem and assert that slave still
93#       gets the loaded file
94remove_file $file;
95
96# now that the file is removed it is safe (regarding what we want to test)
97# to start slave
98connection slave;
99source include/start_slave.inc;
100
101connection master;
102--source include/sync_slave_sql_with_master.inc
103
104# assertion: assert that the slave got the updates even
105#            if the file was removed before the slave started,
106#            meaning that contents were indeed transfered
107#            through binlog (in row format)
108let $diff_tables= master:t1, slave:t1;
109source include/diff_tables.inc;
110
111# CLEAN UP
112--connection master
113DROP TABLE t1;
114DROP PROCEDURE p;
115
116--source include/rpl_end.inc
117