Database error after upgrade 2023.2

At3 · February 10, 2023, 12:11pm

I have updated my HA to 2023.2 but after the upgrade HA does a database migration which fails all the time. It’s a seperate MariaDB on my Synology where HA writes to. If I restart HA it does a migration again and after around 4 hours it fails again.

I did get something out of the logs but I can’t make sense out of it and I dont want to lose my DB which is 50GB in size. Don’t care about state history but I don’t want to lose my energy history

Logger: homeassistant.components.recorder.core
Source: components/recorder/migration.py:202
Integration: Recorder (documentation, issues)
First occurred: 11:19:39 AM (1 occurrences)
Last logged: 11:19:39 AM

Database error during schema migration
Traceback (most recent call last):
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.10/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.10/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/usr/local/lib/python3.10/site-packages/MySQLdb/connections.py", line 254, in query
    _mysql.connection.query(self, query)
MySQLdb.OperationalError: (1878, 'Temporary file write failure')

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

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 742, in _migrate_schema_and_setup_run
    migration.migrate_schema(
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 154, in migrate_schema
    _apply_update(hass, engine, session_maker, new_version, current_version)
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 854, in _apply_update
    _create_index(session_maker, "states", "ix_states_entity_id_last_updated_ts")
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 204, in _create_index
    raise_if_exception_missing_str(err, ["already exists", "duplicate"])
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 65, in raise_if_exception_missing_str
    raise ex
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 202, in _create_index
    index.create(connection)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/schema.py", line 4353, in create
    bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2211, in _run_ddl_visitor
    visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/visitors.py", line 524, in traverse_single
    return meth(obj, **kw)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/ddl.py", line 938, in visit_index
    self.connection.execute(CreateIndex(index))
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/future/engine.py", line 280, in execute
    return self._execute_20(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/ddl.py", line 80, in _execute_on_connection
    return connection._execute_ddl(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1472, in _execute_ddl
    ret = self._execute_context(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2124, in _handle_dbapi_exception
    util.raise_(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.10/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.10/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/usr/local/lib/python3.10/site-packages/MySQLdb/connections.py", line 254, in query
    _mysql.connection.query(self, query)
sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1878, 'Temporary file write failure')
[SQL: CREATE INDEX ix_states_entity_id_last_updated_ts ON states (entity_id, last_updated_ts)]
(Background on this error at: https://sqlalche.me/e/14/e3q8)

Logger: homeassistant.components.recorder.util
Source: components/recorder/util.py:125
Integration: Recorder (documentation, issues)
First occurred: 5:45:31 PM (1 occurrences)
Last logged: 5:45:31 PM

Error executing query: (MySQLdb.OperationalError) (1878, 'Temporary file write failure') [SQL: CREATE INDEX ix_states_entity_id_last_updated_ts ON states (entity_id, last_updated_ts)] (Background on this error at: https://sqlalche.me/e/14/e3q8)

jorcat · February 13, 2023, 9:36am

I’ve the same configuration, an external MariaDB on Synology NAS.
How can I restart DB ?

(I don’t want to save any previous data)

jorcat · February 13, 2023, 3:27pm

I see a message in HA:

But unfortunately MariaDB on Synology (NAS DS115) has only 10.3.32
(see Release Notes for MariaDB 10 | Synology Inc. )

tsl · February 22, 2023, 2:16pm

So this morning I noticed the same error on my system, which is on a Raspberry. As the error message points out, the system is not able to write the temporary files. In my case, I use a temporary partition (tmpfs) which was set to 1Gb, so I thought that it would have been out of space. After increasing the available space and rebooted HA, the migration worked without any issue.

So, can you check about providing more space to the location of the temporary folder (or just use another folder on another disk/location with more space available)?

BrunoSpy · March 13, 2023, 4:42pm

Hello
I have the same issue (MariaDB 10.3 on a Synology, unable to install 10.5).
There’s plenty of space available (750GB) and as far as I know, there’s no quota applied to MariaDB.
I rolled back to HA 2023.1 but I’m looking for a solution (not involving migrating the DB elsewhere as I do not have this opportunity and even the latest Synology is stuck with MariaDB 10.3).
Thanks

At3 · March 13, 2023, 6:02pm

MariaDB doesn’t save the temp files to your volume1 where you have 750gb. It saves it to a temp folder with way less. You can edit the temp folder of MariaDB to save it on you volume1 which can fix this. But my DB ran so large that even that was not an option. So I just removed the states since I don’t need it and only need energy.

PeteRage · March 13, 2023, 6:04pm

I’m on Synology also. After a bit of trial and error I was able to setup a newer version of MariaDB in Docker and copy the existing data over. If that’s something you want to do I can provide more details.

tsl · March 14, 2023, 12:55pm

The thing you want to check is your RAM memory usage, as it might get full during the migration, especially if we are talking about a DB a few GB big.

However, you can increase the swap space on the relevant machine if that’s the case. I think it’s another worthwhile check.

PeteRage · March 14, 2023, 5:14pm

The problem is that version of MariaDB is no longer supported by HASS and hence it logs an error saying this and fails to connect. The only solve is to install a newer version of MariaDB as a docker container.