I don’t know the DB browser app you are using. But I guess it will have some place where you can manually enter SQL queries and execute them.
Alternatively, if you know how to connect using SSH, you can use sqlite via shell. Depending on the way you have installed everything the needed programm might be missing. In that case you can install it with sudo apt-get install sqlite3. Once that’s done, you can open the database using the command sudo sqlite /path/to/your/database.db. In there perform the queries from above. Once your finished, you exit the sqlite-shell with .exit.
sudo sqlite3 /home/homeassistant/.homeassistant/home-assistant_v2.db
I performed the first query. I pasted only first part
pi@hassbian:~ $ sudo sqlite3 /home/homeassistant/.homeassistant/home-assistant_v 2.db
SQLite version 3.8.7.1 2014-10-29 13:59:56
Enter “.help” for usage hints.
sqlite> SELECT event_type,event_data FROM events ORDER BY length(event_data) DES C LIMIT 1;
call_service|{“domain”: “mqtt”, “service”: “publish”, “service_data”: {“topic”: “master”, “payload”: "{"event_data": {"domain": "mqtt", "service": "pub lish", "service_data": {"topic": "home/states", "payload": "{\"detai ls\": {\"unit_system\": {\"volume\": \"L\", \"mass\": \"g\\ ", \"length\": \"km\", \"temperature\": \"\\u00b0C\"}, \"loc ation_name\": \"Home\", \"version\": \"0.45.0\", \"time_zone\" : \"Europe/Rome\", \"config_dir\": \"/home/homeassistant/.homeassistan t\", \"longitude\": 12.616709, \"elevation\": 83, \"components\": [\"sensor.yr\", \"logbook\", \"zwave\", \"updater\", \"media_p layer.yamaha\", \"sensor.worldclock\", \"config.zwave\", \"light\" , \"discovery\", \"light.hue\", \"sensor.template\", \"mqtt_events tream\", \"switch.broadlink\", \"sensor\", \"sensor.darksky\", \\ "camera.mqtt\", \"tts\", \"logger\", \"config.automation\", \"li ght.xiaomi\", \"climate\", \"websocket_api\", \"xiaomi\", \"neta tmo\", \"zone\", \"light.flux_led\", \"switch.wake_on_lan\", \"s witch.rpi_rf\", \"ffmpeg\", \"http\", \"config\", \"sensor.netat mo\", \"notify\", \"input_select\", \"api\", \"sensor.openweathe rmap\", \"sensor.broadlink\", \"frontend\", \"sun\", \"switch\\ ", \"device_tracker\", \"media_player.cast\", \"automation\", \"se nsor.mqtt\", \"mqtt_export\", \"switch.command_line\", \"binary_sens or.xiaomi\", \"switch.xiaomi\", \"config.group\", \"history\", \\ "scene\", \"sensor.zwave\", \"light.yeelight\", \"sensor.rest\", \ \"scene.homeassistant\", \"mqtt\", \"sensor.xiaomi\", \"camera.gene ric\", \"sensor.speedtest\", \"camera.mjpeg\", \"emulated_hue\", \ \"shell_command\", \"climate.netatmo\", \"sensor.mqtt_room\", \"bin ary_sensor\", \"config.core\", \"conversation\", \"sensor.miflora\\ ", \"media_player\", \"switch.zwave\", \"script\", \"recorder\", \"ifttt\", \"camera\", \"camera.ffmpeg\", \"group\"], \"latit ude\": 41.973358}, \"states\": [{\"last_changed\": \"2017-05-21T20:0 8:13.114139+00:00\", \"state\": \"25.3\", \"attributes\": {\"uni t_of_measurement\": \"\\u00b0C\", \"friendly_name\": \"TV Room Tem perature\"}, \"entity_id\": \"sensor.tv_room_temperature\", \"last_u pdated\": \"2017-05-21T20:08:13.114139+00:00\"}, {\"last_changed\": \ "2017-05-21T20:07:06.563771+00:00\", \"state\": \"off\", \"attribut es\": {\"friendly_name\": \"Living Camera Flash\"}, \"entity_id\": \"switch.camera_flash_living\", \"last_updated\": \"2017-05-21T20:07: 06.563771+00:00\"}, {\"last_changed\": \"2017-05-21T20:07:38.621734+00:0 0\", \"state\": \"on\", \"attributes\": {\"last_triggered\": n ull, \"friendly_name\": \"Motion Living - OFF light\"}, \"entity_id\\ ": \"automation.motion_living__off_light\", \"last_updated\": \"2017-0 5-21T20:07:38.621734+00:00\"}, {\"last_changed\": \"2017-05-21T20:07:24. 844774+00:00\", \"state\": \"off\", \"attributes\": {\"view\": true, \"order\": 4, \"entity_id\": [\"sensor.netatmo_gilma_bedroom_te mperature\", \"climate.gilma_thermo\", \"group.weather_gilma_bedroom\" , \"sensor.netatmo_gilma_bedroom_co2\", \"binary_sensor.door_window_sensor _158d000126a2fb\"], \"friendly_name\": \"Gilma\", \"hidden\": true }, \"entity_id\": \"group.bedroom_gilma\", \"last_updated\": \"201 7-05-21T20:07:24.844774+00:00\"}, {\"last_changed\": \"2017-05-21T20:07: 05.932964+00:00\", \"state\": \"off\", \"attributes\": {\"last_t riggered\": null, \"friendly_name\": \"AVR\"}, \"entity_id\": \" script.avr\", \"last_updated\": \"2017-05-21T20:07:05.932964+00:00\"}, {\"last_changed\": \"2017-05-21T20:07:06.292607+00:00\", \"state\": \"off\", \"attributes\": {\"assumed_state\": true, \"order\": 2 , \"hidden\": true, \"view\": true, \"icon\": \"mdi:television-gui de\", \"entity_id\": [\"switch.tv\", \"switch.blu_ray\", \"switc h.wake_on_lan\", \"media_player.unnamed
second query
sqlite> ^C
C LIMIT 1;LECT entity_id, attributes FROM states ORDER BY length(attributes) DES
group.all_automations|{“friendly_name”: “all automations”, “order”: 48, “entity_id”: [“automation.volume_livingroom_home”, “automation.free_fall_cube”, “automation.volume_down_cast_living”, “automation.motion_kitchen__off_light”, “automation.mqtt_get_states__sensors”, “automation.motion_hallway_upstairs_left”, “automation.changing_radio_station_livingroom”, “automation.foto_soggiorno”, “automation.radio_livingroom_off”, “automation.motion_hallway_upstairs__on_light_at_night_hallway_sensor”, “automation.volume_up_cast_living”, “automation.motion_entrance__off_strip_lights”, “automation.walking_closet_switch_on_for_too_long_10_minutes”, “automation.play_radio_rai_1”, “automation.door_sensor_corridor__on_light”, “automation.office_camera_inside”, “automation.motion_living__off_light”, “automation.door_sensor_off__storageroom_light_off”, “automation.mqtt_switch”, “automation.pause_media_player_livingroom”, “automation.motion_tvroom_turn_off_light”, “automation.door_sensor_on__storageroom_light_on”, “automation.playpause_cast_living”, “automation.motion_kitchen__on_light”, “automation.motion_entrance__on_strip_light”, “automation.play_radio_rai_1_in_the_morning”, “automation.door_sensor_corridor__off_light”, “automation.storage_switch_on_for_too_long_5_minutes”, “automation.turn_off_livingroom_home”, “automation.door_sensor_off__walking_closet_light_off”, “automation.office_camera_outside”, “automation.volume_up_media_player_livingroom”, “automation.motion_hallway_upstairs_right”, “automation.play_media_player_livingroom”, “automation.motion_tvroom_turn_on_light”, “automation.play_radio_rai_2”, “automation.marisol_heater_on_for_too_long_50_minutes”, “automation.door_sensor_on__walking_closet_light_on”, “automation.grandfather_clock”, “automation.radio_livingroom”, “automation.di_buonanotte”, “automation.motion_living__on_light”], “auto”: true, “hidden”: true}
sqlite>
Can you somehow make sense of what’s causing the event data from the first query? It’s truncated, an apparently quite big. Having a lot of those certainly isn’t a good thing.
you can read it now? I removed the CODE format.
It’s mqtt, for which I use zanzito mainly, maybe the developer can help?
@MrMep
is it possiblt to remove mqtt from history?
I do not have recorder (or maybe I add recorder and exclude mqtt?
It’s still truncated, which you can tell by the missing curly brace at the end. Could be a lot more data that’s not visible.
copy/paste
pi@hassbian:~ $ sudo sqlite3 /home/homeassistant/.homeassistant/home-assistant_v 2.db
SQLite version 3.8.7.1 2014-10-29 13:59:56
Enter “.help” for usage hints.
sqlite> SELECT event_type,event_data FROM events ORDER BY length(event_data) DES C LIMIT 1;
call_service|{“domain”: “mqtt”, “service”: “publish”, “service_data”: {“topic”: “master”, “payload”: “{“event_data”: {“domain”: “mqtt”, “service”: “pub lish”, “service_data”: {“topic”: “home/states”, “payload”: “{\“detai ls\”: {\“unit_system\”: {\“volume\”: \“L\”, \“mass\”: \“g\\ “, \“length\”: \“km\”, \“temperature\”: \”\\u00b0C\”}, \“loc ation_name\”: \“Home\”, \“version\”: \“0.45.0\”, \“time_zone\” : \“Europe/Rome\”, \“config_dir\”: \”/home/homeassistant/.homeassistan t\”, \“longitude\”: 12.616709, \“elevation\”: 83, \“components\”: [\“sensor.yr\”, \“logbook\”, \“zwave\”, \“updater\”, \“media_p layer.yamaha\”, \“sensor.worldclock\”, \“config.zwave\”, \“light\” , \“discovery\”, \“light.hue\”, \“sensor.template\”, \“mqtt_events tream\”, \“switch.broadlink\”, \“sensor\”, \“sensor.darksky\”, \\ “camera.mqtt\”, \“tts\”, \“logger\”, \“config.automation\”, \“li ght.xiaomi\”, \“climate\”, \“websocket_api\”, \“xiaomi\”, \“neta tmo\”, \“zone\”, \“light.flux_led\”, \“switch.wake_on_lan\”, \“s witch.rpi_rf\”, \“ffmpeg\”, \“http\”, \“config\”, \“sensor.netat mo\”, \“notify\”, \“input_select\”, \“api\”, \“sensor.openweathe rmap\”, \“sensor.broadlink\”, \“frontend\”, \“sun\”, \"switch\\ ", \“device_tracker\”, \“media_player.cast\”, \“automation\”, \“se nsor.mqtt\”, \“mqtt_export\”, \“switch.command_line\”, \“binary_sens or.xiaomi\”, \“switch.xiaomi\”, \“config.group\”, \“history\”, \\ “scene\”, \“sensor.zwave\”, \“light.yeelight\”, \“sensor.rest\”, \ \“scene.homeassistant\”, \“mqtt\”, \“sensor.xiaomi\”, \“camera.gene ric\”, \“sensor.speedtest\”, \“camera.mjpeg\”, \“emulated_hue\”, \ \“shell_command\”, \“climate.netatmo\”, \“sensor.mqtt_room\”, \“bin ary_sensor\”, \“config.core\”, \“conversation\”, \“sensor.miflora\\ “, \“media_player\”, \“switch.zwave\”, \“script\”, \“recorder\”, \“ifttt\”, \“camera\”, \“camera.ffmpeg\”, \“group\”], \“latit ude\”: 41.973358}, \“states\”: [{\“last_changed\”: \“2017-05-21T20:0 8:13.114139+00:00\”, \“state\”: \“25.3\”, \“attributes\”: {\“uni t_of_measurement\”: \”\\u00b0C\”, \“friendly_name\”: \“TV Room Tem perature\”}, \“entity_id\”: \“sensor.tv_room_temperature\”, \“last_u pdated\”: \“2017-05-21T20:08:13.114139+00:00\”}, {\“last_changed\”: \ “2017-05-21T20:07:06.563771+00:00\”, \“state\”: \“off\”, \“attribut es\”: {\“friendly_name\”: \“Living Camera Flash\”}, \“entity_id\”: \“switch.camera_flash_living\”, \“last_updated\”: \“2017-05-21T20:07: 06.563771+00:00\”}, {\“last_changed\”: \“2017-05-21T20:07:38.621734+00:0 0\”, \“state\”: \“on\”, \“attributes\”: {\“last_triggered\”: n ull, \“friendly_name\”: \“Motion Living - OFF light\”}, \“entity_id\\ “: \“automation.motion_living__off_light\”, \“last_updated\”: \“2017-0 5-21T20:07:38.621734+00:00\”}, {\“last_changed\”: \“2017-05-21T20:07:24. 844774+00:00\”, \“state\”: \“off\”, \“attributes\”: {\“view\”: true, \“order\”: 4, \“entity_id\”: [\“sensor.netatmo_gilma_bedroom_te mperature\”, \“climate.gilma_thermo\”, \“group.weather_gilma_bedroom\” , \“sensor.netatmo_gilma_bedroom_co2\”, \“binary_sensor.door_window_sensor 158d000126a2fb\"], \“friendly_name\”: \“Gilma\”, \“hidden\”: true }, \“entity_id\”: \“group.bedroom_gilma\”, \“last_updated\”: \“201 7-05-21T20:07:24.844774+00:00\”}, {\“last_changed\”: \“2017-05-21T20:07: 05.932964+00:00\”, \“state\”: \“off\”, \“attributes\”: {\“last_t riggered\”: null, \“friendly_name\”: \“AVR\”}, \“entity_id\”: \" script.avr\", \“last_updated\”: \“2017-05-21T20:07:05.932964+00:00\”}, {\“last_changed\”: \“2017-05-21T20:07:06.292607+00:00\”, \“state\”: \“off\”, \“attributes\”: {\“assumed_state\”: true, \“order\”: 2 , \“hidden\”: true, \“view\”: true, \“icon\”: \“mdi:television-gui de\”, \“entity_id\”: [\“switch.tv\”, \“switch.blu_ray\”, \“switc h.wake_on_lan\”, \“media_player.unnamed_device\”, \"media_player.yamaha receiver\”, \“media_player.yamaha_receiver_zone_2\”, \“media_player.cast tv\", \“media_player.cast_audio\”, \“media_player.home_group\”, \“m edia_player.kodi\”, \“climate.gilma_thermo\”, \“sensor.tv_room_temperatu re\”, \“script.avr_cast_audio\”, \“script.avr_server\”], \"friendly name\”: \“TVroom\”}, \“entity_id\”: \“group.tvroom\”, \“last_upd ated\”: \“2017-05-21T20:07:13.427053+00:00\”}, {\“last_changed\”: \” 2017-05-21T20:07:38.613936+00:00\”, \“state\”: \“on\”, \“attributes\ \”: {\“last_triggered\”: null, \“friendly_name\”: \“Motion Kitchen - OFF light\”}, \“entity_id\”: \“automation.motion_kitchen__off_light\”, \“last_updated\”: \“2017-05-21T20:07:38.613936+00:00\”}, {\“last_chan ged\”: \“2017-05-21T20:07:11.840788+00:00\”, \“state\”: \“off\”, \ \“attributes\”: {\“friendly_name\”: \“hallway_motion\”}, \“entity_i d\”: \“switch.hallway_motion\”, \“last_updated\”: \“2017-05-21T20:07 :11.840788+00:00\”}, {\“last_changed\”: \“2017-05-21T20:07:06.320040+00: 00\”, \“state\”: \“off\”, \“attributes\”: {\“auto\”: true, \\ “hidden\”: tr
Still truncated. But that doesn’t really matter. We now know that you have a lot of textual data (possibly the whole state of everything in your installation) in your event-table. And at least in this case it’s MQTT that does that. So now you have to figure out how to stop that from happening.
Something in HA is calling the mqtt publishing service apparently to publish the state of everything in your HA, I agree. Maybe this ?
Yes I had that, I took it out
3GB after 1 night
Now I take out also this
mqtt_export:
publish_topic: "home/states"@anon35356645 did that fix it?
I had alot of problems with Zwave being too slow or not triggering at all, until I started disabling alot of my logging…
Yes it did
It was either
mqtt_export or mqtt_eventstream, or both
now it grows 100MB per day, which is manageable
2 Likes
Had a feeling it was mqtt; glad you got it solved.