Bug #34283 mysqlbinlog leaves tmpfile after termination if binlog contains load data infile
Submitted: 4 Feb 2008 14:50 Modified: 14 Oct 2010 12:24
Reporter: Sven Sandberg Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:4.1/5.0/5.1 OS:Any
Assigned to: Daogang Qu
Tags: LOAD DATA INFILE, mysqlbinlog, temporary file
Triage: Triaged: D3 (Medium)

[4 Feb 2008 14:50] Sven Sandberg
Description:
With statement- or mixed-mode logging, "LOAD DATA INFILE" queries are written to the binlog using special types of log events (Begin_load_query_log_event / Append_block_log_event / Execute_load_query_log_event in >= 5.0, Create_file_log_event / Append_block_log_event / Execute_file_log_event before 5.0). When mysqlbinlog reads such events, it re-creates the file in a temporary directory with a generated filename and outputs a "LOAD DATA INFILE" query where the filename is replaced by the generated file. The temporary file is not deleted by mysqlbinlog (it cannot be deleted since users must be able to pipe the output of mysqlbinlog to a client).

There are several problems with reproducing LOAD DATA INFILE queries in this manner:
 - If the user does not know the internal workings of mysqlbinlog, the temp files will accumulate on disk and never be removed.
 - There is no easy way to remove the temp files automatically (the safest way is to parse the output of mysqlbinlog, which is hard and there is still no guarantee that the format never changes, etc.)
 - Temp files will accumulate on the pushbuild server.

(An associated problem is that the way temporary files are generated, two instances of mysqlbinlog running in parallel may in theory generate the same temporary filename and write to it concurrently. Another issue is BUG#33238.)

How to repeat:
1. Execute a "LOAD DATA INFILE" query.
2. Run mysqlbinlog on the generated binlog.

Suggested fix:
Three possible solutions:

(1) Add flags to mysqlbinlog to aid handling temp files. E.g.:
    --no-generate-tempfiles           suppress creating temp files (useful when
                                      mysqlbinlog is used only to list binlog,
                                      not piping to client)
    --output-tempfile-names=FILENAME  write generated files to a given file
                                      (to simplify automatic and manual removal
                                      of generated tempfiles)
    --tempdir=DIR                     put tempfiles in given directory instead
                                      of system tempdir (also to simplify
                                      removal)

(2) In mixed mode logging, use row-based logging for "LOAD DATA INFILE".

(3) Introduce new SQL syntax allowing to include the entire file contents in the query. E.g.:

  LOAD DATA INSTRING 'file contents' INTO TABLE t1;

The point is that you can replace INFILE 'file name' by INSTRING 'file contents' and get an equivalent query that can be completely contained in the binlog. Then:
 - mysqlbinlog outputs LOAD DATA INSTRING when it sees Execute_file_query_log_event
 - we can get rid of the special events associated with LOAD DATA INFILE and just log a LOAD DATA INSTRING query instead.

I think (1) is a bad option. This is a bug in the architecture and should be fixed by a more robust architecture. (1) is just a workaround. The user still needs to be aware of this strange internal architecture.

I think (2) and (3) are both ok. If we do (2), it still does not work with pure statement-based logging, but I'm a mixed-mode advocate who thinks pure statement-based logging should be considered deprecated. (3) may be an interesting feature in itself but of course requires much more work than (2).
[4 Feb 2008 22:34] Sveta Smirnova
Thank you for the report.

Verified as described.

See also bug #12834 and bug #1073
[2 May 2008 12:19] Andrei Elkin
While I personally do not think it's too heavy burden for the user to remove
--load-local directory with possible associated files in it (not temporary!) -
after all the user is to remove the main mysqlbinlog produced file,
there is still a room for implementing the offered option (2) to switch to row-based replication instead of processing the load data event as currently.
Having that said, setting the ticket back to Open.
[27 Sep 2008 17:29] Sveta Smirnova
Bug #39701 was marked as duplicate of this one.
[18 May 2009 19:34] Daniel Fischer
LOAD DATA INSTRING is a nice idea, but there are certain limits on string length that make it rather unfeasible. (Remember we ignore a tonne of 64 bit warnings because we only support 1G strings, etc.)
[19 Jul 2010 7:35] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/113828

3470 Dao-Gang.Qu@sun.com	2010-07-19
      Bug #34283  mysqlbinlog leaves tmpfile after termination if binlog contains load data infile
      
      With statement- or mixed-mode logging, "LOAD DATA INFILE" queries
      are written to the binlog using special types of log events.
      When mysqlbinlog reads such events, it re-creates the file in a
      temporary directory with a generated filename and outputs a
      "LOAD DATA INFILE" query where the filename is replaced by the
      generated file. The temporary file is not deleted by mysqlbinlog
      after termination.
      
      To fix the problem, in mixed mode we go to row-based. In SBR, we
      document it to remind user the tmpfile is left in a temporary
      directory.
     @ sql/sql_load.cc
        Added code to go to row-based in mixed mode for
        'load data infile ...' statement
[23 Jul 2010 2:21] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/114191

3470 Dao-Gang.Qu@sun.com	2010-07-23
      Bug #34283  mysqlbinlog leaves tmpfile after termination if binlog contains load data infile
      
      With statement- or mixed-mode logging, "LOAD DATA INFILE" queries
      are written to the binlog using special types of log events.
      When mysqlbinlog reads such events, it re-creates the file in a
      temporary directory with a generated filename and outputs a
      "LOAD DATA INFILE" query where the filename is replaced by the
      generated file. The temporary file is not deleted by mysqlbinlog
      after termination.
      
      To fix the problem, in mixed mode we go to row-based. In SBR, we
      document it to remind user the tmpfile is left in a temporary
      directory.
     @ sql/sql_load.cc
        Added code to go to row-based in mixed mode for
        'load data infile ...' statement
[27 Jul 2010 5:32] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/114395

3470 Dao-Gang.Qu@sun.com	2010-07-27
      Bug #34283  mysqlbinlog leaves tmpfile after termination if binlog contains load data infile
      
      With statement- or mixed-mode logging, "LOAD DATA INFILE" queries
      are written to the binlog using special types of log events.
      When mysqlbinlog reads such events, it re-creates the file in a
      temporary directory with a generated filename and outputs a
      "LOAD DATA INFILE" query where the filename is replaced by the
      generated file. The temporary file is not deleted by mysqlbinlog
      after termination.
      
      To fix the problem, in mixed mode we go to row-based. In SBR, we
      document it to remind user the tmpfile is left in a temporary
      directory.
     @ mysql-test/suite/binlog/r/binlog_mixed_load_data.result
        Test result for BUG#34283.
     @ mysql-test/suite/binlog/t/binlog_mixed_load_data.test
        Added the test file to verify that 'load data infile...' statement
        will go to row-based in mixed mode.
     @ sql/sql_load.cc
        Added code to go to row-based in mixed mode for
        'load data infile ...' statement
[27 Jul 2010 9:59] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/114420

3470 Dao-Gang.Qu@sun.com	2010-07-27
      Bug #34283  mysqlbinlog leaves tmpfile after termination if binlog contains load data infile
      
      With statement- or mixed-mode logging, "LOAD DATA INFILE" queries
      are written to the binlog using special types of log events.
      When mysqlbinlog reads such events, it re-creates the file in a
      temporary directory with a generated filename and outputs a
      "LOAD DATA INFILE" query where the filename is replaced by the
      generated file. The temporary file is not deleted by mysqlbinlog
      after termination.
      
      To fix the problem, in mixed mode we go to row-based. In SBR, we
      document it to remind user the tmpfile is left in a temporary
      directory.
     @ mysql-test/suite/binlog/r/binlog_mixed_load_data.result
        Test result for BUG#34283.
     @ mysql-test/suite/binlog/t/binlog_mixed_load_data.test
        Added the test file to verify that 'load data infile...' statement
        will go to row-based in mixed mode.
     @ sql/sql_load.cc
        Added code to go to row-based in mixed mode for
        'load data infile ...' statement
[30 Jul 2010 4:04] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/114699

3463 Dao-Gang.Qu@sun.com	2010-07-30
      Bug #34283  mysqlbinlog leaves tmpfile after termination if binlog contains load data infile
      
      With statement- or mixed-mode logging, "LOAD DATA INFILE" queries
      are written to the binlog using special types of log events.
      When mysqlbinlog reads such events, it re-creates the file in a
      temporary directory with a generated filename and outputs a
      "LOAD DATA INFILE" query where the filename is replaced by the
      generated file. The temporary file is not deleted by mysqlbinlog
      after termination.
      
      To fix the problem, in mixed mode we go to row-based. In SBR, we
      document it to remind user the tmpfile is left in a temporary
      directory.
     @ mysql-test/suite/binlog/r/binlog_mixed_load_data.result
        Test result for BUG#34283.
     @ mysql-test/suite/binlog/t/binlog_mixed_load_data.test
        Added the test file to verify that 'load data infile...' statement
        will go to row-based in mixed mode.
     @ sql/sql_load.cc
        Added code to go to row-based in mixed mode for
        'load data infile ...' statement
[30 Jul 2010 5:21] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/114700

3153 Dao-Gang.Qu@sun.com	2010-07-30 [merge]
      Bug #34283  mysqlbinlog leaves tmpfile after termination if binlog contains load data infile
            
      With statement- or mixed-mode logging, "LOAD DATA INFILE" queries
      are written to the binlog using special types of log events.
      When mysqlbinlog reads such events, it re-creates the file in a
      temporary directory with a generated filename and outputs a
      "LOAD DATA INFILE" query where the filename is replaced by the
      generated file. The temporary file is not deleted by mysqlbinlog
      after termination.
            
      To fix the problem, in mixed mode we go to row-based. In SBR, we
      document it to remind user the tmpfile is left in a temporary
      directory.
     @ mysql-test/suite/binlog/r/binlog_mixed_load_data.result
        Test result for BUG#34283.
     @ mysql-test/suite/binlog/t/binlog_mixed_load_data.test
        Added the test file to verify that 'load data infile...' statement
        will go to row-based in mixed mode.
     @ sql/sql_load.cc
        Added code to go to row-based in mixed mode for
        'load data infile ...' statement
[30 Jul 2010 13:45] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/114743

3466 Luis Soares	2010-07-30
      Revert patch for BUG#34283. Causing lots of test failures in PB2,
      mostly because existing test result files were not updated.
[3 Aug 2010 2:27] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/114886

3470 Dao-Gang.Qu@sun.com	2010-08-03
      Bug #34283  mysqlbinlog leaves tmpfile after termination if binlog contains load data infile
      
      With statement- or mixed-mode logging, "LOAD DATA INFILE" queries
      are written to the binlog using special types of log events.
      When mysqlbinlog reads such events, it re-creates the file in a
      temporary directory with a generated filename and outputs a
      "LOAD DATA INFILE" query where the filename is replaced by the
      generated file. The temporary file is not deleted by mysqlbinlog
      after termination.
      
      To fix the problem, in mixed mode we go to row-based. In SBR, we
      document it to remind user the tmpfile is left in a temporary
      directory.
     @ mysql-test/extra/rpl_tests/rpl_loaddata.test
        Updated for Bug#34283
     @ mysql-test/suite/binlog/r/binlog_mixed_load_data.result
        Test result for BUG#34283.
     @ mysql-test/suite/binlog/t/binlog_killed_simulate.test
        Updated for Bug#34283
     @ mysql-test/suite/binlog/t/binlog_mixed_load_data.test
        Added the test file to verify that 'load data infile...' statement
        will go to row-based in mixed mode.
     @ mysql-test/suite/binlog/t/binlog_stm_blackhole.test
        Updated for Bug#34283
     @ mysql-test/suite/rpl/r/rpl_innodb_mixed_dml.result
        Updated for Bug#34283
     @ mysql-test/suite/rpl/t/rpl_loaddata_fatal.test
        Updated for Bug#34283
     @ mysql-test/suite/rpl/t/rpl_loaddata_map.test
        Updated for Bug#34283
     @ mysql-test/suite/rpl/t/rpl_slave_load_remove_tmpfile.test
        Updated for Bug#34283
     @ mysql-test/suite/rpl/t/rpl_stm_log.test
        Updated for Bug#34283
     @ sql/sql_load.cc
        Added code to go to row-based in mixed mode for
        'load data infile ...' statement
[3 Aug 2010 3:55] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/114888

3159 Dao-Gang.Qu@sun.com	2010-08-03 [merge]
      Bug #34283  mysqlbinlog leaves tmpfile after termination if binlog contains load data infile
            
      With statement- or mixed-mode logging, "LOAD DATA INFILE" queries
      are written to the binlog using special types of log events.
      When mysqlbinlog reads such events, it re-creates the file in a
      temporary directory with a generated filename and outputs a
      "LOAD DATA INFILE" query where the filename is replaced by the
      generated file. The temporary file is not deleted by mysqlbinlog
      after termination.
            
      To fix the problem, in mixed mode we go to row-based. In SBR, we
      document it to remind user the tmpfile is left in a temporary
      directory.
     @ mysql-test/suite/binlog/r/binlog_mixed_load_data.result
        Test result for BUG#34283.
     @ mysql-test/suite/binlog/t/binlog_killed_simulate.test
        Updated for BUg#34283
     @ mysql-test/suite/binlog/t/binlog_mixed_load_data.test
        Added the test file to verify that 'load data infile...' statement
        will go to row-based in mixed mode.
     @ mysql-test/suite/binlog/t/binlog_stm_blackhole.test
        Updated for BUg#34283
     @ mysql-test/suite/rpl/r/rpl_innodb_mixed_dml.result
        Updated for Bug#34283
     @ mysql-test/suite/rpl/t/rpl_loaddata.test
        Updated for Bug#34283
     @ mysql-test/suite/rpl/t/rpl_loaddata_fatal.test
        Updated for Bug#34283
     @ mysql-test/suite/rpl/t/rpl_loaddata_map.test
        Updated for Bug#34283
     @ mysql-test/suite/rpl/t/rpl_slave_load_remove_tmpfile.test
        Updated for Bug#34283
     @ mysql-test/suite/rpl/t/rpl_stm_log.test
        Updated for B
     @ mysys/stacktrace.c
        Auto merge
     @ sql/sql_lex.cc
        Auto merg
     @ sql/sql_load.cc
        Added code to go to row-based in mixed mode for
        'load data infile ...' statement
[3 Aug 2010 5:42] Daogang Qu
Pushed into mysql-5.1-bugteam and mysql-trunk-merge.
[3 Aug 2010 10:32] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/114915

3471 Dao-Gang.Qu@sun.com	2010-08-03
      Bug #34283  	mysqlbinlog leaves tmpfile after termination if binlog contains load data infile
      
      Post fix
     @ mysql-test/t/mysqlbinlog.test
        Updated for Bug#34283
[3 Aug 2010 10:41] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/114916

3160 Dao-Gang.Qu@sun.com	2010-08-03 [merge]
      Bug #34283  	mysqlbinlog leaves tmpfile after termination if binlog contains load data infile
      
      Post fix
     @ mysql-test/t/mysqlbinlog.test
        Updated for Bug#34283
[18 Aug 2010 7:20] Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100818071819-2lu46b0mm3cs34rf) (version source revid:alik@sun.com-20100818071732-g682fg1v0nnrrutx) (merge vers: 5.6.1-m4) (pib:20)
[18 Aug 2010 7:22] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100818071923-4ounwbhiium2met1) (version source revid:alik@sun.com-20100818071743-lrzordai06i2crty) (pib:20)
[18 Aug 2010 7:22] Bugs System
Pushed into mysql-5.5 5.5.6-m3 (revid:alik@sun.com-20100818071719-dktnkvt8zvidj0sy) (version source revid:alik@sun.com-20100818071719-dktnkvt8zvidj0sy) (merge vers: 5.5.6-m3) (pib:20)
[18 Aug 2010 16:35] Jon Stephens
Documented bugfix in the 5.1.50, 5.5.6, and 5.6.1 changelogs, as follows:

        The LOAD DATA INFILE statement is now considered unsafe for
        statement-based replication. When using statement-based logging
        mode, the statement now produces a warning; when using
        mixed-format logging, the statement is made using the row-based
        format.

Added a note about potential tempfile issues to 5.0+ versions of mysqlbinlog section.

Closed.
[19 Aug 2010 15:41] Bugs System
Pushed into mysql-5.1 5.1.51 (revid:build@mysql.com-20100819151858-muaaor6jojb5ouzj) (version source revid:build@mysql.com-20100819151858-muaaor6jojb5ouzj) (merge vers: 5.1.51) (pib:20)
[20 Aug 2010 14:11] Jon Stephens
Already documented in 5.1.50 changelog, setting back to Closed state.
[28 Sep 2010 8:47] Bugs System
Pushed into mysql-5.1 5.1.52 (revid:sunanda.menon@sun.com-20100928083322-wangbv97uobu7g66) (version source revid:sunanda.menon@sun.com-20100928083322-wangbv97uobu7g66) (merge vers: 5.1.52) (pib:21)
[14 Oct 2010 8:36] Bugs System
Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (version source revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (merge vers: 5.1.51-ndb-7.0.20) (pib:21)
[14 Oct 2010 8:52] Bugs System
Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (version source revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (merge vers: 5.1.51-ndb-6.3.39) (pib:21)
[14 Oct 2010 9:07] Bugs System
Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (version source revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (merge vers: 5.1.51-ndb-6.2.19) (pib:21)
[14 Oct 2010 12:24] Jon Stephens
No new changelog entry required. Setting back to Closed state.