Hiya, you sound like you’re at my level 
Go to Settings / Add ons / Maria DB, check that you’re running v 2.5.2 (you should be if you’ve kept MariaDB updated), if so you’re golden
Thanks for the hint. Although I only have approx 600MB the update to 2.5.2 took some time.
Upgrade from 2012.12 to 2023.2 went smooth … kudos to the developers.
1 Like
Watching my Database Memory and CPU Use helped me know when it was over
1 Like
" ESPHome is deprecating the old password-based authentication for its API in favor of the more secure encryption key. If one of your ESPHome devices is still using a plain password, Home Assistant will notify you by creating an issue in your Repairs dashboard."
What would be really useful is a clear set of instructions on how to resolve this. What do we need to replace and how, without crippling our ESPHome devices with experiments…
Could you link the notes above to this article
2023.2: ESPHome deprecated API password: how to update to encryption key - ESPHome - Home Assistant Community (home-assistant.io)
Or even link it from the notification in the dashboard?
3 Likes
What a great release and special thanks you for the EnergyZero integration!
I’m currently using Nord Pool for hourly energy prices and a custom REST call to the energyzero API for daily gas prices.
Unfortunately it doesn’t seem possible to add VAT or other static pricing to the EnergyZero prices visible in Home Assist (unless creating new sensors and calculating them multiple times). Am I wrong?
The change has an impact on SQL sensors though — I’ve had to go looking to work out why one of my sensors stopped working, and have fielded a couple of related questions [1], [2].
Unless you’re suggesting SQL sensors from the HA database are discouraged? Looks much harder to solve my requirement through the history API, for example.
I do agree. Bet even worse; its about Home Automation, not remote controling. I use HA to automate my home, so I almost never have to touch it or talk to it 
2 Likes
I have my database corrupted and lost all history. Luckily I had a backup from yesterday.
I had just a bit more than 1gb of free disk space available and now, after restore, it consumes all of it.
It is probably why it got corrupted. Looking back, I had to have 10gb of free disk space (my partition did grow from 44gb to 54gb.
So reminder/warning: make sure you have enough disk space left!
1 Like
Then I guess you won’t be worried about it.
1 Like
not worried, but a bit wondered
Great update, can’t wait to see what the future brings us in the voice-area.
Am I the only one that always gets an <empty response> response, when talking to the Assist? Can’t do anything with it.
This is super easy.
I too migrated from the outdated Synology solution to a Docker container with MariaDB 10.10.2.
I setup the new DB using the same names an credentials as I did on my Synology NAS before.
Then did the following steps:
- shut down Home Assistant
- connect to old DB via HeidiSQL (freeware)
- do a SQL export of the “homeassistant” DB (including database and table creation scripts)
- connect to new DB via HeidiSQL
- execute the previously exported SQL script via HeidiSQL
- change the connection string in Home Assistant config
- start Home Assistant
1 Like
I’m not saying it’s discouraged, I’m saying it’s a 'use at your own risk because you’re accessing things that aren’t meant to be touched". It’s like the unofficial CSS attributes for the front end. Use them all you want, but they may change. I’d argue the database is in the same position. Use it at your own risk, but it may change. However, it is documented. So it probably should have been listed as a breaking change that could have affected SQL.
1 Like
I started the upgrade to 2022.02 around 6pm yesterday and it completed sometime overnight. Some upgrades go smoothly, but I’m afraid this wasn’t one of those. I am using the MariaDB addon (on a Home Assistant Blue). The HA core update was quick, but it resulted in the “critical” MariaDB update warning being prominently displayed in the settings screen. I had been on MariaDB addon v2.5.1 and it wanted me to update to patch version v2.5.2. I’m in the habit of responding promptly to critical warnings, so I started the update. Perhaps I should have spent 30 mins reading this thread first.
Here’s a summary of my bumpy ride, which ultimately ended well. Maybe it will help another user to be patient and not to nuke their DB. I’m not posting this to be negative. I just want to leave it here as reassurance to anyone who is in the same situation and is considering drastic action against their database.
The addon update process took about 2 hours, during which I had a spinner icon over the MariaDB entry on the updates page. During these two hours HA core seemed to restart itself twice. The third time it restarted, the update seemed to be complete with the addon showing the new version, so I continued to update any other addons that needed it, and then did a HA core restart. When the HA came back up, I had the notification about a database update being in progress and that system performance might be degraded. Any attempt to restart HA was met with an error that restarts are not permitted while a database update is in progress. This was naturally confusing, because I could verify that the MariaDB addon update was complete. After some hunting around the forums, I found that if I restarted the MariaDB addon it would allow me to restart the system, but that after each reboot the database update notification was back. I was pretty convinced that I was looking at a bug, and that something was setting this DB update flag erroneously. Some old posts on the forums reported that nuking the database and starting over was the only way forward.
I don’t care much about historical sensor data, but I do care about long-term statistics and energy stats, so I decided to look at exporting the DB before attempting to destroy and recreate it. I then noticed that the DB was about 18GB. This size seems to be the product of lots of sensors and a recorder history of 100 days. I have various entities excluded from the recorder history, but DB size has never really been an issue on the Home Assistant Blue with MariaDB, so I don’t maintain the list of excluded entities much. I decided that doing a mysqldump on 18GB would probably take many hours, so I instead just reconfigured my recorder history to 30 days and went to bed, hoping the DB size would be a bit more manageable in the morning. Thankfully, this morning the whole problem seems to have gone away.
On reflection, I think that HA core must have been running a data migration over the database, independently and completely unconnected to the MariaDB critical update, but I couldn’t find anything concrete online to support this idea (with a better idea of what to look for, I now see a post in the developer blog about a schema update). There was also nothing you might expect in the UI, like a progress bar, to indicate a long-running data migration was happening. That it was timed alongside a major critical DBMS upgrade made the notification message very confusing, and led me to conclude that the MariaDB update had corrupted itself somehow. Probably the only thing that prevented me from doing something drastic like destroying my database and starting over was the fact that it was 11pm and I wanted to go to bed.
So my takeaway from this is that if I ever see the “database update in progress” notification I should know that it means there is a background data migration running deep down in HA Core and that it might take hours or a few days to complete, and to just be patient and wait for it to finish before attempting to proceed with any other updates that might require a restart.
I wonder if a root cause of this is that development and testing instances of HA are likely to have smaller databases, and that databases in the order of 18GB are likely only to be found in long-running production instances. If the database was one or two orders of magnitude smaller, the data migration would be very quick, and no expectation of UI feedback on progress would arise.
1 Like
Just upgraded from 2023.2.0 to 2023.2.1 and none of my custom lovelace cards (installed via HACS) are loading anymore…
Anywhere that I have a custom card on the dashboard it shows “Custom element doesn’t exist:” and then card name…
Can’t find anything pertinent in the logs…
EDIT: Appears to be chrome related (running Version 109.0.5414.119); and yes closing and restarting chrome fixes it.
So I’m getting 2 database related errors, see below.
Logger: homeassistant.components.recorder.core
Source: components/recorder/migration.py:993
Integration: Recorder (documentation, issues)
First occurred: 12:35:43 (1 occurrences)
Last logged: 12:35:43
Database error during schema migration
Traceback (most recent call last):
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
self.dialect.do_execute(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, 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: (1205, 'Lock wait timeout exceeded; try restarting transaction')
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 742, in _migrate_schema_and_setup_run
migration.migrate_schema(
File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 154, in migrate_schema
_apply_update(hass, engine, session_maker, new_version, current_version)
File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 856, in _apply_update
_migrate_columns_to_timestamp(session_maker, engine)
File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 993, in _migrate_columns_to_timestamp
result = session.connection().execute(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/future/engine.py", line 280, in execute
return self._execute_20(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_20
return meth(self, args_10style, kwargs_10style, execution_options)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 334, in _execute_on_connection
return connection._execute_clauseelement(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1572, in _execute_clauseelement
ret = self._execute_context(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context
self._handle_dbapi_exception(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2124, in _handle_dbapi_exception
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 1900, in _execute_context
self.dialect.do_execute(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, 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) (1205, 'Lock wait timeout exceeded; try restarting transaction')
[SQL: UPDATE states set last_updated_ts=IF(last_updated is NULL,0,UNIX_TIMESTAMP(CONVERT_TZ(last_updated,'+00:00',@@global.time_zone)) ), last_changed_ts=UNIX_TIMESTAMP(CONVERT_TZ(last_changed,'+00:00',@@global.time_zone)) where last_updated_ts is NULL LIMIT 250000;]
(Background on this error at: https://sqlalche.me/e/14/e3q8)
Logger: homeassistant.components.recorder.util
Source: components/recorder/util.py:130
Integration: Recorder (documentation, issues)
First occurred: 12:35:23 (1 occurrences)
Last logged: 12:35:23
Error executing query: (MySQLdb.OperationalError) (1205, 'Lock wait timeout exceeded; try restarting transaction') [SQL: UPDATE states set last_updated_ts=IF(last_updated is NULL,0,UNIX_TIMESTAMP(CONVERT_TZ(last_updated,'+00:00',@@global.time_zone)) ), last_changed_ts=UNIX_TIMESTAMP(CONVERT_TZ(last_changed,'+00:00',@@global.time_zone)) where last_updated_ts is NULL LIMIT 250000;] (Background on this error at: https://sqlalche.me/e/14/e3q8)
Here is the PR
That means you restarted HA without waiting for the database migration to finish.
So the migration may still be working or you caused an issue where the lock hasn’t been released. If you restarted your hardware, you most likely corrupted the database. It would probably be worth while to restore from a back up and try again. But be patient and wait for the migration to complete.
Of all the times it had failed i can only imagine the update to 2023.2.1 to have caused a reboot, all the other times not to my knowledge. Also not sure how long it should take it takes hours and hours 
Yes, it’s a database migration, if you have a large database, it can take hours.