core: Database migration failed due to low disk space when updating from 2021.12 to 2022.2

The problem

Hi

Upgrade of the recorder database failed on this query with error: (MySQLdb._exceptions.InternalError) (3, ‘Error writing file '/tmp/MYa8ZPri' (Errcode: 28 “No space left on device”)’)

[SQL: SELECT statistics_short_term.id AS statistics_short_term_id, statistics_short_term.created AS statistics_short_term_created, statistics_short_term.start AS statistics_short_term_start, statistics_short_term.mean AS statistics_short_term_mean, statistics_short_term.min AS statistics_short_term_min, statistics_short_term.max AS statistics_short_term_max, statistics_short_term.last_reset AS statistics_short_term_last_reset, statistics_short_term.state AS statistics_short_term_state, statistics_short_term.sum AS statistics_short_term_sum, statistics_short_term.metadata_id AS statistics_short_term_metadata_id FROM statistics_short_term LEFT OUTER JOIN (SELECT statistics_short_term.start AS start, statistics_short_term.metadata_id AS metadata_id, 1 AS is_duplicate FROM statistics_short_term GROUP BY statistics_short_term.metadata_id, statistics_short_term.start HAVING count(*) > %s) AS anon_1 ON anon_1.metadata_id = statistics_short_term.metadata_id AND anon_1.start = statistics_short_term.start WHERE anon_1.is_duplicate = %s ORDER BY statistics_short_term.metadata_id, statistics_short_term.start, statistics_short_term.id DESC LIMIT %s] [parameters: (1, 1, 998)]

My recorder is databese is stored in official MariaDb 10 component from the Qnap nas. I tried to run this script directly on the database, but with the same result. Most probably temp file created for this query is too large for 64MB temp on qnap.

This inner query give me no values: SELECT statistics_short_term.start AS start, statistics_short_term.metadata_id AS metadata_id, 1 AS is_duplicate FROM statistics_short_term GROUP BY statistics_short_term.metadata_id, statistics_short_term.start HAVING count(*) > 1

But with combination with the rest it always throws an error. Maybe this is because the number of rows in the statistics_short_term table: 1663922

What version of Home Assistant Core has the issue?

core-2022.2.0

What was the last working version of Home Assistant Core?

No response

What type of installation are you running?

Home Assistant OS

Integration causing the issue

recorder

Link to integration documentation on our website

No response

Example YAML snippet

No response

Anything in the logs that might be useful for us?

No response

Additional information

MariaDB version 10.5.8 on qts 5.0.0.1891