search

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
71.83k stars 30.08k forks source link

Recorder unable to store data due to states_state_id_seq overflow #119167

Closed saschaludwig closed 2 months ago

saschaludwig commented 3 months ago

The problem

I'm getting a lot of psycopg2.errors.SequenceGeneratorLimitExceeded: nextval: reached maximum value of sequence "states_state_id_seq" (2147483647) errors and the recorder is unable to write any new history data into the Postgres DB.

Here is my recorder config:

# Recorder
recorder:
  db_url: postgresql://xxxx:xxxx@192.168.xx.xx/homeassistant
  purge_keep_days: 7
  exclude:
    entities:
      - sensor.cerbo_em540_nshv_total_power
    entity_globs:
      - sensor.cerbo_em540_nshv_*

Is there anything I can do on the short term, or do I have to wait for a fix/migration of the DB to alter the field type to BIGINT for example.

My Postgres DB is only <1 year old, what can be done in the long term to don't run into this again?

What version of Home Assistant Core has the issue?

core-2024.6.1

What was the last working version of Home Assistant Core?

No response

What type of installation are you running?

Home Assistant OS

Integration causing the issue

recorder

Link to integration documentation on our website

https://www.home-assistant.io/integrations/recorder/

Diagnostics information

No response

Example YAML snippet

No response

Anything in the logs that might be useful for us?

Logger: homeassistant.components.recorder.core
Source: components/recorder/core.py:926
integration: Recorder (documentation, issues)
First occurred: 20:51:34 (1040 occurrences)
Last logged: 22:18:31

Unhandled database error while processing task CommitTask()
Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2116, in _exec_insertmany_context
    dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.SequenceGeneratorLimitExceeded: nextval: reached maximum value of sequence "states_state_id_seq" (2147483647)

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 926, in _process_one_task_or_event_or_recover
    task.run(self)
  File "/usr/src/homeassistant/homeassistant/components/recorder/tasks.py", line 291, in run
    instance._commit_event_session_or_retry()  # noqa: SLF001
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 1191, in _commit_event_session_or_retry
    self._commit_event_session()
  File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 1227, in _commit_event_session
    session.commit()
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2017, in commit
    trans.commit(_to_root=True)
  File "<string>", line 2, in commit
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/state_changes.py", line 139, in _go
    ret_value = fn(self, *arg, **kw)
                ^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 1302, in commit
    self._prepare_impl()
  File "<string>", line 2, in _prepare_impl
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/state_changes.py", line 139, in _go
    ret_value = fn(self, *arg, **kw)
                ^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 1277, in _prepare_impl
    self.session.flush()
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 4341, in flush
    self._flush(objects)
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 4476, in _flush
    with util.safe_reraise():
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 4437, in _flush
    flush_context.execute()
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/unitofwork.py", line 463, in execute
    n.execute_aggregate(self, set_)
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/unitofwork.py", line 759, in execute_aggregate
    persistence.save_obj(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/persistence.py", line 93, in save_obj
    _emit_insert_statements(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/persistence.py", line 1143, in _emit_insert_statements
    result = connection.execute(
             ^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1418, in execute
    return meth(
           ^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/elements.py", line 515, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1640, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1844, in _execute_context
    return self._exec_insertmany_context(dialect, context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2124, in _exec_insertmany_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2353, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2116, in _exec_insertmany_context
    dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.DataError: (psycopg2.errors.SequenceGeneratorLimitExceeded) nextval: reached maximum value of sequence "states_state_id_seq" (2147483647)

[SQL: INSERT INTO states (entity_id, state, attributes, event_id, last_changed, last_changed_ts, last_reported_ts, last_updated, last_updated_ts, old_state_id, attributes_id, context_id, context_user_id, context_parent_id, origin_idx, context_id_bin, conte ... 71838 characters truncated ... 17, p18, sen_counter) ORDER BY sen_counter RETURNING states.state_id, states.state_id AS state_id__1]
[parameters: {'metadata_id__0': 296, 'last_reported_ts__0': 1717872689.642626, 'context_user_id__0': None, 'context_id__0': None, 'context_parent_id__0': None, 'old_state_id__0': None, 'last_updated_ts__0': 1717872689.642626, 'state__0': '-422', 'context_user_id_bin__0': None, 'last_updated__0': None, 'context_parent_id_bin__0': None, 'last_changed_ts__0': None, 'attributes_id__0': 659390, 'event_id__0': None, 'context_id_bin__0': <psycopg2.extensions.Binary object at 0x7f0865406c70>, 'attributes__0': None, 'last_changed__0': None, 'origin_idx__0': 0, 'entity_id__0': None, 'metadata_id__1': 151, 'last_reported_ts__1': 1717872689.64788, 'context_user_id__1': None, 'context_id__1': None, 'context_parent_id__1': None, 'old_state_id__1': None, 'last_updated_ts__1': 1717872689.64788, 'state__1': '-123', 'context_user_id_bin__1': None, 'last_updated__1': None, 'context_parent_id_bin__1': None, 'last_changed_ts__1': None, 'attributes_id__1': 659386, 'event_id__1': None, 'context_id_bin__1': <psycopg2.extensions.Binary object at 0x7f0865406dc0>, 'attributes__1': None, 'last_changed__1': None, 'origin_idx__1': 0, 'entity_id__1': None, 'metadata_id__2': 267, 'last_reported_ts__2': 1717872689.67417, 'context_user_id__2': None, 'context_id__2': None, 'context_parent_id__2': None, 'old_state_id__2': None, 'last_updated_ts__2': 1717872689.67417, 'state__2': '-377', 'context_user_id_bin__2': None, 'last_updated__2': None, 'context_parent_id_bin__2': None, 'last_changed_ts__2': None ... 2940 parameters truncated ... 'state__157': '0.79', 'context_user_id_bin__157': None, 'last_updated__157': None, 'context_parent_id_bin__157': None, 'last_changed_ts__157': None, 'attributes_id__157': 6523049, 'event_id__157': None, 'context_id_bin__157': <psycopg2.extensions.Binary object at 0x7f0865e4b150>, 'attributes__157': None, 'last_changed__157': None, 'origin_idx__157': 0, 'entity_id__157': None, 'metadata_id__158': 291, 'last_reported_ts__158': None, 'context_user_id__158': None, 'context_id__158': None, 'context_parent_id__158': None, 'old_state_id__158': None, 'last_updated_ts__158': 1717872694.169166, 'state__158': '-498', 'context_user_id_bin__158': None, 'last_updated__158': None, 'context_parent_id_bin__158': None, 'last_changed_ts__158': None, 'attributes_id__158': 659389, 'event_id__158': None, 'context_id_bin__158': <psycopg2.extensions.Binary object at 0x7f0865e4b450>, 'attributes__158': None, 'last_changed__158': None, 'origin_idx__158': 0, 'entity_id__158': None, 'metadata_id__159': 1044, 'last_reported_ts__159': None, 'context_user_id__159': None, 'context_id__159': None, 'context_parent_id__159': None, 'old_state_id__159': None, 'last_updated_ts__159': 1717872694.200174, 'state__159': '23.0', 'context_user_id_bin__159': None, 'last_updated__159': None, 'context_parent_id_bin__159': None, 'last_changed_ts__159': None, 'attributes_id__159': 1939613, 'event_id__159': None, 'context_id_bin__159': <psycopg2.extensions.Binary object at 0x7f0865e4b2d0>, 'attributes__159': None, 'last_changed__159': None, 'origin_idx__159': 0, 'entity_id__159': None}]
(Background on this error at: https://sqlalche.me/e/20/9h9h)

Additional information

No response

home-assistant[bot] commented 3 months ago

Hey there @home-assistant/core, mind taking a look at this issue as it has been labeled with an integration (recorder) you are listed as a code owner for? Thanks!

Code owner commands Code owners of `recorder` can trigger bot actions by commenting: - `@home-assistant close` Closes the issue. - `@home-assistant rename Awesome new title` Renames the issue. - `@home-assistant reopen` Reopen the issue. - `@home-assistant unassign recorder` Removes the current integration label and assignees on the issue, add the integration domain after the command. - `@home-assistant add-label needs-more-information` Add a label (needs-more-information, problem in dependency, problem in custom component) to the issue. - `@home-assistant remove-label needs-more-information` Remove a label (needs-more-information, problem in dependency, problem in custom component) on the issue.

(message by CodeOwnersMention)

recorder documentation recorder source (message by IssueLinks)

thenoid commented 3 months ago

Heh, looks like we got to the same place around the same time: https://github.com/home-assistant/core/issues/120115

bdraco commented 3 months ago

https://github.com/home-assistant/core/blob/c3ab72a1f9753070f8258695ee4caa28b21b03da/homeassistant/components/recorder/db_schema.py#L250

https://github.com/home-assistant/core/blob/c3ab72a1f9753070f8258695ee4caa28b21b03da/homeassistant/components/recorder/db_schema.py#L424

https://github.com/home-assistant/core/blob/c3ab72a1f9753070f8258695ee4caa28b21b03da/homeassistant/components/recorder/db_schema.py#L436

https://github.com/home-assistant/core/blob/c3ab72a1f9753070f8258695ee4caa28b21b03da/homeassistant/components/recorder/db_schema.py#L563

https://github.com/home-assistant/core/blob/c3ab72a1f9753070f8258695ee4caa28b21b03da/homeassistant/components/recorder/db_schema.py#L648

The ids are all Integer which works fine for sqlite, but can overflow since its a lot easier to hit the max with mariadb/postgresql

aarontc commented 3 months ago

I see the merged change above relating to the legacy foreign key constraint, but unless I missed something in that MR, it seems like we also need a type change for the states table ID column under Postgres and MySQL to use a 64-bit value.

bdraco commented 3 months ago

That PR solved an unrelated problem. This issue has not been worked on yet

bdraco commented 2 months ago

fix is targeted for 2024.8.x