0

During our nightly backup I saw that two files vanished while the backup was being performed:

file has vanished: "/tmp/#sql-temptable-3ab-aca6d-316f6.MAD"
file has vanished: "/tmp/#sql-temptable-3ab-aca6d-316f6.MAI"

This raised my suspicion of why these two files were there to begin with! On my search through the web then it seems that these .MAD and .MAI files are only created when a ALTER or CREATE query is performed.

Is that true? Or can other queries (like some optimization search, join or others like that) create those files also? Would temporary files create from other queries then ALTER/CREATE have the same file endings?

My suspicion is raised because the backup is run during night when no one is working on the database, so no one will be running a ALTER/CREATE query at that time and the mysqldump does not run those queries.

2
  • Note rsync isn't a reliable backup mechanism while MariaDB is running. Recommend changing to a reliable mechanism before you need a restore.
    – danblack
    May 9, 2023 at 0:26
  • Just for clarification, we don't rely on rsync backup of the live database. We do a mysqldump before rsync, then rsync the dump.
    – proxymoxy
    May 10, 2023 at 11:03

1 Answer 1

0

No, MAD and MAI temporary files can be created for a number of reasons, including normal SELECT queries that for whatever reason need on-disk temporary tables. Implicit temporary tables are dropped immediately at the end of the query whereas explicit temporary tables are dropped at the end of the session.

Sufficiently small temporary tables are created just in memory whereas disk is used otherwise.

The MAD and MAI file suffixes are ones used by the Aria storage engine which is the default choice for on-disk temporary tables these days. (It was MyISAM previously.)

See MariaDB Knowledgebase - Aria system variables: aria_used_for_temp_tables

Aria table information is stored in 2 files: the .MAI file contains base table information and the index and the .MAD file contains the data.

Source: MariaDB Knowledgebase: aria_chk

1
  • This looks plausible, will mark it as answer.
    – proxymoxy
    May 8, 2023 at 13:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.