Description
The problem
I tried to set up a custom sensor based on an SQL command. The problem I noticed was that the entity_id field in my state table in SQLite is not populated.
I have tried to remove the old database (including -shm and -wal) and recreate a fresh one but the problem persist.
The command .schema states gives me the following:
CREATE TABLE states (
state_id INTEGER NOT NULL,
entity_id CHAR(0),
state VARCHAR(255),
attributes CHAR(0),
event_id SMALLINT,
last_changed CHAR(0),
last_changed_ts FLOAT,
last_reported_ts FLOAT,
last_updated CHAR(0),
last_updated_ts FLOAT,
old_state_id INTEGER,
attributes_id INTEGER,
context_id CHAR(0),
context_user_id CHAR(0),
context_parent_id CHAR(0),
origin_idx SMALLINT,
context_id_bin BLOB,
context_user_id_bin BLOB,
context_parent_id_bin BLOB,
metadata_id INTEGER,
PRIMARY KEY (state_id),
FOREIGN KEY(old_state_id) REFERENCES states (state_id),
FOREIGN KEY(attributes_id) REFERENCES state_attributes (attributes_id),
FOREIGN KEY(metadata_id) REFERENCES states_meta (metadata_id)
);
CREATE INDEX ix_states_context_id_bin ON states (context_id_bin);
CREATE INDEX ix_states_last_updated_ts ON states (last_updated_ts);
CREATE INDEX ix_states_old_state_id ON states (old_state_id);
CREATE INDEX ix_states_metadata_id_last_updated_ts ON states (metadata_id, last_updated_ts);
CREATE INDEX ix_states_attributes_id ON states (attributes_id);
But if I do a select, I get the following:
sqlite> SELECT * FROM states;
|
|
3924||479.7|||||||1749750313.21569|3830|1516||||0|?e>??!??T?<|||1401
3925||2833.195|||||||1749750313.21866|3832|1530||||0|?e>???{??u???|||534
3926||0.365|||||||1749750313.22236|3835|184||||0|?e>??m/????|||143
3927||39.705|||||||1749750313.22511|3836|183||||0|?e>":mU???0|||142
3928||15.996|||||||1749750313.22614|3872|199||||0|?e>":mU???0|||158
|
|
The log files don't give me any hint what might be wrong
What version of Home Assistant Core has the issue?
2025.4.1
What was the last working version of Home Assistant Core?
No response
What type of installation are you running?
Home Assistant Core
Integration causing the issue
No response
Link to integration documentation on our website
No response
Diagnostics information
No response
Example YAML snippet
Anything in the logs that might be useful for us?
Additional information
In my configuration.yaml file, I have the following attributes:
default_config:
recorder:
purge_keep_days: 10
db_url: sqlite:////<correct_path_to_config_folder>/.homeassistant/home-assistant_v2.db