joostlek
commented
11 months ago What database are you using?
Closed viu-x closed 6 months ago
joostlek
commented
11 months ago What database are you using?
bdraco
commented
11 months ago Please run this query and see if there are any rows:
select * from statistics_short_term where start_ts is NULL;
This query should not return any rows unless the database is somehow corrupted
viu-x
commented
11 months ago It's a default HA OS installation.
I try to figure out how to connect to that database. Haven't done it yet, because the yellow is only 2 weeks old.
viu-x
commented
11 months ago I have 1000 entries. But all are from 2023-09-16 where I switched to my Yellow and started with a backup from my previous Docker installation.
bdraco
commented
11 months ago It looks like you have statistics data in the old format.
Are there errors in your log about statistics failing to migrate?
Do you have an integration that is manually inserting statistics rows into the database?
viu-x
commented
11 months ago The previously posted logs are the only ones I got when downloading the logs via /config/logs and via SSH I can't find any other.
I don't know if any of my integrations does that. But I think my setup has not many integrations compared to other installations.
I know it wouldn't help to understand the underlying cause for the problem, but I wouldn't mind to loose the data which is currently in that table.
bdraco
commented
11 months ago At some point in the past your statistics failed to migrate to db schema version 34. It likely happened a long time ago before you moved the database.
You could try backing up your database, and than removing from schema_changes table any version 34 and newer, and than restart to force it to try again
viu-x
commented
11 months ago I backed up my database, removed all rows with schema_version >= 34 restarted HA.
Now I have no data in my energy dashboard whatsoever. And the statistics_short_term table has 410k rows of data.
bdraco
commented
11 months ago Do you see log messages about it trying to migrate it the rows?
Are there any errors?
viu-x
commented
11 months ago There are entries that show those migration steps.
After finishing that there are the same recorder errors TypeError: unsupported operand type(s) for +: 'NoneType' and 'float' as before.
2023-10-05 15:48:07.975 WARNING (Recorder) [homeassistant.components.recorder.migration] Database is about to upgrade from schema version: 33 to: 41
2023-10-05 15:48:07.981 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding columns created_ts, start_ts, last_reset_ts to table statistics. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:07.986 WARNING (Recorder) [homeassistant.components.recorder.migration] Column created_ts already exists on statistics, continuing
2023-10-05 15:48:07.989 WARNING (Recorder) [homeassistant.components.recorder.migration] Column start_ts already exists on statistics, continuing
2023-10-05 15:48:07.994 WARNING (Recorder) [homeassistant.components.recorder.migration] Column last_reset_ts already exists on statistics, continuing
2023-10-05 15:48:07.995 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding columns created_ts, start_ts, last_reset_ts to table statistics_short_term. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:08.012 WARNING (Recorder) [homeassistant.components.recorder.migration] Column created_ts already exists on statistics_short_term, continuing
2023-10-05 15:48:08.014 WARNING (Recorder) [homeassistant.components.recorder.migration] Column start_ts already exists on statistics_short_term, continuing
2023-10-05 15:48:08.024 WARNING (Recorder) [homeassistant.components.recorder.migration] Column last_reset_ts already exists on statistics_short_term, continuing
2023-10-05 15:48:08.025 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding index `ix_statistics_start_ts` to table `statistics`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:08.027 WARNING (Recorder) [homeassistant.components.recorder.migration] Index ix_statistics_start_ts already exists on statistics, continuing
2023-10-05 15:48:08.028 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding index `ix_statistics_statistic_id_start_ts` to table `statistics`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:08.031 WARNING (Recorder) [homeassistant.components.recorder.migration] Index ix_statistics_statistic_id_start_ts already exists on statistics, continuing
2023-10-05 15:48:08.031 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding index `ix_statistics_short_term_start_ts` to table `statistics_short_term`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:08.034 WARNING (Recorder) [homeassistant.components.recorder.migration] Index ix_statistics_short_term_start_ts already exists on statistics_short_term, continuing
2023-10-05 15:48:08.035 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding index `ix_statistics_short_term_statistic_id_start_ts` to table `statistics_short_term`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:08.037 WARNING (Recorder) [homeassistant.components.recorder.migration] Index ix_statistics_short_term_statistic_id_start_ts already exists on statistics_short_term, continuing
2023-10-05 15:48:11.760 WARNING (MainThread) [homeassistant.components.mqtt.client] No ACK from MQTT server in 10 seconds (mid: 2)
2023-10-05 15:48:30.210 WARNING (Recorder) [homeassistant.components.recorder.migration] Upgrade to version 34 done
2023-10-05 15:48:30.213 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_statistics_statistic_id_start` from table `statistics`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:30.218 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_statistics_short_term_statistic_id_start` from table `statistics_short_term`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:30.224 WARNING (Recorder) [homeassistant.components.recorder.migration] Upgrade to version 35 done
2023-10-05 15:48:30.225 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding columns context_id_bin, context_user_id_bin, context_parent_id_bin to table states. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:30.228 WARNING (Recorder) [homeassistant.components.recorder.migration] Column context_id_bin already exists on states, continuing
2023-10-05 15:48:30.230 WARNING (Recorder) [homeassistant.components.recorder.migration] Column context_user_id_bin already exists on states, continuing
2023-10-05 15:48:30.232 WARNING (Recorder) [homeassistant.components.recorder.migration] Column context_parent_id_bin already exists on states, continuing
2023-10-05 15:48:30.233 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding columns context_id_bin, context_user_id_bin, context_parent_id_bin to table events. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:30.236 WARNING (Recorder) [homeassistant.components.recorder.migration] Column context_id_bin already exists on events, continuing
2023-10-05 15:48:30.237 WARNING (Recorder) [homeassistant.components.recorder.migration] Column context_user_id_bin already exists on events, continuing
2023-10-05 15:48:30.239 WARNING (Recorder) [homeassistant.components.recorder.migration] Column context_parent_id_bin already exists on events, continuing
2023-10-05 15:48:30.240 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding index `ix_events_context_id_bin` to table `events`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:30.242 WARNING (Recorder) [homeassistant.components.recorder.migration] Index ix_events_context_id_bin already exists on events, continuing
2023-10-05 15:48:30.243 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding index `ix_states_context_id_bin` to table `states`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:30.244 WARNING (Recorder) [homeassistant.components.recorder.migration] Index ix_states_context_id_bin already exists on states, continuing
2023-10-05 15:48:30.248 WARNING (Recorder) [homeassistant.components.recorder.migration] Upgrade to version 36 done
2023-10-05 15:48:30.249 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding columns event_type_id to table events. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:30.252 WARNING (Recorder) [homeassistant.components.recorder.migration] Column event_type_id already exists on events, continuing
2023-10-05 15:48:30.252 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_events_event_type_time_fired_ts` from table `events`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:30.261 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding index `ix_events_event_type_id_time_fired_ts` to table `events`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:30.263 WARNING (Recorder) [homeassistant.components.recorder.migration] Index ix_events_event_type_id_time_fired_ts already exists on events, continuing
2023-10-05 15:48:30.268 WARNING (Recorder) [homeassistant.components.recorder.migration] Upgrade to version 37 done
2023-10-05 15:48:30.268 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding columns metadata_id to table states. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:30.272 WARNING (Recorder) [homeassistant.components.recorder.migration] Column metadata_id already exists on states, continuing
2023-10-05 15:48:30.272 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding index `ix_states_metadata_id_last_updated_ts` to table `states`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:30.273 WARNING (Recorder) [homeassistant.components.recorder.migration] Index ix_states_metadata_id_last_updated_ts already exists on states, continuing
2023-10-05 15:48:30.280 WARNING (Recorder) [homeassistant.components.recorder.migration] Upgrade to version 38 done
2023-10-05 15:48:30.280 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_events_event_type_time_fired_ts` from table `events`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:30.289 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_events_event_type` from table `events`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:30.293 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_events_event_type_time_fired` from table `events`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:30.296 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_events_time_fired` from table `events`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:30.300 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_events_context_user_id` from table `events`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:30.303 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_events_context_parent_id` from table `events`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:30.307 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_states_entity_id_last_updated` from table `states`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:30.310 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_states_last_updated` from table `states`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:30.314 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_states_entity_id` from table `states`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:30.318 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_states_context_user_id` from table `states`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:30.321 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_states_context_parent_id` from table `states`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:30.325 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_states_created_domain` from table `states`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:30.328 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_states_entity_id_created` from table `states`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:30.332 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `states__state_changes` from table `states`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:30.336 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `states__significant_changes` from table `states`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:30.340 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_states_entity_id_created` from table `states`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:30.343 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_statistics_statistic_id_start` from table `statistics`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:30.346 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_statistics_short_term_statistic_id_start` from table `statistics_short_term`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:30.352 WARNING (Recorder) [homeassistant.components.recorder.migration] Upgrade to version 39 done
2023-10-05 15:48:30.353 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_events_event_type_id` from table `events`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:30.357 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_states_metadata_id` from table `states`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:30.361 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_statistics_metadata_id` from table `statistics`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:30.364 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_statistics_short_term_metadata_id` from table `statistics_short_term`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:30.369 WARNING (Recorder) [homeassistant.components.recorder.migration] Upgrade to version 40 done
2023-10-05 15:48:30.370 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding index `ix_event_types_event_type` to table `event_types`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:30.372 WARNING (Recorder) [homeassistant.components.recorder.migration] Index ix_event_types_event_type already exists on event_types, continuing
2023-10-05 15:48:30.373 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding index `ix_states_meta_entity_id` to table `states_meta`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:30.374 WARNING (Recorder) [homeassistant.components.recorder.migration] Index ix_states_meta_entity_id already exists on states_meta, continuing
2023-10-05 15:48:30.378 WARNING (Recorder) [homeassistant.components.recorder.migration] Upgrade to version 41 done
2023-10-05 15:48:30.500 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_states_context_id` from table `states`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:30.510 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_events_context_id` from table `events`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:32.544 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: unsupported operand type(s) for +: 'NoneType' and 'float'
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 130, in session_scope
yield session
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 1941, in get_latest_short_term_statistics
return _sorted_statistics_to_dict(
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 2110, in _sorted_statistics_to_dict
"end": start_ts + table_duration_seconds,
~~~~~~~~~^~~~~~~~~~~~~~~~~~~~~~~~
TypeError: unsupported operand type(s) for +: 'NoneType' and 'float'
2023-10-05 15:48:32.553 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: unsupported operand type(s) for +: 'NoneType' and 'float'
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 130, in session_scope
yield session
File "/usr/src/homeassistant/homeassistant/components/sensor/recorder.py", line 402, in compile_statistics
compiled = _compile_statistics(hass, session, start, end)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/sensor/recorder.py", line 483, in _compile_statistics
last_stats = statistics.get_latest_short_term_statistics(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 1941, in get_latest_short_term_statistics
return _sorted_statistics_to_dict(
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 2110, in _sorted_statistics_to_dict
"end": start_ts + table_duration_seconds,
~~~~~~~~~^~~~~~~~~~~~~~~~~~~~~~~~
TypeError: unsupported operand type(s) for +: 'NoneType' and 'float'
2023-10-05 15:48:32.561 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: unsupported operand type(s) for +: 'NoneType' and 'float'
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 130, in session_scope
yield session
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 454, in compile_missing_statistics
modified_statistic_ids = _compile_statistics(
^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 528, in _compile_statistics
compiled: PlatformCompiledStatistics = platform_compile_statistics(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/sensor/recorder.py", line 402, in compile_statistics
compiled = _compile_statistics(hass, session, start, end)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/sensor/recorder.py", line 483, in _compile_statistics
last_stats = statistics.get_latest_short_term_statistics(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 1941, in get_latest_short_term_statistics
return _sorted_statistics_to_dict(
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 2110, in _sorted_statistics_to_dict
"end": start_ts + table_duration_seconds,
~~~~~~~~~^~~~~~~~~~~~~~~~~~~~~~~~
TypeError: unsupported operand type(s) for +: 'NoneType' and 'float'
2023-10-05 15:48:32.571 ERROR (Recorder) [homeassistant.components.recorder.core] Error while processing event CompileMissingStatisticsTask(): unsupported operand type(s) for +: 'NoneType' and 'float'
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 893, in _guarded_process_one_task_or_recover
self._process_one_task_or_recover(task)
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 904, in _process_one_task_or_recover
return task.run(self)
^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/tasks.py", line 177, in run
if statistics.compile_missing_statistics(instance):
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 609, in wrapper
return job(instance, *args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 454, in compile_missing_statistics
modified_statistic_ids = _compile_statistics(
^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 528, in _compile_statistics
compiled: PlatformCompiledStatistics = platform_compile_statistics(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/sensor/recorder.py", line 402, in compile_statistics
compiled = _compile_statistics(hass, session, start, end)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/sensor/recorder.py", line 483, in _compile_statistics
last_stats = statistics.get_latest_short_term_statistics(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 1941, in get_latest_short_term_statistics
return _sorted_statistics_to_dict(
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 2110, in _sorted_statistics_to_dict
"end": start_ts + table_duration_seconds,
~~~~~~~~~^~~~~~~~~~~~~~~~~~~~~~~~
TypeError: unsupported operand type(s) for +: 'NoneType' and 'float'
2023-10-05 15:48:36.787 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_statistics_start` from table `statistics`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:48:36.792 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_statistics_short_term_start` from table `statistics_short_term`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:49:38.500 WARNING (MainThread) [homeassistant.helpers.entity] Update of sensor.wechselrichter_pv1_voltage is taking over 10 seconds
2023-10-05 15:50:10.669 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: unsupported operand type(s) for +: 'NoneType' and 'float'
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 130, in session_scope
yield session
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 1941, in get_latest_short_term_statistics
return _sorted_statistics_to_dict(
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 2110, in _sorted_statistics_to_dict
"end": start_ts + table_duration_seconds,
~~~~~~~~~^~~~~~~~~~~~~~~~~~~~~~~~
TypeError: unsupported operand type(s) for +: 'NoneType' and 'float'
2023-10-05 15:50:10.674 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: unsupported operand type(s) for +: 'NoneType' and 'float'
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 130, in session_scope
yield session
File "/usr/src/homeassistant/homeassistant/components/sensor/recorder.py", line 402, in compile_statistics
compiled = _compile_statistics(hass, session, start, end)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/sensor/recorder.py", line 483, in _compile_statistics
last_stats = statistics.get_latest_short_term_statistics(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 1941, in get_latest_short_term_statistics
return _sorted_statistics_to_dict(
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 2110, in _sorted_statistics_to_dict
"end": start_ts + table_duration_seconds,
~~~~~~~~~^~~~~~~~~~~~~~~~~~~~~~~~
TypeError: unsupported operand type(s) for +: 'NoneType' and 'float'
2023-10-05 15:50:10.681 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: unsupported operand type(s) for +: 'NoneType' and 'float'
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 130, in session_scope
yield session
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 477, in compile_statistics
modified_statistic_ids = _compile_statistics(
^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 528, in _compile_statistics
compiled: PlatformCompiledStatistics = platform_compile_statistics(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/sensor/recorder.py", line 402, in compile_statistics
compiled = _compile_statistics(hass, session, start, end)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/sensor/recorder.py", line 483, in _compile_statistics
last_stats = statistics.get_latest_short_term_statistics(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 1941, in get_latest_short_term_statistics
return _sorted_statistics_to_dict(
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 2110, in _sorted_statistics_to_dict
"end": start_ts + table_duration_seconds,
~~~~~~~~~^~~~~~~~~~~~~~~~~~~~~~~~
TypeError: unsupported operand type(s) for +: 'NoneType' and 'float'
2023-10-05 15:50:10.688 ERROR (Recorder) [homeassistant.components.recorder.core] Error while processing event StatisticsTask(start=datetime.datetime(2023, 10, 5, 13, 45, tzinfo=datetime.timezone.utc), fire_events=True): unsupported operand type(s) for +: 'NoneType' and 'float'
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 893, in _guarded_process_one_task_or_recover
self._process_one_task_or_recover(task)
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 904, in _process_one_task_or_recover
return task.run(self)
^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/tasks.py", line 165, in run
if statistics.compile_statistics(instance, self.start, self.fire_events):
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 609, in wrapper
return job(instance, *args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 477, in compile_statistics
modified_statistic_ids = _compile_statistics(
^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 528, in _compile_statistics
compiled: PlatformCompiledStatistics = platform_compile_statistics(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/sensor/recorder.py", line 402, in compile_statistics
compiled = _compile_statistics(hass, session, start, end)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/sensor/recorder.py", line 483, in _compile_statistics
last_stats = statistics.get_latest_short_term_statistics(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 1941, in get_latest_short_term_statistics
return _sorted_statistics_to_dict(
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 2110, in _sorted_statistics_to_dict
"end": start_ts + table_duration_seconds,
~~~~~~~~~^~~~~~~~~~~~~~~~~~~~~~~~
TypeError: unsupported operand type(s) for +: 'NoneType' and 'float'
2023-10-05 15:53:45.721 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_states_entity_id_last_updated_ts` from table `states`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-10-05 15:55:10.271 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: unsupported operand type(s) for +: 'NoneType' and 'float'
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 130, in session_scope
yield session
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 1941, in get_latest_short_term_statistics
return _sorted_statistics_to_dict(
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 2110, in _sorted_statistics_to_dict
"end": start_ts + table_duration_seconds,
~~~~~~~~~^~~~~~~~~~~~~~~~~~~~~~~~
TypeError: unsupported operand type(s) for +: 'NoneType' and 'float'
2023-10-05 15:55:10.276 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: unsupported operand type(s) for +: 'NoneType' and 'float'
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 130, in session_scope
yield session
File "/usr/src/homeassistant/homeassistant/components/sensor/recorder.py", line 402, in compile_statistics
compiled = _compile_statistics(hass, session, start, end)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/sensor/recorder.py", line 483, in _compile_statistics
last_stats = statistics.get_latest_short_term_statistics(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 1941, in get_latest_short_term_statistics
return _sorted_statistics_to_dict(
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 2110, in _sorted_statistics_to_dict
"end": start_ts + table_duration_seconds,
~~~~~~~~~^~~~~~~~~~~~~~~~~~~~~~~~
TypeError: unsupported operand type(s) for +: 'NoneType' and 'float'
2023-10-05 15:55:10.283 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: unsupported operand type(s) for +: 'NoneType' and 'float'
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 130, in session_scope
yield session
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 477, in compile_statistics
modified_statistic_ids = _compile_statistics(
^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 528, in _compile_statistics
compiled: PlatformCompiledStatistics = platform_compile_statistics(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/sensor/recorder.py", line 402, in compile_statistics
compiled = _compile_statistics(hass, session, start, end)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/sensor/recorder.py", line 483, in _compile_statistics
last_stats = statistics.get_latest_short_term_statistics(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 1941, in get_latest_short_term_statistics
return _sorted_statistics_to_dict(
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 2110, in _sorted_statistics_to_dict
"end": start_ts + table_duration_seconds,
~~~~~~~~~^~~~~~~~~~~~~~~~~~~~~~~~
TypeError: unsupported operand type(s) for +: 'NoneType' and 'float'
2023-10-05 15:55:10.291 ERROR (Recorder) [homeassistant.components.recorder.core] Error while processing event StatisticsTask(start=datetime.datetime(2023, 10, 5, 13, 50, tzinfo=datetime.timezone.utc), fire_events=True): unsupported operand type(s) for +: 'NoneType' and 'float'
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 893, in _guarded_process_one_task_or_recover
self._process_one_task_or_recover(task)
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 904, in _process_one_task_or_recover
return task.run(self)
^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/tasks.py", line 165, in run
if statistics.compile_statistics(instance, self.start, self.fire_events):
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 609, in wrapper
return job(instance, *args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 477, in compile_statistics
modified_statistic_ids = _compile_statistics(
^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 528, in _compile_statistics
compiled: PlatformCompiledStatistics = platform_compile_statistics(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/sensor/recorder.py", line 402, in compile_statistics
compiled = _compile_statistics(hass, session, start, end)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/sensor/recorder.py", line 483, in _compile_statistics
last_stats = statistics.get_latest_short_term_statistics(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 1941, in get_latest_short_term_statistics
return _sorted_statistics_to_dict(
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 2110, in _sorted_statistics_to_dict
"end": start_ts + table_duration_seconds,
~~~~~~~~~^~~~~~~~~~~~~~~~~~~~~~~~
TypeError: unsupported operand type(s) for +: 'NoneType' and 'float'
2023-10-05 15:56:03.064 WARNING (SyncWorker_6) [custom_components.solarman.solarman] Querying [1 - 125] failed with exception [V5FrameError: V5 frame contains invalid sequence number]
2023-10-05 15:56:03.565 WARNING (SyncWorker_6) [custom_components.solarman.solarman] Querying [1 - 125] failed, [1] retry attempts left
2023-10-05 16:00:10.285 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: unsupported operand type(s) for +: 'NoneType' and 'float'
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 130, in session_scope
yield session
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 1941, in get_latest_short_term_statistics
return _sorted_statistics_to_dict(
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 2110, in _sorted_statistics_to_dict
"end": start_ts + table_duration_seconds,
~~~~~~~~~^~~~~~~~~~~~~~~~~~~~~~~~
TypeError: unsupported operand type(s) for +: 'NoneType' and 'float'
2023-10-05 16:00:10.290 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: unsupported operand type(s) for +: 'NoneType' and 'float'
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 130, in session_scope
yield session
File "/usr/src/homeassistant/homeassistant/components/sensor/recorder.py", line 402, in compile_statistics
compiled = _compile_statistics(hass, session, start, end)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/sensor/recorder.py", line 483, in _compile_statistics
last_stats = statistics.get_latest_short_term_statistics(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 1941, in get_latest_short_term_statistics
return _sorted_statistics_to_dict(
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 2110, in _sorted_statistics_to_dict
"end": start_ts + table_duration_seconds,
~~~~~~~~~^~~~~~~~~~~~~~~~~~~~~~~~
TypeError: unsupported operand type(s) for +: 'NoneType' and 'float'
2023-10-05 16:00:10.297 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: unsupported operand type(s) for +: 'NoneType' and 'float'
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 130, in session_scope
yield session
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 477, in compile_statistics
modified_statistic_ids = _compile_statistics(
^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 528, in _compile_statistics
compiled: PlatformCompiledStatistics = platform_compile_statistics(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/sensor/recorder.py", line 402, in compile_statistics
compiled = _compile_statistics(hass, session, start, end)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/sensor/recorder.py", line 483, in _compile_statistics
last_stats = statistics.get_latest_short_term_statistics(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 1941, in get_latest_short_term_statistics
return _sorted_statistics_to_dict(
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 2110, in _sorted_statistics_to_dict
"end": start_ts + table_duration_seconds,
~~~~~~~~~^~~~~~~~~~~~~~~~~~~~~~~~
TypeError: unsupported operand type(s) for +: 'NoneType' and 'float'
2023-10-05 16:00:10.305 ERROR (Recorder) [homeassistant.components.recorder.core] Error while processing event StatisticsTask(start=datetime.datetime(2023, 10, 5, 13, 55, tzinfo=datetime.timezone.utc), fire_events=True): unsupported operand type(s) for +: 'NoneType' and 'float'
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 893, in _guarded_process_one_task_or_recover
self._process_one_task_or_recover(task)
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 904, in _process_one_task_or_recover
return task.run(self)
^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/tasks.py", line 165, in run
if statistics.compile_statistics(instance, self.start, self.fire_events):
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 609, in wrapper
return job(instance, *args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 477, in compile_statistics
modified_statistic_ids = _compile_statistics(
^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 528, in _compile_statistics
compiled: PlatformCompiledStatistics = platform_compile_statistics(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/sensor/recorder.py", line 402, in compile_statistics
compiled = _compile_statistics(hass, session, start, end)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/sensor/recorder.py", line 483, in _compile_statistics
last_stats = statistics.get_latest_short_term_statistics(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 1941, in get_latest_short_term_statistics
return _sorted_statistics_to_dict(
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 2110, in _sorted_statistics_to_dict
"end": start_ts + table_duration_seconds,
~~~~~~~~~^~~~~~~~~~~~~~~~~~~~~~~~
TypeError: unsupported operand type(s) for +: 'NoneType' and 'float'
8OND007
commented
11 months ago same problem here. Enery dashboard is broken since 2023.10 update. I'm using HA OS with just the regular Sqlite DB. Running in VM on ESXi on a NUC.
I only see now 1 big stacked energy bar, instead of spread bars over the day:
select * from statistics_short_term where start_ts is NULL; returned no result.
M-See
commented
11 months ago In my case (even after downgrading to core-2023.9.x again) the file editor is not showing any files anymore. This .10 update broke also some custom integrations, but seems to have some bigger impact even after downgrading from it..
Running HAOS
joostlek
commented
11 months ago @M-See that's a completely different issue, please create a new one
joostlek
commented
11 months ago @8OND007 do you see any strange stuff with the entities that you use to feed the dashboard?
8OND007
commented
11 months ago @8OND007 do you see any strange stuff with the entities that you use to feed the dashboard?
no nothing special. I use a AI Camera (AI on the Edge) to digitize my analoge ferraris meter. Since I also have Solar inverter, the analog Ferraris counter can count backwards. So with some Template sensors & calculations I end up with 2 totaliser sensors :
Positive energy total (kWh) from grid to house:
Negative energy total (kWh) from house to grid:
Both are totalisers, but can go up and down. (so not exclusively increasing total).
The trend and values look exactly the same as with 2023.9.x. Only now it appears all data is stacked onto 1 bar, instead of calculation each hours the energy use. The Solar producion is still calculated correctly. I read on the forum that more people experience a broken Energy Dashboard since this october update.
bdraco
commented
11 months ago @8OND007 How are you getting the stats into Home Assistant?
bdraco
commented
11 months ago If they are externally inserted, I think it will be fixed by https://github.com/home-assistant/core/pull/101490
But that also means its not the same issue as the op since the op's issue is likely because of failed migration or manual insertion by a custom component.
bdraco
commented
11 months ago If they are externally inserted, I think it will be fixed by https://github.com/home-assistant/core/pull/101490
But that also means its not the same issue as the op since the op's issue is likely because of failed migration.
bdraco
commented
11 months ago If they are externally inserted, I think it will be fixed by https://github.com/home-assistant/core/pull/101490
But that also means its not the same issue as the op since the op's issue is likely because of failed migration.
bdraco
commented
11 months ago diff --git a/homeassistant/components/recorder/statistics.py b/homeassistant/components/recorder/statistics.py
index 24fb209ae07..dc8a25d3ac5 100644
--- a/homeassistant/components/recorder/statistics.py
+++ b/homeassistant/components/recorder/statistics.py
@@ -2370,6 +2370,7 @@ def _find_latest_short_term_statistic_for_metadata_id_stmt(
StatisticsShortTerm.id,
)
.where(StatisticsShortTerm.metadata_id == metadata_id)
+ .where(StatisticsShortTerm.start_ts.is_not(None))
.order_by(StatisticsShortTerm.start_ts.desc())
.limit(1)
)This change would filter out the bad rows, but we first need to understand how they are getting into the database itself.
Do you know which entity they are coming from?
home-assistant[bot]
commented
11 months ago Hey there @home-assistant/core, mind taking a look at this issue as it has been labeled with an integration (recorder) you are listed as a code owner for? Thanks!
(message by CodeOwnersMention)
recorder documentation recorder source (message by IssueLinks)
8OND007
commented
11 months ago today I checked again, and the energy graph looks normal:
So I went into devellopers statistics fix tool and found out around the time I updated to 2023.10 there was 1 out of range value for both positive and negative calculated statistics :
I removed this 2 out of range values and then the graph looks normal yesterday:
The source totalisers looked normal and nothing special happend during the update to my values :
So hopefully it was 1 bad statistics calculation by HA.
My positive and negative grid use totalisers are calculated by 2 template sensors :
template:
- sensor:
- name: "positive energy total from grid to house"
unique_id: "positive_energy_total_from_grid_to_house"
unit_of_measurement: "kWh"
state_class: total_increasing
device_class: energy
state: >
{% if states('sensor.dagverbruik_fluvius') | float > 0.0 %}
{{ (states('sensor.dagverbruik_fluvius') | float) | abs }}
{% else -%}
0
{% endif %}
availability: >-
{{ is_number(states('sensor.dagverbruik_fluvius')) }}
- name: "negative energy total from house to grid"
unique_id: "negative_energy_total_from_house_to_grid"
unit_of_measurement: "kWh"
state_class: total_increasing
device_class: energy
state: >
{% if states('sensor.dagverbruik_fluvius') | float < 0.0 %}
{{ (states('sensor.dagverbruik_fluvius') | float) | abs }}
{% else -%}
0
{% endif %}
availability: >-
{{ is_number(states('sensor.dagverbruik_fluvius')) }}the daily counter used as a source above comes from a utility_meter:
utility_meter:
dagverbruik_fluvius:
source: sensor.main_meter_fluvius
cycle: daily
net_consumption: Truethe source sensor above comes from a MQTT read value from an ESP32 AI on the edge camera:
mqtt:
- name: "Main meter Fluvius"
unique_id: powermeter-main_value
state_topic: "powermeter/main/value"
value_template: "{{ (value | float) }}"
unit_of_measurement: 'kWh'
device_class: energy
state_class: total # total can go up and down
last_reset_value_template: 1970-01-01T00:00:00+00:00
icon: "mdi:flash"
availability_topic: powermeter/connection
payload_available: connected
payload_not_available: connection lost
pergolafabio
commented
11 months ago Hey , just out of interest, you live in Belgium? ,I read my Ferraris and Solars with a smappee, it works with clamps... Your camera, can actually see the direction of the Ferraris? I had a youless before, and making the sums was not possible, because that camara/sensor couldn't differentiate the direction, making the youless useless :-)
8OND007
commented
11 months ago Hey , just out of interest, you live in Belgium? ,I read my Ferraris and Solars with a smappee, it works with clamps... Your camera, can actually see the direction of the Ferraris? I had a youless before, and making the sums was not possible, because that camara/sensor couldn't differentiate the direction, making the youless useless :-)
yes, I live in Belgium, I use following ESP32-CAM project : https://github.com/jomjol/AI-on-the-edge-device The AI just converts the image of the Ferraris counter to a digital value on my MQTT broker. Since it just converts the value it also sees the value going up or down depending on energy use or production sum. It works great, even the number behind the digit is calculated (while the wheel in fact is then in between values). As you can see in my screenshots above there are some small spikes sometimes because of misrecogniton. As I see on Youless camera sensor, it only monitors the spinning disc and not the actual wheel numbers. I also tried that route, but then indeed you need at least 2 sensors on the spinning disc. I used that with 2 laser photo sensors to detect direction & current wattage use which worked great, but the laser light was sometime not good reflected. Also not good possible to attach in front of the Fluvius plastic cabinet :-D
viu-x
commented
11 months ago diff --git a/homeassistant/components/recorder/statistics.py b/homeassistant/components/recorder/statistics.py index 24fb209ae07..dc8a25d3ac5 100644 --- a/homeassistant/components/recorder/statistics.py +++ b/homeassistant/components/recorder/statistics.py @@ -2370,6 +2370,7 @@ def _find_latest_short_term_statistic_for_metadata_id_stmt( StatisticsShortTerm.id, ) .where(StatisticsShortTerm.metadata_id == metadata_id) + .where(StatisticsShortTerm.start_ts.is_not(None)) .order_by(StatisticsShortTerm.start_ts.desc()) .limit(1) )This change would filter out the bad rows, but we first need to understand how they are getting into the database itself.
Do you know which entity they are coming from?
@bdraco is this question regarded towards me? cause the last comment until then have been clearly for @8OND007 issue
8OND007
commented
11 months ago today I installed latest 2023.10.1 update of HA.
And again got a big stacked spike of my energy use values.
I checked the statistics again and on both positive and negative sensors there was bad statistics value :
Removed those manually and energy trend looks normal again.
So statistics calculation bug is not fixed yet.
bdraco
commented
11 months ago diff --git a/homeassistant/components/recorder/statistics.py b/homeassistant/components/recorder/statistics.py index 24fb209ae07..dc8a25d3ac5 100644 --- a/homeassistant/components/recorder/statistics.py +++ b/homeassistant/components/recorder/statistics.py @@ -2370,6 +2370,7 @@ def _find_latest_short_term_statistic_for_metadata_id_stmt( StatisticsShortTerm.id, ) .where(StatisticsShortTerm.metadata_id == metadata_id) + .where(StatisticsShortTerm.start_ts.is_not(None)) .order_by(StatisticsShortTerm.start_ts.desc()) .limit(1) )This change would filter out the bad rows, but we first need to understand how they are getting into the database itself.
Do you know which entity they are coming from?
@bdraco is this question regarded towards me? cause the last comment until then have been clearly for @8OND007 issue
Yes. It's for anyone with the None traceback
bdraco
commented
11 months ago today I installed latest 2023.10.1 update of HA.
And again got a big stacked spike of my energy use values.
I checked the statistics again and on both positive and negative sensors there was bad statistics value :
Removed those manually and energy trend looks normal again.
So statistics calculation bug is not fixed yet.
Which integration provides thoose statistics?
viu-x
commented
11 months ago @bdraco I ran the following query to see which sensors are still present in statistics_short_term.
SELECT statistic_id, count(*) as cnt
FROM "statistics_short_term"
LEFT JOIN statistics_meta ON statistics_meta.id=metadata_id
WHERE start_ts is NULL
GROUP BY metadata_id
ORDER BY cnt DESCCurrently I have 220k rows in that table. And they come from all over the place. For integrations there are sensors from Bosch, Tasmota, Tibber, Solarman, Synology as well as custom helper sensors.
justinmaiuto
commented
11 months ago
having the same issue on 2023.10.0 and 2023.10.1
My energy dashboard is fed by these:
bdraco
commented
11 months ago @8OND007 since you don't have the type error would you please open a new issue
We are going to have a lot of trouble figuring out the root cause if we try to solve two different problems in the same issue
bdraco
commented
11 months ago
having the same issue on 2023.10.0 and 2023.10.1
My energy dashboard is fed by these:
Do you have the same TypeError traceback in the log?
Did you migrate your database from SQLite to something else in the past?
justinmaiuto
commented
11 months ago no TypeError in the log and I did not attempt any migration in the past
bdraco
commented
11 months ago no TypeError in the log and I did not attempt any migration in the past
Please continue in a new issue or the one I asked 8OND007 to open above
justinmaiuto
commented
11 months ago no TypeError in the log and I did not attempt any migration in the past
Please continue in a new issue or the one I asked 8OND007 to open above
miroslavvuj
commented
11 months ago Same issue here, energy shows nothing for the last couple of days, since Oct 5. Same errors in the logs related to statistics.
Is it safe to truncate all statistics tables? I wouldn't mind starting from scratch, but unsure if doing this will break something else...
bdraco
commented
11 months ago Assuming everyone has the same reason for their database migration failure, I'm going to need a backup of a database that failed to migrate to be able to come up a solution for this problem so I can dig through it and figure out why.
If that's something you can provide, please do a backup, extract the database, and send it via Dropbox/google drive/other secure method to nick@koston.org
sdirkwinkel
commented
11 months ago I just sent you a mariadb backup
bdraco
commented
11 months ago I just sent you a mariadb backup
Thanks. I'll load it into my lab and analyze it later today
bdraco
commented
11 months ago @sdirkwinkel Did you by chance downgrade to an older version of Home Assistant around March 3/4/5 of this year?
sdirkwinkel
commented
11 months ago @sdirkwinkel Did you by chance downgrade to an older version of Home Assistant around March 3/4/5 of this year?
Yes, I apparently restored a backup on march 2. I also seem to have grepped the logs for migration errors
bdraco
commented
11 months ago Whats happening is there are rows from the old version and the new version in the table.
When you upgraded again the rows created during the downgrade don't get migrated so they cause the query to fail because we don't expect any rows in the old format in the table. In the future if you do additional downgrades, be sure to restore the database to make the version of Home Assistant you are downgrading to as running the new db format with an older version is likely to cause other issues as well.
https://github.com/home-assistant/core/pull/101781 will do one more migration of the rows in 2023.11.x to get the missing rows migrated.
spanzetta
commented
11 months ago I've reported a similar issue ... I can't figure out how to fix some wrong values See #101750
spanzetta
commented
11 months ago Guys, today (many days after the upgrade) I get other totally wrong value in Energy dashboard.. How to fix those wrong values? It seems the situation is becoming worst and worst
UPDATE: I am now updating to 2023.10.2.. (just realised this new release) Will see if it will fix it..
2° UPDATE: NO, the situation hasn't changed and I see - for today - TOTALLY WRONG energy data..
Pls fix it!!!
viu-x
commented
11 months ago Assuming everyone has the same reason for their database migration failure, I'm going to need a backup of a database that failed to migrate to be able to come up a solution for this problem so I can dig through it and figure out why.
If that's something you can provide, please do a backup, extract the database, and send it via Dropbox/google drive/other secure method to nick@koston.org
Sorry for my late reply, have been knocked out from a cold. You'll get an email in the next couple of minutes with a link to my database backup.
viu-x
commented
11 months ago @spanzetta I think this is normal behavior when the energy gets its first data or if there are no previous values. This should regulate itself tomorrow because then there are only delta values and those high initial values from your sensors.
spanzetta
commented
11 months ago Hi @viu-x I manually changed the wrong value with developer tools -> statistics (they was all at 06:30 this morning).. Did I make a wrong thing? Will I have again the problem in the next days ? (now I have 2023.10.2) Thanks
PS I didn't fix yet the negative values of the "monitor individual devices".. I will wait until tomorrow morning to see if they will automatically fix as you said..
viu-x
commented
11 months ago @spanzetta for the negative values for "monitor individual devices" I don't know. Haven't that before.
But for the energy usage and solar production graphs I'm pretty sure they will be normal again. Currently the energy dashboard thinks that you consumed 1279 kWh between 6-7am for example. Because it is the first value it saw from that sensor. Perhaps because of your change value through the developer tools. The values after 7am should already be correct relative values. But in the graph the are not "visible" because the bar is super tiny in relation to that spike. So if you hover the mini bars after 7am you should see reasonable values per hours already.
spanzetta
commented
11 months ago @viu-x ok thanks I fixed manually also the individual device (they was also wrong at 06:30) this morning and now everything looks normal...
But I still can't figure out the wrong COST of october 9th ..
I can't see cost entry in Statistics.. how can I manually adjust them?
Thanks
bdraco
commented
11 months ago Thanks @viu-x
I'll try to find some time this weekend to triage your data
The problem
After updating the Supervisor and Core in my HomeAssistant OS on my Yellow to latest version 2023.10.0 the energy dashboard did not update anymore with new data. Every sensor normally delivers data, but they are not reflected in the dashboard.
Restart of HA OS did not resolve the issue.
Seeing error messages in the log.
Same behavior like #101103 .
What version of Home Assistant Core has the issue?
core-2023.10.0
What was the last working version of Home Assistant Core?
core-2023.9.x
What type of installation are you running?
Home Assistant OS
Integration causing the issue
No response
Link to integration documentation on our website
No response
Diagnostics information
No response
Example YAML snippet
No response
Anything in the logs that might be useful for us?
Additional information
No response