Rows with Empty Event_Data in Events table

Configuration
1

Hello,

I have a relatively small setup, four z-wave devices and a cell phone connected to Home Assistant Core with MYSQL as the back-end. I’m configured to retain five days of data.

I have been looking through my database since it seems to be relatively large for the small environment that I have.

MariaDB [hass]> SELECT   TABLE_NAME AS `Table`,   ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)` FROM   information_schema.TABLES WHERE   TABLE_SCHEMA = "hass" ORDER BY   (DATA_LENGTH + INDEX_LENGTH) DESC;
+----------------+-----------+
| Table          | Size (MB) |
+----------------+-----------+
| events         |       178 |
| states         |       132 |
| recorder_runs  |         0 |
| schema_changes |         0 |
+----------------+-----------+
4 rows in set (0.001 sec)

What I noticed was almost double the number of rows where event_data is essentially blank ( i.e. an empty json {} ).

My queries are as followed:

MariaDB [hass]> select count(*) from events where event_data <> '{}';
+----------+
| count(*) |
+----------+
|    80405 |
+----------+
1 row in set (0.086 sec)

MariaDB [hass]> select count(*) from events where event_data = '{}';
+----------+
| count(*) |
+----------+
|   165548 |
+----------+
1 row in set (0.085 sec)

When I join to the states table, I see that the devices are creating events in the table even though the state is not actually changing:

MariaDB [hass]> select evn.event_id, event_type, evn.context_id, st.domain, st.state, st.entity_id, st.created from events evn join states st on evn.event_id = st.event_id where evn.event_data = '{}' and st.entity_id = 'zwave.master_ac' order by created desc limit 10;
+----------+---------------+----------------------------------+--------+-------+-----------------+---------------------+
| event_id | event_type    | context_id                       | domain | state | entity_id       | created             |
+----------+---------------+----------------------------------+--------+-------+-----------------+---------------------+
|  3590593 | state_changed | 3c726fe1c72c4e6e9dbb7401ff448abb | zwave  | ready | zwave.master_ac | 2020-07-06 15:10:44 |
|  3590577 | state_changed | 24a2dad15b3342f1bc5a653e45088e12 | zwave  | ready | zwave.master_ac | 2020-07-06 15:10:14 |
|  3590561 | state_changed | f40af0fd86a742dc82880509bfc8afe9 | zwave  | ready | zwave.master_ac | 2020-07-06 15:09:44 |
|  3590545 | state_changed | c3a7259dc1fd4a2eb8c7326f8c4c8468 | zwave  | ready | zwave.master_ac | 2020-07-06 15:09:15 |
|  3590542 | state_changed | be67ef7370b249a680feae1a80480eda | zwave  | ready | zwave.master_ac | 2020-07-06 15:09:14 |
|  3590526 | state_changed | a44e793d81d84b44ae2e0e797fc80b62 | zwave  | ready | zwave.master_ac | 2020-07-06 15:08:44 |
|  3590511 | state_changed | bb2c32453299430ebe9a25701eac2573 | zwave  | ready | zwave.master_ac | 2020-07-06 15:08:14 |
|  3590495 | state_changed | 71873e79f91543a08e224258f2b73831 | zwave  | ready | zwave.master_ac | 2020-07-06 15:07:44 |
|  3590478 | state_changed | f392cc70efbe4d47aaa0e57fa8ccfe8c | zwave  | ready | zwave.master_ac | 2020-07-06 15:07:14 |
|  3590462 | state_changed | 529676215e8d46b99c2c9832f43d23b3 | zwave  | ready | zwave.master_ac | 2020-07-06 15:06:44 |
+----------+---------------+----------------------------------+--------+-------+-----------------+---------------------+
10 rows in set (0.032 sec)

This is occurring for all of the devices I have including their sensors where the value doesn’t change:

MariaDB [hass]> select evn.event_id, event_type, evn.context_id, st.domain, st.state, st.entity_id, st.created from events evn join states st on evn.event_id = st.event_id where evn.event_data = '{}' and st.entity_id = 'sensor.master_ac_power' order by created desc limit 10;
+----------+---------------+----------------------------------+--------+-------+------------------------+---------------------+
| event_id | event_type    | context_id                       | domain | state | entity_id              | created             |
+----------+---------------+----------------------------------+--------+-------+------------------------+---------------------+
|  3590678 | state_changed | 2464bb55d46e4636bf7288fa464d63ad | sensor | 0.0   | sensor.master_ac_power | 2020-07-06 15:13:14 |
|  3590662 | state_changed | a8440b0a414246b2aee7c0d33f36f6dc | sensor | 0.0   | sensor.master_ac_power | 2020-07-06 15:12:44 |
|  3590645 | state_changed | 732cc7922c784ab48bb5ea07b2341079 | sensor | 0.0   | sensor.master_ac_power | 2020-07-06 15:12:14 |
|  3590629 | state_changed | cb584ca5b9124621990d034d2000f8f9 | sensor | 0.0   | sensor.master_ac_power | 2020-07-06 15:11:44 |
|  3590612 | state_changed | 1004ecb340e44a41a9ea29ee82bc4c6b | sensor | 0.0   | sensor.master_ac_power | 2020-07-06 15:11:14 |
|  3590596 | state_changed | 065a8724310a4daf9f120af1bc323113 | sensor | 0.0   | sensor.master_ac_power | 2020-07-06 15:10:44 |
|  3590580 | state_changed | baa15bf110754e8c93b82e67ffe36dac | sensor | 0.0   | sensor.master_ac_power | 2020-07-06 15:10:14 |
|  3590564 | state_changed | 9064c3407d3f431d8aebc097fd85d948 | sensor | 0.0   | sensor.master_ac_power | 2020-07-06 15:09:44 |
|  3590548 | state_changed | b2f918397b2b42a1ad0b66ecd130221d | sensor | 0.0   | sensor.master_ac_power | 2020-07-06 15:09:15 |
|  3590529 | state_changed | f568a660e6af4432a84c0ead09163a6e | sensor | 0.0   | sensor.master_ac_power | 2020-07-06 15:08:44 |
+----------+---------------+----------------------------------+--------+-------+------------------------+---------------------+
10 rows in set (0.030 sec)

Seems rather excessive to write to the database every 30 seconds. Is this expected behavior?

Thanks
Ray

2

state changed events contain all attributes that update as well, which are not shown in your test. Those are most likely changing.

3

Thanks for the response @petro

So are you saying that any change in the list of attributes will cause a state row to be written? Mind you, the output shows that I am getting a record every thirty seconds which seems to be less about a state change and more about a frequency in checking the state itself.

Continuing on my checks, here is a query showing only the last two records for an entity including the attributes from the states table:

MariaDB [hass]> select evn.event_id, event_type, evn.context_id, st.domain, st.state, st.entity_id, st.created, st.attributes from events evn join states st on evn.event_id = st.event_id where evn.event_data = '{}' and st.entity_id = 'zwave.master_ac' order by created desc limit 2;
+----------+---------------+----------------------------------+--------+-------+-----------------+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| event_id | event_type    | context_id                       | domain | state | entity_id       | created             | attributes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
+----------+---------------+----------------------------------+--------+-------+-----------------+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  3593724 | state_changed | 749d76711abc41d0a778c33f635b40ec | zwave  | ready | zwave.master_ac | 2020-07-06 16:44:15 | {"node_id": 2, "node_name": "Zooz ZEN15 Power Switch", "manufacturer_name": "Zooz", "product_name": "ZEN15 Power Switch", "query_stage": "Complete", "is_awake": true, "is_ready": true, "is_failed": false, "is_info_received": true, "max_baud_rate": 40000, "is_zwave_plus": true, "capabilities": ["zwave_plus", "beaming", "listening", "routing"], "neighbors": [3, 4], "sentCnt": 59, "sentFailed": 0, "retries": 0, "receivedCnt": 3765, "receivedDups": 4, "receivedUnsolicited": 3701, "sentTS": "2020-07-06 16:25:02:901 ", "receivedTS": "2020-07-06 16:44:14:982 ", "lastRequestRTT": 274, "averageRequestRTT": 273, "lastResponseRTT": 409, "averageResponseRTT": 409, "application_version": "1.02", "friendly_name": "Master AC"} |
|  3593721 | state_changed | cc65b8915c8546eb90a1679a1f0cc542 | zwave  | ready | zwave.master_ac | 2020-07-06 16:44:14 | {"node_id": 2, "node_name": "Zooz ZEN15 Power Switch", "manufacturer_name": "Zooz", "product_name": "ZEN15 Power Switch", "query_stage": "Complete", "is_awake": true, "is_ready": true, "is_failed": false, "is_info_received": true, "max_baud_rate": 40000, "is_zwave_plus": true, "capabilities": ["zwave_plus", "beaming", "listening", "routing"], "neighbors": [3, 4], "sentCnt": 59, "sentFailed": 0, "retries": 0, "receivedCnt": 3764, "receivedDups": 4, "receivedUnsolicited": 3700, "sentTS": "2020-07-06 16:25:02:901 ", "receivedTS": "2020-07-06 16:44:14:789 ", "lastRequestRTT": 274, "averageRequestRTT": 273, "lastResponseRTT": 409, "averageResponseRTT": 409, "application_version": "1.02", "friendly_name": "Master AC"} |
+----------+---------------+----------------------------------+--------+-------+-----------------+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.019 sec)

The only items that are different, other than the dates, are “receivedCnt” and receivedUnsolicited which are just being incrementally counted.

Apologies if this seems trivial, I’m trying to understand if this is normal and/or why it may be necessary to do so. It seems like an awful lot of records to right with no change.

Thanks
Ray

4

Yep it’s normal. Without it you wouldn’t get a history. If you don’t want a history of that device, remove it from recorder.

5

Thanks for the validation @petro