#At file:///home/acorreia/workspace.sun/repository.mysql/bzrwork/bug-38174/mysql-5.1-bugteam/ based on revid:matthias.leich@stripped
2768 Alfranio Correia 2009-02-09
BUG#38174 secure-file-priv breaks LOAD DATA INFILE replication in statement mode
If secure-file-priv was set on slave, it became unable to execute
LOAD DATA INFILE statements sent from master using mixed or
statement-based replication.
This patch fixes the issue by forcing that the temporary files are always
created under the --secure-file-priv unless --secure-file-priv is defined.
added:
mysql-test/suite/bugs/r/rpl_bug38174.result
mysql-test/suite/bugs/t/rpl_bug38174-slave.opt
mysql-test/suite/bugs/t/rpl_bug38174.test
modified:
sql/log_event.cc
sql/log_event.h
sql/sql_load.cc
per-file messages:
sql/log_event.cc
Changed the log_event such that the --secure-file-priv option takes precedence over --slave-load-tmpdir.
In other words, temporary files required to process LOAD_FILE and LOAD DATA INFILE are created under the
directory --slave-load-tmpdir unless --secure-file-priv is defined.
sql/log_event.h
Defines "SQL_LOAD-" as a macro.
=== added file 'mysql-test/suite/bugs/r/rpl_bug38174.result'
--- a/mysql-test/suite/bugs/r/rpl_bug38174.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/bugs/r/rpl_bug38174.result 2009-02-09 21:38:38 +0000
@@ -0,0 +1,84 @@
+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 table if exists t1;
+stop slave;
+CREATE TABLE t1 (
+`pk` int(11) NOT NULL AUTO_INCREMENT,
+`int_nokey` int(11) NOT NULL,
+`int_key` int(11) NOT NULL,
+`date_key` date NOT NULL,
+`date_nokey` date NOT NULL,
+`time_key` time NOT NULL,
+`time_nokey` time NOT NULL,
+`datetime_key` datetime NOT NULL,
+`datetime_nokey` datetime NOT NULL,
+`varchar_key` varchar(1) NOT NULL,
+`varchar_nokey` varchar(1) NOT NULL,
+PRIMARY KEY (`pk`),
+KEY `int_key` (`int_key`),
+KEY `date_key` (`date_key`),
+KEY `time_key` (`time_key`),
+KEY `datetime_key` (`datetime_key`),
+KEY `varchar_key` (`varchar_key`)
+);
+INSERT INTO t1 VALUES (1,8,5,'0000-00-00','0000-00-00','10:37:38','10:37:38','0000-00-00 00:00:00','0000-00-00 00:00:00','p','p'),
+(2,1,6,'2005-12-23','2005-12-23','02:24:28','02:24:28','0000-00-00 00:00:00','0000-00-00 00:00:00','g','g'),
+(3,0,3,'2009-09-14','2009-09-14','00:00:00','00:00:00','2000-01-30 16:39:40','2000-01-30 16:39:40','q','q'),
+(4,1,4,'2002-12-03','2002-12-03','20:34:58','20:34:58','2009-10-28 09:46:21','2009-10-28 09:46:21','g','g'),
+(5,1,6,'2007-03-29','2007-03-29','15:49:00','15:49:00','0000-00-00 00:00:00','0000-00-00 00:00:00','m','m'),
+(6,4,0,'2002-12-04','2002-12-04','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','o','o'),
+(7,9,0,'2005-01-28','2005-01-28','00:00:00','00:00:00','2001-05-18 00:00:00','2001-05-18 00:00:00','w','w'),
+(8,6,0,'0000-00-00','0000-00-00','06:57:25','06:57:25','0000-00-00 00:00:00','0000-00-00 00:00:00','m','m'),
+(9,0,0,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','z','z'),
+(10,4,6,'2006-08-15','2006-08-15','00:00:00','00:00:00','2002-04-12 14:44:25','2002-04-12 14:44:25','j','j'),
+(11,0,5,'2006-12-20','2006-12-20','10:13:53','10:13:53','2008-07-22 00:00:00','2008-07-22 00:00:00','y','y'),
+(12,9,7,'0000-00-00','0000-00-00','00:00:00','00:00:00','2004-07-05 00:00:00','2004-07-05 00:00:00','{','{'),
+(13,4,3,'2007-01-26','2007-01-26','23:00:51','23:00:51','2001-05-16 00:00:00','2001-05-16 00:00:00','f','f'),
+(14,7,0,'2004-03-27','2004-03-27','00:00:00','00:00:00','2005-01-24 03:30:37','2005-01-24 03:30:37','',''),
+(15,6,0,'2006-07-26','2006-07-26','18:43:57','18:43:57','0000-00-00 00:00:00','0000-00-00 00:00:00','{','{'),
+(16,0,6,'2000-01-14','2000-01-14','00:00:00','00:00:00','2000-09-21 00:00:00','2000-09-21 00:00:00','o','o'),
+(17,9,8,'0000-00-00','0000-00-00','21:15:08','21:15:08','0000-00-00 00:00:00','0000-00-00 00:00:00','a','a'),
+(18,2,0,'2004-10-27','2004-10-27','00:00:00','00:00:00','2004-03-24 22:13:43','2004-03-24 22:13:43','',''),
+(19,7,4,'0000-00-00','0000-00-00','08:38:27','08:38:27','2002-03-18 19:51:44','2002-03-18 19:51:44','t','t'),
+(20,5,3,'2008-03-07','2008-03-07','03:29:07','03:29:07','2007-12-01 18:44:44','2007-12-01 18:44:44','t','t'),
+(77,6,0,'2009-05-19','2009-05-19','14:56:44','14:56:44','2007-09-09 22:47:39','2007-09-09 22:47:39','',''),
+(35,6,3,'0000-00-00','0000-00-00','09:38:22','09:38:22','0000-00-00 00:00:00','0000-00-00 00:00:00','i','i'),
+(39,8,8,'0000-00-00','0000-00-00','10:25:31','10:25:31','2003-01-24 10:45:23','2003-01-24 10:45:23','',''),
+(29,5,6,'0000-00-00','0000-00-00','00:21:35','00:21:35','0000-00-00 00:00:00','0000-00-00 00:00:00','y','y'),
+(53,3,5,'0000-00-00','0000-00-00','00:00:00','00:00:00','2007-11-05 23:51:21','2007-11-05 23:51:21','p','p'),
+(60,0,2,'2009-09-11','2009-09-11','23:56:26','23:56:26','2003-12-24 04:28:32','2003-12-24 04:28:32','n','n'),
+(93,7,2,'2007-08-18','2007-08-18','00:00:00','00:00:00','2009-02-01 00:00:00','2009-02-01 00:00:00','y','y');
+SELECT * FROM t1 ORDER BY RAND () INTO OUTFILE "MYSQL_TEST_DIR/var/tmp/bug38174-1.test";
+drop table if exists t1;
+start slave;
+CREATE TABLE t1 (
+`pk` int(11) NOT NULL AUTO_INCREMENT,
+`int_nokey` int(11) NOT NULL,
+`int_key` int(11) NOT NULL,
+`date_key` date NOT NULL,
+`date_nokey` date NOT NULL,
+`time_key` time NOT NULL,
+`time_nokey` time NOT NULL,
+`datetime_key` datetime NOT NULL,
+`datetime_nokey` datetime NOT NULL,
+`varchar_key` varchar(1) NOT NULL,
+`varchar_nokey` varchar(1) NOT NULL,
+PRIMARY KEY (`pk`),
+KEY `int_key` (`int_key`),
+KEY `date_key` (`date_key`),
+KEY `time_key` (`time_key`),
+KEY `datetime_key` (`datetime_key`),
+KEY `varchar_key` (`varchar_key`)
+);
+LOAD DATA INFILE "MYSQL_TEST_DIR/var/tmp/bug38174-1.test" REPLACE INTO TABLE t1;
+select count(*) from t1;
+count(*)
+27
+select count(*) from t1;
+count(*)
+27
+drop table t1;
=== added file 'mysql-test/suite/bugs/t/rpl_bug38174-slave.opt'
--- a/mysql-test/suite/bugs/t/rpl_bug38174-slave.opt 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/bugs/t/rpl_bug38174-slave.opt 2009-02-09 21:38:38 +0000
@@ -0,0 +1 @@
+--secure-file-priv=/tmp
=== added file 'mysql-test/suite/bugs/t/rpl_bug38174.test'
--- a/mysql-test/suite/bugs/t/rpl_bug38174.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/bugs/t/rpl_bug38174.test 2009-02-09 21:38:38 +0000
@@ -0,0 +1,132 @@
+# This test verifies if it is possible to execute LOAD DATA INFILE
+# in while using the secure-file-priv option on slave.
+#
+# The test is divide in three steps:
+# 1 - Stops replication and then creates a file in the master from which
+# a table should be populate.
+# 2 - Starts replication, creates a table and populates it through
+# "LOAD DATA INFILE".
+# 3 - Compares the master and slave.
+#
+source include/master-slave.inc;
+
+connection master;
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+
+##########################################################################
+
+connection slave;
+
+stop slave;
+source include/wait_for_slave_to_stop.inc;
+
+connection master;
+
+
+CREATE TABLE t1 (
+ `pk` int(11) NOT NULL AUTO_INCREMENT,
+ `int_nokey` int(11) NOT NULL,
+ `int_key` int(11) NOT NULL,
+ `date_key` date NOT NULL,
+ `date_nokey` date NOT NULL,
+ `time_key` time NOT NULL,
+ `time_nokey` time NOT NULL,
+ `datetime_key` datetime NOT NULL,
+ `datetime_nokey` datetime NOT NULL,
+ `varchar_key` varchar(1) NOT NULL,
+ `varchar_nokey` varchar(1) NOT NULL,
+ PRIMARY KEY (`pk`),
+ KEY `int_key` (`int_key`),
+ KEY `date_key` (`date_key`),
+ KEY `time_key` (`time_key`),
+ KEY `datetime_key` (`datetime_key`),
+ KEY `varchar_key` (`varchar_key`)
+);
+
+INSERT INTO t1 VALUES (1,8,5,'0000-00-00','0000-00-00','10:37:38','10:37:38','0000-00-00 00:00:00','0000-00-00 00:00:00','p','p'),
+(2,1,6,'2005-12-23','2005-12-23','02:24:28','02:24:28','0000-00-00 00:00:00','0000-00-00 00:00:00','g','g'),
+(3,0,3,'2009-09-14','2009-09-14','00:00:00','00:00:00','2000-01-30 16:39:40','2000-01-30 16:39:40','q','q'),
+(4,1,4,'2002-12-03','2002-12-03','20:34:58','20:34:58','2009-10-28 09:46:21','2009-10-28 09:46:21','g','g'),
+(5,1,6,'2007-03-29','2007-03-29','15:49:00','15:49:00','0000-00-00 00:00:00','0000-00-00 00:00:00','m','m'),
+(6,4,0,'2002-12-04','2002-12-04','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','o','o'),
+(7,9,0,'2005-01-28','2005-01-28','00:00:00','00:00:00','2001-05-18 00:00:00','2001-05-18 00:00:00','w','w'),
+(8,6,0,'0000-00-00','0000-00-00','06:57:25','06:57:25','0000-00-00 00:00:00','0000-00-00 00:00:00','m','m'),
+(9,0,0,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','z','z'),
+(10,4,6,'2006-08-15','2006-08-15','00:00:00','00:00:00','2002-04-12 14:44:25','2002-04-12 14:44:25','j','j'),
+(11,0,5,'2006-12-20','2006-12-20','10:13:53','10:13:53','2008-07-22 00:00:00','2008-07-22 00:00:00','y','y'),
+(12,9,7,'0000-00-00','0000-00-00','00:00:00','00:00:00','2004-07-05 00:00:00','2004-07-05 00:00:00','{','{'),
+(13,4,3,'2007-01-26','2007-01-26','23:00:51','23:00:51','2001-05-16 00:00:00','2001-05-16 00:00:00','f','f'),
+(14,7,0,'2004-03-27','2004-03-27','00:00:00','00:00:00','2005-01-24 03:30:37','2005-01-24 03:30:37','',''),
+(15,6,0,'2006-07-26','2006-07-26','18:43:57','18:43:57','0000-00-00 00:00:00','0000-00-00 00:00:00','{','{'),
+(16,0,6,'2000-01-14','2000-01-14','00:00:00','00:00:00','2000-09-21 00:00:00','2000-09-21 00:00:00','o','o'),
+(17,9,8,'0000-00-00','0000-00-00','21:15:08','21:15:08','0000-00-00 00:00:00','0000-00-00 00:00:00','a','a'),
+(18,2,0,'2004-10-27','2004-10-27','00:00:00','00:00:00','2004-03-24 22:13:43','2004-03-24 22:13:43','',''),
+(19,7,4,'0000-00-00','0000-00-00','08:38:27','08:38:27','2002-03-18 19:51:44','2002-03-18 19:51:44','t','t'),
+(20,5,3,'2008-03-07','2008-03-07','03:29:07','03:29:07','2007-12-01 18:44:44','2007-12-01 18:44:44','t','t'),
+(77,6,0,'2009-05-19','2009-05-19','14:56:44','14:56:44','2007-09-09 22:47:39','2007-09-09 22:47:39','',''),
+(35,6,3,'0000-00-00','0000-00-00','09:38:22','09:38:22','0000-00-00 00:00:00','0000-00-00 00:00:00','i','i'),
+(39,8,8,'0000-00-00','0000-00-00','10:25:31','10:25:31','2003-01-24 10:45:23','2003-01-24 10:45:23','',''),
+(29,5,6,'0000-00-00','0000-00-00','00:21:35','00:21:35','0000-00-00 00:00:00','0000-00-00 00:00:00','y','y'),
+(53,3,5,'0000-00-00','0000-00-00','00:00:00','00:00:00','2007-11-05 23:51:21','2007-11-05 23:51:21','p','p'),
+(60,0,2,'2009-09-11','2009-09-11','23:56:26','23:56:26','2003-12-24 04:28:32','2003-12-24 04:28:32','n','n'),
+(93,7,2,'2007-08-18','2007-08-18','00:00:00','00:00:00','2009-02-01 00:00:00','2009-02-01 00:00:00','y','y');
+
+--replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR
+--eval SELECT * FROM t1 ORDER BY RAND () INTO OUTFILE "$MYSQL_TEST_DIR/var/tmp/bug38174-1.test"
+
+drop table if exists t1;
+
+##########################################################################
+
+connection slave;
+
+start slave;
+source include/wait_for_slave_to_start.inc;
+
+connection master;
+
+CREATE TABLE t1 (
+ `pk` int(11) NOT NULL AUTO_INCREMENT,
+ `int_nokey` int(11) NOT NULL,
+ `int_key` int(11) NOT NULL,
+ `date_key` date NOT NULL,
+ `date_nokey` date NOT NULL,
+ `time_key` time NOT NULL,
+ `time_nokey` time NOT NULL,
+ `datetime_key` datetime NOT NULL,
+ `datetime_nokey` datetime NOT NULL,
+ `varchar_key` varchar(1) NOT NULL,
+ `varchar_nokey` varchar(1) NOT NULL,
+ PRIMARY KEY (`pk`),
+ KEY `int_key` (`int_key`),
+ KEY `date_key` (`date_key`),
+ KEY `time_key` (`time_key`),
+ KEY `datetime_key` (`datetime_key`),
+ KEY `varchar_key` (`varchar_key`)
+);
+
+--replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR
+--eval LOAD DATA INFILE "$MYSQL_TEST_DIR/var/tmp/bug38174-1.test" REPLACE INTO TABLE t1
+
+##########################################################################
+
+save_master_pos;
+connection slave;
+sync_with_master;
+
+connection master;
+select count(*) from t1;
+connection slave;
+select count(*) from t1;
+
+connection master;
+--replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR
+--remove_file $MYSQL_TEST_DIR/var/tmp/bug38174-1.test
+
+drop table t1;
+
+save_master_pos;
+connection slave;
+sync_with_master;
=== modified file 'sql/log_event.cc'
--- a/sql/log_event.cc 2009-02-04 11:08:27 +0000
+++ b/sql/log_event.cc 2009-02-09 21:38:38 +0000
@@ -13,7 +13,6 @@
along with this program; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */
-
#ifdef MYSQL_CLIENT
#include "mysql_priv.h"
@@ -349,12 +348,13 @@ static char *pretty_print_str(char *pack
@return
Pointer to start of extension
*/
-
static char *slave_load_file_stem(char *buf, uint file_id,
int event_server_id, const char *ext)
{
char *res;
- fn_format(buf,"SQL_LOAD-",slave_load_tmpdir, "", MY_UNPACK_FILENAME);
+ fn_format(buf, PREFIX_SQL_LOAD,
+ (opt_secure_file_priv ? opt_secure_file_priv : slave_load_tmpdir),
+ "", MY_UNPACK_FILENAME);
to_unix_path(buf);
buf = strend(buf);
@@ -382,7 +382,8 @@ static void cleanup_load_tmpdir()
uint i;
char fname[FN_REFLEN], prefbuf[31], *p;
- if (!(dirp=my_dir(slave_load_tmpdir,MYF(MY_WME))))
+ if (!(dirp=my_dir((opt_secure_file_priv ? opt_secure_file_priv : slave_load_tmpdir),
+ MYF(MY_WME))))
return;
/*
@@ -393,7 +394,7 @@ static void cleanup_load_tmpdir()
we cannot meet Start_log event in the middle of events from one
LOAD DATA.
*/
- p= strmake(prefbuf, STRING_WITH_LEN("SQL_LOAD-"));
+ p= strmake(prefbuf, STRING_WITH_LEN(PREFIX_SQL_LOAD));
p= int10_to_str(::server_id, p, 10);
*(p++)= '-';
*p= 0;
@@ -403,7 +404,9 @@ static void cleanup_load_tmpdir()
file=dirp->dir_entry+i;
if (is_prefix(file->name, prefbuf))
{
- fn_format(fname,file->name,slave_load_tmpdir,"",MY_UNPACK_FILENAME);
+ fn_format(fname,file->name,
+ (opt_secure_file_priv ? opt_secure_file_priv : slave_load_tmpdir),
+ "",MY_UNPACK_FILENAME);
my_delete(fname, MYF(0));
}
}
=== modified file 'sql/log_event.h'
--- a/sql/log_event.h 2009-01-23 12:22:05 +0000
+++ b/sql/log_event.h 2009-02-09 21:38:38 +0000
@@ -47,6 +47,8 @@
#include "rpl_reporting.h"
#endif
+#define PREFIX_SQL_LOAD "SQL_LOAD-"
+
/**
Either assert or return an error.
=== modified file 'sql/sql_load.cc'
--- a/sql/sql_load.cc 2008-09-17 12:54:50 +0000
+++ b/sql/sql_load.cc 2009-02-09 21:38:38 +0000
@@ -310,14 +310,27 @@ int mysql_load(THD *thd,sql_exchange *ex
is_fifo = 1;
#endif
- if (opt_secure_file_priv &&
- strncmp(opt_secure_file_priv, name, strlen(opt_secure_file_priv)))
+ if (opt_secure_file_priv)
{
- /* Read only allowed from within dir specified by secure_file_priv */
- my_error(ER_OPTION_PREVENTS_STATEMENT, MYF(0), "--secure-file-priv");
- DBUG_RETURN(TRUE);
+ char buf[FN_REFLEN];
+ char *check_dir;
+ if (thd->slave_thread)
+ {
+ fn_format(buf, PREFIX_SQL_LOAD, opt_secure_file_priv, "", MY_UNPACK_FILENAME);
+ to_unix_path(buf);
+ check_dir= buf;
+ }
+ else
+ {
+ check_dir= opt_secure_file_priv;
+ }
+ if (strncmp(check_dir, name, strlen(check_dir)))
+ {
+ /* Read only allowed from within dir specified by secure_file_priv */
+ my_error(ER_OPTION_PREVENTS_STATEMENT, MYF(0), "--secure-file-priv");
+ DBUG_RETURN(TRUE);
+ }
}
-
}
if ((file=my_open(name,O_RDONLY,MYF(MY_WME))) < 0)
DBUG_RETURN(TRUE);
