You need to change the SQlite add-on config to allow writing. Where it says:
read_only: true
Change it to:
read_only: false
I know this is an old thread, but you never know if someone else might be running into the same issue.
5 Likes
Thanks for this guide. Sadly I found it too late.
I’ve deleted the not needed elements from the STATES table before I tried to delete from the EVENTS table. Maybe that’s the reason for my error:
I have the same …
This helped me with my issue of deleting certain values. Thanks for sharing the code as well. I had no idea how to format to get what I wanted.
If anyone else finds this post years later, as I did, it is now possible to call the recorder.purge service, which is friendlier and safer than running SQL queries manually.
See the recorder integration docs for more info.
10 Likes
ran the delete command from SQLite and it corrupted the db file =(
Does anybody know how to delete entries if you use maria-db?
Shit is that what i just did too.
DELETE FROM states WHERE entity_id=“sensor.electricity_demand_max_monthly_1”
and all my history is gone for everything
I had that happened- it caused by HA detecting DB corruption and recreating db file
did you fix it or just left it to start fresh. If it’s an easy fix ill do it but otherwise ill live
I tried but gave up
I am using mariadb, for this I created the precedure:
CREATE OR REPLACE PROCEDURE delete_history (IN entityID VARCHAR(255))
BEGIN
begin_finding: LOOP
SET @z = NULL;
select @z := s.state_id from states as s where s.entity_id=entityID and s.old_state_id and s.state_id not in (select old_state_id from homeassistant.states as os where os.old_state_id is not null and os.entity_id= entityID ) ;
If @z is NULL then
LEAVE begin_finding;
END IF;
delete from states where entity_id=entityID and old_state_id and state_id not in (select os.old_state_id from states os where os.old_state_id is not null and os.entity_id=entityID);
END LOOP;
END;
call the script with sensor id:
CALL delete_history('sensor.stromzahlerstand');
1 Like
This works fine. 
I had to add some extra using MySQL Workbench to do the job.
DELIMITER //
CREATE PROCEDURE delete_history (IN entityID VARCHAR(255))
BEGIN
begin_finding: LOOP
SET @z = null;
select @z := s.state_id from states as s where s.entity_id=entityID and s.old_state_id and s.state_id not in (select old_state_id from homeassistant.states as os where os.old_state_id is not null and os.entity_id= entityID );
IF @z is NULL then
LEAVE begin_finding;
END IF;
delete from states where entity_id=entityID and old_state_id and state_id not in (select os.old_state_id from states os where os.old_state_id is not null and os.entity_id=entityID);
END LOOP;
END //
Else it complained about syntax errors due to missing delimiter.
For anybody who finds this: deleting something from events will also delete the corresponding records from the state, as the state table is created with a “CASCADE DELETE” clause. Not sure why this error appears then…
UPDATE: after playing around in DB Browser for SQLite, the following seems the most compact way to delete an entity from the database, and you can customize it to your liking:
DELETE FROM events
WHERE event_id IN (
SELECT a.event_id FROM states a
WHERE entity_id LIKE '%myexpression%'
);
Due to the foreign key constraint, this automatically deletes all dependent states from the states table.
1 Like
So I have a similar problem. I ran analyze-db.py, and it told me that “automation.automation_1”, an automation I created for testing a while ago and don’t need anymore, takes up over 90% of my events table.
But if I use purge_entities with this entity, nothing happens. At least for other entities I do get entries in the log file, but they are still there in the database.
I ended up just doing a
DELETE FROM events WHERE event_data LIKE '%automation.automation_1%'
Now I need to find a way to repack the database. there’s not enough diskspace…
UPDATE: analyze-db.py is not really a good way to analyze the database, as it ignores all events that don’t have an entity_id in their event_data (quite a few, and especially the state changes). The commands given here:
work much better, and you can easily modify them for ordering by byte size etc.
For large databases on underpowered systems, I found it more convenient to copy the database to my desktop (but stop homeassistant to avoid database corruption: ha core stop), and then work on it using DB Browser for sqlite. This also allows you to VACUUM the database (i.e., repack) even if you don’t have enough space on the homeassistant drive (you need the size of the database as additional free space).
I was able to shrink my database from 29GB to 13GB (I like to keep a long history, so this already has 6 months). The culprit were mostly the Unifi bandwidth and uptime trackers.
I then used the hints here:
to shrink the virtualbox image size (using fstrim).
I had an issue with my energymeter sensor. So the sensor recorded a value of 0,81 kWh, but there ar 8100 kWh. I removed the false values from DB using the SQlite Plugin, in the historyview of the entity i don’t see the spike downwards anymore. But in the Energydashboard there is shown that i consumed in the hour i corrected the sensor ~8100kWh because the value changed from 0,81 to 8100.
Is there some mechanism to retrigger the calculation for energydashboard from history data? Or should i just wait? Is the history somewhere else saved or just in the states table?
Thanks for your help!
Intereseting is, that in the quickview of the entity there is still the spike:
but in the details there is no spike to see…
I have similar problem, my DB(mariadb) have over 24GB.
when I run:
DELETE FROM states WHERE entity_id=“sensor.sun_elevation”
Cannot delete or update a parent row: a foreign key constraint fails
I think the problem is FOREIGN KEY “states_ibfk_2”. Can I remove it pernamently?
The procedure seems ok, but when i try to execute it, i receive an error because of different table encoding:
#1267 - Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='
Indeed, some tables has utf8mb4_unicode_ci, others utf8mb4_general_ci. Is there any way to get rid of this? Thanx
For anyone coming here in 2024 and getting confused because all entity_ids are NULL, the database structure is different now and entity IDs are stored elsewhere. This thread has the details:
In my case, I’m using a standard SQLite backend and I have a “sensor.hot_tub_solar_mat_temp” sensor pulling values from a counterfeit DS18B20 that occasionally returns 0C i.e. 32F.
This query finds all rows with state equal to 32F that I want to delete.
SELECT *
FROM
states
WHERE
metadata_id = (
SELECT
metadata_id
FROM
states_meta
WHERE
entity_id = 'sensor.hot_tub_solar_mat_temp'
)
AND
states.state==32
Replace SELECT * with DELETE to purge them.
2 Likes