Energy Dashboard SQL problem after 2021.10 upgrade

Remko · October 7, 2021, 11:21am

I have been getting errors ever since I upgraded to 2021.10. I see several SQL errors in the log (below) and they keep appearing when the Energy Dashboard tries to get the latest info (so 12 minutes past the hour). This started hapening right after the latest upgrade so I am afraid something is wrong here.

I am using Mysql on my Drobo NAS to store the database and use following connection string (which all works great for the rest)
Anyone any idea what I could do here?

confguration.yaml

db_url: mysql://user:password@INTERNAL-IP/homeassistant?charset=utf8mb4

The log error:

Logger: homeassistant.components.recorder.util
Source: components/recorder/util.py:135
Integration: Recorder (documentation, issues)
First occurred: 11:00:10 AM (9 occurrences)
Last logged: 1:00:11 PM

Error executing query: (MySQLdb._exceptions.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY statistics_short_term.metadata_id ORDER BY statistics_short_term.s' at line 2") [SQL: SELECT anon_1.metadata_id AS anon_1_metadata_id, anon_1.start AS anon_1_start, anon_1.last_reset AS anon_1_last_reset, anon_1.state AS anon_1_state, anon_1.sum AS anon_1_sum, anon_1.rownum AS anon_1_rownum FROM (SELECT statistics_short_term.metadata_id AS metadata_id, statistics_short_term.start AS start, statistics_short_term.last_reset AS last_reset, statistics_short_term.state AS state, statistics_short_term.sum AS sum, row_number() OVER (PARTITION BY statistics_short_term.metadata_id ORDER BY statistics_short_term.start DESC) AS rownum FROM statistics_short_term WHERE statistics_short_term.start >= %s AND statistics_short_term.start < %s) AS anon_1 WHERE anon_1.rownum = %s ORDER BY anon_1.metadata_id] [parameters: (datetime.datetime(2021, 10, 7, 8, 0, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 10, 7, 9, 0, tzinfo=datetime.timezone.utc), 1)] (Background on this error at: https://sqlalche.me/e/14/f405)
Error executing query: (MySQLdb._exceptions.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY statistics_short_term.metadata_id ORDER BY statistics_short_term.s' at line 2") [SQL: SELECT anon_1.metadata_id AS anon_1_metadata_id, anon_1.start AS anon_1_start, anon_1.last_reset AS anon_1_last_reset, anon_1.state AS anon_1_state, anon_1.sum AS anon_1_sum, anon_1.rownum AS anon_1_rownum FROM (SELECT statistics_short_term.metadata_id AS metadata_id, statistics_short_term.start AS start, statistics_short_term.last_reset AS last_reset, statistics_short_term.state AS state, statistics_short_term.sum AS sum, row_number() OVER (PARTITION BY statistics_short_term.metadata_id ORDER BY statistics_short_term.start DESC) AS rownum FROM statistics_short_term WHERE statistics_short_term.start >= %s AND statistics_short_term.start < %s) AS anon_1 WHERE anon_1.rownum = %s ORDER BY anon_1.metadata_id] [parameters: (datetime.datetime(2021, 10, 7, 9, 0, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 10, 7, 10, 0, tzinfo=datetime.timezone.utc), 1)] (Background on this error at: https://sqlalche.me/e/14/f405)
Error executing query: (MySQLdb._exceptions.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY statistics_short_term.metadata_id ORDER BY statistics_short_term.s' at line 2") [SQL: SELECT anon_1.metadata_id AS anon_1_metadata_id, anon_1.start AS anon_1_start, anon_1.last_reset AS anon_1_last_reset, anon_1.state AS anon_1_state, anon_1.sum AS anon_1_sum, anon_1.rownum AS anon_1_rownum FROM (SELECT statistics_short_term.metadata_id AS metadata_id, statistics_short_term.start AS start, statistics_short_term.last_reset AS last_reset, statistics_short_term.state AS state, statistics_short_term.sum AS sum, row_number() OVER (PARTITION BY statistics_short_term.metadata_id ORDER BY statistics_short_term.start DESC) AS rownum FROM statistics_short_term WHERE statistics_short_term.start >= %s AND statistics_short_term.start < %s) AS anon_1 WHERE anon_1.rownum = %s ORDER BY anon_1.metadata_id] [parameters: (datetime.datetime(2021, 10, 7, 10, 0, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 10, 7, 11, 0, tzinfo=datetime.timezone.utc), 1)] (Background on this error at: https://sqlalche.me/e/14/f405)

Logger: homeassistant.components.recorder.util
Source: components/recorder/util.py:72
Integration: Recorder (documentation, issues)
First occurred: 11:00:10 AM (3 occurrences)
Last logged: 1:00:11 PM

Error executing query: (MySQLdb._exceptions.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY statistics_short_term.metadata_id ORDER BY statistics_short_term.s' at line 2") [SQL: SELECT anon_1.metadata_id AS anon_1_metadata_id, anon_1.start AS anon_1_start, anon_1.last_reset AS anon_1_last_reset, anon_1.state AS anon_1_state, anon_1.sum AS anon_1_sum, anon_1.rownum AS anon_1_rownum FROM (SELECT statistics_short_term.metadata_id AS metadata_id, statistics_short_term.start AS start, statistics_short_term.last_reset AS last_reset, statistics_short_term.state AS state, statistics_short_term.sum AS sum, row_number() OVER (PARTITION BY statistics_short_term.metadata_id ORDER BY statistics_short_term.start DESC) AS rownum FROM statistics_short_term WHERE statistics_short_term.start >= %s AND statistics_short_term.start < %s) AS anon_1 WHERE anon_1.rownum = %s ORDER BY anon_1.metadata_id] [parameters: (datetime.datetime(2021, 10, 7, 8, 0, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 10, 7, 9, 0, tzinfo=datetime.timezone.utc), 1)] (Background on this error at: https://sqlalche.me/e/14/f405)
Error executing query: (MySQLdb._exceptions.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY statistics_short_term.metadata_id ORDER BY statistics_short_term.s' at line 2") [SQL: SELECT anon_1.metadata_id AS anon_1_metadata_id, anon_1.start AS anon_1_start, anon_1.last_reset AS anon_1_last_reset, anon_1.state AS anon_1_state, anon_1.sum AS anon_1_sum, anon_1.rownum AS anon_1_rownum FROM (SELECT statistics_short_term.metadata_id AS metadata_id, statistics_short_term.start AS start, statistics_short_term.last_reset AS last_reset, statistics_short_term.state AS state, statistics_short_term.sum AS sum, row_number() OVER (PARTITION BY statistics_short_term.metadata_id ORDER BY statistics_short_term.start DESC) AS rownum FROM statistics_short_term WHERE statistics_short_term.start >= %s AND statistics_short_term.start < %s) AS anon_1 WHERE anon_1.rownum = %s ORDER BY anon_1.metadata_id] [parameters: (datetime.datetime(2021, 10, 7, 9, 0, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 10, 7, 10, 0, tzinfo=datetime.timezone.utc), 1)] (Background on this error at: https://sqlalche.me/e/14/f405)
Error executing query: (MySQLdb._exceptions.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY statistics_short_term.metadata_id ORDER BY statistics_short_term.s' at line 2") [SQL: SELECT anon_1.metadata_id AS anon_1_metadata_id, anon_1.start AS anon_1_start, anon_1.last_reset AS anon_1_last_reset, anon_1.state AS anon_1_state, anon_1.sum AS anon_1_sum, anon_1.rownum AS anon_1_rownum FROM (SELECT statistics_short_term.metadata_id AS metadata_id, statistics_short_term.start AS start, statistics_short_term.last_reset AS last_reset, statistics_short_term.state AS state, statistics_short_term.sum AS sum, row_number() OVER (PARTITION BY statistics_short_term.metadata_id ORDER BY statistics_short_term.start DESC) AS rownum FROM statistics_short_term WHERE statistics_short_term.start >= %s AND statistics_short_term.start < %s) AS anon_1 WHERE anon_1.rownum = %s ORDER BY anon_1.metadata_id] [parameters: (datetime.datetime(2021, 10, 7, 10, 0, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 10, 7, 11, 0, tzinfo=datetime.timezone.utc), 1)] (Background on this error at: https://sqlalche.me/e/14/f405)

Logger: homeassistant.components.recorder
Source: components/recorder/util.py:116
Integration: Recorder (documentation, issues)
First occurred: 11:00:10 AM (3 occurrences)
Last logged: 1:00:11 PM

Unhandled database error while processing event StatisticsTask(start=datetime.datetime(2021, 10, 7, 8, 55, tzinfo=datetime.timezone.utc)): (MySQLdb._exceptions.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY statistics_short_term.metadata_id ORDER BY statistics_short_term.s' at line 2") [SQL: SELECT anon_1.metadata_id AS anon_1_metadata_id, anon_1.start AS anon_1_start, anon_1.last_reset AS anon_1_last_reset, anon_1.state AS anon_1_state, anon_1.sum AS anon_1_sum, anon_1.rownum AS anon_1_rownum FROM (SELECT statistics_short_term.metadata_id AS metadata_id, statistics_short_term.start AS start, statistics_short_term.last_reset AS last_reset, statistics_short_term.state AS state, statistics_short_term.sum AS sum, row_number() OVER (PARTITION BY statistics_short_term.metadata_id ORDER BY statistics_short_term.start DESC) AS rownum FROM statistics_short_term WHERE statistics_short_term.start >= %s AND statistics_short_term.start < %s) AS anon_1 WHERE anon_1.rownum = %s ORDER BY anon_1.metadata_id] [parameters: (datetime.datetime(2021, 10, 7, 8, 0, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 10, 7, 9, 0, tzinfo=datetime.timezone.utc), 1)] (Background on this error at: https://sqlalche.me/e/14/f405)
Unhandled database error while processing event StatisticsTask(start=datetime.datetime(2021, 10, 7, 9, 55, tzinfo=datetime.timezone.utc)): (MySQLdb._exceptions.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY statistics_short_term.metadata_id ORDER BY statistics_short_term.s' at line 2") [SQL: SELECT anon_1.metadata_id AS anon_1_metadata_id, anon_1.start AS anon_1_start, anon_1.last_reset AS anon_1_last_reset, anon_1.state AS anon_1_state, anon_1.sum AS anon_1_sum, anon_1.rownum AS anon_1_rownum FROM (SELECT statistics_short_term.metadata_id AS metadata_id, statistics_short_term.start AS start, statistics_short_term.last_reset AS last_reset, statistics_short_term.state AS state, statistics_short_term.sum AS sum, row_number() OVER (PARTITION BY statistics_short_term.metadata_id ORDER BY statistics_short_term.start DESC) AS rownum FROM statistics_short_term WHERE statistics_short_term.start >= %s AND statistics_short_term.start < %s) AS anon_1 WHERE anon_1.rownum = %s ORDER BY anon_1.metadata_id] [parameters: (datetime.datetime(2021, 10, 7, 9, 0, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 10, 7, 10, 0, tzinfo=datetime.timezone.utc), 1)] (Background on this error at: https://sqlalche.me/e/14/f405)
Unhandled database error while processing event StatisticsTask(start=datetime.datetime(2021, 10, 7, 10, 55, tzinfo=datetime.timezone.utc)): (MySQLdb._exceptions.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY statistics_short_term.metadata_id ORDER BY statistics_short_term.s' at line 2") [SQL: SELECT anon_1.metadata_id AS anon_1_metadata_id, anon_1.start AS anon_1_start, anon_1.last_reset AS anon_1_last_reset, anon_1.state AS anon_1_state, anon_1.sum AS anon_1_sum, anon_1.rownum AS anon_1_rownum FROM (SELECT statistics_short_term.metadata_id AS metadata_id, statistics_short_term.start AS start, statistics_short_term.last_reset AS last_reset, statistics_short_term.state AS state, statistics_short_term.sum AS sum, row_number() OVER (PARTITION BY statistics_short_term.metadata_id ORDER BY statistics_short_term.start DESC) AS rownum FROM statistics_short_term WHERE statistics_short_term.start >= %s AND statistics_short_term.start < %s) AS anon_1 WHERE anon_1.rownum = %s ORDER BY anon_1.metadata_id] [parameters: (datetime.datetime(2021, 10, 7, 10, 0, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 10, 7, 11, 0, tzinfo=datetime.timezone.utc), 1)] (Background on this error at: https://sqlalche.me/e/14/f405)
Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1771, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 717, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.9/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.9/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/usr/local/lib/python3.9/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY statistics_short_term.metadata_id ORDER BY statistics_short_term.s' at line 2")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  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 357, in compile_statistics
    compile_hourly_statistics(instance, session, start)
  File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 288, in compile_hourly_statistics
    stats = execute(query.params(start_time=start_time, end_time=end_time))
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 116, in execute
    result = list(qry)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 2839, in __iter__
    return self._iter().__iter__()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 2846, in _iter
    result = self.session.execute(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1689, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1583, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1452, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1814, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1995, in _handle_dbapi_exception
    util.raise_(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1771, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 717, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.9/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.9/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/usr/local/lib/python3.9/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)
sqlalchemy.exc.ProgrammingError: (MySQLdb._exceptions.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY statistics_short_term.metadata_id ORDER BY statistics_short_term.s' at line 2")
[SQL: SELECT anon_1.metadata_id AS anon_1_metadata_id, anon_1.start AS anon_1_start, anon_1.last_reset AS anon_1_last_reset, anon_1.state AS anon_1_state, anon_1.sum AS anon_1_sum, anon_1.rownum AS anon_1_rownum 
FROM (SELECT statistics_short_term.metadata_id AS metadata_id, statistics_short_term.start AS start, statistics_short_term.last_reset AS last_reset, statistics_short_term.state AS state, statistics_short_term.sum AS sum, row_number() OVER (PARTITION BY statistics_short_term.metadata_id ORDER BY statistics_short_term.start DESC) AS rownum 
FROM statistics_short_term 
WHERE statistics_short_term.start >= %s AND statistics_short_term.start < %s) AS anon_1 
WHERE anon_1.rownum = %s ORDER BY anon_1.metadata_id]
[parameters: (datetime.datetime(2021, 10, 7, 8, 0, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 10, 7, 9, 0, tzinfo=datetime.timezone.utc), 1)]
(Background on this error at: https://sqlalche.me/e/14/f405)

CAP-Team · October 7, 2021, 5:50pm

Same problem here

Remko · October 7, 2021, 5:59pm

Ok. So at least I am not alone…

What kind of SQL server are you using? I mean in what hardware have you got it running etc.

allocater2 · October 7, 2021, 7:58pm

Seems the update messed with time zones. Automation now tells me it’s executed now at 19:57.

It is 21:57 now.

In developer tool template we can all insert

{{ utcnow().tzinfo }}
{{ now().tzinfo }}
{{ now().astimezone().tzinfo }}

and see what it says. For me it’s:

UTC
Europe/Berlin
CEST

Remko · October 7, 2021, 8:17pm

UTC
Europe/Berlin
CEST.

allocater2 · October 7, 2021, 8:21pm

Yeah, seems correct, but all graphs also show UTC now at the end

Remko · October 8, 2021, 8:33am

I also found an interesting message in my logfile about one of my cameras:

The date/time on the device (UTC) is '2021-10-08 08:28:54+00:00', which is different from the system '2021-10-08 08:28:44.624512+00:00', this could lead to authentication issues

As you can see the system time is in a very strange format. Apparently there is something wrong in the Homeassistent Core… I have no clue on where to start fixing this.

allocater2 · October 8, 2021, 10:07am

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.

Remko · October 8, 2021, 10:16am

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.

Remko · October 8, 2021, 3:13pm

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

Naesstrom · October 9, 2021, 7:24am

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!

CAP-Team · October 9, 2021, 9:21am

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

koying · October 9, 2021, 9:52am

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).

Naesstrom · October 9, 2021, 4:12pm

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

evb · October 9, 2021, 4:55pm

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

CAP-Team · October 9, 2021, 9:37pm

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.

evb · October 10, 2021, 8:50am

@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

CAP-Team · October 10, 2021, 10:18am

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 )

crackers8199 · October 11, 2021, 5:44pm

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?

splinter · October 12, 2021, 8:50am

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?