problems with 2022.5 and 2022.6

[50494554524F]

2022.5, simple test script

  sequence:

    - service: var.set
      target:
        entity_id: var.sql
      data:
        query: 'SELECT COUNT( * ) AS value FROM states WHERE entity_id = "whateverentityid"'
        column: 'value'

    - service: var.update
      target:
        entity_id: var.sql

    - service: persistent_notification.create
      data:
        title: 'aaa'
        message: "{{ states('var.sql') }}"

i get this warning Detected integration that accesses the database without the database executor; Use homeassistant.components.recorder.get_instance(hass).async_add_executor_job() for faster database operations. Please report issue to the custom component author for var using this method at custom_components/var/__init__.py, line 433: result = self._session.execute(self._query) upgrading to 2022.6 it stops working with error Detected blocking call to _do_get_db_connection_protected inside the event loop. This is causing stability issues. Please report issue for recorder doing blocking calls at homeassistant/components/recorder/pool.py, line 75: check_loop( and

Update for var.sql fails
Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/helpers/entity.py", line 515, in async_update_ha_state
    await self.async_device_update()
  File "/usr/src/homeassistant/homeassistant/helpers/entity.py", line 743, in async_device_update
    raise exc
  File "/config/custom_components/var/__init__.py", line 433, in async_update
    result = self._session.execute(self._query)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1711, in execute
    conn = self._connection_for_bind(bind)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1552, in _connection_for_bind
    return self._transaction._connection_for_bind(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 747, in _connection_for_bind
    conn = bind.connect()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/future/engine.py", line 406, in connect
    return super(Engine, self).connect()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3234, in connect
    return self._connection_cls(self, close_with_result=close_with_result)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 96, in __init__
    else engine.raw_connection()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3313, in raw_connection
    return self._wrap_pool_connect(self.pool.connect, _connection)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3280, in _wrap_pool_connect
    return fn()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/impl.py", line 382, in connect
    return _ConnectionFairy._checkout(self, self._fairy)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 868, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 476, in checkout
    rec = pool._do_get()
  File "/usr/src/homeassistant/homeassistant/components/recorder/pool.py", line 75, in _do_get
    check_loop(
  File "/usr/src/homeassistant/homeassistant/util/async_.py", line 168, in check_loop
    raise RuntimeError(
RuntimeError: Blocking calls must be done in the executor or a separate thread; Use homeassistant.components.recorder.get_instance(hass).async_add_executor_job(); at homeassistant/components/recorder/pool.py, line 75: check_loop(

thanks

edit: forgot... same results if i pass the entity_id under data:

[50494554524F]

breaking change

https://github.com/home-assistant/core/pull/71547

[snarky-snark]

As suggested in the warning, I moved the DB access to the DB executor. I haven't tested this change yet.

@50494554524F, any chance you would want to test this by installing the unreleased version (latest commit on develop branch)?

[50494554524F]

perfectly working! no more errors or warnings... thanks

[50494554524F]

an addition... updating the integration using hacs i still get a warning

Detected integration that accesses the database without the database executor; Use homeassistant.components.recorder.get_instance(hass).async_add_executor_job() for faster database operations. Please report issue to the custom component author for var using this method at custom_components/var/__init__.py, line 436: result = self._session.execute(self._query)

while copying the init.py file i have no warnings...

[snarky-snark]

Strange. Does this happen when you upgrade to v0.13.1 in HACS or the head of develop in HACS?

[50494554524F]

upgrading to 0.13.1 in hacs... tried to copy init.py only, the warning is still there... my configuration didn't change since last night

edit: test script works with no warnings. i'm investigating where my script gives the warning because last night didn't...

[50494554524F]

i was wrong about the warning... it appears only the first time i use .update after a reboot... the scripts work without errors, so it can be ignored (i think)