Comments (4)
bdraco
commented on September 26, 2024
2
Looks like the queries need to exclude that column until the schema version is new enough
from core.
home-assistant
commented on September 26, 2024
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 closeCloses the issue.@home-assistant rename Awesome new titleRenames the issue.@home-assistant reopenReopen the issue.@home-assistant unassign recorderRemoves the current integration label and assignees on the issue, add the integration domain after the command.@home-assistant add-label needs-more-informationAdd a label (needs-more-information, problem in dependency, problem in custom component) to the issue.@home-assistant remove-label needs-more-informationRemove 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)
from core.
joostlek
commented on September 26, 2024
Probably also cc @emontnemery
from core.
firstof9
commented on September 26, 2024
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)
from core.
Related Issues (20)
- Cannot change credentials of user HOT 1
- WARNING: Waiting on integrations to complete setup HOT 4
- duplicated upnp devices detected HOT 2
- Airtouch 5 integration stopped working after console upgrade HOT 3
- Synology photos shared items are not visible in the media browser HOT 15
- Tuya fan entity (Duux Whisper Flex) automatically starts oscillating when clicked wherever in the interface HOT 1
- Here Travel Time detected blocking call to listdir with args / 2024.7.0b4 HOT 3
- Smartlife Tuya integration HOT 6
- Error handling request - ConnectionResetError: Cannot write to closing transport HOT 8
- Somfy Cover shows strange and missing values. HOT 9
- Met.no not reporting weather data HOT 5
- Buienradar unable to retrieve data HOT 17
- AlarmDecoder integration sometimes internally crashes and silently stops updating HOT 3
- Amber Electric integration suddenly failing HOT 16
- Clear nights reporting as sunny on weather forecast with Openweathermap HOT 5
- Blink: Fails to properly recover after a system restart. HOT 1
- Extremely poor performance of compilation of statistics on MariaDB HOT 8
- Tuya integration problem HOT 2
- Turning on a Samsung Windfree airco via HA turns the display light on HOT 1
- Energy Dashboard doesn't generate a _cost if the value is negative
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from core.