Upgrade to 2023.2.0 - Database upgrade never finishes

Installation
1

I’m running HA Core 2023.2 and mysql 8.0.32 on Ubuntu 18.04LTS.

I upgraded to 2023.2.0 and saw the notification message, “Database upgrade in progress” and thought nothing of it. On Feb. 2nd and Feb 4th when the updates came out, I upgraded and noticed that the same notification came up.

It’s been a few days now and I still see HA and mysql using a great deal of cpu time on my server.

image
image2090×104 60.5 KB

Does anyone know if there is a way to check if this upgrade is stuck or progressing? I’ve restarted my HA instance a few times now. Do y’all think that the db upgrade could be completely messed up?

Suggestions on what I can do besides dumping the database and starting with a fresh on?

2

I think there were a breaking change.
Maybe it was listed under mariadb though

3

The same happens here.
my database is huge:
image

but in this migration state, the recorder is not working… and this is bad

4

125G… and I thought my table was large (28G) …Anyway the upgrade took ages to run (I let it go over night) but it did finish. I queried the db for active queries to keep an eye on it. My db though is PostgreSQL.

5

Since I was running mysql and not MariaDB, I ignored that breaking change.

It should have been simple to switch to mariadb from mysql… Turns out there were issues, but eventually got mariadb server 10.10 running…

Same issue with MariaDB as I had with mySQL

image
image2126×108 61.1 KB

6

I ran watch "mysql -e 'SHOW FULL PROCESSLIST\G'" I see output like the below. Is it normal to see: Info: ROLLBACK

**************************************************** - first update - ***********************************************************************

      db: ha
 Command: Sleep
    Time: 0
   State: 
    Info: NULL
Progress: 0.000

******************************************** - next update after 2 seconds - ****************************************************************

      db: ha
 Command: Query
    Time: 0
   State: Searching rows for update
    Info: UPDATE events set time_fired_ts=IF(time_fired is NULL,0,UNIX_TIMESTAMP(CONVERT_TZ(time_fired,'+00:00',@@global.time_zone))) where time_fired_ts is NULL LIMIT 250000
Progress: 0.000

******************************************** - next update after 2 seconds - ****************************************************************

      db: ha
 Command: Query
    Time: 0
   State: Writing to net
    Info: ROLLBACK
Progress: 0.000

******************************************** - next update after 2 seconds - ****************************************************************
   
         db: ha
 Command: Query
    Time: 0
   State: starting
    Info: COMMIT
Progress: 0.000
  
******************************************** - next update after 2 seconds - ****************************************************************
  
        db: ha
 Command: Query
    Time: 0
   State: Updating
    Info: UPDATE events set time_fired_ts=IF(time_fired is NULL,0,UNIX_TIMESTAMP(CONVERT_TZ(time_fired,'+00:00',@@global.time_zone))) where time_fired_ts is NULL LIMIT 250000
Progress: 0.000

******************************************** - next update after 2 seconds - ****************************************************************

      db: ha
 Command: Query
    Time: 0
   State: NULL
    Info: UPDATE events set time_fired_ts=IF(time_fired is NULL,0,UNIX_TIMESTAMP(CONVERT_TZ(time_fired,'+00:00',@@global.time_zone))) where time_fired_ts is NULL LIMIT 250000
Progress: 0.000

******************************************** - next update after 2 seconds - ****************************************************************

      db: ha
 Command: Query
    Time: 0
   State: Writing to net
    Info: ROLLBACK
Progress: 0.000
7

HA and MariaDB with small working (hass) database.
Everything works great before upgrading to 2023-2-0 to 3.
The upgrade converts the database from last_updated (DATIME) to last_updated_ts (DOUBLE) and sets last_updated to NULL. MariaDB cannot handle DOUBLE as DATETIME.

Does anyone have any suggestions on how this can be resolved.

8

MariaDB ver 10.11.1

9

@Prem Would you consider this a bug? I think you might be able to explain it better than I can. Perhaps you could open an issue on github so the developers see it?

G.