Sqlite3 Operational Errors #114327

Anto79-ops · 2024-03-27T18:57:34Z

The problem

Hello

Updating to HA Core to 2024.0.0b0 lead to the following errors related to the recorder database (RPi4-4GB/HAOS/SSD)

2024-03-27 12:07:46.895 ERROR (DbWorker_0) [homeassistant.components.recorder.util] Error executing query: (sqlite3.OperationalError) no such column: states.last_reported_ts
[SQL: SELECT states.metadata_id, states.state, states.last_updated_ts, states.last_reported_ts, CASE WHEN (state_attributes.shared_attrs IS NULL) THEN states.attributes ELSE state_attributes.shared_attrs END AS attributes 
FROM states LEFT OUTER JOIN state_attributes ON states.attributes_id = state_attributes.attributes_id 
WHERE (states.last_changed_ts = states.last_updated_ts OR states.last_changed_ts IS NULL) AND states.last_updated_ts > ? AND states.metadata_id = ? ORDER BY states.metadata_id, states.last_updated_ts
 LIMIT ? OFFSET ?]
[parameters: (1711562266.829372, 682, 35, 0)]
(Background on this error at: https://sqlalche.me/e/20/e3q8)
2024-03-27 12:07:46.904 ERROR (DbWorker_1) [homeassistant.components.recorder.util] Error executing query: (sqlite3.OperationalError) no such column: states.last_reported_ts
[SQL: SELECT states.metadata_id, states.state, states.last_updated_ts, states.last_reported_ts, CASE WHEN (state_attributes.shared_attrs IS NULL) THEN states.attributes ELSE state_attributes.shared_attrs END AS attributes 
FROM states LEFT OUTER JOIN state_attributes ON states.attributes_id = state_attributes.attributes_id 
WHERE (states.last_changed_ts = states.last_updated_ts OR states.last_changed_ts IS NULL) AND states.last_updated_ts > ? AND states.metadata_id = ? ORDER BY states.metadata_id, states.last_updated_ts
 LIMIT ? OFFSET ?]
[parameters: (1711562266.820155, 681, 35, 0)]
(Background on this error at: https://sqlalche.me/e/20/e3q8)
2024-03-27 12:07:47.009 ERROR (DbWorker_1) [homeassistant.components.recorder.util] Error executing query: (sqlite3.OperationalError) no such column: states.last_reported_ts
[SQL: SELECT states.metadata_id, states.state, states.last_updated_ts, states.last_reported_ts, CASE WHEN (state_attributes.shared_attrs IS NULL) THEN states.attributes ELSE state_attributes.shared_attrs END AS attributes 
FROM states LEFT OUTER JOIN state_attributes ON states.attributes_id = state_attributes.attributes_id 
WHERE (states.last_changed_ts = states.last_updated_ts OR states.last_changed_ts IS NULL) AND states.last_updated_ts > ? AND states.metadata_id = ? ORDER BY states.metadata_id, states.last_updated_ts
 LIMIT ? OFFSET ?]
[parameters: (1711562266.820155, 681, 35, 0)]
(Background on this error at: https://sqlalche.me/e/20/e3q8)
2024-03-27 12:07:47.013 ERROR (DbWorker_0) [homeassistant.components.recorder.util] Error executing query: (sqlite3.OperationalError) no such column: states.last_reported_ts
[SQL: SELECT states.metadata_id, states.state, states.last_updated_ts, states.last_reported_ts, CASE WHEN (state_attributes.shared_attrs IS NULL) THEN states.attributes ELSE state_attributes.shared_attrs END AS attributes 
FROM states LEFT OUTER JOIN state_attributes ON states.attributes_id = state_attributes.attributes_id 
WHERE (states.last_changed_ts = states.last_updated_ts OR states.last_changed_ts IS NULL) AND states.last_updated_ts > ? AND states.metadata_id = ? ORDER BY states.metadata_id, states.last_updated_ts
 LIMIT ? OFFSET ?]
[parameters: (1711562266.829372, 682, 35, 0)]
(Background on this error at: https://sqlalche.me/e/20/e3q8)
2024-03-27 12:07:47.153 ERROR (DbWorker_1) [homeassistant.components.recorder.util] Error executing query: (sqlite3.OperationalError) no such column: states.last_reported_ts
[SQL: SELECT states.metadata_id, states.state, states.last_updated_ts, states.last_reported_ts, CASE WHEN (state_attributes.shared_attrs IS NULL) THEN states.attributes ELSE state_attributes.shared_attrs END AS attributes 
FROM states LEFT OUTER JOIN state_attributes ON states.attributes_id = state_attributes.attributes_id 
WHERE (states.last_changed_ts = states.last_updated_ts OR states.last_changed_ts IS NULL) AND states.last_updated_ts > ? AND states.metadata_id = ? ORDER BY states.metadata_id, states.last_updated_ts
 LIMIT ? OFFSET ?]
[parameters: (1711562266.820155, 681, 35, 0)]
(Background on this error at: https://sqlalche.me/e/20/e3q8)
2024-03-27 12:07:47.156 ERROR (DbWorker_0) [homeassistant.components.recorder.util] Error executing query: (sqlite3.OperationalError) no such column: states.last_reported_ts
[SQL: SELECT states.metadata_id, states.state, states.last_updated_ts, states.last_reported_ts, CASE WHEN (state_attributes.shared_attrs IS NULL) THEN states.attributes ELSE state_attributes.shared_attrs END AS attributes 
FROM states LEFT OUTER JOIN state_attributes ON states.attributes_id = state_attributes.attributes_id 
WHERE (states.last_changed_ts = states.last_updated_ts OR states.last_changed_ts IS NULL) AND states.last_updated_ts > ? AND states.metadata_id = ? ORDER BY states.metadata_id, states.last_updated_ts
 LIMIT ? OFFSET ?]
[parameters: (1711562266.829372, 682, 35, 0)]
(Background on this error at: https://sqlalche.me/e/20/e3q8)
2024-03-27 12:07:47.156 ERROR (DbWorker_0) [homeassistant.components.recorder.util] Error executing query: (sqlite3.OperationalError) no such column: states.last_reported_ts
[SQL: SELECT states.metadata_id, states.state, states.last_updated_ts, states.last_reported_ts, CASE WHEN (state_attributes.shared_attrs IS NULL) THEN states.attributes ELSE state_attributes.shared_attrs END AS attributes 
FROM states LEFT OUTER JOIN state_attributes ON states.attributes_id = state_attributes.attributes_id 
WHERE (states.last_changed_ts = states.last_updated_ts OR states.last_changed_ts IS NULL) AND states.last_updated_ts > ? AND states.metadata_id = ? ORDER BY states.metadata_id, states.last_updated_ts
 LIMIT ? OFFSET ?]
[parameters: (1711562266.829372, 682, 35, 0)]
(Background on this error at: https://sqlalche.me/e/20/e3q8)
Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1971, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 919, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: no such column: states.last_reported_ts

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 141, in session_scope
    yield session
  File "/usr/src/homeassistant/homeassistant/components/recorder/history/modern.py", line 437, in state_changes_during_period
    execute_stmt_lambda_element(
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 232, in execute_stmt_lambda_element
    executed = session.connection().execute(stmt)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1422, in execute
    return meth(
           ^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/lambdas.py", line 603, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1644, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1850, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1990, in _exec_single_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2357, 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 1971, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 919, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column: states.last_reported_ts
[SQL: SELECT states.metadata_id, states.state, states.last_updated_ts, states.last_reported_ts, CASE WHEN (state_attributes.shared_attrs IS NULL) THEN states.attributes ELSE state_attributes.shared_attrs END AS attributes 
FROM states LEFT OUTER JOIN state_attributes ON states.attributes_id = state_attributes.attributes_id 
WHERE (states.last_changed_ts = states.last_updated_ts OR states.last_changed_ts IS NULL) AND states.last_updated_ts > ? AND states.metadata_id = ? ORDER BY states.metadata_id, states.last_updated_ts
 LIMIT ? OFFSET ?]
[parameters: (1711562266.829372, 682, 35, 0)]
(Background on this error at: https://sqlalche.me/e/20/e3q8)
2024-03-27 12:07:47.153 ERROR (DbWorker_1) [homeassistant.components.recorder.util] Error executing query: (sqlite3.OperationalError) no such column: states.last_reported_ts
[SQL: SELECT states.metadata_id, states.state, states.last_updated_ts, states.last_reported_ts, CASE WHEN (state_attributes.shared_attrs IS NULL) THEN states.attributes ELSE state_attributes.shared_attrs END AS attributes 
FROM states LEFT OUTER JOIN state_attributes ON states.attributes_id = state_attributes.attributes_id 
WHERE (states.last_changed_ts = states.last_updated_ts OR states.last_changed_ts IS NULL) AND states.last_updated_ts > ? AND states.metadata_id = ? ORDER BY states.metadata_id, states.last_updated_ts
 LIMIT ? OFFSET ?]
[parameters: (1711562266.820155, 681, 35, 0)]
(Background on this error at: https://sqlalche.me/e/20/e3q8)
Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1971, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 919, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: no such column: states.last_reported_ts

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 141, in session_scope
    yield session
  File "/usr/src/homeassistant/homeassistant/components/recorder/history/modern.py", line 437, in state_changes_during_period
    execute_stmt_lambda_element(
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 232, in execute_stmt_lambda_element
    executed = session.connection().execute(stmt)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1422, in execute
    return meth(
           ^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/lambdas.py", line 603, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1644, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1850, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1990, in _exec_single_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2357, 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 1971, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 919, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column: states.last_reported_ts
[SQL: SELECT states.metadata_id, states.state, states.last_updated_ts, states.last_reported_ts, CASE WHEN (state_attributes.shared_attrs IS NULL) THEN states.attributes ELSE state_attributes.shared_attrs END AS attributes 
FROM states LEFT OUTER JOIN state_attributes ON states.attributes_id = state_attributes.attributes_id 
WHERE (states.last_changed_ts = states.last_updated_ts OR states.last_changed_ts IS NULL) AND states.last_updated_ts > ? AND states.metadata_id = ? ORDER BY states.metadata_id, states.last_updated_ts
 LIMIT ? OFFSET ?]
[parameters: (1711562266.820155, 681, 35, 0)]
(Background on this error at: https://sqlalche.me/e/20/e3q8)
2024-03-27 12:07:48.454 WARNING (Recorder) [homeassistant.components.recorder.migration] Database is about to upgrade from schema version: 42 to: 43
2024-03-27 12:07:48.454 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding columns last_reported_ts to table states. Note: this can take several minutes on large databases and slow computers. Please be patient!
2024-03-27 12:07:48.474 WARNING (Recorder) [homeassistant.components.recorder.migration] Upgrade to version 43 done
2024-03-27 12:07:48.460 ERROR (MainThread) [homeassistant] Error doing job: Task exception was never retrieved
Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1971, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 919, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: no such column: states.last_reported_ts

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/statistics/sensor.py", line 586, in _initialize_from_database
    if states := await get_instance(self.hass).async_add_executor_job(
                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/concurrent/futures/thread.py", line 58, in run
    result = self.fn(*self.args, **self.kwargs)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/statistics/sensor.py", line 567, in _fetch_states_from_database
    return history.state_changes_during_period(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/recorder/history/__init__.py", line 175, in state_changes_during_period
    return _target(
           ^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/recorder/history/modern.py", line 437, in state_changes_during_period
    execute_stmt_lambda_element(
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 232, in execute_stmt_lambda_element
    executed = session.connection().execute(stmt)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1422, in execute
    return meth(
           ^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/lambdas.py", line 603, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1644, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1850, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1990, in _exec_single_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2357, 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 1971, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 919, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column: states.last_reported_ts
[SQL: SELECT states.metadata_id, states.state, states.last_updated_ts, states.last_reported_ts, CASE WHEN (state_attributes.shared_attrs IS NULL) THEN states.attributes ELSE state_attributes.shared_attrs END AS attributes 
FROM states LEFT OUTER JOIN state_attributes ON states.attributes_id = state_attributes.attributes_id 
WHERE (states.last_changed_ts = states.last_updated_ts OR states.last_changed_ts IS NULL) AND states.last_updated_ts > ? AND states.metadata_id = ? ORDER BY states.metadata_id, states.last_updated_ts
 LIMIT ? OFFSET ?]
[parameters: (1711562266.829372, 682, 35, 0)]
(Background on this error at: https://sqlalche.me/e/20/e3q8)
2024-03-27 12:07:48.540 ERROR (MainThread) [homeassistant] Error doing job: Task exception was never retrieved
Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1971, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 919, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: no such column: states.last_reported_ts

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/statistics/sensor.py", line 586, in _initialize_from_database
    if states := await get_instance(self.hass).async_add_executor_job(
                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/concurrent/futures/thread.py", line 58, in run
    result = self.fn(*self.args, **self.kwargs)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/statistics/sensor.py", line 567, in _fetch_states_from_database
    return history.state_changes_during_period(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/recorder/history/__init__.py", line 175, in state_changes_during_period
    return _target(
           ^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/recorder/history/modern.py", line 437, in state_changes_during_period
    execute_stmt_lambda_element(
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 232, in execute_stmt_lambda_element
    executed = session.connection().execute(stmt)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1422, in execute
    return meth(
           ^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/lambdas.py", line 603, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1644, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1850, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1990, in _exec_single_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2357, 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 1971, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 919, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column: states.last_reported_ts
[SQL: SELECT states.metadata_id, states.state, states.last_updated_ts, states.last_reported_ts, CASE WHEN (state_attributes.shared_attrs IS NULL) THEN states.attributes ELSE state_attributes.shared_attrs END AS attributes 
FROM states LEFT OUTER JOIN state_attributes ON states.attributes_id = state_attributes.attributes_id 
WHERE (states.last_changed_ts = states.last_updated_ts OR states.last_changed_ts IS NULL) AND states.last_updated_ts > ? AND states.metadata_id = ? ORDER BY states.metadata_id, states.last_updated_ts
 LIMIT ? OFFSET ?]
[parameters: (1711562266.820155, 681, 35, 0)]

CC: @bdraco

What version of Home Assistant Core has the issue?

2024.4.0b0

What was the last working version of Home Assistant Core?

2024.3.3

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?

No response

Additional information

No response

The text was updated successfully, but these errors were encountered:

home-assistant · 2024-03-27T18:57:38Z

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)}}

joostlek · 2024-03-27T18:59:23Z

Probably also cc @emontnemery

firstof9 · 2024-03-27T19:03:58Z

Adding my trace to the issue as requested:

2024-03-27 11:59:09.075 ERROR (DbWorker_1) [homeassistant.components.recorder.util] Error executing query: (MySQLdb.OperationalError) (1054, "Unknown column 'states.last_reported_ts' in 'field list'")
[SQL: SELECT states.metadata_id, states.state, states.last_updated_ts, states.last_reported_ts, CASE WHEN (state_attributes.shared_attrs IS NULL) THEN states.attributes ELSE state_attributes.shared_attrs END AS attributes 
FROM states LEFT OUTER JOIN state_attributes ON states.attributes_id = state_attributes.attributes_id 
WHERE (states.last_changed_ts = states.last_updated_ts OR states.last_changed_ts IS NULL) AND states.last_updated_ts > %s AND states.metadata_id = %s ORDER BY states.metadata_id, states.last_updated_ts 
 LIMIT %s]
[parameters: (0.0, 1363, 20)]
(Background on this error at: https://sqlalche.me/e/20/e3q8)
2024-03-27 11:59:09.187 ERROR (DbWorker_1) [homeassistant.components.recorder.util] Error executing query: (MySQLdb.OperationalError) (1054, "Unknown column 'states.last_reported_ts' in 'field list'")
[SQL: SELECT states.metadata_id, states.state, states.last_updated_ts, states.last_reported_ts, CASE WHEN (state_attributes.shared_attrs IS NULL) THEN states.attributes ELSE state_attributes.shared_attrs END AS attributes 
FROM states LEFT OUTER JOIN state_attributes ON states.attributes_id = state_attributes.attributes_id 
WHERE (states.last_changed_ts = states.last_updated_ts OR states.last_changed_ts IS NULL) AND states.last_updated_ts > %s AND states.metadata_id = %s ORDER BY states.metadata_id, states.last_updated_ts 
 LIMIT %s]
[parameters: (0.0, 1363, 20)]
(Background on this error at: https://sqlalche.me/e/20/e3q8)
2024-03-27 11:59:09.299 ERROR (DbWorker_1) [homeassistant.components.recorder.util] Error executing query: (MySQLdb.OperationalError) (1054, "Unknown column 'states.last_reported_ts' in 'field list'")
[SQL: SELECT states.metadata_id, states.state, states.last_updated_ts, states.last_reported_ts, CASE WHEN (state_attributes.shared_attrs IS NULL) THEN states.attributes ELSE state_attributes.shared_attrs END AS attributes 
FROM states LEFT OUTER JOIN state_attributes ON states.attributes_id = state_attributes.attributes_id 
WHERE (states.last_changed_ts = states.last_updated_ts OR states.last_changed_ts IS NULL) AND states.last_updated_ts > %s AND states.metadata_id = %s ORDER BY states.metadata_id, states.last_updated_ts 
 LIMIT %s]
[parameters: (0.0, 1363, 20)]
(Background on this error at: https://sqlalche.me/e/20/e3q8)
2024-03-27 11:59:09.299 ERROR (DbWorker_1) [homeassistant.components.recorder.util] Error executing query: (MySQLdb.OperationalError) (1054, "Unknown column 'states.last_reported_ts' in 'field list'")
[SQL: SELECT states.metadata_id, states.state, states.last_updated_ts, states.last_reported_ts, CASE WHEN (state_attributes.shared_attrs IS NULL) THEN states.attributes ELSE state_attributes.shared_attrs END AS attributes 
FROM states LEFT OUTER JOIN state_attributes ON states.attributes_id = state_attributes.attributes_id 
WHERE (states.last_changed_ts = states.last_updated_ts OR states.last_changed_ts IS NULL) AND states.last_updated_ts > %s AND states.metadata_id = %s ORDER BY states.metadata_id, states.last_updated_ts 
 LIMIT %s]
[parameters: (0.0, 1363, 20)]
(Background on this error at: https://sqlalche.me/e/20/e3q8)
Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1971, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 919, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.12/site-packages/MySQLdb/cursors.py", line 179, in execute
    res = self._query(mogrified_query)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/MySQLdb/cursors.py", line 330, in _query
    db.query(q)
  File "/usr/local/lib/python3.12/site-packages/MySQLdb/connections.py", line 261, in query
    _mysql.connection.query(self, query)
MySQLdb.OperationalError: (1054, "Unknown column 'states.last_reported_ts' in 'field list'")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 141, in session_scope
    yield session
  File "/usr/src/homeassistant/homeassistant/components/recorder/history/modern.py", line 437, in state_changes_during_period
    execute_stmt_lambda_element(
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 232, in execute_stmt_lambda_element
    executed = session.connection().execute(stmt)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1422, in execute
    return meth(
           ^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/lambdas.py", line 603, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1644, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1850, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1990, in _exec_single_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2357, 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 1971, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 919, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.12/site-packages/MySQLdb/cursors.py", line 179, in execute
    res = self._query(mogrified_query)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/MySQLdb/cursors.py", line 330, in _query
    db.query(q)
  File "/usr/local/lib/python3.12/site-packages/MySQLdb/connections.py", line 261, in query
    _mysql.connection.query(self, query)
sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'states.last_reported_ts' in 'field list'")
[SQL: SELECT states.metadata_id, states.state, states.last_updated_ts, states.last_reported_ts, CASE WHEN (state_attributes.shared_attrs IS NULL) THEN states.attributes ELSE state_attributes.shared_attrs END AS attributes 
FROM states LEFT OUTER JOIN state_attributes ON states.attributes_id = state_attributes.attributes_id 
WHERE (states.last_changed_ts = states.last_updated_ts OR states.last_changed_ts IS NULL) AND states.last_updated_ts > %s AND states.metadata_id = %s ORDER BY states.metadata_id, states.last_updated_ts 
 LIMIT %s]
[parameters: (0.0, 1363, 20)]
(Background on this error at: https://sqlalche.me/e/20/e3q8)
2024-03-27 11:59:11.739 ERROR (MainThread) [homeassistant] Error doing job: Task exception was never retrieved
Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1971, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 919, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.12/site-packages/MySQLdb/cursors.py", line 179, in execute
    res = self._query(mogrified_query)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/MySQLdb/cursors.py", line 330, in _query
    db.query(q)
  File "/usr/local/lib/python3.12/site-packages/MySQLdb/connections.py", line 261, in query
    _mysql.connection.query(self, query)
MySQLdb.OperationalError: (1054, "Unknown column 'states.last_reported_ts' in 'field list'")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/statistics/sensor.py", line 586, in _initialize_from_database
    if states := await get_instance(self.hass).async_add_executor_job(
                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/concurrent/futures/thread.py", line 58, in run
    result = self.fn(*self.args, **self.kwargs)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/statistics/sensor.py", line 567, in _fetch_states_from_database
    return history.state_changes_during_period(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/recorder/history/__init__.py", line 175, in state_changes_during_period
    return _target(
           ^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/recorder/history/modern.py", line 437, in state_changes_during_period
    execute_stmt_lambda_element(
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 232, in execute_stmt_lambda_element
    executed = session.connection().execute(stmt)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1422, in execute
    return meth(
           ^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/lambdas.py", line 603, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1644, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1850, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1990, in _exec_single_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2357, 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 1971, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 919, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.12/site-packages/MySQLdb/cursors.py", line 179, in execute
    res = self._query(mogrified_query)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/MySQLdb/cursors.py", line 330, in _query
    db.query(q)
  File "/usr/local/lib/python3.12/site-packages/MySQLdb/connections.py", line 261, in query
    _mysql.connection.query(self, query)
sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'states.last_reported_ts' in 'field list'")
[SQL: SELECT states.metadata_id, states.state, states.last_updated_ts, states.last_reported_ts, CASE WHEN (state_attributes.shared_attrs IS NULL) THEN states.attributes ELSE state_attributes.shared_attrs END AS attributes 
FROM states LEFT OUTER JOIN state_attributes ON states.attributes_id = state_attributes.attributes_id 
WHERE (states.last_changed_ts = states.last_updated_ts OR states.last_changed_ts IS NULL) AND states.last_updated_ts > %s AND states.metadata_id = %s ORDER BY states.metadata_id, states.last_updated_ts 
 LIMIT %s]
[parameters: (0.0, 1363, 20)]
(Background on this error at: https://sqlalche.me/e/20/e3q8)

bdraco · 2024-03-27T19:19:34Z

Looks like the queries need to exclude that column until the schema version is new enough

home-assistant bot added the integration: recorder label Mar 27, 2024

joostlek mentioned this issue Mar 27, 2024

Plant does not work (SQL error) #114328

Closed

joostlek added this to the 2024.4.0 milestone Mar 27, 2024

emontnemery mentioned this issue Mar 27, 2024

Don't access States.last_reported_ts before it's added #114333

Merged

20 tasks

frenck modified the milestone: 2024.4.0 Mar 27, 2024

frenck closed this as completed in #114333 Mar 27, 2024

github-actions bot locked and limited conversation to collaborators Apr 27, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Sqlite3 Operational Errors #114327

Sqlite3 Operational Errors #114327

Anto79-ops commented Mar 27, 2024

home-assistant bot commented Mar 27, 2024

joostlek commented Mar 27, 2024

firstof9 commented Mar 27, 2024

bdraco commented Mar 27, 2024

Sqlite3 Operational Errors #114327

Sqlite3 Operational Errors #114327

Comments

Anto79-ops commented Mar 27, 2024

The problem

What version of Home Assistant Core has the issue?

What was the last working version of Home Assistant Core?

What type of installation are you running?

Integration causing the issue

Link to integration documentation on our website

Diagnostics information

Example YAML snippet

Anything in the logs that might be useful for us?

Additional information

home-assistant bot commented Mar 27, 2024

joostlek commented Mar 27, 2024

firstof9 commented Mar 27, 2024

bdraco commented Mar 27, 2024