Error executing post migrate states entity_ids to states_meta #90892

vturekhanov · 2023-04-06T02:58:03Z

The problem

During first start after upgrading from 2023.3.6 to 2023.4.0 got a following errors in HA log.
I use external MySQL server, version 8.0.30.

What version of Home Assistant Core has the issue?

core-2023.4.0

What was the last working version of Home Assistant Core?

core-2023.3.6

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?

2023-04-06 08:36:47.152 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: (MySQLdb.OperationalError) (1093, "You can't specify target table 'states' for update in FROM clause")
[SQL: UPDATE states SET entity_id=%s WHERE states.state_id IN (SELECT states.state_id 
FROM states INNER JOIN (SELECT states.state_id AS state_id_with_entity_id 
FROM states 
WHERE states.entity_id IS NOT NULL 
 LIMIT %s) AS anon_1 ON states.state_id = anon_1.state_id_with_entity_id)]
[parameters: (None, 5000)]
(Background on this error at: https://sqlalche.me/e/20/e3q8)
Traceback (most recent call last):
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1964, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 748, 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.OperationalError: (1093, "You can't specify target table 'states' for update in FROM clause")

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 129, in session_scope
    yield session
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 1580, in post_migrate_entity_ids
    cursor_result = session.connection().execute(batch_cleanup_entity_ids())
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1414, in execute
    return meth(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/lambdas.py", line 605, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1638, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1842, in _execute_context
    return self._exec_single_context(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1983, in _exec_single_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2326, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1964, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 748, 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.OperationalError: (MySQLdb.OperationalError) (1093, "You can't specify target table 'states' for update in FROM clause")
[SQL: UPDATE states SET entity_id=%s WHERE states.state_id IN (SELECT states.state_id 
FROM states INNER JOIN (SELECT states.state_id AS state_id_with_entity_id 
FROM states 
WHERE states.entity_id IS NOT NULL 
 LIMIT %s) AS anon_1 ON states.state_id = anon_1.state_id_with_entity_id)]
[parameters: (None, 5000)]
(Background on this error at: https://sqlalche.me/e/20/e3q8)
2023-04-06 08:36:47.222 WARNING (Recorder) [homeassistant.components.recorder.util] Error executing post migrate states entity_ids to states_meta: (MySQLdb.OperationalError) (1093, "You can't specify target table 'states' for update in FROM clause")
[SQL: UPDATE states SET entity_id=%s WHERE states.state_id IN (SELECT states.state_id 
FROM states INNER JOIN (SELECT states.state_id AS state_id_with_entity_id 
FROM states 
WHERE states.entity_id IS NOT NULL 
 LIMIT %s) AS anon_1 ON states.state_id = anon_1.state_id_with_entity_id)]
[parameters: (None, 5000)]
(Background on this error at: https://sqlalche.me/e/20/e3q8)
2023-04-06 08:42:24.241 ERROR (MainThread) [frontend.js.latest.202304050] :0:0 ResizeObserver loop completed with undelivered notifications.

Additional information

No response

home-assistant · 2023-04-06T02:58:06Z

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.

_{^{(message by CodeOwnersMention)}}

recorder documentation
recorder source
_{^{(message by IssueLinks)}}

bdraco · 2023-04-06T03:40:14Z

I just tried that query on MariaDB and it works fine

MariaDB [hass]> UPDATE 
    ->   states 
    -> SET 
    ->   entity_id = NULL 
    -> WHERE 
    ->   states.state_id IN (
    ->     SELECT 
    ->       states.state_id 
    ->     FROM 
    ->       states 
    ->       INNER JOIN (
    ->         SELECT 
    ->           states.state_id AS state_id_with_entity_id 
    ->         FROM 
    ->           states 
    ->         WHERE 
    ->           states.entity_id IS NOT NULL 
    ->         LIMIT 
    ->           5000
    ->       ) AS anon_1 ON states.state_id = anon_1.state_id_with_entity_id
    ->   );

Query OK, 5000 rows affected (2.540 sec)
Rows matched: 5000  Changed: 5000  Warnings: 0

bdraco · 2023-04-06T03:40:48Z

Does it fail if you run it manually?

UPDATE 
  states 
SET 
  entity_id = NULL 
WHERE 
  states.state_id IN (
    SELECT 
      states.state_id 
    FROM 
      states 
      INNER JOIN (
        SELECT 
          states.state_id AS state_id_with_entity_id 
        FROM 
          states 
        WHERE 
          states.entity_id IS NOT NULL 
        LIMIT 
          5000
      ) AS anon_1 ON states.state_id = anon_1.state_id_with_entity_id
  );

bdraco · 2023-04-06T03:41:52Z

Also if it does fail,

    SELECT 
      states.state_id 
    FROM 
      states 
      INNER JOIN (
        SELECT 
          states.state_id AS state_id_with_entity_id 
        FROM 
          states 
        WHERE 
          states.entity_id IS NOT NULL 
        LIMIT 
          5000
      ) AS anon_1 ON states.state_id = anon_1.state_id_with_entity_id

fail as well?

bdraco · 2023-04-06T03:46:15Z

https://groups.google.com/g/sqlalchemy/c/T-X9gxuoqA8

bdraco · 2023-04-06T03:46:25Z

Similar issue from the past #11713

This query worked fine on MariaDB but failed on MySQL 8.0.30 fixes #90892

vturekhanov · 2023-04-06T04:54:01Z

Does it fail if you run it manually?

UPDATE 
  states 
SET 
  entity_id = NULL 
WHERE 
  states.state_id IN (
    SELECT 
      states.state_id 
    FROM 
      states 
      INNER JOIN (
        SELECT 
          states.state_id AS state_id_with_entity_id 
        FROM 
          states 
        WHERE 
          states.entity_id IS NOT NULL 
        LIMIT 
          5000
      ) AS anon_1 ON states.state_id = anon_1.state_id_with_entity_id
  );

Fail

vturekhanov · 2023-04-06T04:55:15Z

Also if it does fail,

    SELECT 
      states.state_id 
    FROM 
      states 
      INNER JOIN (
        SELECT 
          states.state_id AS state_id_with_entity_id 
        FROM 
          states 
        WHERE 
          states.entity_id IS NOT NULL 
        LIMIT 
          5000
      ) AS anon_1 ON states.state_id = anon_1.state_id_with_entity_id

fail as well?

It works.

bdraco · 2023-04-06T04:55:41Z

Well thats annoying that it works on MariaDB but fails on MySQL

bdraco · 2023-04-06T05:12:42Z

This problem won't actually cause any breakage. The worst that happens is there is some data that is not cleaned up until old rows are purged during normal nightly purge.

bdraco · 2023-04-06T05:13:09Z

We will get the query adjusted to work with MySQL. Thanks for the report

bdraco · 2023-04-06T05:13:44Z

I also opened #90898 to add MySQL to the CI since we currently only test against MariaDB

home-assistant bot added the integration: recorder label Apr 6, 2023

bdraco added the database: mysql label Apr 6, 2023

bdraco added a commit that referenced this issue Apr 6, 2023

Fix entity_id migration query failing with MySQL 8.0.30

6143f16

This query worked fine on MariaDB but failed on MySQL 8.0.30 fixes #90892

This was referenced Apr 6, 2023

Fix entity_id migration query failing with MySQL 8.0.30 #90895

Merged

Add mysql 8.0.32 to the CI #90898

Merged

frenck closed this as completed in #90895 Apr 6, 2023

github-actions bot locked and limited conversation to collaborators May 6, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Error executing post migrate states entity_ids to states_meta #90892

Error executing post migrate states entity_ids to states_meta #90892

vturekhanov commented Apr 6, 2023

home-assistant bot commented Apr 6, 2023

bdraco commented Apr 6, 2023

bdraco commented Apr 6, 2023

bdraco commented Apr 6, 2023

bdraco commented Apr 6, 2023

bdraco commented Apr 6, 2023

vturekhanov commented Apr 6, 2023

vturekhanov commented Apr 6, 2023

bdraco commented Apr 6, 2023

bdraco commented Apr 6, 2023

bdraco commented Apr 6, 2023

bdraco commented Apr 6, 2023

Error executing post migrate states entity_ids to states_meta #90892

Error executing post migrate states entity_ids to states_meta #90892

Comments

vturekhanov commented Apr 6, 2023

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 Apr 6, 2023

bdraco commented Apr 6, 2023

bdraco commented Apr 6, 2023

bdraco commented Apr 6, 2023

bdraco commented Apr 6, 2023

bdraco commented Apr 6, 2023

vturekhanov commented Apr 6, 2023

vturekhanov commented Apr 6, 2023

bdraco commented Apr 6, 2023

bdraco commented Apr 6, 2023

bdraco commented Apr 6, 2023

bdraco commented Apr 6, 2023