A question about esphome password deprecation.
Will password eventually be removed and devices with password then stop working?
There seem to be more than a few who lost their yaml files, or maybe bought a pre-flashed device and don’t even have esphome installed, for whom it is difficult re-flash with encryption. Is it safe long term to ignore the new repair issue?
I formerly defined my sql sensor with the following syntax:
sensor:
- platform: sql
db_url: !secret recorder_db_url
queries:
- name: Solar System Daily Utilized Energy History
unit_of_measurement: kWh
device_class: energy
query: "SELECT * FROM last_30_records WHERE entity_id = 'sensor.solar_system_daily_utilized_energy'"
column: "0"
Will this syntax valid again?
No I have no error in the log, but the sensor is not created after restart. The following format works perfectly.
sql:
- name: Solar System Daily Utilized Energy History Test
db_url: !secret recorder_db_url
unit_of_measurement: kWh
query: "SELECT * FROM last_30_records WHERE entity_id = 'sensor.solar_system_daily_utilized_energy'"
column: "0"
I thought log level should have been “info” by default, no? Should I specify that in the configuration.yaml?
logger:
default: info
That appears to be the longest DB update ever happening (for me running on a Quad Core i5 server with 32 GB RAM and a NVMe module with MariaDB 10.6.11 which is just the DB host - HA host is idling as to be expected) - for a 18 GiB database I am 30 minutes in and running…
I think one of the things contributing to the long run time is MariaDB only using one CPU core per session / query and the update is just one query running, to for me it uses ~25% of the CPU.
Not a problem, just an observation…
Any idea how to fix this SQL sensor (MariaDB)?
SELECT state FROM states WHERE entity_id = 'sensor.total_cost_today' AND HOUR(TIMEDIFF(UTC_TIMESTAMP(), last_updated))>=24 ORDER BY last_updated DESC LIMIT 1;
This isn’t it:
SELECT state FROM states WHERE entity_id = 'sensor.total_cost_today' AND HOUR(TIMEDIFF(UTC_TIMESTAMP(), last_updated_ts))>=24 ORDER BY last_updated DESC LIMIT 1;
EDIT: With help from Andre, this is the correct query:
SELECT state FROM states
WHERE entity_id = 'sensor.total_cost_today'
AND last_updated_ts <= UNIX_TIMESTAMP(NOW() - INTERVAL 1 DAY)
ORDER BY last_updated_ts DESC LIMIT 1;
Also I have had to delete every single one of my forty three esphome devices from Devices and Services and add them back again after deleting the password and updating my devices. If I don’t do this I get continual disconnections.
This and the last update certainly have been “challenging”.
I only have 10 esphome devices, but I had to do this with several to get them working again, and to prevent others from being rediscovered every hour.
Oddly, some devices were fine and didn’t need to be deleted/readded. Nothing of value in the logs either.
I use MariaDB. Since Migration to 2023.2 the Log is horrible, but Recorder seems to work.
Anybody has a Solution?
Log:
2023-02-05 08:12:51.545 ERROR (Recorder) [homeassistant.components.recorder.core] Error during connection setup: (MySQLdb.OperationalError) (2005, "Unknown MySQL server host 'core-mariadb' (-2)")
(Background on this error at: https://sqlalche.me/e/14/e3q8) (retrying in 3 seconds)
Traceback (most recent call last):
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3361, in _wrap_pool_connect
return fn()
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 325, in connect
return _ConnectionFairy._checkout(self)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 888, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 491, in checkout
rec = pool._do_get()
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 145, in _do_get
with util.safe_reraise():
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
compat.raise_(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
raise exception
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 143, in _do_get
return self._create_connection()
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 271, in _create_connection
return _ConnectionRecord(self)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 386, in __init__
self.__connect()
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 684, in __connect
with util.safe_reraise():
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
compat.raise_(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
raise exception
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 680, in __connect
self.dbapi_connection = connection = pool._invoke_creator(self)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/create.py", line 578, in connect
return dialect.connect(*cargs, **cparams)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 598, in connect
return self.dbapi.connect(*cargs, **cparams)
File "/usr/local/lib/python3.10/site-packages/MySQLdb/__init__.py", line 123, in Connect
return Connection(*args, **kwargs)
File "/usr/local/lib/python3.10/site-packages/MySQLdb/connections.py", line 185, in __init__
super().__init__(*args, **kwargs2)
MySQLdb.OperationalError: (2005, "Unknown MySQL server host 'core-mariadb' (-2)")
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 704, in _setup_recorder
self._setup_connection()
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 1173, in _setup_connection
Base.metadata.create_all(self.engine)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/schema.py", line 4930, in create_all
bind._run_ddl_visitor(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/future/engine.py", line 341, in _run_ddl_visitor
with self.begin() as conn:
File "/usr/local/lib/python3.10/contextlib.py", line 135, in __enter__
return next(self.gen)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/future/engine.py", line 379, in begin
with self.connect() as conn:
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/future/engine.py", line 406, in connect
return super(Engine, self).connect()
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3315, in connect
return self._connection_cls(self, close_with_result=close_with_result)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 96, in __init__
else engine.raw_connection()
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3394, in raw_connection
return self._wrap_pool_connect(self.pool.connect, _connection)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3364, in _wrap_pool_connect
Connection._handle_dbapi_exception_noconnection(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2198, in _handle_dbapi_exception_noconnection
util.raise_(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
raise exception
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3361, in _wrap_pool_connect
return fn()
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 325, in connect
return _ConnectionFairy._checkout(self)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 888, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 491, in checkout
rec = pool._do_get()
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 145, in _do_get
with util.safe_reraise():
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
compat.raise_(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
raise exception
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 143, in _do_get
return self._create_connection()
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 271, in _create_connection
return _ConnectionRecord(self)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 386, in __init__
self.__connect()
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 684, in __connect
with util.safe_reraise():
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
compat.raise_(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
raise exception
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 680, in __connect
self.dbapi_connection = connection = pool._invoke_creator(self)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/create.py", line 578, in connect
return dialect.connect(*cargs, **cparams)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 598, in connect
return self.dbapi.connect(*cargs, **cparams)
File "/usr/local/lib/python3.10/site-packages/MySQLdb/__init__.py", line 123, in Connect
return Connection(*args, **kwargs)
File "/usr/local/lib/python3.10/site-packages/MySQLdb/connections.py", line 185, in __init__
super().__init__(*args, **kwargs2)
sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (2005, "Unknown MySQL server host 'core-mariadb' (-2)")
(Background on this error at: https://sqlalche.me/e/14/e3q8)
The mold indicator integration is no longer initialising as it did in the previous version, https://github.com/home-assistant/core/issues/87464
There is no mention of MariaDB 10.9.x, where some patches are older than 10.8.4.
Is there an “or later” missing from the description?
MariaDB versions > 10.8 have not been tested, they probably work fine, but may have different regressions.
Right!
Guess I soon will venture into the unknown then 
I’m sure it will make it back in again soon.
It’s funny that it was when testing the new Group sensor that I found the need for it. Averaging 3 temps led to a recurring decimal!
Is there anything I’m doing wrong or is assis: xyz on or xyz off really not working and I always have to (type/say) turn xyz on, turn xyz off? Or only a problem with German language? Example domain: light.
I’m really not used to use this longer version via Alexa and to be honest, it would be better to have it as short as possible as well in internal HA assist.
Were there any power level tuning for bluetooth in this release?
I had zigbee and BT adapter connected to the same hub, very close to each other and I know - not ideal. Up to this release both radios were working OK, but after the upgrade the zigbee can’t create the network if the BT dongle is also plugged.
Hi Xander1,
Indeed a great integration, and the possibility to add static price components would be a good improvement! I’'m new here, so what is te right way to aks for such a improvement?
Greetings, Bob.
That would be the feature requests section of the forum.
Thanks, I started a topic in that section.
Greetings, Bob.
After database migration, the table “states” no longer have a date value available in columns last_changed or lastupdated, so my sql max/min serach no longer works:
SELECT MAX(cast(state as decimal)) AS value FROM states WHERE entity_id='sensor.temperatura_giardino' AND state!='unknown' AND datetime(last_updated) >= datetime(date('now'));
How to re-enable it?