Database upgrade in progress since 6h after update core_2024.8.0

Configuration
22

I can’t get this update to finish.

homeassistant Query 532 copy to tmp table 2.938 ALTER TABLE states MODIFY event_id BIGINT, MODIFY old_state_id BIGINT, MODIFY attributes_id BIGINT, MODIFY metadata_id BIGINT

This runs for ages until like 53% (which takes like 20h).
Then it fails with the Error: “MySQLServer has gone away” and HA is pretty much dead. No logging, lots of errors.
After Restart it tries to do the Database update again, which will fail after 12 … 20h.

So I’m stuck in this loop since around 5 days.

Right now it’s telling me that it did 15,267% in 3234 secs. So 100% will be reached at 21182 secs (5,88h) - tell me an other joke …

And now? This update really sucks…

Update a few hours later:

time ________________ percent
| 3234 |copy to tmp table |15.267|
|18168 |copy to tmp table |39.549|
|19162 |copy to tmp table |40.926|
|19431 |copy to tmp table |41.299|
|21541 |copy to tmp table |44.049|
|25083 |copy to tmp table |48.310|
|26806 |copy to tmp table |50.193|

If i’m right, it will crash between 53 and 58% …

1 Like
23

hi!
i have the same problem.
are you still waiting?

24

Maybe I have the same problem,

25

Crashed again … Downgraded via Backup to prior 2024.8.0 and now cleaning the database via the development tools.

This may take days, but what do you do to escape the crash-cycle…

This update sucks…

26

For my database (MariaDB), the update always seems to stop around 2:30 a.m. Therefore the update stops and restarts. Can the update be canceled?

27

how do i check if it’s completed?

i saw the update message and my HA crashed with my proxmox n now i do not know if the database is updated or not.

tried to do a back up restore but it keeps crashing. my version shows 2024.8

28

I check on my PhpMyAdmin Db, he has stopped again at 15%,look like HA has a limit of time do doing this

29

I have the same problem. Db is 51GB, took days till it finally failed. I’m going back to 2024.7.4. What did you do to clean your database if you don’t mind me asking?

30

My database is 62MB in the morning and almost 70MB before I shrink it again.
Don’t understand how you can have a database from 51GB :roll_eyes:

31

Our environments are most likely very different. I’m running 30 particle sensors (Sensirion SPS30) that updates every 5 second together with ~50 climate sensors. They all write to a mariaDB database. Any help on how to keep the database size down is appreciated.

32

Did you read the link I posted?
Is it necessary to hold all that data from all your sensors for more than X hours/days?

33

Started the update at 23:07 (2.1gb database on a 16gb Intel NUC with a core i3). 8 hours later logbook isn’t working, home assistant cannot be restarted (giving warning about database upgrade in progress) and MariaDB log shows the below. What could be the culprit?

2024-08-16 23:10:08 19 [Warning] Aborted connection 19 to db: ‘homeassistant’ user: ‘homeassistant’ host: ‘172.30.32.1’ (Got an error reading communication packets)
2024-08-16 23:10:08 17 [Warning] Aborted connection 17 to db: ‘homeassistant’ user: ‘homeassistant’ host: ‘172.30.32.1’ (Got an error reading communication packets)
2024-08-16 23:10:08 18 [Warning] Aborted connection 18 to db: ‘homeassistant’ user: ‘homeassistant’ host: ‘172.30.32.1’ (Got an error reading communication packets)
2024-08-16 23:10:08 22 [Warning] Aborted connection 22 to db: ‘homeassistant’ user: ‘homeassistant’ host: ‘172.30.32.1’ (Got an error reading communication packets)

EDIT: might be noteworthy to say I upgraded from 2024.7.4 directly to 2024.8.2

34

How much memory is allocated to MariaDB? Another way to answer this is how much memory is it consuming.

35

Am 99% sure it is 1 gigabyte. Am fully sure it is the default amount (as changing that is quite hard)

36

Rolled back a backup, did a full purge of my DB (last time I kept 7 days of history resulting in 2.2Gb, this time did 0 days which resulted in about 740Mb). This time the upgrade went as expected.

37

Yes, it’s very funny.
I have a database running on MariaDB, external. Weight ~25 GB. Yes, I store several years of values. There is space, why not? So - 9 hours it is unclear what was happening, there is no progress bar, I rebooted. Everything works fine. But I looked at the Proxmox summary - these 9 hours are constant reading and writing to the disc (which is SSD), and network activity. And no values are recorded. Super. I would like to know from the developers, or from the wonderful (honestly!) community a manual way to update the database via Adminer. That would be super!

1 Like
38

hello.
My database is more than 50gb ( a lot of sensor, and consumption sensors) with a lot of exceptions (without exceptions was more than 80gb of database)

With the update to 2024.8 i have some problems, but solved increasing the innodb_buffer_pool_size to 2GB.

but with the update to 2024.8.2 its imposible to finish.
Takes one day and crash.
I try 3 trimes and allways crash because a timeout.

I open a issue:
Database migration errors in 2024.8.2 · Issue #124186 · home-assistant/core (github.com)

2 Likes
39

after third attempt the database finish with the migration.
I do nothing.
only reboot when a errors of migration, and start again.

and in this third attempt y after 36 hours a message of migration done, apears.

mi database have more than 40gb

40

Hi, I have the same problem my database is over 40gb. How did you increased the innodb_buffer_pool_size to 2gb?

41

MariaDB Enterprise Server Configure the InnoDB Buffer Pool — MariaDB Documentation

I use this query:
SET GLOBAL innodb_buffer_pool_size=(2 * 1024 * 1024 * 1024);

but after a reboot, the innodb_buffer_pool backs to the original value.
Only works as temporaly solution. but works in my case