MySQL Bugs: #41969: With 5.1.30 & Mixed replication, mysql doesn't seem to delete temp files

Bug #41969	With 5.1.30 & Mixed replication, mysql doesn't seem to delete temp files
Submitted:	8 Jan 2009 15:20	Modified:	12 Jan 2009 10:33
Reporter:	jocelyn fournier (Silver Quality Contributor)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S1 (Critical)
Version:	5.1.30	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
Hi,

I've just figured out since the switch of our masters and slaves to 5.1.30 (x86-64) we have issues with temp files not dropped from the slaves file system, and the file descriptors remain used. (same issue on our two slaves)

Master doesn't have any issue.

The slave are configured in STATEMENT base replication.
binlog_cache_size : 32768
table_open_cache : 16384

Example on a slave system :

Server version: 5.1.30-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show full processlist;
+--------+-------------+-----------+------+---------+---------+----------------------------------+-----------------------+
| Id     | User        | Host      | db   | Command | Time    | State                            | Info                  |
+--------+-------------+-----------+------+---------+---------+----------------------------------+-----------------------+
|      1 | system user |           | NULL | Connect | 3646105 | Waiting for master to send event | NULL                  |
| 887945 | root        | localhost | NULL | Query   |       0 | NULL                             | show full processlist |
+--------+-------------+-----------+------+---------+---------+----------------------------------+-----------------------+
2 rows in set (0.00 sec)

cd /tmp ; ls | wc -l
48994

ls

[...]
#sql11d8_2_13b8.frm  #sql11d8_2_255.MYI   #sql11d8_2_3642.MYD  #sql11d8_2_4986.frm  #sql11d8_2_5723.MYI  #sql11d8_2_648d.MYD  #sql11d8_2_70ee.frm  #sql11d8_2_7e7a.MYI  #sql11d8_2_8e2.MYD
#sql11d8_2_13b8.MYD  #sql11d8_2_2560.frm  #sql11d8_2_3642.MYI  #sql11d8_2_4986.MYD  #sql11d8_2_5725.frm  #sql11d8_2_648d.MYI  #sql11d8_2_70ee.MYD  #sql11d8_2_7e7b.frm  #sql11d8_2_8e2.MYI
#sql11d8_2_13b8.MYI  #sql11d8_2_2560.MYD  #sql11d8_2_3647.frm  #sql11d8_2_4986.MYI  #sql11d8_2_5725.MYD  #sql11d8_2_648f.frm  #sql11d8_2_70ee.MYI  #sql11d8_2_7e7b.MYD  #sql11d8_2_8e3f.frm
#sql11d8_2_13b9.frm  #sql11d8_2_2560.MYI  #sql11d8_2_3647.MYD  #sql11d8_2_4987.frm  #sql11d8_2_5725.MYI  #sql11d8_2_648f.MYD  #sql11d8_2_70ef.frm  #sql11d8_2_7e7b.MYI  #sql11d8_2_8e3f.MYD
#sql11d8_2_13b9.MYD  #sql11d8_2_2561.frm  #sql11d8_2_3647.MYI  #sql11d8_2_4987.MYD  #sql11d8_2_5726.frm  #sql11d8_2_648f.MYI  #sql11d8_2_70ef.MYD  #sql11d8_2_7e7c.frm  #sql11d8_2_8e3f.MYI
#sql11d8_2_13b9.MYI  #sql11d8_2_2561.MYD  #sql11d8_2_364a.frm  #sql11d8_2_4987.MYI  #sql11d8_2_5726.MYD  #sql11d8_2_648.frm   #sql11d8_2_70ef.MYI  #sql11d8_2_7e7c.MYD  #sql11d8_2_8e3.frm
[...]

lsof 

[...]
mysqld     4568    root 3188u      REG                8,2        572      45562 /tmp/#sql11d8_2_9b0.MYD
mysqld     4568    root 3189u      REG                8,2       1024      45564 /tmp/#sql11d8_2_9b1.MYI
mysqld     4568    root 3190u      REG                8,2        572      45565 /tmp/#sql11d8_2_9b1.MYD
mysqld     4568    root 3191u      REG                8,2       1024      45567 /tmp/#sql11d8_2_9b2.MYI
mysqld     4568    root 3192u      REG                8,2        284      45568 /tmp/#sql11d8_2_9b2.MYD
mysqld     4568    root 3193u      REG                8,2       1024      45570 /tmp/#sql11d8_2_9b3.MYI
mysqld     4568    root 3194u      REG                8,2        284      45571 /tmp/#sql11d8_2_9b3.MYD
mysqld     4568    root 3195u      REG                8,2       1024      45573 /tmp/#sql11d8_2_9b4.MYI
mysqld     4568    root 3196u      REG                8,2        284      45574 /tmp/#sql11d8_2_9b4.MYD
mysqld     4568    root 3197u      REG                8,2       1024      45576 /tmp/#sql11d8_2_9b6.MYI
[...]

lsof | grep /tmp/#sql | wc -l
32662

Any idea of what could be wrong here ?

Thanks and regards,
  Jocelyn Fournier

How to repeat:
Run replication with query creating temp table / temp files, and check the file descriptor are not released.

Hi,

A simple way to reproduce the bug :

On the master, execute the following :

use test;
CREATE TEMPORARY TABLE t1 SELECT 1;
INSERT INTO t1 SELECT 1 LIMIT 1;
DROP TEMPORARY TABLE t1;

With this sequence, the temp file will not be deleted on the slaves.

Note if you try to execute those queries on the slave, you will have a warning for the insert :

mysql> INSERT INTO t1 SELECT 1 LIMIT 1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------------+
| Level   | Code | Message                                           |
+---------+------+---------------------------------------------------+
| Warning | 1592 | Statement is not safe to log in statement format. |
+---------+------+---------------------------------------------------+
1 row in set (0.00 sec)

If you remove the LIMIT 1, the warning disappears, as well as the temp file bug issue.

Regards,
  Jocelyn Fournier

Something is wrong in my first message : the master is configured in MIXED replication mode.

I confirmed the problem is not reproducable in STATEMENT binlog mode, only in MIXED binlog mode.

It seems to be the same issue than http://bugs.mysql.com/bug.php?id=40013

I agree with you looks duplicate of  http://bugs.mysql.com/bug.php?id=40013. So you agree to mark this bug as duplicate of that one?. Thanks in advance.

Actually what's not clear for me is why INSERT INTO t1 SELECT 1 LIMIT 1; is considered as not safe to log in statement format and hence why MySQL is switching in ROW binlog format ?

Note that even if an ORDER BY is added in this kind of INSERT ... SELECT query, and hence is deterministic, it still considered as not statement safe.

Jocelyn,

LIMIT is non deterministic.

Anyway, that has nothing to do with the original report.

I will set this as duplicate of bug #40013 now.

Please, feel free to reopen this bug report, when you disagree that this is a duplicate.