Temporary files created by binary log cache are not purged after transaction commit

[Alexey Kopytov]

When you modify lot of data inside a transaction, for example a big LOAD DATA INFILE, and the size overcomes the binary log cache it will create temporary files in the tmp partition. Those files don't disappear until you close the connection. Therefore, if you are running some kind of persistent connection technique you can run out of disk space on /tmp partition.

Those files are always in "deleted" status, so they're not visible with a simple "ls" on the partition:

# lsof -p 6112|grep ML
mysqld 6112 root 38u REG 7,0 106594304 18 /tmp/MLjw4ecJ (deleted)
mysqld 6112 root 39u REG 7,0 237314310 17 /tmp/MLwdWDGW (deleted)
mysqld 6112 root 41u REG 7,0 107872256 19 /tmp/MLOqxGox (deleted)
mysqld 6112 root 44u REG 7,0 90832896 20 /tmp/MLr05Ga9 (deleted)
mysqld 6112 root 47u REG 7,0 79855616 21 /tmp/ML83dV2t (deleted)
mysqld 6112 root 50u REG 7,0 65077248 22 /tmp/ML5M7KqM (deleted)
mysqld 6112 root 53u REG 7,0 64094208 23 /tmp/MLSfMSMh (deleted)
mysqld 6112 root 56u REG 7,0 62259200 24 /tmp/MLnAcfOY (deleted)
mysqld 6112 root 59u REG 7,0 62521344 25 /tmp/ML4VPBxK (deleted)
mysqld 6112 root 62u REG 7,0 62390272 26 /tmp/ML19sFCA (deleted)

How to repeat:
1- Master-Slave replication with row based binary log.
2- Create a /tmp partition, for example on tmpfs.
3- Run several LOAD DATA INFILE with persistent connection (or just don't close the session).
4- Check the size of the temporary files with lsof.
5- Finally, you'll fill up the tmp partition.

[Raghavendra D Prabhu (raghavendra-prabhu)]

Also, discussed in the blog post and in the comments: http://www.mysqlperformanceblog.com/2012/08/06/filling-the-tmp-partition-with-persistent-connections/comment-page-1/#comment-995102

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-599