Some commands to investigate and cleanup my Home Assistant database.
I'm using MariaDB in docker, open a docker cli on homeassistant docker image (using Portainer for example)
bash-5.0# mysql homeassistant
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 38
Server version: 10.4.13-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [homeassistant]> First show all databases, and delete unused, if you install and add-on and remove it, the database is kept so it seems.
MariaDB [homeassistant]> show databases;
+--------------------+
| Database |
+--------------------+
| homeassistant |
| information_schema |
| mysql |
| nginxproxymanager |
| performance_schema |
| phpmyadmin |
| test |
+--------------------+
7 rows in set (0.001 sec)
MariaDB [homeassistant]>
MariaDB [homeassistant]> drop database nginxproxymanager;
Query OK, 14 rows affected (0.499 sec)
MariaDB [homeassistant]> drop database phpmyadmin;
Query OK, 19 rows affected (0.646 sec)
MariaDB [homeassistant]> drop database test;
Query OK, 19 rows affected (0.646 sec)Show tables inside homeassistant database.
MariaDB [homeassistant]> show tables;
+-------------------------+
| Tables_in_homeassistant |
+-------------------------+
| events |
| recorder_runs |
| schema_changes |
| states |
+-------------------------+
4 rows in set (0.000 sec)Show structure of the 2 most important tables.
MariaDB [homeassistant]> show columns from events;
+-------------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+----------------+
| event_id | int(11) | NO | PRI | NULL | auto_increment |
| event_type | varchar(32) | YES | MUL | NULL | |
| event_data | text | YES | | NULL | |
| origin | varchar(32) | YES | | NULL | |
| time_fired | datetime | YES | MUL | NULL | |
| created | datetime | YES | | NULL | |
| context_id | varchar(36) | YES | MUL | NULL | |
| context_user_id | varchar(36) | YES | MUL | NULL | |
| context_parent_id | char(36) | YES | MUL | NULL | |
+-------------------+-------------+------+-----+---------+----------------+
9 rows in set (0.002 sec)
MariaDB [homeassistant]> show columns from states;
+-----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+----------------+
| state_id | int(11) | NO | PRI | NULL | auto_increment |
| domain | varchar(64) | YES | | NULL | |
| entity_id | varchar(255) | YES | MUL | NULL | |
| state | varchar(255) | YES | | NULL | |
| attributes | text | YES | | NULL | |
| event_id | int(11) | YES | MUL | NULL | |
| last_changed | datetime | YES | | NULL | |
| last_updated | datetime | YES | MUL | NULL | |
| created | datetime | YES | | NULL | |
| context_id | varchar(36) | YES | | NULL | |
| context_user_id | varchar(36) | YES | | NULL | |
| old_state_id | int(11) | YES | | NULL | |
+-----------------+--------------+------+-----+---------+----------------+
12 rows in set (0.001 sec)Show all domains logged.
MariaDB [homeassistant]> SELECT DISTINCT domain FROM states;
+----------------+
| domain |
+----------------+
| switch |
| sensor |
| person |
| binary_sensor |
| light |
| climate |
| zone |
| scene |
| input_boolean |
| input_select |
| device_tracker |
+----------------+
11 rows in set (3.158 sec)Show all entities logged.
MariaDB [homeassistant]> SELECT DISTINCT entity_id FROM states;
| sun.sun |
| switch.alles_uit |
| switch.alles_uit_aan |
| switch.alles_uit_off |
| switch.bureau |
| switch.hobbykamer_plug |
| switch.incident_response |
| switch.pc_ron |
| switch.schuur_siren |
| switch.server |
| switch.sim_racing_plug |
| timer.bwr_nesthub |
| timer.fsr_nesthub |
| weather.br_unknown_station |
| zone.home |
| zone.sportcity |
| zone.work |
+------------------------------------------------------------------------+
627 rows in set (0.019 sec)Show all device_tracker entries.
MariaDB [homeassistant]> SELECT entity_id FROM states WHERE domain = "device_tracker";
| device_tracker.84_0d_8e_5d_53_70 |
| device_tracker.6001941eedfa |
| device_tracker.a4cf12c60075 |
| device_tracker.philipstvslpk |
| device_tracker.xn826l |
| device_tracker.ringchimeboven |
+-------------------------------------+
27766 rows in set (2.047 sec)Some delete examples, use with care!
MariaDB [homeassistant]> DELETE from states where entity_id = 'device_tracker.f4_06_16_86_7e_1d';
Query OK, 684 rows affected (0.079 sec)
MariaDB [homeassistant]> DELETE from states where domain = 'device_tracker';
Query OK, 26671 rows affected (10.161 sec)Optimize tables.
MariaDB [homeassistant]> OPTIMIZE TABLE states;
+----------------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+----------------------+----------+----------+-------------------------------------------------------------------+
| homeassistant.states | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| homeassistant.states | optimize | status | OK |
+----------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (55.374 sec)Check you purge day setting from home assistant config.
MariaDB [homeassistant]> select min(created),max(created) from events;
+---------------------+---------------------+
| min(created) | max(created) |
+---------------------+---------------------+
| 2020-09-08 02:12:34 | 2020-10-09 13:14:09 |
+---------------------+---------------------+
1 row in set (1.765 sec)Show all domains and entities active in your home assistant using Lovelace cards.
cards:
- content: >-
**domains:** {%- set unique_domains = states | map(attribute='domain')
|list | unique | list -%} {%- for domain in unique_domains -%} {{"\n"}}-
{{domain}} {%- endfor -%} {{"\n"}}
type: markdown
- content: >-
**entities:** {%- for state in states -%} {{"\n"}} - {{state.entity_id}}
{%- endfor -%}
type: markdown
type: horizontal-stack
