Description:
With internal_tmp_mem_storage_engine=Temptable and tmpdir=/tmp (or /dev/shm or /dev/shm/mysql-tmp) the user session is executing PowerBI SQL with subqueries and they're getting:
"Table './tmp/#sql2730_2b2_4' doesn't exist"
/dev/shm -> 16Gb free
/tmp -> 44Gb free
/var -> 13Gb free
Total /var/lib/mysql non-mysql schema size is 8.8Gb in 22 schemas.
32 Gb ram, 10Gb swap, 4x cpus
| Variable_name | Value |
+--------------------+-------------------+
| temptable_max_mmap | 0 |
| temptable_max_ram | 1073741824 |
| temptable_use_mmap | OFF |
| timestamp | 1761832765.323254 |
| tmp_table_size | 131072000 |
| tmpdir | /dev/shm |
+--------------------+-------------------+
When I change the internal_tmp_mem_storage_engine=Memory, no errors are produced, but obviously the machine starts consuming much more RAM and Swap.
We've also tried and tested:
- grep -R "/dev/shm" /usr/lib/tmpfiles.d /etc/tmpfiles.d
- systemctl status systemd-tmpfiles-clean.timer
● systemd-tmpfiles-clean.timer - Daily Cleanup of Temporary Directories
Loaded: loaded (/usr/lib/systemd/system/systemd-tmpfiles-clean.timer; static)
Active: active (waiting) since Thu 2025-10-02 02:07:06 UTC; 3 weeks 6 days ago
Until: Thu 2025-10-02 02:07:06 UTC; 3 weeks 6 days ago
Trigger: Thu 2025-10-30 02:26:01 UTC; 17h left
Triggers: ● systemd-tmpfiles-clean.service
Docs: man:tmpfiles.d(5)
man:systemd-tmpfiles(8)
- vi /etc/tmpfiles.d/mysql-tmp.conf
d /dev/shm/mysql-tmp 0750 mysql mysql -
x /dev/shm/mysql-tmp
# systemd-tmpfiles --create
- Change tmpdir from /dev/shm to /tmp to /dev/shm/mysql_tmp.
The only change that allows us to proceed and not generate tmpfile "doesn't exist" is changing the internal temporary mem storage engine to Memory.
How to repeat:
Run queries with a range of sub-sub-queries. There are a number of views that are employed:
SELECT i.incident_id as 'Incident Id',
i.mission_id,
i.mission_name as 'Mission Name',
ic.parent AS Parent,
ic.subcategory as Subcategory,
i.last_updated,
i.mode_id as 'Submit - Medium ID',
CASE
WHEN lower(ic.parent) like 'what%' THEN '1'
WHEN lower(ic.parent) like 'who%' THEN '2'
WHEN lower(ic.parent) like 'where%' THEN '3'
ELSE ic.parent
END AS n_parent
,i.incident_date, i.incident_datedeleted from incident_vw i
left join incident_category_vw ic on i.incident_id = ic.incident_id;
where:
CREATE VIEW incident_vw AS
WITH stagingincident AS (
SELECT
i.id AS Incident_id,
(SELECT value FROM settings WHERE `key` = 'mission_id') AS mission_id,
(SELECT value FROM settings WHERE `key` = 'mission_name') AS mission_name,
i.incident_title,
i.incident_description,
i.form_id,
i.location_id,
l.location_name,
i.user_id,
i.incident_mode AS mode_id,
i.incident_date,
i.incident_dateadd AS incident_creation_date,
i.incident_datemodify AS incident_modified_date,
(YEAR(i.incident_date) * 10000 + MONTH(i.incident_date) * 100 + DAY(i.incident_date)) AS Date_id,
HOUR(i.incident_date) AS time_id,
CASE
WHEN i.incident_active = 0 THEN 0
WHEN i.incident_active = 2 THEN 0
WHEN i.incident_active = 3 THEN 0
WHEN i.incident_active = 1 AND i.incident_verified = 0 THEN 1
WHEN i.incident_active = 1 AND i.incident_verified = 1 THEN 2
ELSE NULL
END AS Status_id,
CASE
WHEN i.incident_active = 0 THEN 'Approval Pending'
WHEN i.incident_active = 1 THEN 'Approved'
WHEN i.incident_active = 2 THEN 'Draft'
WHEN i.incident_active = 3 THEN 'Not Approved'
ELSE NULL
END AS Status_Description,
i.incident_verified AS verified_id,
i.incident_active AS approved_id,
CASE
WHEN fdf.disabled_map_fields = 1 THEN NULL
ELSE l.longitude
END AS x,
CASE
WHEN fdf.disabled_map_fields = 1 THEN NULL
ELSE l.latitude
END AS y,
i.Incident_severity,
i.last_updated,
i.section_id,
i.incident_datereport,
i.catlist,
i.incident_credibility,
i.main_category_id,
i.sector_id,
i.incident_dateapproved,
i.incident_dateverified,
i.incident_datedeleted,
i.approver_name,
i.approver_email,
i.verifier_name,
i.verifier_email,
i.updated_by_name,
i.updated_by_email
FROM incident i
LEFT JOIN location l ON l.id = i.location_id
LEFT JOIN form_disabled_fields fdf ON (fdf.fdf_form_id = i.form_id)
),
DBO_Formresponse AS (
SELECT
u.id,
form_field_id,
stg.incident_id,
Form_Response,
stg.mission_id,
stg.mission_name,
u.last_updated
FROM form_response u
LEFT JOIN stagingincident stg
ON u.Incident_Id = stg.incident_id
),
IncidentData AS (
SELECT
i.Incident_id,
i.mission_id,
i.mission_name,
COALESCE(f.Form_Response, '') AS Form_Response,
FI.Id AS Form_id,
FI.Form_Title,
FI.Form_Description,
ROW_NUMBER() OVER (
PARTITION BY i.Incident_id
ORDER BY CASE WHEN NULLIF(f.Form_Response, '') IS NOT NULL THEN 1 ELSE 0 END DESC
) AS Rowid
FROM stagingincident i
LEFT JOIN form FI ON i.form_id = FI.Id
LEFT JOIN form_field FF ON FI.Id = FF.Form_id
LEFT JOIN DBO_Formresponse f ON i.Incident_id = f.Incident_Id AND f.Form_Field_Id = FF.Id
WHERE FI.Form_Title IS NOT NULL
),
FormResponsedata AS (
SELECT
Incident_id,
mission_id,
mission_name,
Form_Response,
Form_id,
Form_Title,
Form_Description
FROM IncidentData
WHERE Rowid = 1
)
SELECT
i.incident_id,
i.mission_id,
i.mission_name,
i.incident_title,
LEFT(i.incident_description, 8000) AS incident_description,
i.location_name,
i.user_id,
i.mode_id,
i.incident_date,
i.incident_creation_date,
i.incident_modified_date,
i.Date_id,
i.time_id,
i.Status_id,
i.Status_Description,
i.verified_id,
i.approved_id,
i.x,
i.y,
COALESCE(f.Form_Title, '') AS Form_Title,
COALESCE(f.Form_Description, '') AS Form_Description,
COALESCE(fr.Form_Response, '') AS Reporting_officer,
i.form_id,
i.Incident_severity,
i.last_updated,
i.location_id AS LocationID_orig,
i.section_id,
i.incident_datereport,
i.catlist,
i.incident_credibility,
i.main_category_id,
i.sector_id,
u.name AS username,
u.email AS user_email,
i.incident_dateapproved,
i.incident_dateverified,
i.incident_datedeleted,
i.approver_name,
i.approver_email,
i.verifier_name,
i.verifier_email,
i.updated_by_name,
i.updated_by_email
FROM stagingincident i
INNER JOIN form f ON i.form_id = f.id
INNER JOIN FormResponsedata fr ON i.Incident_id = fr.Incident_id
INNER JOIN users u ON i.user_id = u.id;
Suggested fix:
I can understand a tmpdir disk full but when it doesn't exist, it doesn't make sense. Unless something within MySQL that's removing the tmpfile before moving on to the next part of the query.
Description: With internal_tmp_mem_storage_engine=Temptable and tmpdir=/tmp (or /dev/shm or /dev/shm/mysql-tmp) the user session is executing PowerBI SQL with subqueries and they're getting: "Table './tmp/#sql2730_2b2_4' doesn't exist" /dev/shm -> 16Gb free /tmp -> 44Gb free /var -> 13Gb free Total /var/lib/mysql non-mysql schema size is 8.8Gb in 22 schemas. 32 Gb ram, 10Gb swap, 4x cpus | Variable_name | Value | +--------------------+-------------------+ | temptable_max_mmap | 0 | | temptable_max_ram | 1073741824 | | temptable_use_mmap | OFF | | timestamp | 1761832765.323254 | | tmp_table_size | 131072000 | | tmpdir | /dev/shm | +--------------------+-------------------+ When I change the internal_tmp_mem_storage_engine=Memory, no errors are produced, but obviously the machine starts consuming much more RAM and Swap. We've also tried and tested: - grep -R "/dev/shm" /usr/lib/tmpfiles.d /etc/tmpfiles.d - systemctl status systemd-tmpfiles-clean.timer ● systemd-tmpfiles-clean.timer - Daily Cleanup of Temporary Directories Loaded: loaded (/usr/lib/systemd/system/systemd-tmpfiles-clean.timer; static) Active: active (waiting) since Thu 2025-10-02 02:07:06 UTC; 3 weeks 6 days ago Until: Thu 2025-10-02 02:07:06 UTC; 3 weeks 6 days ago Trigger: Thu 2025-10-30 02:26:01 UTC; 17h left Triggers: ● systemd-tmpfiles-clean.service Docs: man:tmpfiles.d(5) man:systemd-tmpfiles(8) - vi /etc/tmpfiles.d/mysql-tmp.conf d /dev/shm/mysql-tmp 0750 mysql mysql - x /dev/shm/mysql-tmp # systemd-tmpfiles --create - Change tmpdir from /dev/shm to /tmp to /dev/shm/mysql_tmp. The only change that allows us to proceed and not generate tmpfile "doesn't exist" is changing the internal temporary mem storage engine to Memory. How to repeat: Run queries with a range of sub-sub-queries. There are a number of views that are employed: SELECT i.incident_id as 'Incident Id', i.mission_id, i.mission_name as 'Mission Name', ic.parent AS Parent, ic.subcategory as Subcategory, i.last_updated, i.mode_id as 'Submit - Medium ID', CASE WHEN lower(ic.parent) like 'what%' THEN '1' WHEN lower(ic.parent) like 'who%' THEN '2' WHEN lower(ic.parent) like 'where%' THEN '3' ELSE ic.parent END AS n_parent ,i.incident_date, i.incident_datedeleted from incident_vw i left join incident_category_vw ic on i.incident_id = ic.incident_id; where: CREATE VIEW incident_vw AS WITH stagingincident AS ( SELECT i.id AS Incident_id, (SELECT value FROM settings WHERE `key` = 'mission_id') AS mission_id, (SELECT value FROM settings WHERE `key` = 'mission_name') AS mission_name, i.incident_title, i.incident_description, i.form_id, i.location_id, l.location_name, i.user_id, i.incident_mode AS mode_id, i.incident_date, i.incident_dateadd AS incident_creation_date, i.incident_datemodify AS incident_modified_date, (YEAR(i.incident_date) * 10000 + MONTH(i.incident_date) * 100 + DAY(i.incident_date)) AS Date_id, HOUR(i.incident_date) AS time_id, CASE WHEN i.incident_active = 0 THEN 0 WHEN i.incident_active = 2 THEN 0 WHEN i.incident_active = 3 THEN 0 WHEN i.incident_active = 1 AND i.incident_verified = 0 THEN 1 WHEN i.incident_active = 1 AND i.incident_verified = 1 THEN 2 ELSE NULL END AS Status_id, CASE WHEN i.incident_active = 0 THEN 'Approval Pending' WHEN i.incident_active = 1 THEN 'Approved' WHEN i.incident_active = 2 THEN 'Draft' WHEN i.incident_active = 3 THEN 'Not Approved' ELSE NULL END AS Status_Description, i.incident_verified AS verified_id, i.incident_active AS approved_id, CASE WHEN fdf.disabled_map_fields = 1 THEN NULL ELSE l.longitude END AS x, CASE WHEN fdf.disabled_map_fields = 1 THEN NULL ELSE l.latitude END AS y, i.Incident_severity, i.last_updated, i.section_id, i.incident_datereport, i.catlist, i.incident_credibility, i.main_category_id, i.sector_id, i.incident_dateapproved, i.incident_dateverified, i.incident_datedeleted, i.approver_name, i.approver_email, i.verifier_name, i.verifier_email, i.updated_by_name, i.updated_by_email FROM incident i LEFT JOIN location l ON l.id = i.location_id LEFT JOIN form_disabled_fields fdf ON (fdf.fdf_form_id = i.form_id) ), DBO_Formresponse AS ( SELECT u.id, form_field_id, stg.incident_id, Form_Response, stg.mission_id, stg.mission_name, u.last_updated FROM form_response u LEFT JOIN stagingincident stg ON u.Incident_Id = stg.incident_id ), IncidentData AS ( SELECT i.Incident_id, i.mission_id, i.mission_name, COALESCE(f.Form_Response, '') AS Form_Response, FI.Id AS Form_id, FI.Form_Title, FI.Form_Description, ROW_NUMBER() OVER ( PARTITION BY i.Incident_id ORDER BY CASE WHEN NULLIF(f.Form_Response, '') IS NOT NULL THEN 1 ELSE 0 END DESC ) AS Rowid FROM stagingincident i LEFT JOIN form FI ON i.form_id = FI.Id LEFT JOIN form_field FF ON FI.Id = FF.Form_id LEFT JOIN DBO_Formresponse f ON i.Incident_id = f.Incident_Id AND f.Form_Field_Id = FF.Id WHERE FI.Form_Title IS NOT NULL ), FormResponsedata AS ( SELECT Incident_id, mission_id, mission_name, Form_Response, Form_id, Form_Title, Form_Description FROM IncidentData WHERE Rowid = 1 ) SELECT i.incident_id, i.mission_id, i.mission_name, i.incident_title, LEFT(i.incident_description, 8000) AS incident_description, i.location_name, i.user_id, i.mode_id, i.incident_date, i.incident_creation_date, i.incident_modified_date, i.Date_id, i.time_id, i.Status_id, i.Status_Description, i.verified_id, i.approved_id, i.x, i.y, COALESCE(f.Form_Title, '') AS Form_Title, COALESCE(f.Form_Description, '') AS Form_Description, COALESCE(fr.Form_Response, '') AS Reporting_officer, i.form_id, i.Incident_severity, i.last_updated, i.location_id AS LocationID_orig, i.section_id, i.incident_datereport, i.catlist, i.incident_credibility, i.main_category_id, i.sector_id, u.name AS username, u.email AS user_email, i.incident_dateapproved, i.incident_dateverified, i.incident_datedeleted, i.approver_name, i.approver_email, i.verifier_name, i.verifier_email, i.updated_by_name, i.updated_by_email FROM stagingincident i INNER JOIN form f ON i.form_id = f.id INNER JOIN FormResponsedata fr ON i.Incident_id = fr.Incident_id INNER JOIN users u ON i.user_id = u.id; Suggested fix: I can understand a tmpdir disk full but when it doesn't exist, it doesn't make sense. Unless something within MySQL that's removing the tmpfile before moving on to the next part of the query.