MySQL Bugs: #1073: Replicating LOAD DATA INFILE mysqlbinlog does not cleanup tmp files

Bug #1073	Replicating LOAD DATA INFILE mysqlbinlog does not cleanup tmp files
Submitted:	18 Aug 2003 4:40	Modified:	10 Dec 2003 3:50
Reporter:	Peter Zaitsev (Basic Quality Contributor)	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0	OS:	Any (all)
Assigned to:	Bugs System	CPU Architecture:	Any

Description:
The mysqlbinlog does not seem to cleanup files in the temporary directory
properly at least in case of error condition.

Even worse at subsequent runs it failes due to existing file (it shall probably assign the new name in this case instead)

How to repeat:
pz@abyss:~/work/db> ls /tmp/ | grep gg
gg.sql
gg.txt
pz@abyss:~/work/db> /home/pz/mysql/mysql-4.0/client/mysqlbinlog abyss-bin.011 | mysql test
ERROR 1148 at line 14: The used command is not allowed with this MySQL version
pz@abyss:~/work/db> ls /tmp/ | grep gg
gg.sql
gg.txt
gg.txt-00000002

As you might see gg.txt-00000002 file is created and not cleaned up after exit of mysqlbinlog

Now trying to load it once again:

pz@abyss:~/work/db> /home/pz/mysql/mysql-4.0/client/mysqlbinlog abyss-bin.011
# at 4
#030814 10:05:47 server id 1  log_pos 4         Start: binlog v 3, server v 4.0.15-log created 030814 10:05:47 at startup
# at 79
#030814 10:07:00 server id 1  log_pos 79        Query   thread_id=4     exec_time=0     error_code=0
use test;
SET TIMESTAMP=1060841220;
DELETE FROM gg;
# at 128
#LOAD DATA INFILE '/tmp/gg.txt' INTO TABLE gg  FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' STARTING BY '' (n);
# file_id: 2  block_len: 5
/home/pz/mysql/mysql-4.0/client/mysqlbinlog: File '/tmp/gg.txt-00000002' not found (Errcode: 17)

Here is the full log file content:

pz@abyss:~/work/db> /home/pz/mysql/mysql-4.0/client/mysqlbinlog abyss-bin.011
# at 4
#030814 10:05:47 server id 1  log_pos 4         Start: binlog v 3, server v 4.0.15-log created 030814 10:05:47 at startup
# at 79
#030814 10:07:00 server id 1  log_pos 79        Query   thread_id=4     exec_time=0     error_code=0
use test;
SET TIMESTAMP=1060841220;
DELETE FROM gg;
# at 128
#LOAD DATA INFILE '/tmp/gg.txt' INTO TABLE gg  FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' STARTING BY '' (n);
# file_id: 2  block_len: 5
# at 206
#030814 10:07:00 server id 1  log_pos 206
#Exec_load: file_id=2
LOAD DATA LOCAL INFILE '/tmp/gg.txt-00000002' INTO TABLE gg  FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' STARTING BY '' (n);
# file_id: 2  block_len: 5

File gg.txt is the following:

1

Table structure:
CREATE TABLE gg (
  n int(11) default NULL
) TYPE=MyISAM;

> The mysqlbinlog does not seem to cleanup files in the temporary directory
> properly at least in case of error condition.

Presently it does not clear these files, in any case, true.
If there's an error in 'mysql' (like in your example below) then who should delete it? Not 'mysql' (you don't want your client to delete your data files). Not 'mysqlbinlog' (it is the not the program which got the error).
Even in the case where 'mysqlbinlog' has an error (which usually means a corrupted binlog), it should not delete the file: imagine the error is not related to LOAD stuff.

> Even worse at subsequent runs it failes due to existing file (it shall
> probably assign the new name in this case instead)

This part makes sense. I am now assigning this bug to Victor, who recently fixed a lot of things with LOAD DATA & mysqlbinlog. He will know what we can and can't do, if we should consider this a bug or not. Thanks a lot, Victor.

> pz@abyss:~/work/db> ls /tmp/ | grep gg
> gg.sql
> gg.txt
> gg.txt-00000002
> 
> As you might see gg.txt-00000002 file is created and not cleaned up after
> exit of mysqlbinlog

It should not be cleaned up, at least in some cases: if you are running mysqlbinlog > file.sql and saving {file.sql and the attached gg.txt-00000002} for future use.

In MySQL 4.0.16 we fixed that mysqlbinlog will notice if there was old temporary files and not reuse these names.

We can't for the moment delete the temporary files as this will break mysqlbinlog usage. I did however update the manual about this.

In the future we will fix this problem by allowing mysqlbinlog
to connect directly to a mysqld server.  In this case we can
safely remove the log files when the logs has been applied.