MySQL Bugs: #34283: mysqlbinlog leaves tmpfile after termination if binlog contains load data infile

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	CPU Architecture:	Any
Tags:	LOAD DATA INFILE, mysqlbinlog, temporary file

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).

Thank you for the report.

Verified as described.

See also bug #12834 and bug #1073

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.

Bug #39701 was marked as duplicate of this one.

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.)

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

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

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

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

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

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

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.

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

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

Pushed into mysql-5.1-bugteam and mysql-trunk-merge.

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

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

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)

Pushed into mysql-next-mr (revid:alik@sun.com-20100818071923-4ounwbhiium2met1) (version source revid:alik@sun.com-20100818071743-lrzordai06i2crty) (pib:20)

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)

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.

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)

Already documented in 5.1.50 changelog, setting back to Closed state.

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)

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)

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)

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)

No new changelog entry required. Setting back to Closed state.