Today I switched to MariaDB. It wasn’t EZ for me. But finally it started to work. But…
The history values are very strange. With the SQLite DB I see for example this pattern for my rPI CPU temp:
After I switched to MariaDB I can see this in the history:
Or sometimes like this (no values and 0:00 continuesly on x axis):
Or if I check another sensor history with SQLite:
I have deleted the SQLite DB and recreated. The new history log immediately started to populate with quite good resolution:
But with MariaDB the history values are totally strange.
Sometimes it can be loaded sometimes not. Sometimes the current sensor value are not on the history at all:
I can confirm that in this last history the rPI CPU temp was not constantly 37C 3+ hours…
Its marked with green when the scrennshot was taken the sensor showd 40.9C while the history showed much less.
I have tried with different commit_interval but non the default non ‘1’ helped.
Any idea? In the logs I cannot find DB related issue.
funny you’re posting this - or better: not.
I tried to do the same thing over the weekend. I added the MariaDB addon, set up the database, user, and access rights according to the addon documentation and added the recorder entry to the configuration.yaml.
After some back & forth with the recorder entry (I had to replace the string core-mariadb with the IP address Portainer showed for the addon) HA would eventually start up and start recording.
But when I looked at the history, I saw similar entries to what you’re showing above - it seemed rather random what was recorded when and how.
And some graphs even seemed to show multiple days but without any actual values.
Couldn’t make any sense of it so I just commented out the recorder entry and went back to the built-in database.
Hope that somebody has an idea what might cause that.
Something is still not fine. Most of my sensors are showing well. But my PIR sensors not. In case of motion my PIRs are showing motion for 3 sec. The recorder commit_interval is 1 sec. However the PIR history shows there were no move the whole morning even if the actual Motion status is ON:
I had to restart HASSIO after I upgraded to 0.111.3 to get everything running again. On the first boot after upgrade some sensors were still incorrect. Try a reboot.
Hmm… It’s something with the Node-Red addon. If I uninstall my history starts working after I restart MariaDB addon. Strange…
Ok… Not - or not only - the node-red:
Something happened around 3am. In the logs I cannot any strange. Any idea how can I debug this? Anyone else experiencing similar? The history works a while then suddenly stops.
I can confirm my sensors are still reporting up2date values.
Ok, Now I’m a bit affraid of saying anything but I believe I figured it out.
Finally I was able to connect to my HA’s MariaDB with MySQL Workbench and I saw that most of my sensor values are comitted 3-5 minutes later then it happened. I was thinking of maybe there is some “buffering/caching” before the system writes it out to the SD card. But I wasn’t sure how it works.
So finally I decided to replace my SD card. Voila… My system is much faster now and currently the history is correctly wrote to the DB. Let’s see the situation a few hours later/tomorrow. But it highly looks like a shitty/dieing SD card caused all of my trouble… We will see.