home-assistant / core

:house_with_garden: Open source home automation that puts local control and privacy first.
https://www.home-assistant.io
Apache License 2.0
73.86k stars 30.94k forks source link

Database problem with history #130115

Open wpfog opened 1 week ago

wpfog commented 1 week ago

The problem

The update to core 2024.11 broke my history. Instead of history data, only endlessly spinning wheels were displayed for entities. I had to roll back to 2024.10.4 to get the data shown again.

What version of Home Assistant Core has the issue?

core-2024.11.0

What was the last working version of Home Assistant Core?

core-2024.10.4

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?

No response

Additional information

No response

wpfog commented 1 week ago

PS. Im not using the default database for recorder but a mariaDB recorder: db_url: mysql://homeassistant:.......@core-mariadb/...

primedead commented 1 week ago

I have the same setup with the recorder and the same issue with the update. In the logs it only warned about the recorder taking too long to setup. After a while home assistant started but without the recorder function enabled. For me it seems to be similar to #129985

blieven1 commented 1 week ago

same issue here, even with the standard database. Fixed itself after an extra reboot tough

drmodding commented 1 week ago

Same here, also using mariadb

JonasPed commented 1 week ago

I am expericing the same issue with Setup of recorder is taking over 10 seconds in the logs. I am also using MariaDB. I can see that it is stuck at below query. An explain of the query reveals that it does not utilize any indexes and I have close to 90 million rows in the states table. That will for sure take some time to execute that query. I have rolled back to 2024.10 for now.

SELECT states.state_id FROM states WHERE states.entity_id IS NOT NULL LIMIT 1
peno64 commented 1 week ago

same issue here. pretty standard installation. Even removing home-assistant_v2.db and then restarting HA doesn't help (home-assistant_v2.db is automatically recreated) and it doesn't grow very fast. No errors at all in log file.

returned to previous state and all is ok again. This is:

Core 2024.10.4 Supervisor 2024.11.2 Operating System 13.2 Frontend 20241002.4

Magca78 commented 1 week ago

Here also the same, no error, but recorder takes to long, also with version 2024.11.1 the same problem as 2024.11.0, chanced back to 2024.10.4, and no problems

primedead commented 1 week ago

I have the same setup with the recorder and the same issue with the update. In the logs it only warned about the recorder taking too long to setup. After a while home assistant started but without the recorder function enabled. For me it seems to be similar to #129985

After waiting for a while on the new update the recorder started working again. It seems like the recorder takes a long time to update the database but after it has been updated it works. Also after a restart.

peno64 commented 1 week ago

My problem is solved. I even had another problem. Units were not shown in any of my cards. The solution is easy: clear cache Chome, ..., Settings, Privacy and security, Delete browsing data, Time range: All time. Check all except Browsing history and then delete data. Now refresh the home assistant page and everything is solved.

Magca78 commented 1 week ago

I have the same setup with the recorder and the same issue with the update. In the logs it only warned about the recorder taking too long to setup. After a while home assistant started but without the recorder function enabled. For me it seems to be similar to #129985

After waiting for a while on the new update the recorder started working again. It seems like the recorder takes a long time to update the database but after it has been updated it works. Also after a restart.

Thx, I did also the update, waited 1 hour, then a restart, and everything was oke :)

wpfog commented 3 days ago

Core 2024.11.1 seemed to solve the issue. Everything worked fine. But with 2024.12.1 it's back again: Endless spinning wheels and no history data for sensors etc.

peno64 commented 3 days ago

@wpfog Try it on another computer, with another browser. If it then works it is definitely a cache issue. Maybe just refresh (F5) helps and otherwise clear cache on the browser.

wpfog commented 3 days ago

@peno64 It is definetly not a caching issue because it‘s the same with all my devices: iPhone, iPad, Mac, Android… Apps and Browser

peno64 commented 3 days ago

@wpfog Well for me it was so I hoped it was also in your case