I have a problem with Energy Dashboard after moving to Mariadb

Energy
#1

Yesterday I switched to Mariadb, copying the data from standard sql to the new Mariadb. I did this between 10:00 and 11:00 yesterday. After that I noticed that the energy display from 10:00-11:00 a.m. was not available, I thought ok, probably because of the upgrade. But today it is again the case that the data for the period 10-11 a.m. is not displayed. Attached are two screenshots

1
11396×493 50.6 KB

I’ve checked the data on the counter, the total data for the day is correct, so it’s just the bar that’s not showing.

and no status is displayed for the sensors, although the data is available!

Does somebody has any idea?

Thanks

#2

2
21391×491 51.8 KB

#3

3
31409×517 69.1 KB

#4

4
41020×454 46.8 KB

#5

The problem still occurs, I noticed this error, it occurs every 8 hours.

‘2023-02-26 15:00:10.588 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: float division by zero
2023-02-26 15:00:10.589 ERROR (Recorder) [homeassistant.components.recorder.core] Error while processing event StatisticsTask(start=datetime.datetime(2023, 2, 26, 13, 55, tzinfo=datetime.timezone.utc), fire_events=True): float division by zero
Traceback (most recent call last):
File “/usr/src/homeassistant/homeassistant/components/recorder/core.py”, line 671, in _run_event_loop
self._process_one_task_or_recover(task)
File “/usr/src/homeassistant/homeassistant/components/recorder/core.py”, line 684, in _process_one_task_or_recover
return task.run(self)
File “/usr/src/homeassistant/homeassistant/components/recorder/tasks.py”, line 143, in run
if statistics.compile_statistics(instance, self.start, self.fire_events):
File “/usr/src/homeassistant/homeassistant/components/recorder/util.py”, line 600, in wrapper
return job(instance, *args, **kwargs)
File “/usr/src/homeassistant/homeassistant/components/recorder/statistics.py”, line 701, in compile_statistics
compiled: PlatformCompiledStatistics = platform.compile_statistics(
File “/usr/src/homeassistant/homeassistant/components/sensor/recorder.py”, line 382, in compile_statistics
compiled = _compile_statistics(hass, session, start, end)
File “/usr/src/homeassistant/homeassistant/components/sensor/recorder.py”, line 522, in _compile_statistics
stat[“mean”] = _time_weighted_average(fstates, start, end)
File “/usr/src/homeassistant/homeassistant/components/sensor/recorder.py”, line 127, in _time_weighted_average
return accumulated / (end - start).total_seconds()
ZeroDivisionError: float division by zero’

#6

I’m seeing the same thing, also at 10 am but I saw it at 1:40 am as well, and also after migrating to MariaDB. However, I did my migration at around 5:30 pm yesterday, so I don’t think the time at which you did your migration is related.
I’ve opened an issue to triage: [Recorder, MariaDB?] StatisticsTask: float division by zero · Issue #88846 · home-assistant/core · GitHub

#7

Hello and thanks for the reply, I’m glad I’m not the only one with this. I also migrated the data from sql to mariadb and since then I’ve had the problem. 1 hour is missing in the Energy Dashboard, which is always shifted by 25 hours, and the value of the missing hour is added to the next hour. It seems you found the solution to this problem, could you tell me where I can change these incorrect values?

#8

I can’t say what’s wrong with your database, but if it’s the same problem as with mine, these modifications were likely the ones that fixed it for me: [Recorder, MariaDB?] StatisticsTask: float division by zero after migration from sqlite to MariaDB · Issue #88846 · home-assistant/core · GitHub. 6 columns in 2 tables needed their data types changed. The comment after that one also attached the full set of changes that get it back into parity with the original HA schema.

I ran the ALTER TABLE queries directly on the mariadb using a mysql client. If you don’t have a client or know how to get one, you can try the phpMyAdmin add-on. But proceed at your own risk. If you aren’t comfortable making those changes, you might be better off reverting back to the default sqlite database.

#9

Hello, thank you! I fixed the bad columns with phpMyAdmin add-on and the problem is fixed, thanks again.