2023.6: Network storage, favorite light colors, new integrations dashboard

jprates · June 15, 2023, 12:25pm

Database connection broken in 2023.6.2 !!!

Hi all,

As soon as I upgraded my install to core 2023.6.2 the recorder stopped working, reason being it can’t connect to my external MariaDB any more.

After trying with no success to make it work I had to revert to 2023.6.1 using a backup.

Did anyone else notice this behaviour?
I did not see any mention on the release notes relative to the recorder or database access, so why did this happen?

My recorder is set up using:
db_url: mysql://<user>:<pwd>@<mariadb_ip>:<mariadb_port>/<db_name>?charset=utf8mb4

The strangest thing is that while still trying to figure out what was going on with 2023.6.2 I connected to home assistant via ssh and sucessfully conected to the db from home assistant shell as you can see:

➜  ~ mysql -h <ip> -P <port> -u <user> -p<pwd>
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1337
Server version: 10.3.37-MariaDB Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> exit
Bye
➜  ~ uname -a
Linux a0d7b954-ssh 6.1.29 #1 SMP PREEMPT_DYNAMIC Wed May 24 18:54:58 UTC 2023 x86_64 Linux
➜  ~ ll
total 0
lrwxrwxrwx    1 root     root           7 Jun 15 11:17 addons -> /addons
lrwxrwxrwx    1 root     root           7 Jun 15 11:17 backup -> /backup
lrwxrwxrwx    1 root     root           7 Jun 15 11:17 config -> /config
lrwxrwxrwx    1 root     root           6 Jun 15 11:17 media -> /media
lrwxrwxrwx    1 root     root           6 Jun 15 11:17 share -> /share
lrwxrwxrwx    1 root     root           4 Jun 15 11:17 ssl -> /ssl

Anyone has any ideia of what is going on?
Here’s the logs from HASS regarding the failing connection:

`2023-06-15 10:42:26.910 WARNING (MainThread) [homeassistant.setup] Setup of recorder is taking over 10 seconds.
2023-06-15 10:43:13.796 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
2023-06-15 10:44:14.112 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
2023-06-15 10:44:27.449 ERROR (Recorder) [homeassistant.components.recorder.core] Error during connection setup: (MySQLdb.OperationalError) (2002, "Can't connect to server on 'xxx.xxx.xxx.xxx' (115)")
(Background on this error at: https://sqlalche.me/e/20/e3q8) (retrying in 3 seconds)
Traceback (most recent call last):
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 145, in __init__
    self._dbapi_connection = engine.raw_connection()
                             ^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 3293, in raw_connection
    return self.pool.connect()
           ^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 452, in connect
    return _ConnectionFairy._checkout(self)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 1268, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 716, in checkout
    rec = pool._do_get()
          ^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/pool/impl.py", line 168, in _do_get
    with util.safe_reraise():
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/util/langhelpers.py", line 147, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/pool/impl.py", line 166, in _do_get
    return self._create_connection()
           ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 393, in _create_connection
    return _ConnectionRecord(self)
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 678, in __init__
    self.__connect()
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 902, in __connect
    with util.safe_reraise():
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/util/langhelpers.py", line 147, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 898, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
                                         ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/create.py", line 637, in connect
    return dialect.connect(*cargs, **cparams)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 616, in connect
    return self.loaded_dbapi.connect(*cargs, **cparams)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/MySQLdb/__init__.py", line 123, in Connect
    return Connection(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/MySQLdb/connections.py", line 185, in __init__
    super().__init__(*args, **kwargs2)
MySQLdb.OperationalError: (2002, "Can't connect to server on 'xxx.xxx.xxx.xxx' (115)")

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 924, in _setup_recorder
    self._setup_connection()
  File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 1397, in _setup_connection
    Base.metadata.create_all(self.engine)
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/sql/schema.py", line 5796, in create_all
    bind._run_ddl_visitor(
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 3243, in _run_ddl_visitor
    with self.begin() as conn:
  File "/usr/local/lib/python3.11/contextlib.py", line 137, in __enter__
    return next(self.gen)
           ^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 3233, in begin
    with self.connect() as conn:
         ^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 3269, in connect
    return self._connection_cls(self)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 147, in __init__
    Connection._handle_dbapi_exception_noconnection(
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2431, in _handle_dbapi_exception_noconnection
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 145, in __init__
    self._dbapi_connection = engine.raw_connection()
                             ^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 3293, in raw_connection
    return self.pool.connect()
           ^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 452, in connect
    return _ConnectionFairy._checkout(self)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 1268, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 716, in checkout
    rec = pool._do_get()
          ^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/pool/impl.py", line 168, in _do_get
    with util.safe_reraise():
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/util/langhelpers.py", line 147, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/pool/impl.py", line 166, in _do_get
    return self._create_connection()
           ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 393, in _create_connection
    return _ConnectionRecord(self)
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 678, in __init__
    self.__connect()
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 902, in __connect
    with util.safe_reraise():
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/util/langhelpers.py", line 147, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 898, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
                                         ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/create.py", line 637, in connect
    return dialect.connect(*cargs, **cparams)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 616, in connect
    return self.loaded_dbapi.connect(*cargs, **cparams)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/MySQLdb/__init__.py", line 123, in Connect
    return Connection(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/MySQLdb/connections.py", line 185, in __init__
    super().__init__(*args, **kwargs2)
sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (2002, "Can't connect to server on 'xxx.xxx.xxx.xxx' (115)")
(Background on this error at: https://sqlalche.me/e/20/e3q8)`

I see a lot of references to python… did anything change on python side?

Thanks in advance for any assistance,
-jprates

Aphotrax · June 15, 2023, 12:45pm

I had the same issue and honestly no idea what happend. But this worked for me:

Stopping MariaDB and HA
Removing ib_logfile0 and ib_logfile1 (or the one that exists for you) from MariaDB folder
Restart MariaDB
When fully loaded, start HA.

Hope that solves it for you as well.

AttilaS · June 15, 2023, 1:46pm

After upgrading to 2023.6.2 from 2023.6.1, all my command line switches started spamming the log with “Updating command_line switch took longer than the scheduled update interval 0:00:30” in every 30 seconds. They are all working and their states are correct, just logging the above. Reverted back to 6.1, these log entries disappeared.

VDRainer · June 15, 2023, 1:53pm

Python changed from 3.11.3 to 3.11.4 in 2023.6.2 if you’re using OS/Docker.

shipset · June 15, 2023, 1:56pm

Hello,
Since I updated my HA (2023.6.1 or 2023.6.2) my Airzone integration does not work anymore
Am I alone with this problem or not ?

Thanks.

–
Shipset

jprates · June 15, 2023, 2:16pm

Thanks @Aphotrax and @VDRainer .

The fact that just restarting the DB had solved the issue (the ib_logfilex should not make any difference IMHO, these are redo files, not used after a clean shutdown), made me think the upgrade was worth a second try.

Stopping the DB was not a good option for me since I have other instances running there that should not shutdown during daytime, so I just plain tried the upgrade of HA again.

Don’t ask me what changed, AFAIK nothing changed, but this time it worked, took a long time but worked.

I guess we’ll never know what caused the first time failure, but thanks for your answers, it made me decide to give it another try.

Cheers,
-jprates

checking12 · June 15, 2023, 2:20pm

what version MariaDB you’re on?
I had no issue at all with upgrade today, working on maraidb 10.6.9
Your steps for reviving sound bit scary and indicate corruption or performance loops/issues… It’s been mentioned in previous releases, for larger DB’s at least. SMall ones should not be affected really.

github.com/home-assistant/core

Add a repair issue when using MariaDB is affected by MDEV-25020

home-assistant:dev ← bdraco:mariadb_repair

opened 05:49PM - 31 Jan 23 UTC

bdraco

+195 -4

~~Needs to wait for the [MariaDB addon to be updated from 10.6.8 to 10.6.10](htt…ps://github.com/home-assistant/addons/pull/2867) since users can't do much about this if they are using the addon.~~ ## Proposed change  https://jira.mariadb.org/browse/MDEV-25020 <img width="687" alt="Screenshot 2023-01-31 at 12 40 43 PM" src="https://user-images.githubusercontent.com/663432/215852743-45d3221f-9ed3-4491-b77c-03ab073379b1.png"> <img width="577" alt="Screenshot 2023-01-31 at 12 44 25 PM" src="https://user-images.githubusercontent.com/663432/215853441-1771e8e2-12b4-465d-af0f-bed9c7640af8.png"> MariaDB fixed this in 10.5.17, 10.6.9, 10.7.5, 10.8.4. Previous versions would have history and purge queries running for hours which consumed all the available I/O and could make the whole system unstable. closes #83787 ## Type of change  - [ ] Dependency upgrade - [ ] Bugfix (non-breaking change which fixes an issue) - [ ] New integration (thank you!) - [ ] New feature (which adds functionality to an existing integration) - [ ] Deprecation (breaking change to happen in the future) - [ ] Breaking change (fix/feature causing existing functionality to break) - [ ] Code quality improvements to existing code or addition of tests ## Additional information  - This PR fixes or closes issue: fixes # - This PR is related to issue: - Link to documentation pull request: ## Checklist  - [x] The code change is tested and works locally. - [ ] Local tests pass. **Your PR cannot be merged unless tests pass** - [ ] There is no commented out code in this PR. - [ ] I have followed the [development checklist][dev-checklist] - [ ] The code has been formatted using Black (`black --fast homeassistant tests`) - [x] Tests have been added to verify that the new code works. If user exposed functionality or configuration variables are added/changed: - [ ] Documentation added/updated for [www.home-assistant.io][docs-repository] If the code communicates with devices, web services, or third-party tools: - [ ] The [manifest file][manifest-docs] has all fields filled out correctly. Updated and included derived files by running: `python3 -m script.hassfest`. - [ ] New or updated dependencies have been added to `requirements_all.txt`. Updated by running `python3 -m script.gen_requirements_all`. - [ ] For the updated dependencies - a link to the changelog, or at minimum a diff between library versions is added to the PR description. - [ ] Untested files have been added to `.coveragerc`.  To help with the load of incoming pull requests: - [ ] I have reviewed two other [open pull requests][prs] in this repository. [prs]: https://github.com/home-assistant/core/pulls?q=is%3Aopen+is%3Apr+-author%3A%40me+-draft%3Atrue+-label%3Awaiting-for-upstream+sort%3Acreated-desc+review%3Anone+-status%3Afailure  [dev-checklist]: https://developers.home-assistant.io/docs/en/development_checklist.html [manifest-docs]: https://developers.home-assistant.io/docs/en/creating_integration_manifest.html [quality-scale]: https://developers.home-assistant.io/docs/en/next/integration_quality_scale_index.html [docs-repository]: https://github.com/home-assistant/home-assistant.io

@jprates your db grown large?

aheath · June 15, 2023, 3:06pm

I just upgraded to this version and have lost my on-board Bluetooth adapter. The adapter is there, as reported by both hcitool and bluetoothctl, but HA can’t seem to locate it after the upgrade to this version. Multiple reboots (read in the documentation that it can sometimes need a few reboots to detect it) have not made any difference. There is plenty of talk about Bluetooth device tracker no longer working as bluez is not maintained anymore. I don’t use Bluetooth tracker, but I do have a number of bluetooth thermometers that are obviously no longer reporting through to HA.

I realise that a workable alternative is an ESP32 with Bluetooth proxy, but just interested as to why it would no longer work when there was no mention of issues in breaking changes. Is it just me? Or are others having similar issues?

Running HA inside proxmox (supervised, and in fully supported configuration) on a Dell Optiplex Micro 7060 with on-board bluetooth adapter.

jprates · June 15, 2023, 3:33pm

nah, just 2 TB

Gaffo_Roberto · June 15, 2023, 4:48pm

NETATMO - With the last two updates, if I install them, the Gateway and the Netatmo sockets no longer work, I have to go back to 2023.5.4

Duke_box · June 15, 2023, 7:21pm

I suspect you are also a victim of pybluez being removed from the core because it doesn’t work (YET) with python 3.11.x.

There is a notice in the full changelog for this release but as a few people have commented it has been buried so deep that no-one realises. However the devs knew about it in March this year.
Is this because it was deemed as unimportant or because it shouldn’t be highlighted?
Either way it hasn’t been depreciated officially so there is hope that once pybluez is updated to work with python 3.11.x AND it is built back into the HA core, it will work again.

This happened when HA adopted python 3.10.x last year and as if by magic ( no announcements) it started working again a few months later

wmaker · June 15, 2023, 7:23pm

Yes, same here.
Here is the Github issue on it

Brian_Reis · June 15, 2023, 8:04pm

Anyone having issues using the media, from the Network storage, inside the cards?

I am able to use /local/Images/xxxxxx.png for the items inside “Media-> My media”
Now inside said My media I also see the NAS folder I added and the images inside it. But if I try using it inside a card as /local/Images/NAS_Folder/xxxxxx.png doesn’t work

I understand it makes little sense to still use /local/xxxxxxxxx but I can’t find the proper way to do it

nickrout · June 15, 2023, 8:08pm

No logs. no issue.

alexd123 · June 15, 2023, 9:31pm

Yeah similar issues here.

Using zha and a RaspBee II. zha_events from buttons, switches etc work but no or very delayed response from ikea or tuya zigbee devices (lights and sockets)

Nothing unusual in logs

Partial core restore did not fix, had to do full restore. Could this be a python issue?

zagnuts · June 16, 2023, 12:26am

Is anyone else having issues when using the HA client app on mobile devices, where it now comes up with a reload ui message? Seems to be due to the server caching messages to the client and once the queue hits 4096 messages (perhaps due to the client being asleep/in the background) it curls up and dies? Some discussion about it here: 2023.6 Home Assistant Cloud page stuck reloading · Issue #16854 · home-assistant/frontend · GitHub

darecki · June 16, 2023, 12:38am

Somehow storage settings has blank page in my Home Assistant…

nickrout · June 16, 2023, 12:38am

Have you cleared your cache?

darecki · June 16, 2023, 1:33am

Now I did and not even a link to it

petro · June 16, 2023, 1:44am

Are you using hassos?