Open ghiki opened 9 months ago
Hi
I have the same problem. After restarting Home Assistant, the Integration fails to boot and it is necessary to manually reload to get it working again.
The integration runs in a HA Green:
Core: 2023.11.3 Supervisor: 2023.11.6 Operating System: 11.1 Frontend: 20231030.2
It seems to be caused by some kind of race condition in this code: https://github.com/ldotlopez/ha-historical-sensor/blob/main/homeassistant_historical_sensor/recorderutil.py#L81
get_entity_states_meta
tries to retrieve the StateMeta
object corresponding to the entity_id
of the sensor from the database, if not found it is created but some someone else does it first.
Not sure why or how to fix it :confused:
@Anacletus, @feserdel, @ALL …
I just released a new version that should fix the database corruption. Please check it and comment anything (remember to backup your configuration)
I updated the integration, and got no database corruption for a few days. The historical consumption showed data, but historical power demand sensor, though, was always empty, maybe because of some errors that appear in the log:
2024-01-11 10:04:23.841 DEBUG (MainThread) [custom_components.ideenergy.datacoordinator] Request update for datasets: ALL
2024-01-11 10:04:23.842 DEBUG (MainThread) [custom_components.ideenergy.datacoordinator] update denied for MEASURE: update window
is closed
2024-01-11 10:04:23.842 DEBUG (MainThread) [custom_components.ideenergy.datacoordinator] update allowed for HISTORICAL_CONSUMPTIO
N
2024-01-11 10:04:31.773 INFO (MainThread) [ideenergy] successful authentication as 'XXXXXXXXXX@gmail.com'
2024-01-11 10:04:51.479 INFO (MainThread) [ideenergy] <ideenergy.Client username=XXXXXXXXXX@gmail.com, contract=XXXXXXXXXX>: '
XXXXXXXXXX' contract selected
2024-01-11 10:04:51.479 INFO (MainThread) [ideenergy] contract 'XXXXXXXXXX' selected
2024-01-11 10:04:52.633 DEBUG (MainThread) [custom_components.ideenergy.datacoordinator] update successful for HISTORICAL_CONSUMP
TION
2024-01-11 10:04:52.634 DEBUG (MainThread) [custom_components.ideenergy.datacoordinator] update allowed for HISTORICAL_GENERATION
2024-01-11 10:04:55.354 DEBUG (MainThread) [custom_components.ideenergy.datacoordinator] update error for HISTORICAL_GENERATION:
Forbidden (403)
2024-01-11 10:04:55.354 DEBUG (MainThread) [custom_components.ideenergy.datacoordinator] update allowed for HISTORICAL_POWER_DEMAND
2024-01-11 10:04:58.483 DEBUG (MainThread) [custom_components.ideenergy.datacoordinator] update error for HISTORICAL_POWER_DEMAND: invalid encoding. File a bug
2024-01-11 10:04:58.484 DEBUG (MainThread) [custom_components.ideenergy.datacoordinator] Finished fetching XXXXXXXXXX@gmail.com/XXXXXXXXXX coordinator data in 34.643 seconds (success: True)
2024-01-11 10:04:58.490 DEBUG (MainThread) [homeassistant_historical_sensor.sensor] sensor.es00XXXXXXXXXXjv_historical_consumption: 157 historical states present in sensor
2024-01-11 10:04:58.490 DEBUG (MainThread) [homeassistant_historical_sensor.sensor] sensor.es00XXXXXXXXXXjv_historical_power_demand: 0 historical states present in sensor
2024-01-11 10:04:58.490 DEBUG (MainThread) [homeassistant_historical_sensor.sensor] sensor.es00XXXXXXXXXXjv_historical_generation: 0 historical states present in sensor
2024-01-11 10:04:58.547 DEBUG (DbWorker_1) [homeassistant_historical_sensor.sensor] sensor.es00XXXXXXXXXXjv_historical_consumption: cleaned 0 invalid states
2024-01-11 10:04:58.559 DEBUG (DbWorker_1) [homeassistant_historical_sensor.sensor] sensor.es00XXXXXXXXXXjv_historical_consumption: lastest state found at 2024-01-08 23:00:00+00:00 (0.237)
2024-01-11 10:04:58.560 DEBUG (DbWorker_1) [homeassistant_historical_sensor.sensor] sensor.es00XXXXXXXXXXjv_historical_consumption: found 48 new states
2024-01-11 10:04:58.659 DEBUG (MainThread) [homeassistant_historical_sensor.sensor] sensor.es00XXXXXXXXXXjv_historical_consumption: 48 states written into the database
2024-01-11 10:04:58.663 DEBUG (MainThread) [custom_components.ideenergy.sensor] sensor.es00XXXXXXXXXXjv_historical_consumption: calculating statistics using 80.81299999999997 as base accumulated (registed at 2024-01-08 23:00:00+01:00)
2024-01-11 10:04:58.664 DEBUG (MainThread) [homeassistant_historical_sensor.sensor] sensor.es00XXXXXXXXXXjv_historical_consumption: 48 statistics points written into database
2024-01-10 21:54:55.935 DEBUG (MainThread) [custom_components.ideenergy.datacoordinator] update error for HISTORICAL_POWER_DEMAND
: **FIXME** handle HISTORICAL_POWER_DEMAND raised exception: KeyError('potMaxMens')
However, I disabled the device from the web app (config/integrations/integration/ideenergy), and after enabling it again, database corruption shows up instantly. If needed, I can replicate it easyly.
2024-01-14 10:59:15.286 ERROR (Recorder) [homeassistant.components.recorder.core] Unrecoverable sqlite3 database corruption detect
ed: (sqlite3.IntegrityError) FOREIGN KEY constraint failed
[SQL: INSERT INTO states (entity_id, state, attributes, event_id, last_changed, last_changed_ts, last_updated, last_updated_ts, ol
d_state_id, attributes_id, context_id, context_user_id, context_parent_id, origin_idx, context_id_bin, context_user_id_bin, contex
t_parent_id_bin, metadata_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING state_id]
[parameters: (None, 'unknown', None, None, None, None, None, 1705226352.054044, 1871087, 177218, None, None, None, 0, <memory at 0
x7f2eea40bd00>, None, None, 267)]
(Background on this error at: https://sqlalche.me/e/20/gkpj)
Traceback (most recent call last):
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2112, in _exec_insertmany_context
dialect.do_execute(
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute
cursor.execute(statement, parameters)
sqlite3.IntegrityError: FOREIGN KEY constraint failed
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 912, in _process_one_task_or_recover
return task.run(self)
^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/tasks.py", line 305, in run
instance._commit_event_session_or_retry()
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 1163, in _commit_event_session_or_retry
self._commit_event_session()
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 1183, in _commit_event_session
session.commit()
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 1969, in commit
trans.commit(_to_root=True)
File "<string>", line 2, in commit
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/state_changes.py", line 139, in _go
ret_value = fn(self, *arg, **kw)
^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 1256, in commit
self._prepare_impl()
File "<string>", line 2, in _prepare_impl
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/state_changes.py", line 139, in _go
ret_value = fn(self, *arg, **kw)
^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 1231, in _prepare_impl
self.session.flush()
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 4312, in flush
self._flush(objects)
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 4447, in _flush
with util.safe_reraise():
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
raise exc_value.with_traceback(exc_tb)
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 4408, in _flush
flush_context.execute()
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/unitofwork.py", line 466, in execute
rec.execute(self)
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/unitofwork.py", line 642, in execute
util.preloaded.orm_persistence.save_obj(
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/persistence.py", line 93, in save_obj
_emit_insert_statements(
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/persistence.py", line 1136, in _emit_insert_statements
result = connection.execute(
^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1416, in execute
return meth(
^^^^^
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/sql/elements.py", line 516, in _execute_on_connection
return connection._execute_clauseelement(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1639, in _execute_clauseelement
ret = self._execute_context(
^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1843, in _execute_context
return self._exec_insertmany_context(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2120, in _exec_insertmany_context
self._handle_dbapi_exception(
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2343, in _handle_dbapi_exception
raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2112, in _exec_insertmany_context
dialect.do_execute(
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) FOREIGN KEY constraint failed
[SQL: INSERT INTO states (entity_id, state, attributes, event_id, last_changed, last_changed_ts, last_updated, last_updated_ts, old_state_id, attributes_id, context_id, context_user_id, context_parent_id, origin_idx, context_id_bin, context_user_id_bin, context_parent_id_bin, metadata_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING state_id]
[parameters: (None, 'unknown', None, None, None, None, None, 1705226352.054044, 1871087, 177218, None, None, None, 0, <memory at 0x7f2eea40bd00>, None, None, 267)]
(Background on this error at: https://sqlalche.me/e/20/gkpj)
2024-01-14 10:59:15.345 ERROR (Recorder) [homeassistant.components.recorder.util] The system will rename the corrupt database file //config/home-assistant_v2.db to //config/home-assistant_v2.db.corrupt.2024-01-14T09:59:15.345066+00:00 in order to allow startup to proceed
2024-01-14 10:59:22.141 DEBUG (MainThread) [custom_components.ideenergy.datacoordinator] Request update for datasets: ALL
2024-01-14 10:59:22.142 DEBUG (MainThread) [custom_components.ideenergy.datacoordinator] update allowed for MEASURE
2024-01-14 10:59:22.142 DEBUG (MainThread) [ideenergy] Requesting data to the ICP, may take up to a minute.
2024-01-14 10:59:22.403 DEBUG (MainThread) [custom_components.ideenergy.datacoordinator] update error for MEASURE: Forbidden (403)
2024-01-14 10:59:22.403 DEBUG (MainThread) [custom_components.ideenergy.datacoordinator] update allowed for HISTORICAL_CONSUMPTION
2024-01-14 10:59:22.446 DEBUG (MainThread) [custom_components.ideenergy.datacoordinator] update error for HISTORICAL_CONSUMPTION: Forbidden (403)
2024-01-14 10:59:22.447 DEBUG (MainThread) [custom_components.ideenergy.datacoordinator] update allowed for HISTORICAL_GENERATION
2024-01-14 10:59:22.487 DEBUG (MainThread) [custom_components.ideenergy.datacoordinator] update error for HISTORICAL_GENERATION: Forbidden (403)
2024-01-14 10:59:22.487 DEBUG (MainThread) [custom_components.ideenergy.datacoordinator] update allowed for HISTORICAL_POWER_DEMAND
2024-01-14 10:59:22.524 DEBUG (MainThread) [custom_components.ideenergy.datacoordinator] update error for HISTORICAL_POWER_DEMAND: Forbidden (403)
2024-01-14 10:59:22.524 DEBUG (MainThread) [custom_components.ideenergy.datacoordinator] Finished fetching xxxxxx@gmail.com/xxxxxxxxx coordinator data in 0.383 seconds (success: True)
2024-01-14 10:59:22.527 DEBUG (MainThread) [homeassistant_historical_sensor.sensor] sensor.es00210000xxxxxxxxxxjv_historical_generation: 0 historical states present in sensor
2024-01-14 10:59:22.527 DEBUG (MainThread) [homeassistant_historical_sensor.sensor] sensor.es0021000xxxxxxxxxjv_historical_power_demand: 0 historical states present in sensor
2024-01-14 10:59:22.527 DEBUG (MainThread) [homeassistant_historical_sensor.sensor] sensor.es002100000xxxxxxxxxjv_historical_consumption: 0 historical states present in sensor
Hi,
I have made some changes in the https://github.com/ldotlopez/ha-historical-sensor component.
You can tweak the integration manifest.json to use the v2.0.0rc6 version to try it now, otherwise I will update this components in a few days to pull that.
Personally have no db corruption after https://github.com/ldotlopez/ha-ideenergy/commit/88cc9ce46069d6263dabc226c69c8c70e0a4197a
I'm not sure if I updated correctly the historical sensor (I changed the manifest.json in /usr/share/hassio/homeassistant/custom_components/ideenergy
to the new dependency: "homeassistant-historical-sensor==2.0.0rc6"
and restarted home-assistant), but the issue keeps happening for me, only after enabling:
After disabling the integration and enabling it again, I inmediatly see this in home-assistant.log:
2024-02-07 21:08:35.309 ERROR (Recorder) [homeassistant.components.recorder.core
] Unrecoverable sqlite3 database corruption detected: (sqlite3.IntegrityError) F
OREIGN KEY constraint failed
[SQL: INSERT INTO states (entity_id, state, attributes, event_id, last_changed,
last_changed_ts, last_updated, last_updated_ts, old_state_id, attributes_id, con
text_id, context_user_id, context_parent_id, origin_idx, context_id_bin, context
_user_id_bin, context_parent_id_bin, metadata_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?
, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING state_id]
[parameters: (None, 'unknown', None, None, None, None, None, 1707336512.191674, 4313332, 177218, None, None, None, 0, <memory at 0x7f8287ed4e80>, None, None, 267)]
(Background on this error at: https://sqlalche.me/e/20/gkpj)
Traceback (most recent call last):
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2113, in _exec_insertmany_context
dialect.do_execute(
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute
cursor.execute(statement, parameters)
sqlite3.IntegrityError: FOREIGN KEY constraint failed
i was having this issue with the demo component in the main sensor repo. in my case it seemed to crop up when i tried adding data earlier than what was already there. i did an initial test using the test api that makes random data and then i switched over to using a single sensor from my api but the test data for that was about 3 days old. it was when i switched to that test data that it got the corruption error. i then renamed the sensor and that added just fine. i have not done tests to see if it is directly related so this might just be a coincidence.
here is the link to the repo at the time i was getting the errors https://github.com/TomW1605/ha-synergy_old/tree/e6f22a246182737719b9b63f64bf63363f35b3ba (i think that is when i was getting the errors first, although im still getting them with my newer one that is forked from here)
Before filling a bug please provide as much as information as possible. Not all information is required but the more the better.
Stick to English if bug is about code, Spanish is OK if bug is about configuration.
Describe the bug Database gets corrupted after restart if integration is configured
To Reproduce Steps to reproduce the behavior: 1- Simply install integration, configure (mail/pass), select one of the contracts (asked between 2 in my case) an restart 2- After a restart, you will see error in integrations menu
3- Also, in file you'll see "/homeassistant/home-assistant_v2.db.corrupted" (or something like this)
Environment
Core - 2023.11.2 Supervisor - 2023.11.3 Operating System - 11.1 Frontend - 20231030.2
Expected behavior Not corrupting database...
Logs
Screenshots After reloading integration:![imagen](https://github.com/ldotlopez/ha-ideenergy/assets/854302/2f6b60ab-9fbb-4965-9bf8-9f628cd4debc)
Additional context I've found there https://github.com/home-assistant/core/issues/101466 that maybe inserting statistics rows in old format can corrupt database