Home Assistant updates and older mysql databases

Tags: #<Tag:0x00007fc410c976c0> #<Tag:0x00007fc410c97170>

Well, Well,

I use a MySql database stored on my Netgear Readynas, the version of mysql on this device is quite old and cannot be updated.
I have a lot of problems for the last few versions during HA updates, each time an update tries to operate modification on the database, the update fail because the sql uses datetime(6) and my system only support datetime.
Then I read the logs and execute the sql myself by simply replacing datetime(6) by datetime.
Until now I was able to handle it, but with the last update, it tries to create a table called statitstics, it crashes, Then I create the table myself, but each time I restart ha, it deletes the table statistics and try to recreate it. So it crashes.

Is there a file I can modify on the raspberry pi to modify directly the sql to be executed in the update ?

Or any other solution would be appreciated

Here is the log file:

Logger: homeassistant.components.recorder
Source: components/recorder/init.py:937
Integration: recorder (documentation, issues)
First occurred: 3:55:13 PM (10 occurrences)
Last logged: 3:55:41 PM

Error during connection setup to mysql://hassistant:[email protected]/homeass?charset=utf8: (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 ‘(6), \n\tsource VARCHAR(32), \n\tstatistic_id VARCHAR(255), \n\tstart DATETIME(6), \n\tm’ at line 3”) [SQL: CREATE TABLE statistics ( id INTEGER NOT NULL AUTO_INCREMENT, created DATETIME(6), source VARCHAR(32), statistic_id VARCHAR(255), start DATETIME(6), mean FLOAT, min FLOAT, max FLOAT, last_reset DATETIME(6), state FLOAT, sum FLOAT, PRIMARY KEY (id) ) ] (Background on this error at: http://sqlalche.me/e/14/f405) (retrying in 3 seconds)
Traceback (most recent call last):
File “/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py”, line 1763, in _execute_context
self.dialect.do_execute(
File “/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py”, line 717, in do_execute
cursor.execute(statement, parameters)
File “/usr/local/lib/python3.8/site-packages/MySQLdb/cursors.py”, line 206, in execute
res = self._query(query)
File “/usr/local/lib/python3.8/site-packages/MySQLdb/cursors.py”, line 319, in _query
db.query(q)
File “/usr/local/lib/python3.8/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 ‘(6), \n\tsource VARCHAR(32), \n\tstatistic_id VARCHAR(255), \n\tstart DATETIME(6), \n\tm’ at line 3”)

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 653, in _setup_recorder
self._setup_connection()
File “/usr/src/homeassistant/homeassistant/components/recorder/init.py”, line 937, in _setup_connection
Base.metadata.create_all(self.engine)
File “/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/schema.py”, line 4744, in create_all
bind._run_ddl_visitor(
File “/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py”, line 3075, in _run_ddl_visitor
conn._run_ddl_visitor(visitorcallable, element, **kwargs)
File “/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py”, line 2074, in _run_ddl_visitor
visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
File “/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py”, line 483, in traverse_single
return meth(obj, **kw)
File “/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/ddl.py”, line 846, in visit_metadata
self.traverse_single(
File “/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py”, line 483, in traverse_single
return meth(obj, **kw)
File “/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/ddl.py”, line 890, in visit_table
self.connection.execute(
File “/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py”, line 1258, in execute
return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
File “/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/ddl.py”, line 77, in _execute_on_connection
return connection._execute_ddl(
File “/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py”, line 1348, in _execute_ddl
ret = self._execute_context(
File “/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py”, line 1806, in _execute_context
self.handle_dbapi_exception(
File “/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py”, line 1987, in handle_dbapi_exception
util.raise
(
File “/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py”, line 211, in raise

raise exception
File “/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py”, line 1763, in _execute_context
self.dialect.do_execute(
File “/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py”, line 717, in do_execute
cursor.execute(statement, parameters)
File “/usr/local/lib/python3.8/site-packages/MySQLdb/cursors.py”, line 206, in execute
res = self._query(query)
File “/usr/local/lib/python3.8/site-packages/MySQLdb/cursors.py”, line 319, in _query
db.query(q)
File “/usr/local/lib/python3.8/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 ‘(6), \n\tsource VARCHAR(32), \n\tstatistic_id VARCHAR(255), \n\tstart DATETIME(6), \n\tm’ at line 3”)
[SQL:
CREATE TABLE statistics (
id INTEGER NOT NULL AUTO_INCREMENT,
created DATETIME(6),
source VARCHAR(32),
statistic_id VARCHAR(255),
start DATETIME(6),
mean FLOAT,
min FLOAT,
max FLOAT,
last_reset DATETIME(6),
state FLOAT,
sum FLOAT,
PRIMARY KEY (id)
)

]
(Background on this error at: http://sqlalche.me/e/14/f405)