Strange history after switching to MariaDB

Configuration
1

Hi there,

Today I switched to MariaDB. It wasn’t EZ for me. But finally it started to work. But… :slight_smile:

The history values are very strange. With the SQLite DB I see for example this pattern for my rPI CPU temp:
image

After I switched to MariaDB I can see this in the history:
image

Or sometimes like this (no values and 0:00 continuesly on x axis):
image

Or if I check another sensor history with SQLite:
image

I have deleted the SQLite DB and recreated. The new history log immediately started to populate with quite good resolution:
image

But with MariaDB the history values are totally strange.
image

Sometimes it can be loaded sometimes not. Sometimes the current sensor value are not on the history at all:
image
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.

2

Hi @TestAck18,

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.

3

There is an issue. Resolved in upcoming 0.111.3

4

If you are using 0.111.2 there is an issue with mariadb.

Looks to be fxed in 0.111.3 - or roll back to something like 0.111.1

See the discussion in the 0.111 forum

5

Thanks @DavidFW1960 and @phileep - I’m sure I looked it up before I tried it.
Must have been late when I did so - or very early.

6

111.3 is released now…

7

Thanks!
It doesn’t show up in the supervisor panel yet - is there a way to force the updater to check and display?

8

no but you can use the ssh addon

ha core update --version=0.111.3
9

Thank you, again.
Forgot about being able to do it via the SSH addon.

10

Thank you! I have just updated. It really looks like 0.111.3 solved the issue!

11

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:

image
image969×185 17.2 KB

According to this history the Motion is off since 2:28:16 AM which is definietly not true.

12

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.

13

Do you mean to restart only HA or the whole host? (HA was already restarted.)

14

After HASSIO booted for the first time after upgrading to 0.111.3 my sensors were still playing up however restarting HA again fixed the sensors.

15

I have restarted the whole host multiple times. Now I have binary_sensor history but with a big delay. Thanks for the tip!

Edit: I was too fast. It worked until ~6pm
image

Since then I have no history again.

16

Seems to be all working fine for me now on 0.111.3 :+1:

1 Like
17

Good for you! :slight_smile:

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:
image

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.

1 Like
18

v0.111.3 put back all my history graphs! thanks :+1:

19

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.