Statistics Graphs card showing only "Loading statistics..." after 2023.3.3 update #89489
Labels
Comments
|
I was fooling around a bit with the 'broken' card on one of my dashboards, and on a whim I changed the reporting interval from Hour to 5 Minutes, and on another from Day to 5 Minutes, and to my surprise, on the same card that was just displaying "Loading statistics...", suddenly a graph appeared again. It's not very useful to have a 30 day overview with 5 minute intervals, but it seems to be an indication that something goes wrong when aggregating the 'raw' numbers into the higher levels of aggregation. |
|
Hey there @home-assistant/core, mind taking a look at this issue as it has been labeled with an integration ( Code owner commandsCode owners of
(message by CodeOwnersMention) recorder documentation |
|
Probably the same root cause as #89170 |
|
We will push a new version with sqlalchemy 2.0.6 when it's released which has sqlalchemy/sqlalchemy#9461 fixed |
|
Thanks for the note, good to know there is a fix on the horizon. Feel free to let me know if I can help with testing. Either way, I will report back here if the issue is fixed after a future release with that new version of sqlalchemy included. |
|
Much to my surprise, the affected Statistics Graphs cards have suddenly started to work again. I haven't made any changes to them, so I don't fully understand how this can have happened. The only thing that I can think about that may have contributed to this, is that I had to do a restart of Home Assistant for an entirely unrelated reason. Perhaps it was a data-related thing all along, some kind of data migration that didn't start or didn't complete. Perhaps I should have tried if restarting would have fixed it in the first place, but I'm fairly new to Home Assistant, and as such somewhat apprehensive to do anything that might have unintended consequences. I apologize to anyone whose time I may have wasted. |
|
It's a entirely expected that it only happens sometimes since it's a concurrency problem with sqlalchemy. You have to have some bad luck with two or more queries of the same type happening to run at the same time with different branching and the query cache gets in a bad state. |
|
I got the same Issue, some statistics work some doesn't. After restarting the host some of the not working statics worked again but others don't. I guess this proves @bdraco theory |
|
Testing #89170 I've run into this problem, but with a different HA log error. It seems to me, that there can be a pure core issue also, not only sqlalchemy, but this is only a guess on my side. If sqlalchemy can explain this, forget about my comment. With core 2023.3.3 I have no visible errors, even after multiple days and 10+ restarts. I've restored core 2023.3.2, and I've got this "never loading statistics" error. There are strange errors in the HA log on each browser refresh:
And the queries for these statistics graphs never reach the database: Click to open
Below is a query log, when there are no problems (it seems I pressed refresh 2 times, so everything is duplicated, sorry): Click to openAdditional investigation: I see that each graph has 2 data fetching select query, one with an INNER JOIN statement, and this INNER JOIN contains a I've added some indentation manually. |
|
All of the above could be (likely) caused by the concurrency issue with sqlalchemy so I think we need to ask if we can get a 2.0.6 release sooner. |
|
I just downgraded to 2023.3.1, statistics are working again. |
|
SQLAlchemy 2.0.6 upgrade will be part of HA 2023.3.4, which will be released today. |
|
The bump to sqlalchemy 2.0.6 via #89650 should fix this in 2023.3.4. Once its released, if there is still a problem after upgrading, please let us know and we will get this issue reopened. |
|
Even though my statistics graphs card started working again after a restart while still on 2023.3.3, I was a bit apprehensive to update to 2023.3.4. But I'm happy to report that the graphs still work on 2023.3.4 as well. |
Sign up for free
to subscribe to this conversation on GitHub.
Already have an account?
Sign in.
The problem
I updated to 2023.3.3 late last night Europe/Amsterdam time (very soon after it was released) and now I find that the Statistics Graph card is having issues displaying the stats. This happens to all cards of this type. Example:
The entity itself is working well and reporting, also in the History:
I did not change anything to the way the cards are defined, example yaml below. The issue occurs in the web app, both local and over a Cloudflared tunnel (and thus also on my Android app).
The first occurence of the log entry I found (and 148 similar items) was indeed a few seconds after the update to 2023.3.3. The log entries appear to me (non coder) to point to a bug. I haven't looked at the code and the indicated line numbers, I'm not sufficiently familiar with Python.
What version of Home Assistant Core has the issue?
2023.3.3
What was the last working version of Home Assistant Core?
2023.3.2
What type of installation are you running?
Home Assistant OS
Integration causing the issue
n/a (I think)
Link to integration documentation on our website
No response
Diagnostics information
No response
Example YAML snippet
Anything in the logs that might be useful for us?
Logger: homeassistant.components.websocket_api.http.connection Source: components/recorder/statistics.py:2076 Integration: Home Assistant WebSocket API (documentation, issues) First occurred: March 9, 2023 at 23:10:16 (148 occurrences) Last logged: 09:07:38 [140131671014176] Error handling message: Unknown error (unknown_error) <username redacted> from <IP address redacted> (Mozilla/5.0 (Linux; Android 13; Pixel 5 Build/TQ1A.230205.002; wv) AppleWebKit/537.36 (KHTML, like Gecko) Version/4.0 Chrome/110.0.5481.154 Mobile Safari/537.36 Home Assistant/2023.1.1-3124 (Android 13; Pixel 5)) [140131670678960] Error handling message: Unknown error (unknown_error) <username redacted> from <IP address redacted> (Mozilla/5.0 (Linux; Android 13; Pixel 5 Build/TQ1A.230205.002; wv) AppleWebKit/537.36 (KHTML, like Gecko) Version/4.0 Chrome/110.0.5481.154 Mobile Safari/537.36 Home Assistant/2023.1.1-3124 (Android 13; Pixel 5)) [140131506440320] Error handling message: Unknown error (unknown_error) <username redacted> from <IP address redacted> (Mozilla/5.0 (Linux; Android 13; Pixel 5 Build/TQ1A.230205.002; wv) AppleWebKit/537.36 (KHTML, like Gecko) Version/4.0 Chrome/110.0.5481.154 Mobile Safari/537.36 Home Assistant/2023.1.1-3124 (Android 13; Pixel 5)) [140131507289152] Error handling message: Unknown error (unknown_error) <username redacted> from <IP address redacted> (Mozilla/5.0 (Linux; Android 13; Pixel 5 Build/TQ1A.230205.002; wv) AppleWebKit/537.36 (KHTML, like Gecko) Version/4.0 Chrome/110.0.5481.154 Mobile Safari/537.36 Home Assistant/2023.1.1-3124 (Android 13; Pixel 5)) [140131523464208] Error handling message: Unknown error (unknown_error) <username redacted> from 192.168.86.50 (Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/111.0.0.0 Safari/537.36) Traceback (most recent call last): File "/usr/src/homeassistant/homeassistant/components/websocket_api/decorators.py", line 26, in _handle_async_response await func(hass, connection, msg) File "/usr/src/homeassistant/homeassistant/components/recorder/websocket_api.py", line 238, in ws_get_statistics_during_period await ws_handle_get_statistics_during_period(hass, connection, msg) File "/usr/src/homeassistant/homeassistant/components/recorder/websocket_api.py", line 205, in ws_handle_get_statistics_during_period await get_instance(hass).async_add_executor_job( File "/usr/local/lib/python3.10/concurrent/futures/thread.py", line 58, in run result = self.fn(*self.args, **self.kwargs) File "/usr/src/homeassistant/homeassistant/components/recorder/websocket_api.py", line 160, in _ws_get_statistics_during_period result = statistics_during_period( File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 1818, in statistics_during_period return _statistics_during_period_with_session( File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 1768, in _statistics_during_period_with_session return _sorted_statistics_to_dict( File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 2076, in _sorted_statistics_to_dict seen_statistic_ids.add(metadata[meta_id]["statistic_id"]) KeyError: 5Additional information
No response
The text was updated successfully, but these errors were encountered: