search

ldotlopez / ha-ideenergy

ideenergy integration for home-assistant
GNU General Public License v3.0
73 stars 13 forks source link

Error in historical dat MariaDB #40

Closed fherreror closed 1 year ago

fherreror commented 1 year ago

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 Since updating HA to 2023.2 I am getting this errors on logs. Today I found that since 14/2/23 it has occurred more than 5.000 times

Logger: homeassistant.components.recorder.util
Source: components/recorder/util.py:125
Integration: Recorder (documentation, issues)
First occurred: 22:53:42 (4 occurrences)
Last logged: 22:58:23

Error executing query: (MySQLdb.IntegrityError) (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`homeassistant`.`states`, CONSTRAINT `states_ibfk_2` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`))') [SQL: DELETE FROM states WHERE states.entity_id = %s AND states.last_updated_ts >= %s] [parameters: ('sensor.icp_esXXXXXXX64sp_historical', 1676160000.0)] (Background on this error at: https://sqlalche.me/e/14/gkpj)
Error executing query: (MySQLdb.IntegrityError) (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`homeassistant`.`states`, CONSTRAINT `states_ibfk_2` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`))') [SQL: DELETE FROM states WHERE states.entity_id = %s AND states.last_updated_ts >= %s] [parameters: ('sensor.es00XXXXXXX4sp_historical_power_demand', 1596146400.0)] (Background on this error at: https://sqlalche.me/e/14/gkpj)

To Reproduce Steps to reproduce the behavior: I do nothing. The error just appears.

Environment

Expected behavior No error should appear

Logs Pasted above

Screenshots No aplicable

Additional context 2023.2 has introduced some changes in recorder and database. Maybe it has something to do with it. I have disabled the historical sensors, but I thoght I had to report this.

ldotlopez commented 1 year ago

Hello @fherreror

I have made some changes in the fix-40 branch, would you have the chance to try it?

fherreror commented 1 year ago

I have just installed fix-40 branch and activated historical sensor. For the time being the error has not appeared again in log. I will keep an eye for a couple of days and post if the error reappears or not.

fherreror commented 1 year ago

@ldotlopez The error has just appeared again:

Unhandled database error while processing task CommitTask(): (MySQLdb.IntegrityError) (1452, 'Cannot add or update a child row: a foreign key constraint fails (`homeassistant`.`states`, CONSTRAINT `states_ibfk_2` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`))') [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) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)] [parameters: ('sensor.icp_esXXXXsp_historical', 'unavailable', None, None, None, None, None, 1677443066.673193, 13410859, 2437116, 'XXX', None, None, 0)] (Background on this error at: https://sqlalche.me/e/14/gkpj)

Traceback (most recent call last):
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.10/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.10/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/usr/local/lib/python3.10/site-packages/MySQLdb/connections.py", line 254, in query
    _mysql.connection.query(self, query)
MySQLdb.IntegrityError: (1452, 'Cannot add or update a child row: a foreign key constraint fails (`homeassistant`.`states`, CONSTRAINT `states_ibfk_2` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`))')

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 684, in _process_one_task_or_recover
    return task.run(self)
  File "/usr/src/homeassistant/homeassistant/components/recorder/tasks.py", line 271, in run
    instance._commit_event_session_or_retry()
  File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 941, in _commit_event_session_or_retry
    self._commit_event_session()
  File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 960, in _commit_event_session
    self.event_session.commit()
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 1451, in commit
    self._transaction.commit(_to_root=self.future)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 829, in commit
    self._prepare_impl()
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 808, in _prepare_impl
    self.session.flush()
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 3444, in flush
    self._flush(objects)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 3583, in _flush
    with util.safe_reraise():
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 3544, in _flush
    flush_context.execute()
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/unitofwork.py", line 453, in execute
    n.execute_aggregate(self, set_)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/unitofwork.py", line 747, in execute_aggregate
    persistence.save_obj(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/persistence.py", line 245, in save_obj
    _emit_insert_statements(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/persistence.py", line 1238, in _emit_insert_statements
    result = connection._execute_20(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 334, in _execute_on_connection
    return connection._execute_clauseelem
[LOG.txt](https://github.com/ldotlopez/ha-ideenergy/files/10834707/LOG.txt)
ent(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1572, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2124, in _handle_dbapi_exception
    util.raise_(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.10/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.10/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/usr/local/lib/python3.10/site-packages/MySQLdb/connections.py", line 254, in query
    _mysql.connection.query(self, query)
sqlalchemy.exc.IntegrityError: (MySQLdb.IntegrityError) (1452, 'Cannot add or update a child row: a foreign key constraint fails (`homeassistant`.`states`, CONSTRAINT `states_ibfk_2` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`))')
[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, cont
[LOG.txt](https://github.com/ldotlopez/ha-ideenergy/files/10834705/LOG.txt)
ext_parent_id, origin_idx) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)]
[parameters: ('sensor.icp_esXXXXsp_historical', 'unavailable', None, None, None, None, None, 1677443066.673193, 13410859, 2437116, 'XXX', None, None, 0)]
(Background on this error at: https://sqlalche.me/e/14/gkpj)

EDITED: @ldotlopez you can find attached to this post the debug logs of the integration. LOG.txt

fherreror commented 1 year ago

UPDATE: Since that error of 3 days ago, no new error has arisen.

ldotlopez commented 1 year ago

Hi @fherreror,

Thanks for your feedback and excuse me about the delay (busy weeks). Glad to hear that error is fixed.

I introduces some changes in latest release to avoid as much as possible database manipulation.

Thanks.