Update from 7.4 to 8.1 , DB can't migrate

Kathou99 · August 12, 2024, 4:06pm

Hi,

I have a database with MariaDB, the size is around 51G.

I updated 8h ago and this morning the message the Database migration is in progress has change to can’t migrate the DB so I look on the logs and I see this:

Enregistreur: homeassistant.components.recorder.core
Source: components/recorder/core.py:988
intégration: Recorder (documentation, problèmes)
S'est produit pour la première fois: 11:14:22 (1 occurrences)
Dernier enregistrement: 11:14:22

Error during schema migration
Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 988, in _migrate_schema
    new_schema_status = migrator(
                        ^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 335, in migrate_schema_live
    schema_status = _migrate_schema(
                    ^^^^^^^^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 301, in _migrate_schema
    _apply_update(instance, hass, engine, session_maker, new_version, start_version)
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 703, in wrapper
    job(instance, *args, **kwargs)
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 712, in _apply_update
    migrator_cls(instance, hass, engine, session_maker, old_version).apply_update()
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 758, in apply_update
    self._apply_update()
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 1492, in _apply_update
    _modify_columns(
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 559, in _modify_columns
    with session_scope(session=session_maker()) as session:
  File "/usr/local/lib/python3.12/contextlib.py", line 144, in __exit__
    next(self.gen)
  File "/usr/src/homeassistant/homeassistant/helpers/recorder.py", line 104, in session_scope
    session.commit()
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2017, in commit
    trans.commit(_to_root=True)
  File "<string>", line 2, in commit
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/state_changes.py", line 139, in _go
    ret_value = fn(self, *arg, **kw)
                ^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 1309, in commit
    trans.commit()
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2629, in commit
    self._do_commit()
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2734, in _do_commit
    self._connection_commit_impl()
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2705, in _connection_commit_impl
    self.connection._commit_impl()
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1146, in _commit_impl
    self._handle_dbapi_exception(e, None, None, None, None)
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2356, in _handle_dbapi_exception
    raise exc_info[1].with_traceback(exc_info[2])
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1144, in _commit_impl
    self.engine.dialect.do_commit(self.connection)
                                  ^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 585, in connection
    return self._revalidate_connection()
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 677, in _revalidate_connection
    self._invalid_transaction()
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 667, in _invalid_transaction
    raise exc.PendingRollbackError(
sqlalchemy.exc.PendingRollbackError: Can't reconnect until invalid transaction is rolled back.  Please rollback() fully before proceeding (Background on this error at: https://sqlalche.me/e/20/8s2b)

so I restart HA and the message is back at:

Database upgrade in progress

System performance will temporarily degrade during the database upgrade. Do not power down or restart the system until the upgrade completes. Integrations that read the database, such as logbook, history, and statistics may return inconsistent results until the upgrade completes. This notification will be automatically dismissed when the upgrade completes.

I will see in +/- 15h if I get still the issue, did I am the only on has got this issue with the migration?

Thank

Kathou99 · August 12, 2024, 4:17pm

tom_l · August 13, 2024, 12:20am

Why?

What are you keeping all that data for?

Ozzoman30 · August 13, 2024, 2:28am

did you solve it? I have the problem

Registrador: homeassistant.components.recorder.core
Fuente: components/recorder/core.py:988
integración: Recorder (documentación, problemas)
Ocurrió por primera vez: 8:50:04 a. m. (1 ocurrencias)
Último inicio de sesión: 8:50:04 a. m.

Error during schema migration
Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 988, in _migrate_schema
    new_schema_status = migrator(
                        ^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 335, in migrate_schema_live
    schema_status = _migrate_schema(
                    ^^^^^^^^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 301, in _migrate_schema
    _apply_update(instance, hass, engine, session_maker, new_version, start_version)
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 703, in wrapper
    job(instance, *args, **kwargs)
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 712, in _apply_update
    migrator_cls(instance, hass, engine, session_maker, old_version).apply_update()
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 758, in apply_update
    self._apply_update()
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 1515, in _apply_update
    _modify_columns(
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 559, in _modify_columns
    with session_scope(session=session_maker()) as session:
  File "/usr/local/lib/python3.12/contextlib.py", line 144, in __exit__
    next(self.gen)
  File "/usr/src/homeassistant/homeassistant/helpers/recorder.py", line 104, in session_scope
    session.commit()
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2017, in commit
    trans.commit(_to_root=True)
  File "<string>", line 2, in commit
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/state_changes.py", line 139, in _go
    ret_value = fn(self, *arg, **kw)
                ^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 1309, in commit
    trans.commit()
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2629, in commit
    self._do_commit()
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2734, in _do_commit
    self._connection_commit_impl()
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2705, in _connection_commit_impl
    self.connection._commit_impl()
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1146, in _commit_impl
    self._handle_dbapi_exception(e, None, None, None, None)
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2356, in _handle_dbapi_exception
    raise exc_info[1].with_traceback(exc_info[2])
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1144, in _commit_impl
    self.engine.dialect.do_commit(self.connection)
                                  ^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 585, in connection
    return self._revalidate_connection()
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 677, in _revalidate_connection
    self._invalid_transaction()
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 667, in _invalid_transaction
    raise exc.PendingRollbackError(
sqlalchemy.exc.PendingRollbackError: Can't reconnect until invalid transaction is rolled back.  Please rollback() fully before proceeding (Background on this error at: https://sqlalche.me/e/20/8s2b)

alexclaret · August 13, 2024, 6:40pm

me too

Kathou99 · August 13, 2024, 7:26pm

I keep it for historical data and reference, it’s been like 24 and I have 12% done aha yeah I know is a lot but I can handle this so I use it

Kathou99 · August 13, 2024, 7:26pm

after 24h I am at 12% so I guess I will wait 1 week or two in my case aha

dar3k · August 13, 2024, 8:07pm

How did you check the progress of it?

alexclaret · August 13, 2024, 8:18pm

i downgraded HA (7.4) and MariaDB start working again

tom_l · August 13, 2024, 9:54pm

Use a time series DB like influx for analysing that sort of data. Or give your sensors a state_class so they collect downsampled long term statistics forever.

I keep 7 days of state data in MariaDB with a lot of LTS. The DB is ~1.5GB

I keep 2 years of state data in InfluxDB for select entities, it is ~4GB. And grafana is way better for analysing the data.

Sir_Goodenough · August 17, 2024, 1:49am

I’m sorry people, but this data, at least beyond a year, is basically useless to you beyond a trophy and a pretty graph. It bog’s your system down, if you are not using the default DB it runs slow anyway, not including the fact that you have 49gb in there of stagnant data.
Yes, this is a personal opinion, but that amount of data in a live DB being run by Ras-Pi’s and small home come computers will make the performance of you your actual home Assistant functionality suffer greatly. IE a week to change 1 field of the database from 32int to 64int.

I suggest make your life easier, just delete it, switch back to the default sqlite, and do what Tom suggests above going forward so that the pile of data you won’t really use anyway won’t be messing with the stuff HA is for…

lirtm · August 19, 2024, 8:50am

I think everyone does what they need to do. How can you know what other people’s needs are? HA doesn’t warn you not to use large databases, even external ones. And it’s very strange that they didn’t manually rebuild the database to avoid doing it through HA, because the rebuild process is so freaking sub-optimal.

heffneil · November 27, 2024, 2:27pm

I think the sifting and the amount of data becomes cumbersome is what @Sir_Goodenough is getting at. I like @tom_l suggestion of influx and grafana. His point is stop using the screwdriver as a hammer and I am going to heed that advice and look to offload data to influx. In the meantime my migration has failed 2x and I don’t know what to do about other than dump the DB and start over (another point of what @Sir_Goodenough is saying) its so big its corrupt and I have to start over.

lirtm · December 3, 2024, 6:36pm

By the way, in between. I solved the problem back in August by increasing the allocated memory space for MariaDB. It took about 5 hours. Tracked everything in the console.

PeteRage · December 3, 2024, 6:51pm

I setup MariaDB on my laptop, allocated it 16gb of RAM (laptop has 32), copied the MariaDB over to laptop ran HA on the laptop to update the database. Once that was done I shutdown HA on production server, used dbeaver to merge missing records (which was 24 hours of data), then moved the upgraded DB back and upgraded HA. You could skip the merging if you are ok with some data loss.

heffneil · December 3, 2024, 7:14pm

Gosh im still down. I disconnected the database from HA and started a new one but the actually maria DB is still broke I guess? I thought for some reason it warehoused things off to it… its just easier to start over at this point.