I know this video is quite old and a lot has changed with home assistant so Im wondering if things like MariaDB are still the way to go or would it be a good thing to go back to default? as always thanks for the videos looking forward to the new sensor when it comes
How about a video of a UPS for a Raspberry Pi? I'm living in an area with a lot of power outages and I am trying to find a solution to keep my Raspberry pi 4 running
@@mrphyslaw i dont have nuc, they're somewhat expensive, i already have a rasp pi and a ssd is cheap as. i dont see a need for now with as little devices as i have.
@@EverythingSmartHome It can be done on a Raspberry Pi 3. Would have to be a USB to SATA Adapter with a SSD and would still need a SD Card. Just have to modify the System to use the SSD as the docker container storage. Could also be easily done with a existing system by using a directory link. Another option is a MicroSD to eMMC Adapter.
Nice video, but this isnt working for me... meybe its because of newer verison of HA ? (2021.12.10 HA OS 7.2) but after MariaDB install whole HA is noticeably slower...
I'm surprised to hear that SQLite isn't performing well (or reliably) considering Home Assistant only has 10 days of data and this should be way, way below any threshold where performance is suffering. Of course i've never analyzed the queries (the achillieas heel of all SQL database apps) but was wondering if you had any metrics on how MariaDB had improved things.
New to HA ,and this channel has definitely been our #1 online resource: *** TIP *** not sure if this is known to all, but as a 'newbie' we found out the hard way: if you set up your 'super secure password' utilizing the character '@' in it (for example, to change the letter a,A with @, as in '@ppl3' instead of 'apple'), the database, logs, records, history will NOT START! Only after looking into the logs for an hour, we discovered the @ is used in the [>]recorder code as a designator for db/server location! So the only @ character in the [>]db_url line MUST BE the one between your 'password' and 'core-mariadb' FYI! :D
Big problem when the supersecure password has the @ character included. The recorder handshake was not happening, and no data was accumulating. No documentation that I could find. Changed the db name, changed the core-mariadb with the IP address, and eventually I noticed that there was a @#$@ sequence in the yaml file that didn't look good at all. Beginner's mistake, I guess...
I wouldn't say that was a mistake on your part. No reason you wouldn't think @ was OK. It was only chance that I didn't think to include that character in mine. It (the HA interface) should make it clear in the settings if the password is restricted to a set of special characters.
I saw the same thing. Apparently Studio Code Server only shows files it can edit. Do you have Terminal installed? If so. you can do it by opening the terminal and entering the following commands: > cd /config > rm home-assistant_v2.db NOTE: instead of using the rm command (remove), I used the mv command (move - which is also used to rename) to rename it. I did this out of an abundance of caution (paranoia). That command is: > mv home-assistant_v2.db home-assistant_v2.db.bak After a few days I'll delete it with: > rm home-assistant_v2.db.bak
Love your video's just wondering why you would put you db_url in the configuration.yml instead of only a reference to the secrets.yml seems the better place like that
I actually do this in my configuration. the entire parameter is in the secrets.yaml file. then I reference it in the configuration.yaml like: db_url: !secret mariadb_url .... while secrets.yaml is not any more secure than the configuration.yaml file since they are colocated and not enciphered, it DOES allow me to do things like backup everything to github and not push the secrets file to github. It also allows me to easily share configuration samples with people without having to scrub them.
Any idea how one might swap history recording to a Docker based MariaDB instance? I’m already using MariaDB for a couple of databases and I not sure of the impact of having two MariaDB instances - one in HA and one in my Docker container.
@EverythingSmartHome if you are running Home Assistant with Proxmox, would you continue to use the Add-On or move the database to a separate container or VM?
Hi, thanks for the video... planning to move to Maria DB... just a quick question. I use RPi 3 and a SSD as external data disk. Home assistant boots from an SD card , but all data is stored on SSD connected via USB port. I heard you mention not to use it with an SD card and to move to SSD. Tried a lot, but can't get to boot from SSD on RPI 3. So only did I start using the data disk feature. So is it ok to use Maria DB on my existing setup? Or will i have to boot from SSD (possible on RPI 4's but hardly available in the market right now)
HA newbie here. I'm on the 2024.2.1 version of HA and it looks like things have changed a bit with the UI since this tutorial was created. Would love an update!
You are not 100% correct here, I get that most people can accept the data loss when the performance and reliability of a real RDMS is used but there's a pretty reasonable way for people with huge history sqlite tables to convert the db file to sql: With sqlite3 and a sqlite3-to-mysql-converter that is shown (4 years ago so this may be even simpler today, or some url or alter command adjustments could be needed) here ua-cam.com/video/4rsxnN8gkHk/v-deo.html. Perhaps doing a new video on the steps in the current GUI for all newcomers to enable them to do some data handling? The key and alter table parts could pose some risks though..
It worked as you suggested, and I know you mentioned Sensor data will be lost, bu I was hoping I had heard you wrong. Of course, it is lost. Is there any way to get the sensor data back. Losing all the energy stats is a biggy. Is there a solution you are aware of?
Im running home assistant on a VM in my Synology NAS. I also have a docker container with mariadb running for my kodi database. can i use this for home assistant too?
Another great tutorial.. Thank you.. Like the Cards format you show at the end.. Could you go into more detail for that final monitoring dashboard look?
Thank you! Sure I can look at that, they are made using "Apex" cards from HACS, there are even some example code in their Github. Hope that helps a little!
MariaDb can also get corrupted after power loss and it doesn't recover automatically! the workaround is to uninstall mariadb and install it again! its even worse than to rename the sqlite database. Do you have any advice?
I use raspberry Pi 4 8gb with 240 ssd, I get error message on check config not sure why can you check see what i did wrong on the command sensor: - platform: systemmonitor resources: - type: processor_use - type: processor_temperature - type: memory_free - type: disk_use_percent - arg: / - type: disk_use - type: disk_free my network never show up when i check on terminal
If you're seeing issues with recorder, logbook etc after switching over to MariaDB; try a shorter password and remove any symbols, restart after changes.
Great video. Any suggestions on manually entering some of the historical sensor data ? Example: I have about 12 utility meters that keep count of my solar gen, electric usage, grid usage and battery usage. I have 3 years of history now so it would be a sad day to lost it. I just have 4 sets of sensors for daily, monthly, yearly and all time. :-) any advice on how to add if I convert are welcome
Hey, the main devs came out recently and said that most people should not be changing databases. any chances on updating the video so people know not to switch
@@EverythingSmartHome Hi! I have an update on the Maria DB and a possible issue. I've had to back out to my previous configuration. My hardware is an INTEL NUC Celeron 7CYJH with 8GB Ram and a 128GB SSD and I'm using NABU CASA. Basically Alexa Media TTS fails to work with Maria DB, at least it keeps requesting re-authentication and then the same again a few hours later. I backed out to the previous snapshot and all ok again. Very strange. I'll try installing again to double check, but has anyone else seen this issue?
Hello! That is strange, MariaDB doesn't have anything to do with the workings of HA, it only handles history and log book data, that is all. No integrations or anything is stored in the database. Did you check your logs before reverting? I've seen others complain that different things aren't working but it's because they've made an error in the mariadb setup and HA can't start properly, preventing it from loading components after the recorder. The HA logs should confirm this
@@EverythingSmartHome I will repeat the process to be 100% sure, but it was the only change I made. I have a policy of making one change at a time exactly for this reason, I'll feed back again. No doubt of the performance improvement on the NUC though. I was wondering if I could easily use a remote Maria DB as I run one on my Synology DiskStation for my Amateur Radio logbook.
Thanks for this. But after following this tutorial and deleted the old database file I see the system monitor-stats didnt get removed. Are those stored somewhere else? Also my energy sensor didnt seem to get resetted!
Great video.... now how about a step further by covering how to keep your database from getting out of hand using recorder to set what to record and also how to cap size of database by setting number of days... is there a simple way to wipe the database or compress it... I don’t know.... I just think there has to be a way to keep the data beast under control and from growing tooooo laaaarrrrrge.
Hey Richard, thanks! I think the default amount of data to retain is 10 days and after that it's purged, but you can lower that value if you want, as Miguel mentioned have a check of the recorder documentation
I tried but never got the feeling it was working as my database seem to constantly grow in size. Is there an way to report that the purge has occurred or... track on a panel the size of the database?
very useful video. thanks for that. It did however leave me wondering if running the db in mardiab somehow will require me to take some steps to make sure that the database is also included in my auto backup to my nas. if anybody has any thoughts on this I would love to hear them!
Having moved over to MariaDb i have run into all sorts of issues with snapshots - specifically the fact that the MariaDb does not appear to function if restored from a snapshot meaning that when i run into issues upon major core releases (which i always do), the restore does not work for history/maria etc and i have to nuke it and rebuild from scratch.. Would you have any recommendations as to how to backup MariaDb? Other users in the forums have recommended stopping the service before taking a snapshot which is fine however it would be great if this could be automated (i am using the GDrive backup solution which is amazing! - apart from with this Db)
Did you ever figure this out? I was looking into moving to MariaDB, but I use the "Home Assistant Google Drive Backup" addon and I'm concerned that my restores won't work, or won't include the MariaDB.
@@davidhill8151 hi David, not as yet no however I would say that HA seems to much better of late in terms of stability that I haven’t needed to restore from backup. Also made some tweaks to my settings so Maria us used got short term only with Influx being used for long term storage, with this setup loosing the Maria would not be too much of a problem.
Great video! Using Home Assistant over a year now. Just switched to MariaDB. One question,- in the roundup you showed a couple of graphs. Which card did you used for the network network_throughput_in and network_throughput_out. Awesome if you can share this :) Kudos for the video. Keep up the good work.
Hi Lads, I understand Maria DB is sensitive to backups unless stopped prior to manual backups. Might I decide to use MariaDB in my forthcoming install , would this cause a problem with automatic routine backups with HA OS, generating corrupted files?
Thanks for this video. I am also running HA virtualized using qemu/kvm and virtual manager however, I am unable to perform snapshots with the current HA qcow. I was wondering what were you using for snapshots? Thx.
adapters that begin with en are cause its in a VM. If you are on bare metal such as a raspberry pi and using the built in ethernet,. the adapter is likely going to be "eth0" or if using built in wifi "wlan0"
Thanks! Tonight, I used this and your related video to setup both influxdb and mariadb with 372 day retention. I guess I'll have to use them to watch my free disk space, now ;) Tonight I also found the Plotly Graph Card (HACS) which I think I'll love. I've been using the apexcharts-card (HACS) and like it a lot.
Hello, I'm planning to move my installation to an ODROID with eMMC. Is it ok to move to MariaDB with eMMC? Or is it not recommended like an SD card install?
This is too difficult for a beginner as the newest Homeassistant does not allow you to alter the configuration file and Supervisor does not appear on the sidebar. Bear in mind that this is a "Beginners Guide". Don't get me wrong, I really like your videos but for a beginner they are very frustrating as Homeassistant seems to change ifs layout and system at the drop of a hat.
Despite the "speed increases" of the past 3 or 4 releases, I'm finding that with 2022.7 it takes much longer to access the HA web UI than it ever has. I'm running a VM with decent and dedicated resource allocation on a fast server and first access to HA feels like I'm on dialup.
Great video. I initially set up HA on an SD card and RPi 4 4G just to play around and see wat it could do. As I am planning on keeping it, is it possible to transfer HA to an SSD drive withing having to do a complete reinstall?
Just switched to MariaDB using this video after corruption with my SQLlite DB. Really appreciate the clear no-nonsense delivery of information. Surprised how easy it was to do. 5 minutes and all up and running.
Good info! I had recently set this up but now realized I’ve made some mistakes so will be going back and correcting them like going to an SSD card lol. One thing to note is that the history will have too much data, like all the entities so you can filter what you want in there by typing “include:” or “exclude:”. This is mentioned in the HA page
Have you tried using MariaDB with a socket connection? This would definitely be faster still. I have a feeling that socket may be obfuscated a bit because I think Maria DB is running inside a docker container and i'm not sure that HA and Maria have a shared volume where this socket can be reached.
There's hardly any difference between running sqlite vs mariadb on an sdcard. If you use an A1 or A2 sd card, this shouldn't be that much of a problem anyway. :)
I need to check how my Docker got things set up, I have the "IOTstack" installer. That community is huge about InfluxDB and Grafana and then Mosquitto. Maybe this is overkill, however I wanted to ask if anyone watching this video had any experiences with InfluxDB
Lewis thanks again for a great video. I followed along and all went well except I am missing a history tab. Is there something I need to add to get it to appear? I also did not see the old db file to delete?
I thoroughly appreciate your SD card warning. My strong preference is to not run Pi's from SD - I was so glad when the Pi 4's started shipping with built in support for booting from USB. I prefer to run them with m.2 SSDs. All that said, my HA now runs on a VM. I migrated a couple weeks ago and it's a LOT snappier than it was on the Pi4.
Maybe you can keep your history with something like this : sqlite3 home-assistant_v2.db .dump > sqlite3.dump.sql # export the DB # find a way to convert it to a MYSQL usable file : mysql_converted.sql mysql -u myuser -h myhostname -p homeassistant < mysql_converted.sql # import the data
I believe it is still desirable to use MariaDB if your database is larger and you want it to be a bit more bulletproof. SQLlite has been fine for years but decided to switch after my DB got corrupted after a crash due to the recent dodgy NodeRed update. So you don't *need* to. But seems to be a popular update once you've got the hang of HA.
Great video, I've set up Maria DB a while back when I saw your video. Do you have any information/video on how to set up reporting or extract data from the events table? Keep up the great work!
Invalid config for [sensor.systemmonitor]: value must be one of ['disk_free', 'disk_use', 'disk_use_percent', 'ipv4_address', 'ipv6_address', 'last_boot', 'load_15m', 'load_1m', 'load_5m', 'memory_free', 'memory_use', 'memory_use_percent', 'network_in', 'network_out', 'packets_in', 'packets_out', 'process', 'processor_temperature', 'processor_use', 'swap_free', 'swap_use', 'swap_use_percent', 'throughput_network_in', 'throughput_network_out'] for dictionary value @ data['resources'][0]['type']. Got 'prozessor_use'. (See ?, line ?). What am I doing wrong?
Question, I use influxDB integration for my historical data and graphs through Grafana. Moving to mariaDB, should the influx data just pick up where it left off?
Is it easy to migrate homeassistant from an SD card to SSD? And if I run from an SSD shouls I use the 64 bit version? And can you make a guide how to? I'm currently running on an pi4 4GB model. Thanks for the video's!
I don't understand, I did the mariadb update, pretty straight forward, but afterwards I get all kinds of errors. I get an error saying the following integratings and platforms could not start. recorder logbook history energy default config I dont undestand why, I saw a complex pw can cause an issue, mine is pretty straigh forward only 8 characters long. Any thoughts?
The errors are a knock on effect from the recorder not working - make sure add-on is started and check logs for errors. Also no @ symbol in the password
@@EverythingSmartHome did my comment get deleted or something? I left a reply with a link to imjur to see a screenshot to the log I have.... Does UA-cam not like that?
@@EverythingSmartHome I just typed out the errors I was seeing. waiting on integrations to complete setup: spotify (warning) bootstrap.py error setting up entry home for smartthings (error) components/smarrthings/_init_.py setup failed for logbook: (dependencyError(...), 'Could not setup dependencies: recorder') (error) setup.py unable to setup dependencies for logbook. setup faild for dependencies:recorder (error) setup.py setup failed for recorder: Integration failed to initialize. (error) setup.py Thanks for the help!
Very important step missed - around 3:30 Restart HA, and wait and wait, make a cup of tea and wait ** Do not faff about with your config file think you have got your password wrong ....... patience is a virtue I don't have! Great Video again
Cheers ... sussed out my missing logbook and history. In MariaDB config I had changed the user name but hadn't updated the URL. Thanks for the kick in the arse. Been bugging me for months
Thanks MariaDB really seems to give that extra umphh, but with the system monitor my only assosiated resource with IP for my home assistant is wlan0, since I have need for wifi for my instance I had to deviate from yor installation to SSD, I used etcher and start directory with wifi credentials to setup my wifi setup, possible my wifi setup method screws with home assistant and wifi is not supported for trafic data collection? After smooth setup with guide and 6 frustrating hours of trying to solve the network trafik problem I apriciate all recomendations. Best regards from sweden
Finally got round to adding an SSD to my install. These TTT videos are amazing. Every single one of them is so clear and concise. It's epic. Thanks again Lewis
Waiting to order my blue when they come back in stock (fingers crossed) before i do the mariadb switch over.. my rpi3 is busy enough :-) Another good video.... nice one
Great video. Hoping for some helpfull tips for more stability problems of HA. Almost daily HA is in a hung status. How can I see why this is happening ?
I've only recently installed Home Assistant as a VM on my Desktop running Windows 10 (cause it's always on). I've watched some of your videos and find them very useful and easy to understand. Now I've got all my smart blubs, smart plugs, printer, router info, person tracking working. I'm adding MariaDB and Visual Studio Code after watching this video. Just wanted to say "Thank you". Keep it going :-)
FYI: after installing MariaDB, MQTT communication with my Arduino was distributed and couldn’t get it working anymore. After rolling back a previous snapshot, everything worked again.
Hello Rob! That's a strange one, although it's not possible for the database to cause those errors, the database has nothing to do with the integrations or sensors, it's only stores history. You can run HA without a database. The only thing that it could have been is if there was a mistake made in the config for the database which made HA not startup properly, sometimes if one component fails to start up it can prevent those that load later than it from starting up too, like the MQTT integration. Were you able to see the history of other sensors after startup?
@@EverythingSmartHome odd thing was: I received my Arduino sensor data but could not switch my 24 relays on my Arduino anymore. Mqtt error: Client arduinoClient has exceeded timeout, disconnecting. After de-installing mariadb, all returned to normal. All other sensors were normal.
Thank you for explaining I had a Influxdb & Grafana Instalation running on my RP4/SSD before insatlling Mariadb - Mariadb is now running... but influx/Gafana not any longer :( - any idea?
I’ m having a question about how to find and install a unique ID in mqtt projects, in Home Assistant installed on a raspberry pi 4 64bit.... Please I’m struggling on this!
@@EverythingSmartHome Thanks for the reply...When I' m trying to connect my nodmcu configured as a switch, the mqtt doesn't let me, because it asks a unique ID....Is there any configuration that bypass this, or I should some how find a way to put this key ?
Has anyone else run into issues where history, recorder, etc. all stop working? The general advice I've seen in the forums is to delete the db file and restart, but I'm not sure if that changes with MariaDB and it didn't work for me so far.
@@EverythingSmartHome I checked logs and all I saw was failure to start the recorder. I was able to get all histories to work again after uninstalling MariaDB and removing it from my config. I'd like to get it back running again so I'm going to take a snapshot or two and then try it all over again. MariaDB was fine for 6+ months and I'm not sure why it suddenly decided to act up.
Hi. Great how to monitor lesson. All worked except the network monitor. Both In and Out return an unknown. I am running HA via a Hyper V VM. Is it possible that this will not work in a VM similar to your description on processor temperature? I have verified my network adapter name. Configuration check is "green" or good. Any thoughts are greatly appreciated.....
Hello and thanks! It should work in Hyper-V no problem, the only thing that won't work as mentioned is temperature. What was the error you got? If you need help feel free to hop into the discord server!
@@EverythingSmartHome I don't get an error. The state just shows "unknown" under developer tools and the history shows "0" for both in and out throughput lines. BTW, I love your channel. You really break things down into simple terms and procedures. I'l be sending you some coffee....
@@EverythingSmartHome makes sense, I did the MariaDB on a supervised Deb install and have let it run now a couple of days. I'll have a look into how the disk usage is now. Trying to read up on how I can controll how long data is kept and so on.
@@EverythingSmartHome So you wouldn't recomend me to use MariaDB for my HA that is installed on a flashdrive? Was thinking of using NGINX which I think is using MariaDB...
Thank you for the guide! But the processor_use entity is not created with a normal installation (no docker) on my rpi2 with a ssd. What could be the problem?
@@EverythingSmartHome yes, it is home assistant OS. I run it on an SSD by configuring it so it uses the SD card to start, then switches to the SSD. Using a RPI2 is not recommended but i have to say it runs very smoothly in my case www.creationfactory.co/2020/10/configuring-home-assistant-to-run-off.html
Hey, Lewis! What do you thing about a video thats help us switching our Raspberry Pi SD card to a SSD? Some adaptors and SSDs recommendations might be a good options for the video too!
Great video, really easy to follow. Any chance on doing a tutorial on energy cost monitoring. Particularly daily/weekly/monthly running cost using day/night tariffs
Check the linked video, the one before this (top 6 essential beginners tips). You don't need to use this add-on, you can do it however you normally access your files. Hope that helps
@@EverythingSmartHome I removed it via the terminal session in the end but just thought that utility looked good. I've already been through the 6 beginner tips :)
![[UA] NAVI проти G2 Esports | Blast Premier Fall Final](http://i.ytimg.com/vi/QWlIm4FGESQ/mqdefault.jpg)
I know this video is quite old and a lot has changed with home assistant so Im wondering if things like MariaDB are still the way to go or would it be a good thing to go back to default? as always thanks for the videos looking forward to the new sensor when it comes
How about a video of a UPS for a Raspberry Pi? I'm living in an area with a lot of power outages and I am trying to find a solution to keep my Raspberry pi 4 running
Hey. Idea for a video: How to run home assistant on an SSD with a raspberry pi!
Just get a NUC or sff pc.
@@mrphyslaw i dont have nuc, they're somewhat expensive, i already have a rasp pi and a ssd is cheap as. i dont see a need for now with as little devices as i have.
Hey Finn, I don't have a Pi 4 to use but hopefully I can try and acquire one at some point!
@@EverythingSmartHome It can be done on a Raspberry Pi 3. Would have to be a USB to SATA Adapter with a SSD and would still need a SD Card. Just have to modify the System to use the SSD as the docker container storage. Could also be easily done with a existing system by using a directory link.
Another option is a MicroSD to eMMC Adapter.
try this video ua-cam.com/video/Okf5WbJ_DAo/v-deo.html these steps worked for me
Nice video, but this isnt working for me... meybe its because of newer verison of HA ? (2021.12.10 HA OS 7.2) but after MariaDB install whole HA is noticeably slower...
I'm surprised to hear that SQLite isn't performing well (or reliably) considering Home Assistant only has 10 days of data and this should be way, way below any threshold where performance is suffering. Of course i've never analyzed the queries (the achillieas heel of all SQL database apps) but was wondering if you had any metrics on how MariaDB had improved things.
Simplicity is the keyword for that channel. Man you make things look so simple. What a great explanation as usual. Keep the good work 💪
Appreciate it Nelson!
New to HA ,and this channel has definitely been our #1 online resource: *** TIP *** not sure if this is known to all, but as a 'newbie' we found out the hard way: if you set up your 'super secure password' utilizing the character '@' in it (for example, to change the letter a,A with @, as in '@ppl3' instead of 'apple'), the database, logs, records, history will NOT START! Only after looking into the logs for an hour, we discovered the @ is used in the [>]recorder code as a designator for db/server location! So the only @ character in the [>]db_url line MUST BE the one between your 'password' and 'core-mariadb' FYI! :D
Thank you, this really helped me out Jose
Big problem when the supersecure password has the @ character included. The recorder handshake was not happening, and no data was accumulating. No documentation that I could find. Changed the db name, changed the core-mariadb with the IP address, and eventually I noticed that there was a @#$@ sequence in the yaml file that didn't look good at all. Beginner's mistake, I guess...
I wouldn't say that was a mistake on your part. No reason you wouldn't think @ was OK. It was only chance that I didn't think to include that character in mine. It (the HA interface) should make it clear in the settings if the password is restricted to a set of special characters.
So I did everything but nowhere in VSCode can I find "home-assistant_v2.db" to delete?
I saw the same thing. Apparently Studio Code Server only shows files it can edit. Do you have Terminal installed? If so. you can do it by opening the terminal and entering the following commands:
> cd /config
> rm home-assistant_v2.db
NOTE: instead of using the rm command (remove), I used the mv command (move - which is also used to rename) to rename it. I did this out of an abundance of caution (paranoia). That command is:
> mv home-assistant_v2.db home-assistant_v2.db.bak
After a few days I'll delete it with:
> rm home-assistant_v2.db.bak
You could also just use
Recorder:
commit_interval: 30
So the data is stored and will only be written on the sd card every 30 seconds
Love your video's just wondering why you would put you db_url in the configuration.yml instead of only a reference to the secrets.yml seems the better place like that
I actually do this in my configuration. the entire parameter is in the secrets.yaml file. then I reference it in the configuration.yaml like: db_url: !secret mariadb_url .... while secrets.yaml is not any more secure than the configuration.yaml file since they are colocated and not enciphered, it DOES allow me to do things like backup everything to github and not push the secrets file to github. It also allows me to easily share configuration samples with people without having to scrub them.
What tool did you use to create the CPU, Memory, and Throughput graphs? The default Lovelace sensor graph doesn't provide that level of granularity.
Hey, great video, but the adapter can also be named by "eth0" so it do not start with "en" :)
Any idea how one might swap history recording to a Docker based MariaDB instance? I’m already using MariaDB for a couple of databases and I not sure of the impact of having two MariaDB instances - one in HA and one in my Docker container.
you can just create a database in your docker and use that one
@EverythingSmartHome if you are running Home Assistant with Proxmox, would you continue to use the Add-On or move the database to a separate container or VM?
Hi, thanks for the video... planning to move to Maria DB... just a quick question. I use RPi 3 and a SSD as external data disk. Home assistant boots from an SD card , but all data is stored on SSD connected via USB port. I heard you mention not to use it with an SD card and to move to SSD. Tried a lot, but can't get to boot from SSD on RPI 3. So only did I start using the data disk feature. So is it ok to use Maria DB on my existing setup? Or will i have to boot from SSD (possible on RPI 4's but hardly available in the market right now)
HA newbie here. I'm on the 2024.2.1 version of HA and it looks like things have changed a bit with the UI since this tutorial was created. Would love an update!
You are not 100% correct here, I get that most people can accept the data loss when the performance and reliability of a real RDMS is used but there's a pretty reasonable way for people with huge history sqlite tables to convert the db file to sql: With sqlite3 and a sqlite3-to-mysql-converter that is shown (4 years ago so this may be even simpler today, or some url or alter command adjustments could be needed) here ua-cam.com/video/4rsxnN8gkHk/v-deo.html. Perhaps doing a new video on the steps in the current GUI for all newcomers to enable them to do some data handling? The key and alter table parts could pose some risks though..
It worked as you suggested, and I know you mentioned Sensor data will be lost, bu I was hoping I had heard you wrong. Of course, it is lost. Is there any way to get the sensor data back. Losing all the energy stats is a biggy. Is there a solution you are aware of?
Why sqlite less reliable ? Mariadb without high avail cluster is really more reliable ??
Im running home assistant on a VM in my Synology NAS. I also have a docker container with mariadb running for my kodi database. can i use this for home assistant too?
Another great tutorial.. Thank you.. Like the Cards format you show at the end.. Could you go into more detail for that final monitoring dashboard look?
Thank you! Sure I can look at that, they are made using "Apex" cards from HACS, there are even some example code in their Github. Hope that helps a little!
MariaDb can also get corrupted after power loss and it doesn't recover automatically! the workaround is to uninstall mariadb and install it again! its even worse than to rename the sqlite database. Do you have any advice?
I use raspberry Pi 4 8gb with 240 ssd, I get error message on check config not sure why can you check see what i did wrong on the command
sensor:
- platform: systemmonitor
resources:
- type: processor_use
- type: processor_temperature
- type: memory_free
- type: disk_use_percent
- arg: /
- type: disk_use
- type: disk_free
my network never show up when i check on terminal
About the SD warning.... how about if I use a SD Card on a IPbox with HA ?
If you're seeing issues with recorder, logbook etc after switching over to MariaDB; try a shorter password and remove any symbols, restart after changes.
Thank you Kevin for that info. That fixed my issue with the recorder, logbook, etc. = )
Great video. Any suggestions on manually entering some of the historical sensor data ? Example: I have about 12 utility meters that keep count of my solar gen, electric usage, grid usage and battery usage. I have 3 years of history now so it would be a sad day to lost it. I just have 4 sets of sensors for daily, monthly, yearly and all time. :-) any advice on how to add if I convert are welcome
+1: I actually came to this video for that exact answer
Hey, the main devs came out recently and said that most people should not be changing databases. any chances on updating the video so people know not to switch
FYI I got a heap of errors on first boot and it turns out that you can't use certain symbols in your MariaDB password
Thanks for the tip on the Maria DB, even though I have been working with HA since 0.4, I still gain a lot of ideas from your channel.
Thank you, your a true veteran of Home Assistant! Appreciate the support!
@@EverythingSmartHome Hi! I have an update on the Maria DB and a possible issue. I've had to back out to my previous configuration. My hardware is an INTEL NUC Celeron 7CYJH with 8GB Ram and a 128GB SSD and I'm using NABU CASA.
Basically Alexa Media TTS fails to work with Maria DB, at least it keeps requesting re-authentication and then the same again a few hours later. I backed out to the previous snapshot and all ok again. Very strange. I'll try installing again to double check, but has anyone else seen this issue?
Hello! That is strange, MariaDB doesn't have anything to do with the workings of HA, it only handles history and log book data, that is all. No integrations or anything is stored in the database.
Did you check your logs before reverting? I've seen others complain that different things aren't working but it's because they've made an error in the mariadb setup and HA can't start properly, preventing it from loading components after the recorder. The HA logs should confirm this
@@EverythingSmartHome I will repeat the process to be 100% sure, but it was the only change I made. I have a policy of making one change at a time exactly for this reason, I'll feed back again. No doubt of the performance improvement on the NUC though. I was wondering if I could easily use a remote Maria DB as I run one on my Synology DiskStation for my Amateur Radio logbook.
Definitely check the logs after migrating to see if there any issues, find us on discord if you need some help and we can try to get you sorted
Hello, it's possible to use Grafana with MariaDB without installing InfluxDB?
Thanks for your inspire videos.
Thanks for this. But after following this tutorial and deleted the old database file I see the system monitor-stats didnt get removed. Are those stored somewhere else? Also my energy sensor didnt seem to get resetted!
Great video.... now how about a step further by covering how to keep your database from getting out of hand using recorder to set what to record and also how to cap size of database by setting number of days... is there a simple way to wipe the database or compress it... I don’t know.... I just think there has to be a way to keep the data beast under control and from growing tooooo laaaarrrrrge.
check out the recorder HA docs. They have what you need.
Hey Richard, thanks! I think the default amount of data to retain is 10 days and after that it's purged, but you can lower that value if you want, as Miguel mentioned have a check of the recorder documentation
I tried but never got the feeling it was working as my database seem to constantly grow in size. Is there an way to report that the purge has occurred or... track on a panel the size of the database?
very useful video. thanks for that.
It did however leave me wondering if running the db in mardiab somehow will require me to take some steps to make sure that the database is also included in my auto backup to my nas. if anybody has any thoughts on this I would love to hear them!
Having moved over to MariaDb i have run into all sorts of issues with snapshots - specifically the fact that the MariaDb does not appear to function if restored from a snapshot meaning that when i run into issues upon major core releases (which i always do), the restore does not work for history/maria etc and i have to nuke it and rebuild from scratch.. Would you have any recommendations as to how to backup MariaDb? Other users in the forums have recommended stopping the service before taking a snapshot which is fine however it would be great if this could be automated (i am using the GDrive backup solution which is amazing! - apart from with this Db)
Did you ever figure this out? I was looking into moving to MariaDB, but I use the "Home Assistant Google Drive Backup" addon and I'm concerned that my restores won't work, or won't include the MariaDB.
@@davidhill8151 hi David, not as yet no however I would say that HA seems to much better of late in terms of stability that I haven’t needed to restore from backup. Also made some tweaks to my settings so Maria us used got short term only with Influx being used for long term storage, with this setup loosing the Maria would not be too much of a problem.
Great video! Using Home Assistant over a year now. Just switched to MariaDB. One question,- in the roundup you showed a couple of graphs. Which card did you used for the network network_throughput_in and network_throughput_out.
Awesome if you can share this :)
Kudos for the video. Keep up the good work.
Hi Lads, I understand Maria DB is sensitive to backups unless stopped prior to manual backups. Might I decide to use MariaDB in my forthcoming install , would this cause a problem with automatic routine backups with HA OS, generating corrupted files?
Thanks for this video. I am also running HA virtualized using qemu/kvm and virtual manager however, I am unable to perform snapshots with the current HA qcow. I was wondering what were you using for snapshots? Thx.
adapters that begin with en are cause its in a VM. If you are on bare metal such as a raspberry pi and using the built in ethernet,. the adapter is likely going to be "eth0" or if using built in wifi "wlan0"
Thanks! Tonight, I used this and your related video to setup both influxdb and mariadb with 372 day retention. I guess I'll have to use them to watch my free disk space, now ;) Tonight I also found the Plotly Graph Card (HACS) which I think I'll love. I've been using the apexcharts-card (HACS) and like it a lot.
Hello, I'm planning to move my installation to an ODROID with eMMC. Is it ok to move to MariaDB with eMMC? Or is it not recommended like an SD card install?
This is too difficult for a beginner as the newest Homeassistant does not allow you to alter the configuration file and Supervisor does not appear on the sidebar. Bear in mind that this is a "Beginners Guide". Don't get me wrong, I really like your videos but for a beginner they are very frustrating as Homeassistant seems to change ifs layout and system at the drop of a hat.
The newest home assistant does allow you to edit the config file. Supervisor is renamed to settings as I replied to your other comment.
Despite the "speed increases" of the past 3 or 4 releases, I'm finding that with 2022.7 it takes much longer to access the HA web UI than it ever has. I'm running a VM with decent and dedicated resource allocation on a fast server and first access to HA feels like I'm on dialup.
Thank you, it was helpfull !
Anyway to do this while retaining all the sensor data?
Great video. I initially set up HA on an SD card and RPi 4 4G just to play around and see wat it could do. As I am planning on keeping it, is it possible to transfer HA to an SSD drive withing having to do a complete reinstall?
Thanks! It would be possible yes, unfortunately I don't have a Pi 4 to be able to demonstrate it
@@EverythingSmartHome Have transferred HA from an SD card to SSD drive all went smoothly. Now going to install MariaDB as suggested.
Just switched to MariaDB using this video after corruption with my SQLlite DB. Really appreciate the clear no-nonsense delivery of information. Surprised how easy it was to do. 5 minutes and all up and running.
Good info! I had recently set this up but now realized I’ve made some mistakes so will be going back and correcting them like going to an SSD card lol. One thing to note is that the history will have too much data, like all the entities so you can filter what you want in there by typing “include:” or “exclude:”. This is mentioned in the HA page
Yes that's good information Miguel!
@@EverythingSmartHome what SSD do you recommend to buy?
Where is the new database located? I want to create a sensor out of it!
Have you tried using MariaDB with a socket connection? This would definitely be faster still. I have a feeling that socket may be obfuscated a bit because I think Maria DB is running inside a docker container and i'm not sure that HA and Maria have a shared volume where this socket can be reached.
There's hardly any difference between running sqlite vs mariadb on an sdcard. If you use an A1 or A2 sd card, this shouldn't be that much of a problem anyway. :)
Where is the new MariaDB file database located (in case you might want to back it up seperately)?
I need to check how my Docker got things set up, I have the "IOTstack" installer. That community is huge about InfluxDB and Grafana and then Mosquitto. Maybe this is overkill, however I wanted to ask if anyone watching this video had any experiences with InfluxDB
Lewis thanks again for a great video. I followed along and all went well except I am missing a history tab. Is there something I need to add to get it to appear? I also did not see the old db file to delete?
This has fixed my biggest gripe i had with my Setup: Web UI being sluggish!! You are my Hero!! ;)
I thoroughly appreciate your SD card warning. My strong preference is to not run Pi's from SD - I was so glad when the Pi 4's started shipping with built in support for booting from USB. I prefer to run them with m.2 SSDs. All that said, my HA now runs on a VM. I migrated a couple weeks ago and it's a LOT snappier than it was on the Pi4.
Is there a need for a strong password at all when it's being stored in plain text in the HA config?
Maybe you can keep your history with something like this :
sqlite3 home-assistant_v2.db .dump > sqlite3.dump.sql # export the DB
# find a way to convert it to a MYSQL usable file : mysql_converted.sql
mysql -u myuser -h myhostname -p homeassistant < mysql_converted.sql # import the data
Yeah you most likely can do something like that, bit out of scope for this more beginners video but yep that's a good idea!
Is it still true, that I should use MariaDB over SQLite?
I believe it is still desirable to use MariaDB if your database is larger and you want it to be a bit more bulletproof. SQLlite has been fine for years but decided to switch after my DB got corrupted after a crash due to the recent dodgy NodeRed update. So you don't *need* to. But seems to be a popular update once you've got the hang of HA.
Great video, I've set up Maria DB a while back when I saw your video. Do you have any information/video on how to set up reporting or extract data from the events table? Keep up the great work!
You are a hero sir! My home assistant owes its looks, functionality and general non broked ness to you! Thank you.
Invalid config for [sensor.systemmonitor]: value must be one of ['disk_free', 'disk_use', 'disk_use_percent', 'ipv4_address', 'ipv6_address', 'last_boot', 'load_15m', 'load_1m', 'load_5m', 'memory_free', 'memory_use', 'memory_use_percent', 'network_in', 'network_out', 'packets_in', 'packets_out', 'process', 'processor_temperature', 'processor_use', 'swap_free', 'swap_use', 'swap_use_percent', 'throughput_network_in', 'throughput_network_out'] for dictionary value @ data['resources'][0]['type']. Got 'prozessor_use'. (See ?, line ?).
What am I doing wrong?
You have a Z in processor use like the error mentions
@@EverythingSmartHome Thanks that was the mistake.😬
🤣
Have these sensor settings changed now? I keep getting error messages when trying to type them in 🤔
Question, I use influxDB integration for my historical data and graphs through Grafana. Moving to mariaDB, should the influx data just pick up where it left off?
There should be no issue since influxdb is a complete seperate standalone integration. Hope that helps!
Is it easy to migrate homeassistant from an SD card to SSD? And if I run from an SSD shouls I use the 64 bit version? And can you make a guide how to? I'm currently running on an pi4 4GB model. Thanks for the video's!
Thanks for watching! Yes it's easy, I would just use the snapshot restore feature and do a fresh install. Hope that helps!
I don't understand, I did the mariadb update, pretty straight forward, but afterwards I get all kinds of errors.
I get an error saying the following integratings and platforms could not start.
recorder
logbook
history
energy
default config
I dont undestand why, I saw a complex pw can cause an issue, mine is pretty straigh forward only 8 characters long.
Any thoughts?
The errors are a knock on effect from the recorder not working - make sure add-on is started and check logs for errors. Also no @ symbol in the password
@@EverythingSmartHome did my comment get deleted or something? I left a reply with a link to imjur to see a screenshot to the log I have.... Does UA-cam not like that?
@@EverythingSmartHome I just typed out the errors I was seeing.
waiting on integrations to complete setup: spotify
(warning) bootstrap.py
error setting up entry home for smartthings
(error) components/smarrthings/_init_.py
setup failed for logbook: (dependencyError(...), 'Could not setup dependencies: recorder')
(error) setup.py
unable to setup dependencies for logbook. setup faild for dependencies:recorder
(error) setup.py
setup failed for recorder: Integration failed to initialize.
(error) setup.py
Thanks for the help!
Very important step missed - around 3:30 Restart HA, and wait and wait, make a cup of tea and wait ** Do not faff about with your config file think you have got your password wrong ....... patience is a virtue I don't have!
Great Video again
Haha glad you got through it in the end 😂
Cheers ... sussed out my missing logbook and history. In MariaDB config I had changed the user name but hadn't updated the URL. Thanks for the kick in the arse. Been bugging me for months
Glad you got it working!
Couldn't wait for tomorrow...or the weekend.....Job done! Big Thumbs Up.....Spot on as usual, thanks!
Awesome Paul, glad it worked!
Thanks MariaDB really seems to give that extra umphh, but with the system monitor my only assosiated resource with IP for my home assistant is wlan0, since I have need for wifi for my instance I had to deviate from yor installation to SSD, I used etcher and start directory with wifi credentials to setup my wifi setup, possible my wifi setup method screws with home assistant and wifi is not supported for trafic data collection? After smooth setup with guide and 6 frustrating hours of trying to solve the network trafik problem I apriciate all recomendations. Best regards from sweden
Hello! In the SSD video I showed how to create WiFi details 😅 but it shouldn't cause an issue anyways no!
Finally got round to adding an SSD to my install. These TTT videos are amazing. Every single one of them is so clear and concise. It's epic. Thanks again Lewis
great vid, everything went swimmingly.
How can i add configuration validation in server controls?
Great video.... again!! You are awesome! Why? Because videos are high quality, quick, no-fluff, very informative. Keep up the good work!
Thanks my friend, really appreciate it! 🙏
Waiting to order my blue when they come back in stock (fingers crossed) before i do the mariadb switch over.. my rpi3 is busy enough :-) Another good video.... nice one
Nice, let me know how the blue goes! You've got lots of exciting projects now 😅
Great video. Hoping for some helpfull tips for more stability problems of HA. Almost daily HA is in a hung status. How can I see why this is happening ?
Thanks! Check your logs to see what's going on!
Good video, been meaning to setup MariaDB for a while watching this spurred me on. Now to see what difference it makes. Cheers!
Thanks Jon! Good luck!!
ever thought of adding a sensor to monitor the size of the mariadb database?
I've only recently installed Home Assistant as a VM on my Desktop running Windows 10 (cause it's always on). I've watched some of your videos and find them very useful and easy to understand. Now I've got all my smart blubs, smart plugs, printer, router info, person tracking working. I'm adding MariaDB and Visual Studio Code after watching this video. Just wanted to say "Thank you". Keep it going :-)
That's awesome, thanks for the taking the time!
FYI: after installing MariaDB, MQTT communication with my Arduino was distributed and couldn’t get it working anymore. After rolling back a previous snapshot, everything worked again.
Hello Rob! That's a strange one, although it's not possible for the database to cause those errors, the database has nothing to do with the integrations or sensors, it's only stores history. You can run HA without a database.
The only thing that it could have been is if there was a mistake made in the config for the database which made HA not startup properly, sometimes if one component fails to start up it can prevent those that load later than it from starting up too, like the MQTT integration.
Were you able to see the history of other sensors after startup?
@@EverythingSmartHome odd thing was: I received my Arduino sensor data but could not switch my 24 relays on my Arduino anymore. Mqtt error: Client arduinoClient has exceeded timeout, disconnecting. After de-installing mariadb, all returned to normal. All other sensors were normal.
Thank you for explaining
I had a Influxdb & Grafana Instalation running on my RP4/SSD before insatlling Mariadb - Mariadb is now running... but influx/Gafana not any longer :( - any idea?
There shouldn't be an issue, can you check your logs?
@@EverythingSmartHome THank you I found the issue ... an old non-updated IP-Adress
Yess!!! :D
Haha thanks for commenting Mateo!
Thank you. This worked great. I love your videos. Please keep them coming.
Thanks for the support Andy!
Another great video, added to the todo list for the weekend. Thank you
Thanks John! Let me know how it goes!
@@EverythingSmartHome All working as expected, thanks Lewis
I’ m having a question about how to find and install a unique ID in mqtt projects, in Home Assistant installed on a raspberry pi 4 64bit.... Please I’m struggling on this!
Hello, sorry I don't understand the question?
@@EverythingSmartHome Thanks for the reply...When I' m trying to connect my nodmcu configured as a switch, the mqtt doesn't let me, because it asks a unique ID....Is there any configuration that bypass this, or I should some how find a way to put this key ?
Has anyone else run into issues where history, recorder, etc. all stop working? The general advice I've seen in the forums is to delete the db file and restart, but I'm not sure if that changes with MariaDB and it didn't work for me so far.
I would guess that your add-on has stopped or not started properly? Have you checked the add-on logs as well as HA logs? Mariadb never fails for me
@@EverythingSmartHome I checked logs and all I saw was failure to start the recorder. I was able to get all histories to work again after uninstalling MariaDB and removing it from my config. I'd like to get it back running again so I'm going to take a snapshot or two and then try it all over again. MariaDB was fine for 6+ months and I'm not sure why it suddenly decided to act up.
make sure there is no @ in your database password
and another one done :p ,although not the system monitoring since i don't see the point for myself :)
Nice! 🙌 Yeah for your setup probably doesn't make an awful lot of sense!
Hi. Great how to monitor lesson. All worked except the network monitor. Both In and Out return an unknown. I am running HA via a Hyper V VM. Is it possible that this will not work in a VM similar to your description on processor temperature? I have verified my network adapter name. Configuration check is "green" or good. Any thoughts are greatly appreciated.....
Hello and thanks! It should work in Hyper-V no problem, the only thing that won't work as mentioned is temperature. What was the error you got? If you need help feel free to hop into the discord server!
@@EverythingSmartHome I don't get an error. The state just shows "unknown" under developer tools and the history shows "0" for both in and out throughput lines. BTW, I love your channel. You really break things down into simple terms and procedures. I'l be sending you some coffee....
Is it advisable to run this on a mini PC with eMMC storage?
Should be OK!
My old db was 120MB when i deleted it. That installation of HA did run for less than a week! Scary numbers there!
Depends how many entities I guess!
@@EverythingSmartHome makes sense, I did the MariaDB on a supervised Deb install and have let it run now a couple of days. I'll have a look into how the disk usage is now. Trying to read up on how I can controll how long data is kept and so on.
Is MariaDB bad for USB drives as well or is it only bad for SD cards?
It's generally as bad, USBs and SD cards are made from much lower quality flash than say an SSD
@@EverythingSmartHome So you wouldn't recomend me to use MariaDB for my HA that is installed on a flashdrive?
Was thinking of using NGINX which I think is using MariaDB...
@@EverythingSmartHome Also is MySQL(I think the defult for HA) better when it comes to this issue?
Because I really want to use my flashdrive for HA.
Thank you for the guide! But the processor_use entity is not created with a normal installation (no docker) on my rpi2 with a ssd. What could be the problem?
Your welcome! Your using Home Assistant OS? (If you are btw then this runs docker) also you have an SSD on a Pi 2?!
@@EverythingSmartHome yes, it is home assistant OS. I run it on an SSD by configuring it so it uses the SD card to start, then switches to the SSD. Using a RPI2 is not recommended but i have to say it runs very smoothly in my case
www.creationfactory.co/2020/10/configuring-home-assistant-to-run-off.html
Thanks again!
Speeded up the reaction of devices and also restarting!
Love your videos! Keep it up!
Thanks my friend!
Woo hoo! MariaDB up and running with all sensors spewing data! 😀
Your on a roll! 😅
Hey, Lewis! What do you thing about a video thats help us switching our Raspberry Pi SD card to a SSD? Some adaptors and SSDs recommendations might be a good options for the video too!
Hey! I'd love to but I don't have any Pi 4s I can use, hopefully I can acquire one soon!
Looking forward for this!
Great video, really easy to follow. Any chance on doing a tutorial on energy cost monitoring. Particularly daily/weekly/monthly running cost using day/night tariffs
Thanks David! I'd loved too but unfortunately don't have anything I could use to demonstrate it on at the moment
Thanks for the great vids Lewis, all worked perfectly!
Excellent, glad it was useful!
To delete the old DB you use "File editor" from the left bar but I don't have this, what add-on is this?
Check the linked video, the one before this (top 6 essential beginners tips). You don't need to use this add-on, you can do it however you normally access your files. Hope that helps
@@EverythingSmartHome I removed it via the terminal session in the end but just thought that utility looked good. I've already been through the 6 beginner tips :)
is there any way to not expose your DB Pw in the configuration.yaml?
You could probably put the whole "mysql:/...." in your secrets file?
Yes as above putting the entire connection string into the secrets file should do the trick!
@@EverythingSmartHome thanks, it worked :)