MySQL Bugs: #42582: why mysql use /tmp path?

Bug #42582	why mysql use /tmp path?
Submitted:	4 Feb 2009 7:38	Modified:	6 Feb 2009 1:24
Reporter:	Kyounghwan Lee	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S1 (Critical)
Version:	all version, 5.1.30	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
when replication
i use mysqlimport  on master 
it is found in binary log as below
LOAD DATA LOCAL INFILE '/tmp/SQL_LOAD_MB-37cd-0' ...
why it use  /tmp  path?
i think it must use mysql tmpdir path!!

How to repeat:
set up replication (statement based)

execute use mysqlimport  on master 

check out  mysql binary log

Suggested fix:
fix mysql to use mysql tmpdir  path

Thank you for the report.

> i use mysqlimport  on master 
> it is found in binary log as below
> LOAD DATA LOCAL INFILE '/tmp/SQL_LOAD_MB-37cd-0' ...

Path in the LOAD DATA LOCAL INFILE is user-specified and not related to any settings in the configuration file. Same for mysqlimport. So this looks like "Not a Bug" for me.

Please explain what is the bug here and how it is related to replication if you don't agree.

i set up linux machine  with small root(/) space
and set big space  for mysql datadir  and tmpdir

surely i define tmpdir  in my.cnf

then  i think it must use mysql tmpdir  for load data  file
but it doesn't

Thank you for the feedback.

I think I got your point: you issue statement like LOAD DATA LOCAL INFILE 'foo' INTO TABLE 'bar' and they are in binlog on master like LOAD DATA LOCAL INFILE '/tmp/XXX' ... Please correct me if I am wrong.

But I can not repeat described behavior if tmpdir set to custom location correctly.

Please indicate accurate minor version of MySQL server you use and provide output of SHOW VARIABLES LIKE '%tmp%' on master

it was what i mean~!!  i'm poor in english  :-)

tested in mysql 5.1.30

mysql> show variables like '%tmp%';
+-------------------+------------------+
| Variable_name     | Value            |
+-------------------+------------------+
| max_tmp_tables    | 32               |
| slave_load_tmpdir | /data3/temp_data |
| tmp_table_size    | 335544320        |
| tmpdir            | /data3/temp_data |
+-------------------+------------------+
4 rows in set (0.00 sec)

tested in mysql 5.0.33

mysql> show variables like '%tmp%';
+-------------------+-------------------+
| Variable_name     | Value             |
+-------------------+-------------------+
| max_tmp_tables    | 32                |
| slave_load_tmpdir | /data3/temp_data/ |
| tmp_table_size    | 335544320         |
| tmpdir            | /data3/temp_data  |
+-------------------+-------------------+
4 rows in set (0.00 sec)

tested in mysql 4.1.11

mysql> show variables like '%tmp%';
+----------------+------------------+
| Variable_name  | Value            |
+----------------+------------------+
| max_tmp_tables | 32               |
| tmp_table_size | 335544320        |
| tmpdir         | /data1/temp_data |
+----------------+------------------+
3 rows in set (0.00 sec)

Thank you for the feedback.

mysqlbinlog is client program and has own option --local-load to specify place where temporary files for LOAD DATA are located. See http://dev.mysql.com/doc/refman/5.1/en/mysqlbinlog.html#option_mysqlbinlog_local-load for details. So report is still "not a bug".

OK~!!  after use '--local-load=/data1/temp_data' option  binary log viewed like below 

#090206  9:55:26 server id 1  log_pos 150381615         Query   thread_id=1872984       exec_t
ime=1
# LOAD DATA INFILE '/somepath/somedata.dat'  REPLACE INTO TABLE `sometable` FIELDS TERMINATED BY '__kkkkk__' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINAT
ED BY '\n' STARTING BY '' (col1,col2,col3);
# file_id: 154072  block_len: 492416
# at 150874284
#090206  9:55:26 server id 1  log_pos 150874284
#Exec_load: file_id=154072
#090206  9:55:26 server id 1  log_pos 150381615         Query   thread_id=1872984       exec_t
ime=1
LOAD DATA LOCAL INFILE '/data1/temp_data/somedata.dat-259d8-0'  REPLACE INTO TABLE
`sometable` FIELDS TERMINATED BY '__kkkkk__' ENCLOSED BY '' ESCAPED BY '\\' LINES TE
RMINATED BY '\n' STARTING BY '' (col1,col2,col3);
# file_id: 154072  block_len: 492416

The original sql was commented out  and it converted into next sql
then  second sql will be executed on slave   right?
and data file (file_id:154072) is capsuled in binary log 
because mysqlbinlog can't show data file  so it need --local-load  option  to dump out it's data file

Thx  
i think mysql document need more detail description the way in replication