Recorder error using MySQL in a remote server

jd1900 · October 6, 2021, 6:50pm

I’m having this error in the logs.

I’m right now with the recorder pointing to an external MySQL server, as in my RPi it “kills” the IO output.

Any idea about how to fix it?
Is it a bug?

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, 6, 17, 0, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 10, 6, 18, 0, tzinfo=datetime.timezone.utc), 1)]
(Background on this error at: https://sqlalche.me/e/14/f405)
2021-10-06 20:23:58 ERROR (Recorder) [homeassistant.components.recorder.util] 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, 6, 17, 0, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 10, 6, 18, 0, tzinfo=datetime.timezone.utc), 1)]
(Background on this error at: https://sqlalche.me/e/14/f405)
2021-10-06 20:23:58 ERROR (Recorder) [homeassistant.components.recorder.util] 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, 6, 17, 0, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 10, 6, 18, 0, tzinfo=datetime.timezone.utc), 1)]
(Background on this error at: https://sqlalche.me/e/14/f405)
2021-10-06 20:23:59 ERROR (Recorder) [homeassistant.components.recorder] Unhandled database error while processing event StatisticsTask(start=datetime.datetime(2021, 10, 6, 17, 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, 6, 17, 0, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 10, 6, 18, 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, 6, 17, 0, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 10, 6, 18, 0, tzinfo=datetime.timezone.utc), 1)]
(Background on this error at: https://sqlalche.me/e/14/f405)

Another log a few minutes after:

2021-10-06 21:15:55 ERROR (Recorder) [homeassistant.components.recorder.util] 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, 6, 18, 0, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 10, 6, 19, 0, tzinfo=datetime.timezone.utc), 1)]
(Background on this error at: https://sqlalche.me/e/14/f405)
2021-10-06 21:15:56 ERROR (Recorder) [homeassistant.components.recorder.util] 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, 6, 18, 0, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 10, 6, 19, 0, tzinfo=datetime.timezone.utc), 1)]
(Background on this error at: https://sqlalche.me/e/14/f405)
2021-10-06 21:15:56 ERROR (Recorder) [homeassistant.components.recorder.util] 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, 6, 18, 0, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 10, 6, 19, 0, tzinfo=datetime.timezone.utc), 1)]
(Background on this error at: https://sqlalche.me/e/14/f405)
2021-10-06 21:15:56 ERROR (Recorder) [homeassistant.components.recorder] Unhandled database error while processing event StatisticsTask(start=datetime.datetime(2021, 10, 6, 18, 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, 6, 18, 0, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 10, 6, 19, 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, 6, 18, 0, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 10, 6, 19, 0, tzinfo=datetime.timezone.utc), 1)]
(Background on this error at: https://sqlalche.me/e/14/f405)

evb · October 8, 2021, 6:34pm

@jd1900 I have the same error since the update to 2021.10.0.
The energy dashboard is also not functioning since the update.
See also Energy Dashboard SQL problem after 2021.10 upgrade

Remko · October 8, 2021, 7:05pm

The underlying problem is the MySQL server version. It needs to be at least 5.8.
My Drobo NAS only has 5.6, so now back to db on the SD Card. More starting to look too add an SSD too my RPi4 and run HASS on there.

evb · October 8, 2021, 8:17pm

@Remko I’m using MariaDb 10.1.48 on Ubuntu.
I did the update 2021.10.1, but still the same error

2021-10-08 22:00:10 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: (MySQLdb._exceptions.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the righ$
[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$
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, 8, 19, 0, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 10, 8, 20, 0, tzinfo=datetime.timezone.utc), 1)]
(Background on this error at: https://sqlalche.me/e/14/f405)

evb · October 8, 2021, 8:50pm

In the query we see the function call row_number()

ROW_NUMBER
MariaDB starting with 10.2
ROW_NUMBER() was first introduced with window functions in MariaDB 10.2.0.

My MariaDB version is also too old…
Will try to update, to be continued

evb · October 9, 2021, 7:23am

I upgrade my MariaDb database from 10.1.48 to 10.6 and the problem is gone!
My dashboard is working again and no sql error anymore!

@frenck, should this not be mentioned as breaking change in the release notes?

People interested how to update MariaDB on Ubuntu 18.04 LTS (Bionic)
All credit goes to following internet articles:

$ sudo mysql -u root -p
mysql> SET GLOBAL innodb_fast_shutdown = 0;
exit
$ sudo mysqldump -u root -p homeassistant > /home/eric/backup_homeassistant.sql
$ sudo systemctl stop mariadb.service
$ sudo apt remove mariadb-server
$ sudo apt-get install software-properties-common
$ sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
$ sudo apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'
$ sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el] https://mariadb.mirror.liquidtelecom.com/repo/10.6/ubuntu bionic main'
$ sudo apt update
$ sudo apt install mariadb-server mariadb-client
$ sudo mysql_secure_installation 
$ sudo systemctl enable mariadb

jd1900 · October 9, 2021, 5:48pm

The server has MySQL 8, so that shouldn’t be the problem.

Remko · October 9, 2021, 6:30pm

No, the server on my Drobo is 5.6. I think exactly that is the problem. I do not know if and how I can upgrade that.