HA in Proxmox with MariaDB separate VM and DB_Size

Configuration
1

I am currently running HAOS on an HP t620 ThinClient (x86_64 installation). I am wanting to move to another computer (new Proxmox setup) as I will be able to expand better as my needs grow (and I learn more).

Currently (and since I started with this) my DB is MariaDB installed on the t620. I have had no problems so far. At one point I used some searches on google and the HA forums to setup a DB_Size sensor, and it has worked flawlessly. Even the Recorder: Purge (service runs with no hitches). I have built out the new HAOS VM (using the TTeck script) and the MariaDB VM as well. They are .241 and .242 (IP Addresses) in my network. There is currently nothing the new HAOS VM, as I want to test as I build copying things from the old one to this new one (i.e. .YAML stuff, etc.). I am trying to get this DB_Size sensor working and I have tried numerous things - to no avail. I know the DB is getting data, as this webpage shows it (most all of these were 0 or very small 24 hours ago):

image
image968×683 39.2 KB

I am of the ‘segmented builder’ type. Meaning that my configuration.yaml has multiple !include stanzas to bring in other .YAML files. I always test the .YAML “Check Configuration”, before I restart. I began this new setup by creating the following .YAML files (I will post their contents below):

recorder.yaml
sensors.yaml

I cannot seem to get the sensor to create. Here are my files (some things ‘masked’):

configuration.yaml
# Loads default set of integrations. Do not remove.
default_config:

# Load frontend themes from the themes folder
frontend:
  themes: !include_dir_merge_named themes

automation: !include automations.yaml
recorder: !include recorder.yaml
script: !include scripts.yaml
scene: !include scenes.yaml
sensor: !include sensors.yaml

:sunglasses: The MariaDB user and password masked in next file:

secrets.yaml
# Use this file to store secrets like usernames and passwords.
# Learn more at https://www.home-assistant.io/docs/configuration/secrets/
some_password: welcome

# MariaDB Configuration for DB location
mariadb_url: mysql://user:[email protected]:3306/homeassistant?charset=utf8mb4

recorder.yaml
# MariaDB Configuration Settings (25-Jan-2024 @ 23:40)
  db_url: !secret mariadb_url
  auto_purge: true
  auto_repack: true
  commit_interval: 20
  purge_keep_days: 10

sensors.yaml
# Lines below allow for Database Sensor (Size) to be collected (commented as of 2022.6, no longer needed)
  - platform: sql
    db_url: !secret mariadb_url
    name: db_size
    query: 'SELECT table_schema "database", Round(Sum(data_length + index_length) / POWER(1024,2), 1) "value" FROM information_schema.tables WHERE table_schema="ha-record" GROUP BY table_schema;'
    column: "value"
    unit_of_measurement: MB
    device_class: data_size

Again…all of these are copied from my existing (working) HA installation only thing that is different is the mariadb_url points to a ‘local DB’ on the same HAOS installation…but here it is if you want to see it (:sunglasses: The MariaDB user and password masked in next file):

secrets.yaml
mariadb_url: mysql://user:passwrd@core-mariadb/homeassistant?charset=utf8mb4

I have also tried the SQL Integration, and cannot get it to work either.

Here is what I see on my existing HAOS installation that I am trying to replicate (I would love to get the update sensor working too - if I can):

image
image1056×641 36.8 KB

2

is it just because you are missing the port?

Additionally - I tested your posted code, and got Unknown, but when I check the SQL integration docs, the posted example:

SELECT table_schema "database", Round(Sum(data_length + index_length) / POWER(1024,2), 1) "value" FROM information_schema.tables WHERE table_schema="homeassistant" GROUP BY table_schema;

which is almost like yours but ever so slightly different - worked immediately.

3

I cannot say. I was advised back in 2022 that the sensor.yaml file lines were no longer needed - I believe when the 2022.6 update got applied I might have been notified they were no longer needed. I added them to this new install - hoping that the entity sensor.db_size would get created and I would get the same warning.

So - I just reinstalled the SQL integration and I put your line in there - and low & behold I got a value — but it only goes to 1 decimal place. I found an old text file, and it had different ‘query’ and I tried it – I got a response with 2 decimal places (which is what I prefer - posting both below:

[Yours]

SELECT table_schema "database", Round(Sum(data_length + index_length) / POWER(1024,2), 1) "value" FROM information_schema.tables WHERE table_schema="homeassistant" GROUP BY table_schema;

[Mine]

SELECT table_schema "database", Round(Sum(data_length + index_length) / 1048576, 2) "value" FROM information_schema.tables WHERE table_schema="homeassistant" GROUP BY table_schema;

The only difference I see is the POWER(1024,2), 1) in yours and mine has 1048576, 2) — I even tried changing the ‘1’ in yours to a ‘2’ and it still only 1 decimal place. 0.5 (from yours) and 0.50 (from mine).

image
image1073×519 23.3 KB

So - still not sure why it started working — as I did not put anything here when I tried first time (it says to leave blank to use the HA recorder database:

image

Thanks for the insight - for the most part it appears to be working.

4

The difference between what works and what does not - is that your not working example says table_schema="ha-record" whereas the working version says table_schema="homeassistant" and obviously we can see from your DB url, that your table is indeed homeassistant and not ha-record.

1 Like
5

I should have caught that…guessing the ‘script’ that installs and sets up the MariaDB ‘locally’ as opposed to the one I followed to install as a separate VM instance, used what I put in, which was ‘homeassistant’.

Thanks for the assist…good eye!

1 Like