Core: mqtt domain not excluded from recorder

[Koma-Andrea]

Home Assistant release with the issue:

Versione: 0.102.1

Last working Home Assistant release (if known):
I don' know if was working before.

Operating environment (Hass.io/Docker/Windows/etc.):

Hass.io on Virtual Machine in vmware esxi

Integration:

https://www.home-assistant.io/integrations/recorder/

Description of problem:
I'm using the recorder on pgsql backend.
Looks like the MQTT domain is not excluded (and is VERY verbose).

Problem-relevant configuration.yaml entries and (fill out even if it seems unimportant):

recorder:
  db_url: !secret posgresql_db
  purge_keep_days: 3
  purge_interval: 1
  include:
    domains:
      - light
      - switch
      - sensor

Traceback (if applicable):

2019-11-23 12:49:13 INFO (MainThread) [homeassistant.setup] Setting up recorder
2019-11-23 12:49:13 INFO (MainThread) [homeassistant.setup] Setting up mqtt
2019-11-23 12:49:13 INFO (MainThread) [homeassistant.setup] Setup of domain mqtt took 0.0 seconds.
2019-11-23 12:49:13 INFO (MainThread) [homeassistant.setup] Setting up webhook
2019-11-23 12:49:13 INFO (MainThread) [homeassistant.setup] Setup of domain webhook took 0.0 seconds.
2019-11-23 12:49:13 INFO (MainThread) [homeassistant.setup] Setup of domain recorder took 0.4 seconds.

Additional information:
It fills up the disk very quickly.

[springstan]

@Koma-Andrea I am sorry but I do not understand your issue exactly. Could you clarify some things?

Looks like the MQTT domain is not excluded

What do you mean by the MQTT domain is not excluded? MQTT is not a domain as far as I know. Adhering to this idea because added MQTT entities do not start with MQTT.name_xyz. Instead they start with their domain e.g. sensor, light, switch, hvac etc. and are followed by a name.

(and is VERY verbose).

What messages do you find to be too verbose? The traceback you mentioned only appears on startup so it should not fill up your disk too rapidly.

Again sorry, maybe I am just misunderstanding you :)

[OttoWinter]

Yeah, as @springstan said, if the entity id is sensor.xyz then the domain is sensor. The integration is mqtt, but the domain (what type of entity it is) is sensor.

[Koma-Andrea]

Looks like the mqtt stream is written to the registry. I'll extract some line as soon as I can.

[Koma-Andrea]

INSERT INTO "events" ("event_id", "event_type", "event_data", "origin", "time_fired", "created", "context_id", "context_user_id") VALUES
(22577446,  'call_service', '{"domain": "mqtt", "service": "publish", "service_data": {"topic": "homeassistant/sensor/aeon_labs_zw100_multisensor_6_temperature/last_changed", "qos": 1, "retain": true, "payload": "2019-11-26T14:05:21.760775+00:00"}}',  'LOCAL',    '2019-11-26 14:05:21.777589+00',    '2019-11-26 14:05:21.900937+00',    'beba663217de41dea617991518b63aa5', NULL),
(22577450,  'call_service', '{"domain": "mqtt", "service": "publish", "service_data": {"topic": "homeassistant/sensor/aeon_labs_zw100_multisensor_6_temperature/value_id", "qos": 1, "retain": true, "payload": "\"72057594143588370\""}}', 'LOCAL',    '2019-11-26 14:05:21.778191+00',    '2019-11-26 14:05:21.931344+00',    '82203fc1684143d7815f366105d81ddf', NULL);

I've a bazillion of this entry, with the stream of everything passes on mqtt.

[OttoWinter]

Ah ok that is another table: events. It looks like you have automations that fire mqtt.publish a lot.

The include/exclude only refer to the states table, not events. I don't know of a way to disable writing events to DB.

[Koma-Andrea]

Welp, this looks like a big hole in the plot. The event table is ginormous.

[springstan]

Ah ok that is another table: events. It looks like you have automations that fire mqtt.publish a lot.

@Koma-Andrea do you have any automation that meets this criteria?

[Koma-Andrea]

I don't think so,
I've tried commenting the mqtt stream and mqtt event but looks like notthing changed so far.

[freebsdgirl]

I've got the same problem with mysql. I do fire mqtt.publish a lot. I can understand why one might think that mqtt is a domain, however, as the content of the call_service entries in events that are blowing up the database reference a mqtt domain.

| 1702 | call_service | {"domain": "mqtt", "service": "publish", "service_data": {"topic": "homeassistant/sensor/living_room_zen25_s2_left_energy/value_index", "qos": 1, "retain": true, "payload": "0"}} | LOCAL | 2019-12-13 15:43:52 | 2019-12-13 15:44:51 | 2f8563285db84711bc9a5e435a3d421c | NULL |

[Koma-Andrea]

Merry Christmas Bump :)

[Gerto]

I was having the same issue as I am using "mqtt_statestream" component which fires a mqtt service call every time any entity is updated causing a huge event table.

For now I have solved this by using the (undocumented) "event_types" exclude from the recorder component (added in #7627 )

recorder:
  exclude:
    event_types:
    - call_service

This obviously excluded all "call_service" logs and not just mqtt ones.

[Koma-Andrea]

That's exactly what I needed! Thank you!

[springstan]

@Gerto could you please open a PR in home-assistant/home-assistant.io to complete the documentation? Thank you :)

[Gerto]

@Gerto could you please open a PR in home-assistant/home-assistant.io to complete the documentation? Thank you :)

Already did, it has been added to the documentation!

[springstan]

I am going to close this issue now, since it has been solved and the documentation has been updated via https://github.com/home-assistant/home-assistant.io/pull/11865.