ha-ideenergy icon indicating copy to clipboard operation
ha-ideenergy copied to clipboard

Published 20 hours ago • verified publisher icon ldotlopez

Database gets corrupted after restart if integration is configured

Open ghiki opened this issue 7 months ago • 8 comments

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 imagen 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

  • ideenergy v2.0.4
  • Instalation method - HassOS (installed from HACS)
  • i-de.es advanced user - yes
  • Did I read the FAQ? (You should): yes
  • Did I enable debug before posting any log as it's required? (see Logs section below): yes

Expected behavior Not corrupting database...

Logs


2023-11-21 19:25:19.594 INFO (MainThread) [ideenergy] <ideenergy.Client [email protected], contract=xxxxxxxxxx>: 'xxxxxxxxxx' contract selected
2023-11-21 19:25:19.595 INFO (MainThread) [ideenergy] <ideenergy.Client [email protected], contract=xxxxxxxxxx>: successful authentication
2023-11-21 19:25:19.782 INFO (MainThread) [homeassistant.components.sensor] Setting up sensor.ideenergy
2023-11-21 19:25:19.842 DEBUG (MainThread) [custom_components.ideenergy.entity] sensor.esxxxxxxxxxxmp_instant_power_demand: cleaned 2 invalid states
2023-11-21 19:25:19.843 DEBUG (MainThread) [custom_components.ideenergy.datacoordinator] Registered sensor 'InstantPowerDemand'
2023-11-21 19:25:19.843 DEBUG (MainThread) [custom_components.ideenergy.datacoordinator] Request update for datasets: MEASURE
2023-11-21 19:25:19.844 DEBUG (MainThread) [custom_components.ideenergy.datacoordinator] update denied for MEASURE: update window is closed
2023-11-21 19:25:19.844 DEBUG (MainThread) [custom_components.ideenergy.datacoordinator] Finished fetching [email protected]/xxxxxxxxxx coordinator data in 0.001 seconds (success: True)
2023-11-21 19:25:19.852 DEBUG (MainThread) [custom_components.ideenergy.sensor] sensor.esxxxxxxxxxxmp_instant_power_demand: restore state failed (unknown)
2023-11-21 19:25:19.872 DEBUG (MainThread) [custom_components.ideenergy.entity] sensor.esxxxxxxxxxxmp_accumulated_consumption: cleaned 2 invalid states
2023-11-21 19:25:19.872 DEBUG (MainThread) [custom_components.ideenergy.datacoordinator] Registered sensor 'AccumulatedConsumption'
2023-11-21 19:25:19.873 DEBUG (MainThread) [custom_components.ideenergy.sensor] sensor.esxxxxxxxxxxmp_accumulated_consumption: restore state failed (unknown)
2023-11-21 19:25:21.072 ERROR (Recorder) [homeassistant.components.recorder.core] Unrecoverable sqlite3 database corruption detected: (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, 1700591119.84527, 1133, 308, None, None, None, 0, <memory at 0x7f620e1e40>, None, None, 350)]
(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 1967, 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 4310, in flush
    self._flush(objects)
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 4445, 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 4406, 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, 1700591119.84527, 1133, 308, None, None, None, 0, <memory at 0x7f620e1e40>, None, None, 350)]
(Background on this error at: https://sqlalche.me/e/20/gkpj)
2023-11-21 19:25:21.285 INFO (Recorder) [homeassistant.components.recorder.pool.RecorderPool] Pool recreating
2023-11-21 19:25:21.287 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.2023-11-21T18:25:21.286964+00:00 in order to allow startup to proceed
2023-11-21 19:25:21.578 WARNING (MainThread) [homeassistant.helpers.entity] Update of vacuum.aspiradora is taking over 10 seconds
2023-11-21 19:25:29.853 DEBUG (MainThread) [custom_components.ideenergy.datacoordinator] Request update for datasets: MEASURE
2023-11-21 19:25:29.855 DEBUG (MainThread) [custom_components.ideenergy.datacoordinator] update denied for MEASURE: update window is closed
2023-11-21 19:25:29.855 DEBUG (MainThread) [custom_components.ideenergy.datacoordinator] Finished fetching [email protected]/xxxxxxxxxx coordinator data in 0.002 seconds (success: True)

Screenshots After reloading integration: imagen

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

ghiki avatar Nov 21 '23 18:11 ghiki