Abstract
Introduction
If you face a lot of statistic issues to be fixed (in my case about 7000), and you don’t want to bother by clicking thousands of time the Fix Issue button, then clicking the Remove button in the dialog message this little how-to might be useful for you.
Unfortunately, the WebGUI doesn’t have a “Fix All” button, therefore we need to run the cleanup manually directly in the database.
Overall cleanup process
Disclaimer
As we will directly interact with the backend database, before executing any modification (UPDATEand/orDELETEqueries), ensure that the selection contains only rows you want to modify linked to your specific case, otherwise it could result in permanent loss of data!!!. And don’t forget to backup your HomeAssistant to have a rollback opportunity
The process of the cleaning is the following:
- Identification of rows to clean
- Install and connect to
sqlite3 - Enforce the
DELETE CASCADE - Run the
DELETEquery
1. Identification of rows to clean
In my specific case, these issues were mainly related to the Remote Home Assistant component which included all of its sensors to itself in a loop… results: a lot of orphaned entries in statistics_meta and statistics data.
For testing rows selection, I used the SQLite Web Add-on which is more user-friendly to build a test query than doing it with sqlite3 command line.
1.1 Limitation of SQLite Web add-on
By using this add-on I figured out there are some limitations:
- Number of rows displayed is limited to 1’000 rows;
- Cannot use features that are based on a connection like
PRAGMA.
Unfortunately, the implementation of the SQLite Web add-on doesn’t allow to use PRAGMA as each request is sent to the DB with a new connection. As PRAGMA is connection-based, it will never remain active between queries.
This can be verified in the SQLite web add-on by simply executing:
PRAGMA foreign_keys=ON;
then verify if it is activated or not with:
PRAGMA foreign_keys;
- returned value 0 means foreign keys are not enforced
- returned value 1 means foreign keys are enforced
With SQLite web add-on the returned value is always 0
1.2 Table constraints
Looking at the home-assistant_v2.db database, fortunately, there are FOREIGN KEY integrity constraints with DELETE CASCADE on statistics_meta and statistics_short_term table which will simplify cleanup task.
This means we can easily delete orphaned metadata_id, in statistics and statistics_shorts_term by using sqlite3 PRAGMA and integrity constraints linked to statistics_meta.
1.3 Build the SELECT query
For me it was easy to identify orphaned data as most of them was prefixed with c16_, I can then execute a simple SELECT statement to find these orphaned data :
SELECT * FROM "statistics_meta" WHERE statistic_id like "%c16_%";
However, to have more control over what data need to be cleaned, and also fix potential other orphaned issues, I built up an advanced query (with JOIN) to verify the incriminated records and identify which data are existing in statistics_meta table but not in states.
Below you will find the SELECT query to find orphaned rows in statistics_meta:
SELECT
CASE WHEN entity_id IS NULL
THEN 'Orphaned'
ELSE 'OK'
END Issue,
entity_id,
statistic_id
FROM (
SELECT
b.entity_id,
a.statistic_id
FROM
statistics_meta a
LEFT JOIN 'states' b
ON a.statistic_id = b.entity_id
GROUP BY
a.statistic_id -- we want to avoid duplicates
);
Now that we have verified the data we want to delete, we need to modify the query to get only the IDs of rows:
SELECT
id
FROM (
SELECT
a.id,
b.entity_id
FROM
statistics_meta a
LEFT JOIN states b ON a.statistic_id = b.entity_id
GROUP BY
a.statistic_id
)
WHERE
entity_id IS NULL; /* NULL means the statistic_id is orphaned */
We can check the exact amount of data to be cleaned by adding a SELECT count(*) this way:
SELECT count(*)
(
-- add here query you want to count rows
);
2. Install and connect to sqlite3
For running the cleanup query, due to the limitations of SQLite Web add-on using PRAGMA, we need to connect HomeAssistant with SSH.
sqlite3 is not installed by default in the official supervised HomeAssistant, then we need to execute the following command to install it:
apk add sqlite
Once installed, we can connect the DB by running the following command:
sqlite3 /config/home-assistant_v2.db
3. Enforce the DELETE CASCADE
First thing to do before running a DELETE query on statistics_meta is to enable PRAGMA in sqlite3:
PRAGMA foreign_keys=ON;
Then verify PRARMA is activated (result must be 1):
PRAGMA foreign_keys;
4. Run the DELETE query
We are now connected to sqlite3 with PRAGMA active to enforce DELETE CASCADE.
Take now the SELECT query built above and include it within a DELETE FROM like following:
DELETE FROM 'statistics_meta'
WHERE id IN (
SELECT
id
FROM (
SELECT
a.id,
b.entity_id,
a.statistic_id
FROM
statistics_meta a
LEFT JOIN states b
ON a.statistic_id = b.entity_id
GROUP BY
a.statistic_id
)
WHERE
entity_id IS NULL /* NULL means the statistic_id is orphaned */
);
Finally run the DELETE query above in sqlite3 to clean orphaned IDs.
