[MDEV-17420] MariaDB slave 10.2 leaks temporary tables - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Won't Fix
Affects Version/s: 10.2.17, 10.2.18
Fix Version/s: N/A
Component/s: Replication, Server
Labels:
- leak
- myisam
- replication
- slave
- stale
- temporary
Environment:
Centos 7.4

Description

This was happening with 10.2.17 on the slaves. We have upgraded to 10.2.18 and the behavior is the same.

The Slave_open_temp_tables variable counter goes up and is consistent with the number of #sql*.MYD files on the tmpdir.

mainro [(none)]> SHOW STATUS LIKE 'Slave_open_temp_tables';

+------------------------+-------+

| Variable_name          | Value |

+------------------------+-------+

| Slave_open_temp_tables | 3665  |

+------------------------+-------+

1 row in set (0.00 sec)

# cd /var/db/tmp ; ls -l *.MYD | wc -l

The server does not delete the tmp files from different slave sql threads execution. For example:

-rw-rw---- 1 mysql mysql      0 Oct  9 16:46 #sql4bb4_4809_ff2c.MYD

-rw-rw---- 1 mysql mysql   1604 Oct  9 16:46 #sql4bb4_4809_ff42.MYD

-rw-rw---- 1 mysql mysql     84 Oct  9 16:46 #sql4bb4_4809_ff43.MYD

-rw-rw---- 1 mysql mysql      0 Oct  9 16:46 #sql4bb4_4809_ff45.MYD

-rw-rw---- 1 mysql mysql   2912 Oct  9 16:46 #sql4bb4_4809_ff5b.MYD

-rw-rw---- 1 mysql mysql     84 Oct  9 16:46 #sql4bb4_4809_ff5c.MYD

-rw-rw---- 1 mysql mysql      0 Oct  9 16:46 #sql4bb4_4809_ff5e.MYD

-rw-rw---- 1 mysql mysql   5928 Oct  9 16:46 #sql4bb4_4809_ffb1.MYD

-rw-rw---- 1 mysql mysql     84 Oct  9 16:46 #sql4bb4_4809_ffb2.MYD

-rw-rw---- 1 mysql mysql      0 Oct  9 16:46 #sql4bb4_4809_ffb4.MYD

-rw-rw---- 1 mysql mysql      0 Oct  8 17:34 #sql4bb4_77_10488.MYD

-rw-rw---- 1 mysql mysql      0 Oct  8 17:34 #sql4bb4_77_10489.MYD

-rw-rw---- 1 mysql mysql     24 Oct  8 17:36 #sql4bb4_77_10866.MYD

-rw-rw---- 1 mysql mysql      0 Oct  8 17:37 #sql4bb4_77_10a73.MYD

-rw-rw---- 1 mysql mysql    468 Oct  8 14:07 #sql4bb4_77_10.MYD

-rw-rw---- 1 mysql mysql      0 Oct  8 17:46 #sql4bb4_77_11ade.MYD

-rw-rw---- 1 mysql mysql   1468 Oct  8 17:46 #sql4bb4_77_11b1e.MYD

-rw-rw---- 1 mysql mysql     84 Oct  8 17:46 #sql4bb4_77_11b1f.MYD

-rw-rw---- 1 mysql mysql      0 Oct  8 17:46 #sql4bb4_77_11b21.MYD

-rw-rw---- 1 mysql mysql   1352 Oct  8 17:46 #sql4bb4_77_11b35.MYD

-rw-rw---- 1 mysql mysql     84 Oct  8 17:46 #sql4bb4_77_11b36.MYD

After stopping the server, also does not remove the files. We have to remove the files manually.

We have tried changing the variable default_tmp_storage_engine from MyISAM to InnoDB and the counter Slave_open_temp_tables also keeps growing but, of course, we don't see any temp files on tmpdir, but we believe that eventually something will broke inside InnoDB.

We have been analyzing the binary log and it appears that all explicit create temporary tables are being dropped, either manually by the application, or automatically by the master after session close. Could these stale temporary tables be internal temporary tables that the server is using to resolve some difficult queries?

Attachments

Issue Links

is blocked by

MDEV-17490 MariaDB 10.3 slave fails to replicate from a MariaDB 10.2 master

Closed

relates to

MDEV-35860 use O_TMPFILE for (implicit) temporary Aria/MyISAM tables

Open

Activity

Ascending order - Click to sort in descending order

Gabriel Gomiz created issue - 2018-10-10 14:54

Gabriel Gomiz made changes - 2018-10-10 14:55

Field	Original Value	New Value
Description	We have 4 slaves using MariaDB 10.2 doing replication from a 10.2 master. Binary log format is MIXED and temporary tables are using MyISAM format. The number of temporary tables on the slave is growing continuously up to the point of exhausting the file descriptor limit of the operating system assigned to the process. After that the only solution is stopping the slave server and starting again. This was happening with 10.2.17 on the slaves. We have upgraded to 10.2.18 and the behavior is the same. The Slave_open_temp_tables variable counter goes up and is consistent with the number of #sql.MYD files on the tmpdir. {{mainro [(none)]> SHOW STATUS LIKE 'Slave_open_temp_tables'; +------------------------+-------+ \| Variable_name \| Value \| +------------------------+-------+ \| Slave_open_temp_tables \| 3665 \| +------------------------+-------+ 1 row in set (0.00 sec)}} {{# cd /var/db/tmp ; ls -l .MYD \| wc -l 3665}} The server does not delete the tmp files from different slave sql threads execution. For example: {{ -rw-rw---- 1 mysql mysql 0 Oct 9 16:46 #sql4bb4_4809_ff2c.MYD -rw-rw---- 1 mysql mysql 1604 Oct 9 16:46 #sql4bb4_4809_ff42.MYD -rw-rw---- 1 mysql mysql 84 Oct 9 16:46 #sql4bb4_4809_ff43.MYD -rw-rw---- 1 mysql mysql 0 Oct 9 16:46 #sql4bb4_4809_ff45.MYD -rw-rw---- 1 mysql mysql 2912 Oct 9 16:46 #sql4bb4_4809_ff5b.MYD -rw-rw---- 1 mysql mysql 84 Oct 9 16:46 #sql4bb4_4809_ff5c.MYD -rw-rw---- 1 mysql mysql 0 Oct 9 16:46 #sql4bb4_4809_ff5e.MYD -rw-rw---- 1 mysql mysql 5928 Oct 9 16:46 #sql4bb4_4809_ffb1.MYD -rw-rw---- 1 mysql mysql 84 Oct 9 16:46 #sql4bb4_4809_ffb2.MYD -rw-rw---- 1 mysql mysql 0 Oct 9 16:46 #sql4bb4_4809_ffb4.MYD -rw-rw---- 1 mysql mysql 0 Oct 8 17:34 #sql4bb4_77_10488.MYD -rw-rw---- 1 mysql mysql 0 Oct 8 17:34 #sql4bb4_77_10489.MYD -rw-rw---- 1 mysql mysql 24 Oct 8 17:36 #sql4bb4_77_10866.MYD -rw-rw---- 1 mysql mysql 0 Oct 8 17:37 #sql4bb4_77_10a73.MYD -rw-rw---- 1 mysql mysql 468 Oct 8 14:07 #sql4bb4_77_10.MYD -rw-rw---- 1 mysql mysql 0 Oct 8 17:46 #sql4bb4_77_11ade.MYD -rw-rw---- 1 mysql mysql 1468 Oct 8 17:46 #sql4bb4_77_11b1e.MYD -rw-rw---- 1 mysql mysql 84 Oct 8 17:46 #sql4bb4_77_11b1f.MYD -rw-rw---- 1 mysql mysql 0 Oct 8 17:46 #sql4bb4_77_11b21.MYD -rw-rw---- 1 mysql mysql 1352 Oct 8 17:46 #sql4bb4_77_11b35.MYD -rw-rw---- 1 mysql mysql 84 Oct 8 17:46 #sql4bb4_77_11b36.MYD }} After stopping the server, also does not remove the files. We have to remove the files manually. We have tried changing the variable default_tmp_storage_engine from MyISAM to InnoDB and the counter Slave_open_temp_tables also keeps growing but, of course, we don't see any temp files on tmpdir, but we believe that eventually something will broke inside InnoDB. We have been analyzing the binary log and it appears that all explicit create temporary tables are being dropped, either manually by the application, or automatically by the master after session close. Could these stale temporary tables be internal temporary tables that the server is using to resolve some difficult queries?	We have 4 slaves using MariaDB 10.2 doing replication from a 10.2 master. Binary log format is MIXED and temporary tables are using MyISAM format. The number of temporary tables on the slave is growing continuously up to the point of exhausting the file descriptor limit of the operating system assigned to the process. After that the only solution is stopping the slave server and starting again. This was happening with 10.2.17 on the slaves. We have upgraded to 10.2.18 and the behavior is the same. The Slave_open_temp_tables variable counter goes up and is consistent with the number of #sql.MYD files on the tmpdir. {{mainro [(none)]> SHOW STATUS LIKE 'Slave_open_temp_tables'; +------------------------+-------+ \| Variable_name \| Value \| +------------------------+-------+ \| Slave_open_temp_tables \| 3665 \| +------------------------+-------+ 1 row in set (0.00 sec)}} {{# cd /var/db/tmp ; ls -l .MYD \| wc -l 3665}} The server does not delete the tmp files from different slave sql threads execution. For example: {{-rw-rw---- 1 mysql mysql 0 Oct 9 16:46 #sql4bb4_4809_ff2c.MYD -rw-rw---- 1 mysql mysql 1604 Oct 9 16:46 #sql4bb4_4809_ff42.MYD -rw-rw---- 1 mysql mysql 84 Oct 9 16:46 #sql4bb4_4809_ff43.MYD -rw-rw---- 1 mysql mysql 0 Oct 9 16:46 #sql4bb4_4809_ff45.MYD -rw-rw---- 1 mysql mysql 2912 Oct 9 16:46 #sql4bb4_4809_ff5b.MYD -rw-rw---- 1 mysql mysql 84 Oct 9 16:46 #sql4bb4_4809_ff5c.MYD -rw-rw---- 1 mysql mysql 0 Oct 9 16:46 #sql4bb4_4809_ff5e.MYD -rw-rw---- 1 mysql mysql 5928 Oct 9 16:46 #sql4bb4_4809_ffb1.MYD -rw-rw---- 1 mysql mysql 84 Oct 9 16:46 #sql4bb4_4809_ffb2.MYD -rw-rw---- 1 mysql mysql 0 Oct 9 16:46 #sql4bb4_4809_ffb4.MYD -rw-rw---- 1 mysql mysql 0 Oct 8 17:34 #sql4bb4_77_10488.MYD -rw-rw---- 1 mysql mysql 0 Oct 8 17:34 #sql4bb4_77_10489.MYD -rw-rw---- 1 mysql mysql 24 Oct 8 17:36 #sql4bb4_77_10866.MYD -rw-rw---- 1 mysql mysql 0 Oct 8 17:37 #sql4bb4_77_10a73.MYD -rw-rw---- 1 mysql mysql 468 Oct 8 14:07 #sql4bb4_77_10.MYD -rw-rw---- 1 mysql mysql 0 Oct 8 17:46 #sql4bb4_77_11ade.MYD -rw-rw---- 1 mysql mysql 1468 Oct 8 17:46 #sql4bb4_77_11b1e.MYD -rw-rw---- 1 mysql mysql 84 Oct 8 17:46 #sql4bb4_77_11b1f.MYD -rw-rw---- 1 mysql mysql 0 Oct 8 17:46 #sql4bb4_77_11b21.MYD -rw-rw---- 1 mysql mysql 1352 Oct 8 17:46 #sql4bb4_77_11b35.MYD -rw-rw---- 1 mysql mysql 84 Oct 8 17:46 #sql4bb4_77_11b36.MYD}} After stopping the server, also does not remove the files. We have to remove the files manually. We have tried changing the variable default_tmp_storage_engine from MyISAM to InnoDB and the counter Slave_open_temp_tables also keeps growing but, of course, we don't see any temp files on tmpdir, but we believe that eventually something will broke inside InnoDB. We have been analyzing the binary log and it appears that all explicit create temporary tables are being dropped, either manually by the application, or automatically by the master after session close. Could these stale temporary tables be internal temporary tables that the server is using to resolve some difficult queries?

Daniel Black made changes - 2018-10-17 22:25

Description

We have 4 slaves using MariaDB 10.2 doing replication from a 10.2 master. Binary log format is MIXED and temporary tables are using MyISAM format. The number of temporary tables on the slave is growing continuously up to the point of exhausting the file descriptor limit of the operating system assigned to the process. After that the only solution is stopping the slave server and starting again.

This was happening with 10.2.17 on the slaves. We have upgraded to 10.2.18 and the behavior is the same.

The Slave_open_temp_tables variable counter goes up and is consistent with the number of #sql*.MYD files on the tmpdir.

{{mainro [(none)]> SHOW STATUS LIKE 'Slave_open_temp_tables';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| Slave_open_temp_tables | 3665 |
+------------------------+-------+
1 row in set (0.00 sec)}}

{{# cd /var/db/tmp ; ls -l *.MYD | wc -l
3665}}

The server does not delete the tmp files from different slave sql threads execution. For example:

{{-rw-rw---- 1 mysql mysql 0 Oct 9 16:46 #sql4bb4_4809_ff2c.MYD
-rw-rw---- 1 mysql mysql 1604 Oct 9 16:46 #sql4bb4_4809_ff42.MYD
-rw-rw---- 1 mysql mysql 84 Oct 9 16:46 #sql4bb4_4809_ff43.MYD
-rw-rw---- 1 mysql mysql 0 Oct 9 16:46 #sql4bb4_4809_ff45.MYD
-rw-rw---- 1 mysql mysql 2912 Oct 9 16:46 #sql4bb4_4809_ff5b.MYD
-rw-rw---- 1 mysql mysql 84 Oct 9 16:46 #sql4bb4_4809_ff5c.MYD
-rw-rw---- 1 mysql mysql 0 Oct 9 16:46 #sql4bb4_4809_ff5e.MYD
-rw-rw---- 1 mysql mysql 5928 Oct 9 16:46 #sql4bb4_4809_ffb1.MYD
-rw-rw---- 1 mysql mysql 84 Oct 9 16:46 #sql4bb4_4809_ffb2.MYD
-rw-rw---- 1 mysql mysql 0 Oct 9 16:46 #sql4bb4_4809_ffb4.MYD
-rw-rw---- 1 mysql mysql 0 Oct 8 17:34 #sql4bb4_77_10488.MYD
-rw-rw---- 1 mysql mysql 0 Oct 8 17:34 #sql4bb4_77_10489.MYD
-rw-rw---- 1 mysql mysql 24 Oct 8 17:36 #sql4bb4_77_10866.MYD
-rw-rw---- 1 mysql mysql 0 Oct 8 17:37 #sql4bb4_77_10a73.MYD
-rw-rw---- 1 mysql mysql 468 Oct 8 14:07 #sql4bb4_77_10.MYD
-rw-rw---- 1 mysql mysql 0 Oct 8 17:46 #sql4bb4_77_11ade.MYD
-rw-rw---- 1 mysql mysql 1468 Oct 8 17:46 #sql4bb4_77_11b1e.MYD
-rw-rw---- 1 mysql mysql 84 Oct 8 17:46 #sql4bb4_77_11b1f.MYD
-rw-rw---- 1 mysql mysql 0 Oct 8 17:46 #sql4bb4_77_11b21.MYD
-rw-rw---- 1 mysql mysql 1352 Oct 8 17:46 #sql4bb4_77_11b35.MYD
-rw-rw---- 1 mysql mysql 84 Oct 8 17:46 #sql4bb4_77_11b36.MYD}}

After stopping the server, also does not remove the files. We have to remove the files manually.

We have tried changing the variable default_tmp_storage_engine from MyISAM to InnoDB and the counter Slave_open_temp_tables also keeps growing but, of course, we don't see any temp files on tmpdir, but we believe that eventually something will broke inside InnoDB.

We have been analyzing the binary log and it appears that all explicit create temporary tables are being dropped, either manually by the application, or automatically by the master after session close. Could these stale temporary tables be internal temporary tables that the server is using to resolve some difficult queries?

We have 4 slaves using MariaDB 10.2 doing replication from a 10.2 master. Binary log format is MIXED and temporary tables are using MyISAM format. The number of temporary tables on the slave is growing continuously up to the point of exhausting the file descriptor limit of the operating system assigned to the process. After that the only solution is stopping the slave server and starting again.

This was happening with 10.2.17 on the slaves. We have upgraded to 10.2.18 and the behavior is the same.

The Slave_open_temp_tables variable counter goes up and is consistent with the number of #sql*.MYD files on the tmpdir.

{noformat}mainro [(none)]> SHOW STATUS LIKE 'Slave_open_temp_tables';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| Slave_open_temp_tables | 3665 |
+------------------------+-------+
1 row in set (0.00 sec){noformat}

{noformat}# cd /var/db/tmp ; ls -l *.MYD | wc -l
3665{noformat}

The server does not delete the tmp files from different slave sql threads execution. For example:

{noformat}-rw-rw---- 1 mysql mysql 0 Oct 9 16:46 #sql4bb4_4809_ff2c.MYD
-rw-rw---- 1 mysql mysql 1604 Oct 9 16:46 #sql4bb4_4809_ff42.MYD
-rw-rw---- 1 mysql mysql 84 Oct 9 16:46 #sql4bb4_4809_ff43.MYD
-rw-rw---- 1 mysql mysql 0 Oct 9 16:46 #sql4bb4_4809_ff45.MYD
-rw-rw---- 1 mysql mysql 2912 Oct 9 16:46 #sql4bb4_4809_ff5b.MYD
-rw-rw---- 1 mysql mysql 84 Oct 9 16:46 #sql4bb4_4809_ff5c.MYD
-rw-rw---- 1 mysql mysql 0 Oct 9 16:46 #sql4bb4_4809_ff5e.MYD
-rw-rw---- 1 mysql mysql 5928 Oct 9 16:46 #sql4bb4_4809_ffb1.MYD
-rw-rw---- 1 mysql mysql 84 Oct 9 16:46 #sql4bb4_4809_ffb2.MYD
-rw-rw---- 1 mysql mysql 0 Oct 9 16:46 #sql4bb4_4809_ffb4.MYD
-rw-rw---- 1 mysql mysql 0 Oct 8 17:34 #sql4bb4_77_10488.MYD
-rw-rw---- 1 mysql mysql 0 Oct 8 17:34 #sql4bb4_77_10489.MYD
-rw-rw---- 1 mysql mysql 24 Oct 8 17:36 #sql4bb4_77_10866.MYD
-rw-rw---- 1 mysql mysql 0 Oct 8 17:37 #sql4bb4_77_10a73.MYD
-rw-rw---- 1 mysql mysql 468 Oct 8 14:07 #sql4bb4_77_10.MYD
-rw-rw---- 1 mysql mysql 0 Oct 8 17:46 #sql4bb4_77_11ade.MYD
-rw-rw---- 1 mysql mysql 1468 Oct 8 17:46 #sql4bb4_77_11b1e.MYD
-rw-rw---- 1 mysql mysql 84 Oct 8 17:46 #sql4bb4_77_11b1f.MYD
-rw-rw---- 1 mysql mysql 0 Oct 8 17:46 #sql4bb4_77_11b21.MYD
-rw-rw---- 1 mysql mysql 1352 Oct 8 17:46 #sql4bb4_77_11b35.MYD
-rw-rw---- 1 mysql mysql 84 Oct 8 17:46 #sql4bb4_77_11b36.MYD{noformat}

After stopping the server, also does not remove the files. We have to remove the files manually.

We have tried changing the variable default_tmp_storage_engine from MyISAM to InnoDB and the counter Slave_open_temp_tables also keeps growing but, of course, we don't see any temp files on tmpdir, but we believe that eventually something will broke inside InnoDB.

We have been analyzing the binary log and it appears that all explicit create temporary tables are being dropped, either manually by the application, or automatically by the master after session close. Could these stale temporary tables be internal temporary tables that the server is using to resolve some difficult queries?

Gabriel Gomiz made changes - 2018-10-18 12:59

Link

This issue is blocked by ~~MDEV-17490~~ [ ~~MDEV-17490~~ ]

Elena Stepanova made changes - 2018-10-25 12:29

Component/s

Replication [ 10100 ]

Elena Stepanova made changes - 2018-10-25 12:30

Assignee

Andrei Elkin [ elkin ]

Elena Stepanova made changes - 2018-11-28 01:04

Fix Version/s

10.2 [ 14601 ]

Sergei Golubchik made changes - 2021-12-06 21:33

Workflow

MariaDB v3 [ 90039 ]

MariaDB v4 [ 140971 ]

Jira Automation (IT) made changes - 2024-07-04 04:28

Zendesk Related Tickets

170302

Daniel Black made changes - 2025-01-16 07:35

Link

This issue relates to MDEV-35860 [ MDEV-35860 ]

Julien Fritsch made changes - 2025-02-05 16:26

Priority

Major [ 3 ]

Critical [ 2 ]

Sergei Golubchik made changes - 2025-02-05 17:47

Priority

Critical [ 2 ]

Major [ 3 ]

Sergei Golubchik made changes - 2025-02-05 17:49

Assignee

Andrei Elkin [ elkin ]

Susil Behera [ JIRAUSER40751 ]

Sergei Golubchik made changes - 2025-02-05 17:49

Fix Version/s

10.2(EOL) [ 14601 ]

Sergei Golubchik made changes - 2025-02-05 17:50

Fix Version/s		10.5.29 [ 29996 ]
Fix Version/s		10.5 [ 23123 ]

Sergei Golubchik made changes - 2025-02-05 17:50

Fix Version/s		10.6 [ 24028 ]
Fix Version/s	10.5.29 [ 29996 ]

Susil Behera made changes - 2025-02-11 15:34

issue.field.resolutiondate

2025-02-11 15:34:54.0

2025-02-11 15:34:53.789

Susil Behera made changes - 2025-02-11 15:34

Fix Version/s		N/A [ 14700 ]
Fix Version/s	10.5 [ 23123 ]
Fix Version/s	10.6 [ 24028 ]
Resolution		Won't Fix [ 2 ]
Status	Open [ 1 ]	Closed [ 6 ]

People

Assignee:: Susil Behera

Reporter:: Gabriel Gomiz

Votes:: 1 Vote for this issue

Watchers:: 9 Start watching this issue

Dates

Created:: 2018-10-10 14:54

Updated:: 2025-02-11 15:34

Resolved:: 2025-02-11 15:34

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration