Upgaded to 2025.1.4 but sqlite database seems to have failed to upgrade, now nightly purge fails preventing new data to be stored

Configuration
1

Hi, last week I upgraded my home assistant core to 2025.1.4. Since then I observed that the recorder stops working every night at 4:10.
I found this post: Purge causes recorder to stop writing to the DB until HA is restarted (Auto purge happens at 4:12am) · Issue #117263 · home-assistant/core · GitHub

and implementing

recorder:
auto_purge: false

at least made my recorder continue to work.

in this post, the suggestion is made to run:
pragma_integrity_check on the database, which in my case returned ‘ok’.

the post links to another post where it is mentioned that this problem is fixed in 2024.8.1: table rebuild fails, it will try again in 2024.8.1 or later

(part of) the solution is the removal of the foreign key with cascade delete, as per post :
FOREIGN KEY(event_id) REFERENCES events (event_id) ON DELETE CASCADE

However, even while running 2025.1.4, this foreign key is still in my database (see full schema below).

how come my database is still in the old state, and how can I enforce the upgrade?

here my full database schema:

CREATE TABLE event_data (
        data_id INTEGER NOT NULL,
        hash BIGINT,
        shared_data TEXT,
        PRIMARY KEY (data_id)
);
CREATE TABLE state_attributes (
        attributes_id INTEGER NOT NULL,
        hash BIGINT,
        shared_attrs TEXT,
        PRIMARY KEY (attributes_id)
);
CREATE TABLE statistics_meta (
        id INTEGER NOT NULL,
        statistic_id VARCHAR(255),
        source VARCHAR(32),
        unit_of_measurement VARCHAR(255),
        has_mean BOOLEAN,
        has_sum BOOLEAN,
        name VARCHAR(255),
        PRIMARY KEY (id)
);
CREATE TABLE recorder_runs (
        run_id INTEGER NOT NULL,
        start DATETIME,
        "end" DATETIME,
        closed_incorrect BOOLEAN,
        created DATETIME,
        PRIMARY KEY (run_id)
);
CREATE TABLE schema_changes (
        change_id INTEGER NOT NULL,
        schema_version INTEGER,
        changed DATETIME,
        PRIMARY KEY (change_id)
);
CREATE TABLE statistics_runs (
        run_id INTEGER NOT NULL,
        start DATETIME,
        PRIMARY KEY (run_id)
);
CREATE TABLE events (
        event_id INTEGER NOT NULL,
        event_type VARCHAR(64),
        event_data TEXT,
        origin VARCHAR(32),
        origin_idx SMALLINT,
        time_fired DATETIME,
        context_id VARCHAR(36),
        context_user_id VARCHAR(36),
        context_parent_id VARCHAR(36),
        data_id INTEGER, time_fired_ts FLOAT, context_id_bin BLOB, context_user_id_bin BLOB, context_parent_id_bin BLOB, event_type_id INTEGER,
        PRIMARY KEY (event_id),
        FOREIGN KEY(data_id) REFERENCES event_data (data_id)
);
CREATE TABLE statistics (
        id INTEGER NOT NULL,
        created DATETIME,
        start DATETIME,
        mean FLOAT,
        min FLOAT,
        max FLOAT,
        last_reset DATETIME,
        state FLOAT,
        sum FLOAT,
        metadata_id INTEGER, created_ts FLOAT, start_ts FLOAT, last_reset_ts FLOAT,
        PRIMARY KEY (id),
        FOREIGN KEY(metadata_id) REFERENCES statistics_meta (id) ON DELETE CASCADE
);
CREATE TABLE statistics_short_term (
        id INTEGER NOT NULL,
        created DATETIME,
        start DATETIME,
        mean FLOAT,
        min FLOAT,
        max FLOAT,
        last_reset DATETIME,
        state FLOAT,
        sum FLOAT,
        metadata_id INTEGER, created_ts FLOAT, start_ts FLOAT, last_reset_ts FLOAT,
        PRIMARY KEY (id),
        FOREIGN KEY(metadata_id) REFERENCES statistics_meta (id) ON DELETE CASCADE
);
CREATE TABLE states (
        state_id INTEGER NOT NULL,
        entity_id VARCHAR(255),
        state VARCHAR(255),
        attributes TEXT,
        event_id INTEGER,
        last_changed DATETIME,
        last_updated DATETIME,
        old_state_id INTEGER,
        attributes_id INTEGER,
        context_id VARCHAR(36),
        context_user_id VARCHAR(36),
        context_parent_id VARCHAR(36),
        origin_idx SMALLINT, last_updated_ts FLOAT, last_changed_ts FLOAT, context_id_bin BLOB, context_user_id_bin BLOB, context_parent_id_bin BLOB, metadata_id INTEGER, last_reported_ts FLOAT,
        PRIMARY KEY (state_id),
        FOREIGN KEY(event_id) REFERENCES events (event_id) ON DELETE CASCADE,
        FOREIGN KEY(old_state_id) REFERENCES states (state_id),
        FOREIGN KEY(attributes_id) REFERENCES state_attributes (attributes_id)
);
CREATE TABLE event_types (
        event_type_id INTEGER NOT NULL,
        event_type VARCHAR(64),
        PRIMARY KEY (event_type_id)
);
CREATE TABLE states_meta (
        metadata_id INTEGER NOT NULL,
        entity_id VARCHAR(255),
        PRIMARY KEY (metadata_id)
);
CREATE TABLE sqlite_stat1(tbl,idx,stat);
CREATE INDEX ix_event_data_hash ON event_data (hash);
CREATE INDEX ix_state_attributes_hash ON state_attributes (hash);
CREATE UNIQUE INDEX ix_statistics_meta_statistic_id ON statistics_meta (statistic_id);
CREATE INDEX ix_recorder_runs_start_end ON recorder_runs (start, "end");
CREATE INDEX ix_statistics_runs_start ON statistics_runs (start);
CREATE INDEX ix_events_data_id ON events (data_id);
CREATE INDEX ix_states_old_state_id ON states (old_state_id);
CREATE INDEX ix_states_attributes_id ON states (attributes_id);
CREATE INDEX ix_states_event_id ON states (event_id);
CREATE UNIQUE INDEX ix_event_types_event_type ON event_types (event_type);
CREATE UNIQUE INDEX ix_states_meta_entity_id ON states_meta (entity_id);
CREATE INDEX ix_events_time_fired_ts ON events (time_fired_ts);
CREATE INDEX ix_states_last_updated_ts ON states (last_updated_ts);
CREATE INDEX ix_statistics_start_ts ON statistics (start_ts);
CREATE UNIQUE INDEX ix_statistics_statistic_id_start_ts ON statistics (metadata_id, start_ts);
CREATE INDEX ix_statistics_short_term_start_ts ON statistics_short_term (start_ts);
CREATE UNIQUE INDEX ix_statistics_short_term_statistic_id_start_ts ON statistics_short_term (metadata_id, start_ts);
CREATE INDEX ix_events_context_id_bin ON events (context_id_bin);
CREATE INDEX ix_states_context_id_bin ON states (context_id_bin);
CREATE INDEX ix_events_event_type_id_time_fired_ts ON events (event_type_id, time_fired_ts);
CREATE INDEX ix_states_metadata_id_last_updated_ts ON states (metadata_id, last_updated_ts);
CREATE TABLE migration_changes (
        migration_id VARCHAR(255) NOT NULL,
        version SMALLINT NOT NULL,
        PRIMARY KEY (migration_id)
);

here my schema_changes:

select * from schema_changes;
1|30|2023-01-05 07:38:12.334344
2|31|2023-06-15 18:29:02.456363
3|32|2023-06-15 18:34:55.013630
4|33|2023-06-15 18:34:55.033203
5|34|2023-06-15 18:37:23.317680
6|35|2023-06-15 18:37:49.178416
7|36|2023-06-15 18:40:04.590199
8|37|2023-06-15 18:40:06.825875
9|38|2023-06-15 18:42:23.265006
10|39|2023-06-15 18:42:23.439434
11|40|2023-06-15 18:42:37.531935
12|41|2023-06-15 18:42:37.592758
13|42|2025-02-01 11:34:27.165683
14|43|2025-02-01 11:40:19.324166
15|44|2025-02-01 11:40:19.344334
16|45|2025-02-01 11:40:19.360605
17|46|2025-02-01 11:40:19.384333
18|47|2025-02-01 11:40:19.392941
19|48|2025-02-01 11:40:19.614583

and here my migration_changes:

event_type_id_migration|1
entity_id_migration|1
state_context_id_as_binary|2
event_context_id_as_binary|2
entity_id_post_migration|1

appreciate your help and guidance!

2

Maybe that? Let is finish rebuilding.
or
Delete the database and let it rebuild from scratch. You will loose the old data.

3

thanks! however, the auto_purge: false will cause that my database keeps growing, which is not advised.
Deleting the database is also not a preferred solution, a that would remove also all my long term statistics on energy usage.
Is there an easy way to have a new database made, and then migrate at least the long term statistics?

4

This is about the third post I’ve seen implying some sort of database corruption. I also had an issue updating to 2024.12.x which had me deleting the database and starting over. Maybe it’s just a coincidence, but I’d never seen this before, and just started noticing it recently.

Doing a database off-load re-load would certainly be possible, but tricky if there were a schema change in between. Also not something the average user is going to do, but your post implies some DBA experience, so certainly worth a shot.

5

There was a database change or two around then. If people ran out of room or had a big database and didn’t want to wait for it to finish the migration, then interrupted it, shit happened.

I know not everyone agrees, but the HA database is for runtime data. Long term data should be stored elsewhere. If your database is over 2gb rethink your options and delete it. You have a (usually) small CPU and big databases slow stuff up, so keep your long term and working data separate. My opinion, but it has served me well.

I delete my database about once a year and have no problems. I don’t care at all what energy the wash machine used 2 years ago. It was 2 years ago. I know, I know, pretty graphs, show trends, worship the data, but just purge it. You will have less stress and live longer.

1 Like