Energy Dashboard SQL problem after 2021.10 upgrade

Configuration → General → Logs also shows for me this and it probably is the reason the energy dashboard is empty since the update:

Logger: homeassistant.components.recorder
Source: util/dt.py:133
Integration: Recorder (documentation, issues)
First occurred: 11:05:21 AM (168 occurrences)
Last logged: 11:50:10 AM

Error while processing event StatisticsTask(start=datetime.datetime(2021, 10, 8, 9, 25, tzinfo=datetime.timezone.utc)): argument 1 must be str, not int
Error while processing event StatisticsTask(start=datetime.datetime(2021, 10, 8, 9, 30, tzinfo=datetime.timezone.utc)): argument 1 must be str, not int
Error while processing event StatisticsTask(start=datetime.datetime(2021, 10, 8, 9, 35, tzinfo=datetime.timezone.utc)): argument 1 must be str, not int
Error while processing event StatisticsTask(start=datetime.datetime(2021, 10, 8, 9, 40, tzinfo=datetime.timezone.utc)): argument 1 must be str, not int
Error while processing event StatisticsTask(start=datetime.datetime(2021, 10, 8, 9, 45, tzinfo=datetime.timezone.utc)): argument 1 must be str, not int
Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/__init__.py", line 673, in _run_event_loop
    self._process_one_event_or_recover(event)
  File "/usr/src/homeassistant/homeassistant/components/recorder/__init__.py", line 682, in _process_one_event_or_recover
    self._process_one_event(event)
  File "/usr/src/homeassistant/homeassistant/components/recorder/__init__.py", line 788, in _process_one_event
    self._run_statistics(event.start)
  File "/usr/src/homeassistant/homeassistant/components/recorder/__init__.py", line 771, in _run_statistics
    if statistics.compile_statistics(self, start):
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 315, in wrapper
    return job(instance, *args, **kwargs)
  File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 336, in compile_statistics
    platform_stat = platform.compile_statistics(instance.hass, start, end)
  File "/usr/src/homeassistant/homeassistant/components/sensor/recorder.py", line 388, in compile_statistics
    result = _compile_statistics(hass, session, start, end)
  File "/usr/src/homeassistant/homeassistant/components/sensor/recorder.py", line 516, in _compile_statistics
    last_reset := _last_reset_as_utc_isoformat(
  File "/usr/src/homeassistant/homeassistant/components/sensor/recorder.py", line 371, in _last_reset_as_utc_isoformat
    last_reset = dt_util.parse_datetime(last_reset_s)
  File "/usr/src/homeassistant/homeassistant/util/dt.py", line 133, in parse_datetime
    return ciso8601.parse_datetime(dt_str)
TypeError: argument 1 must be str, not int

This is the same error as OP:

datetime.datetime(2021, 10, 7, 9, 0, tzinfo=datetime.timezone.utc), 1)

seems they changed some fundamental time function. And now neither SQL nor their own recorder understands the output anymore.

I just saw a new update which is supposed to fix the SQL issue. I have updated so now will wait for an hour, when the next query will be sent…

I do still think this time issue is there too, but may even be unrelated.

I have found that my issue has to do with the MySQL server version; it is too old and the latest update uses a newer query:

OK, I missed that.
The problem is that we are relying on the `row_number()` function which was introduced in 5.8 / 8.0, released in 2016.

I wouldn’t know how to update that on my drobo though. So for now going back to the normal db file on the SDCard

Getting the same problem here since the updated. The latest patch 2021.10.2 didn’t seem to help at all!
Running mysql 5.6.41 so guess I’ll need to update it!

This should have been documented as a breaking change in the changelog.
My current MySQL version is 5.7.33

Pretty sure it was unintended.
It’s worth opening an issue , though (if anything, to update the doc that still mention 5.7 as minimum).

so should we wait for a fix or just switch to mariadb plugin and loose all the earlier long term statistics thats been saved in mysql

I’ve updated my MariaDB from 10.1.48 to 10.6 to get it back working. No sql errors anymore and the energy dashboard is back online.

See also Recorder error using MySQL in a remote server - #6 by evb

Tried updating my MySQL instance to 8.0 but boy that did not go well. I’ll probably switch to MariaDB on an external server, cuz I can’t access the database from my laptop when I use the addon.

@CAP-Team If you use the MariaDB plugin from homeassistant itself, via the Supervisor, then the version should be already correct.
Can’t see from the changelog which version of MariaDB exactly is used, but the current version of the plugin is now 2.4.0 and I see for version 2.2.1, the note

Don’t delete the mariadb.sys user, it’s needed in MariaDB >= 10.4.13

Setting up MariaDB on my server (instead of MySQL) was a breeze.
Got it running in under 10 minutes.
Unfortunately I lost my history, since the database files got corrupted when trying to update to MySQL 8.0 (I know, make back-ups :roll_eyes:)

just updated my mysql container to 8.0.26 and seems to have gone smoothly and resolved my recorder issues…should i look at switching to mariadb though? are there any advantages or disadvantages of one vs the other?

This is a bad shot. My HA data ends up on Mariadb 5.5.57 installed on nas Qnap and I have no way to update or even install MariaDB on docker. Is there any hope that a fix will be released?

According to the release notes, the row_number issue is supposed to be fixed in 2021.10.4

Tried it again yesterday, but still the same issue

Assuming you have the same error, the OVER clause is also someting new in mysql 8.0.

2021.10.4 restored long term stats for me on MySQL5.7

Also for me. Thank you

Yes, I can confirm too… The latest update fixed it for me too. Thanks to all the guys working on this!!

It seems like there’s an issue with the SQL syntax in your query, specifically related to the row_number() function and the OVER (PARTITION BY ... ORDER BY ...) clause. This error likely emerged due to changes in MySQL server version compatibility after your upgrade to 2021.10. To resolve this, you may need to adjust the syntax of your SQL query to be compatible with your current MySQL server version.

Check the MySQL documentation corresponding to your server version to ensure proper syntax usage for functions like row_number() and window functions. Additionally, consider consulting the release notes of your Home Assistant version to see if there are any specific instructions or compatibility issues mentioned regarding MySQL versions.

1 Like