HomeAssisant on Proxmox with MariaDB and MQTT on separate VMs

[bearhntr]

I am building out a new HAOS setup on an HP Z240 (using Proxmox) and I have hit a snag. I want to use MQTT as a separate VM, and I have run the script from tteck and I believe that I have it all setup and running - but I cannot get the HAOS to talk/connect to it. I need a little guidance. I setup a USERNAME and PASSWORD (I believe) - and I have tried using those in the MQTT integration - but all I get is this error (see images):

From the MQTT console - I am able to ping the hostname of the HAOS VM.

[tteck]

Credentials must be incorrect. Hold on, is that for the MQTT integration configuration or the MQTT add-on configuration? It's been a while since I set up mine.

[tteck]

broker name should be an IP address, did you follow the instructions from this repository?

[tteck]

[bearhntr]

I followed the instructions exactly from here: https://www.home-assistant.io/integrations/mqtt#use-a-public-broker

The section [USE A PUBLIC BROKER] when I put in that test broker, it started populating all sorts of things. I then had to delete them all and re-do it. As you can see in those steps, they use a name not an IP. My MQTT instance has both, but I prefer to use hostnames.

I followed the installation guide steps from your script link (it was a little bit confusing) - but I think that I created the default.conf file as per your steps...I can find it from the console. I also can find the passwd file, and see the username I created is there - with a long string of characters (I am assuming some sort of hash of what password I set). I then rebooted the MQTT VM/container.

Then I did the steps you show above - and I did try both hostname and IP - both do the same thing.

Any ideas? I loved your scripts for installing HAOS and MariaDB, super simple - now this one has me stymied. Do I need 2x MQTT (one for ZWave and one for Zigbee)?

[tteck]

I followed the instructions exactly from here: https://www.home-assistant.io/integrations/mqtt#use-a-public-broker

Follow the pictures above, add integration > MQTT > MQTT > input your credentials that you created when you configured the MQTT LXC
Only 1 MQTT broker is needed

[bearhntr]

I will give it a whirl. I think I will delete the MQTT LXC and start all over. Right now I am fighting with the MariaDB one. I cannot get HA to talk to it. I have done it 5 times and HA simply refuses to create the tables in the DB. :-(

[tteck]

Please ensure that you meticulously follow the instructions provided under the create LXC command.

[bearhntr]

Please ensure that you meticulously follow the instructions provided under the create LXC command.

If you are referring to the external MariaDB with HA - I have been every time. I am thinking (looking at the errors) - that this is the line/command that is breaking things (as my network is 10.9.28.% - and that is what I change this to:

GRANT ALL ON *.* TO 'admin'@'192.168.100.%' IDENTIFIED BY 'password' WITH GRANT OPTION;

I am going to try skipping this line and see if it helps.

[tteck]

change the network and password in the command

[bearhntr]

I have done that 5x times already. I know I sound 'dense' but I followed your steps exactly, giving it a username (different from your commands) and a different password (always put them in the ' ' {quotes} - and the login using those does work. I will re-build the MariaDB LXC (as I deleted it this AM) and start over - will then post what I am seeing here.

Thanks again for hanging with me.

[bearhntr]

@tteck

:-( No Go. Re-created MariaDB - following your steps exactly (just changed 'password' and IP Segment to match mine) - and added the line to "configuration.yaml" as it should be and restarted HA (after a check configuration).

I get this when it comes back up - which is what I have been getting every time.

[bearhntr]

[bearhntr]

I did REMOVE the MariaDB add-on from HA and rebooted before I added the line for Recorder.

[bearhntr]

This is from the most recent restart -- I cannot seem to get this working, no matter what I do ... :-( (There are about 8 repeats of the same below all within about 2 minutes)

2023-06-20 15:08:47.155 ERROR (Recorder) [homeassistant.components.recorder.core] Error during connection setup: [<class 'decimal.ConversionSyntax'>] (retrying in 3 seconds)
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 915, in _setup_recorder
self._setup_connection()
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 1394, in _setup_connection
Base.metadata.create_all(self.engine)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/schema.py", line 5796, in create_all
bind._run_ddl_visitor(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3238, 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/engine/base.py", line 3228, in begin
with self.connect() as conn:
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3264, in connect
return self._connection_cls(self)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 145, in __init__
self._dbapi_connection = engine.raw_connection()
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3288, in raw_connection
return self.pool.connect()
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 452, in connect
return _ConnectionFairy._checkout(self)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 1268, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 716, in checkout
rec = pool._do_get()
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 168, in _do_get
with util.safe_reraise():
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 147, in __exit__
raise exc_value.with_traceback(exc_tb)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 166, in _do_get
return self._create_connection()
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 393, in _create_connection
return _ConnectionRecord(self)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 678, in __init__
self.__connect()
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 916, in __connect
)._exec_w_sync_on_first_run(self.dbapi_connection, self)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/event/attr.py", line 473, in _exec_w_sync_on_first_run
self(*args, **kw)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/event/attr.py", line 487, in __call__
fn(*args, **kw)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 1915, in go
return once_fn(*arg, **kw)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/create.py", line 733, in first_connect
dialect.initialize(c)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/dialects/mysql/base.py", line 2791, in initialize
default.DefaultDialect.initialize(self, connection)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 514, in initialize
self.server_version_info = self._get_server_version_info(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/dialects/mysql/base.py", line 2536, in _get_server_version_info
cursor.execute("SELECT VERSION()")
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 321, in _query
self._post_get_result()
File "/usr/local/lib/python3.10/site-packages/MySQLdb/cursors.py", line 355, in _post_get_result
self._rows = self._fetch_row(0)
File "/usr/local/lib/python3.10/site-packages/MySQLdb/cursors.py", line 328, in _fetch_row
return self._result.fetch_row(size, self._fetch_type)
decimal.InvalidOperation: [<class 'decimal.ConversionSyntax'>]
2023-06-20 15:08:50.158 ERROR (MainThread) [homeassistant.setup] Setup failed for recorder: Integration failed to initialize.
2023-06-20 15:08:56.141 ERROR (MainThread) [homeassistant.setup] Unable to set up dependencies of history. Setup failed for dependencies: recorder
2023-06-20 15:08:56.142 ERROR (MainThread) [homeassistant.setup] Setup failed for history: (DependencyError(...), 'Could not setup dependencies: recorder')
2023-06-20 15:08:56.146 ERROR (MainThread) [homeassistant.setup] Unable to set up dependencies of logbook. Setup failed for dependencies: recorder
2023-06-20 15:08:56.147 ERROR (MainThread) [homeassistant.setup] Setup failed for logbook: (DependencyError(...), 'Could not setup dependencies: recorder')
2023-06-20 15:08:56.165 ERROR (MainThread) [homeassistant.setup] Unable to set up dependencies of energy. Setup failed for dependencies: history, recorder
2023-06-20 15:08:56.166 ERROR (MainThread) [homeassistant.setup] Setup failed for energy: (DependencyError(...), 'Could not setup dependencies: history, recorder')
2023-06-20 15:08:56.264 ERROR (MainThread) [homeassistant.setup] Unable to set up dependencies of default_config. Setup failed for dependencies: energy, history, logbook
2023-06-20 15:08:56.266 ERROR (MainThread) [homeassistant.setup] Setup failed for default_config: (DependencyError(...), 'Could not setup dependencies: energy, history, logbook')

[bearhntr]

Is there a command line to install ADMINER - outside of your original .sh file?

[bearhntr]

Rebuilding the HAOS VM and going through this process again has resolved issue.