Description
The problem
I run Homeassistant with MariaDB for multiple years already. Everything was working fine until I noticed errors in my MariaDB yesterday. Beside of this I saw, that my DB has growed up to over 20GB so far.
2025-05-29 11:07:33 18 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery.
2025-05-29 11:07:33 18 [ERROR] InnoDB: Page [page id: space=139, page number=98168] log sequence number 1489684370762 is in the future! Current system log sequence number 802152603138.
2025-05-29 11:07:33 18 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery.
2025-05-29 11:07:33 18 [ERROR] InnoDB: Page [page id: space=139, page number=98169] log sequence number 1489684371268 is in the future! Current system log sequence number 802152603138.
2025-05-29 11:07:33 18 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery.
2025-05-29 11:07:33 18 [ERROR] InnoDB: Page [page id: space=139, page number=98170] log sequence number 1489730172072 is in the future! Current system log sequence number 802152603138.
2025-05-29 11:07:33 18 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery.
2025-05-29 11:07:33 18 [ERROR] InnoDB: Page [page id: space=139, page number=94791] log sequence number 1490104383481 is in the future! Current system log sequence number 802152603138.
2025-05-29 11:07:33 18 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery.
2025-05-29 11:07:33 18 [ERROR] InnoDB: Page [page id: space=139, page number=94798] log sequence number 1489702687262 is in the future! Current system log sequence number 802152603138.
2025-05-29 11:07:33 18 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery.
2025-05-29 11:07:33 18 [ERROR] InnoDB: Page [page id: space=139, page number=94799] log sequence number 1489690778181 is in the future! Current system log sequence number 802152603138.
2025-05-29 11:07:33 18 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery.
2025-05-29 11:07:33 18 [ERROR] InnoDB: Page [page id: space=139, page number=94800] log sequence number 1489693098628 is in the future! Current system log sequence number 802152603138.
2025-05-29 11:07:33 18 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery.
2025-05-29 11:07:33 18 [ERROR] InnoDB: Page [page id: space=139, page number=94801] log sequence number 1489689509943 is in the future! Current system log sequence number 802152603138.
2025-05-29 11:07:33 18 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery.
2025-05-29 11:07:33 18 [ERROR] InnoDB: Page [page id: space=139, page number=94802] log sequence number 1489689398560 is in the future! Current system log sequence number 802152603138.
2025-05-29 11:07:33 18 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery.
2025-05-29 11:07:33 18 [ERROR] InnoDB: Page [page id: space=139, page number=94803] log sequence number 1489802436784 is in the future! Current system log sequence number 802152603138.
As this state seems to be persistent for some days, I do not have a backup from before. The DB purging doesn't work. So no chance to reduce size. I was not able to fix this issue until yet.
So I took VM snapshot and I dumped the DB with innodb-force-recovery=1. I cleared the Database and created a new one where I imported the dump. The errors from above are now gone.
Homeassistant is starting up fine and I've all long term history available. It looks like it is working fine.
Unfortuntly the Purge still doesn't work so my DB is still huge. I do not have any Idea, why I can't purge my DB back to keep only 3 days (and the long term stats ofc).
### recorder.yaml
purge_keep_days: 3 # Placeholder -> Defined in custom automation to purge DB
db_max_retries: 100
db_retry_wait: 3
auto_purge: false
db_url: !secret mysql_dburl
include:
entities:
....
- alias: Purge Database
initial_state: true
trigger:
trigger: time
at: '13:30:00'
action:
action: recorder.purge
data:
keep_days: 3
id: db0f42996dd24c78a15cef65663b47f8
Looks like there was a missing index which prevented purging was working as expected.
Logs:
2025-05-29 14:44:21.574 DEBUG (Recorder) [homeassistant.components.recorder.core] Processing task: PurgeTask(purge_before=datetime.datetime(2025, 5, 26, 12, 37, 18, 213303, tzinfo=datetime.timezone.utc), repack=True, apply_filter=False)
2025-05-29 14:44:23.311 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before target 2025-05-26 12:37:18+00:00
2025-05-29 14:44:23.311 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purge running in new format as there are NO states with event_id remaining
2025-05-29 14:44:23.396 DEBUG (Recorder) [homeassistant.components.recorder.purge] Selected 4000 state ids and 833 attributes_ids to remove
2025-05-29 14:44:23.550 DEBUG (Recorder) [homeassistant.components.recorder.purge] Updated <sqlalchemy.engine.cursor.CursorResult object at 0x7f1581993150> states to remove old_state_id
2025-05-29 14:44:24.077 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted <sqlalchemy.engine.cursor.CursorResult object at 0x7f158a2cc280> states
2025-05-29 14:44:24.128 DEBUG (Recorder) [homeassistant.components.recorder.purge] Selected 4000 state ids and 770 attributes_ids to remove
....
2025-05-29 14:56:49.122 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted <sqlalchemy.engine.cursor.CursorResult object at 0x7f15af9c6740> short term statistics
2025-05-29 14:56:49.123 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging hasn't fully completed yet
2025-05-29 14:56:54.851 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before target 2025-05-26 12:37:18+00:00
2025-05-29 14:56:54.851 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purge running in new format as there are NO states with event_id remaining
2025-05-29 14:56:54.897 DEBUG (Recorder) [homeassistant.components.recorder.purge] Selected 4000 state ids and 652 attributes_ids to remove
2025-05-29 14:56:55.087 DEBUG (Recorder) [homeassistant.components.recorder.purge] Updated <sqlalchemy.engine.cursor.CursorResult object at 0x7f157acd7460> states to remove old_state_id
2025-05-29 14:56:55.517 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted <sqlalchemy.engine.cursor.CursorResult object at 0x7f15af8a88a0> states
2025-05-29 14:56:55.579 DEBUG (Recorder) [homeassistant.components.recorder.purge] Selected 4000 state ids and 623 attributes_ids to remove
2025-05-29 14:56:55.860 DEBUG (Recorder) [homeassistant.components.recorder.purge] Updated <sqlalchemy.engine.cursor.CursorResult object at 0x7f1579ab0050> states to remove old_state_id
2025-05-29 14:56:56.461 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted <sqlalchemy.engine.cursor.CursorResult object at 0x7f1579ab3ee0> states
2025-05-29 14:56:56.569 DEBUG (Recorder) [homeassistant.components.recorder.purge] Selected 4000 state ids and 635 attributes_ids to remove
My questions now are:
- Is it expected, that purging is taking that much time? (in 45minutes only 834'192 entries are purged)
- Is there an way to reimport DB Data but with a new and intact DB scheme (all required indexes and so on are there)
- Is there a way to import just the history data of the sensors. I do not care to loose "states". I just need the old data because of the Energy History charts which are covering more than 2 years now. So this seems to be my main concern to loose the data I guess.
Thanks for inputs and help! Very appreciated.
What version of Home Assistant Core has the issue?
2025.5.3
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
https://www.home-assistant.io/integrations/recorder/
Diagnostics information
No response
Example YAML snippet
Anything in the logs that might be useful for us?
Additional information
No response