Recorder DB Migration Failed

I have a Home Assistant docker installation (not hassio) with an external MariaDB recorder db. Everything runs ok, but since upgrade to version 2021.1 on every restart of Home Assistant I get following messages:

2021-01-21 07:44:06 WARNING (Recorder) [homeassistant.components.recorder.migration] Database is about to upgrade. Schema version: 10
2021-01-21 07:44:06 INFO (Recorder) [homeassistant.components.recorder.migration] Upgrading recorder db schema to version 11
2021-01-21 07:44:06 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding index `ix_states_old_state_id` to database. Note: this can take several minutes on large databases and slow computers. Please be patient!
2021-01-21 07:44:16 WARNING (MainThread) [homeassistant.setup] Setup of recorder is taking over 10 seconds.
2021-01-21 07:45:06 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
2021-01-21 07:46:06 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
2021-01-21 07:47:06 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder

Although after these messages HA starts up normrally, every reboot takes a few minutes more then before and the same update is repeated next reboot. I tried to reduce the retention of the database to one month, because thought it might be related to size, but the messages and delay keep appearing so the updates fails silently every time.

Does anyone know how i can fix this?

Thanks.

I enabled the query log on my mysql server and did see the following trace

                 13124 Connect  ****@*** as anonymous on ***
                 13124 Query    SET autocommit=0
                 13124 Query    SET NAMES utf8
                 13124 Query    SHOW VARIABLES LIKE 'sql_mode'
                 13124 Query    SHOW VARIABLES LIKE 'lower_case_table_names'
                 13124 Query    SELECT VERSION()
                 13124 Query    SELECT DATABASE()
                 13124 Query    SELECT @@tx_isolation
                 13124 Query    show collation where `Charset` = 'utf8mb4' and `Collation` = 'utf8mb4_bin'
                 13124 Query    SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
                 13124 Query    SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
                 13124 Query    SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin AS anon_1
                 13124 Query    ROLLBACK
                 13124 Query    SET NAMES utf8
                 13124 Query    SET session wait_timeout=28800
                 13124 Query    DESCRIBE `events`
                 13124 Query    DESCRIBE `states`
                 13124 Query    DESCRIBE `recorder_runs`
                 13124 Query    DESCRIBE `schema_changes`
                 13124 Query    ROLLBACK
                 13124 Query    SELECT schema_changes.change_id AS schema_changes_change_id, schema_changes.schema_version AS schema_changes_schema_version, schema_changes.changed AS schema_changes_changed 
210122  9:04:45  13125 Connect  ****@*** as anonymous on ***
                 13125 Query    SET autocommit=0
                 13125 Query    SET NAMES utf8
                 13125 Query    SET session wait_timeout=28800
                 13125 Query    CREATE INDEX ix_states_old_state_id ON states (old_state_id)
                 13125 Query    ROLLBACK
                 13125 Query    ROLLBACK
                 13124 Query    COMMIT
                 13124 Query    ROLLBACK
                 13125 Query    SELECT recorder_runs.run_id AS recorder_runs_run_id, recorder_runs.start AS recorder_runs_start, recorder_runs.end AS recorder_runs_end, recorder_runs.closed_incorrect AS recorder_runs_closed_incorrect, recorder_runs.created AS recorder_runs_created 
                 13125 Query    INSERT INTO recorder_runs (start, end, closed_incorrect, created) VALUES ('2021-01-22 08:04:44.583426', NULL, 0, '2021-01-22 08:08:54.757298')
                 13125 Query    COMMIT
                 13125 Query    ROLLBACK

The rollback after creating the index, i do not expect, unless it was temporarily needed for the migration process.
Manually creating that index and restarting did not make any difference.

Issue resolved itself. I noticed at last restart, HA justed waited longer for it to complete. No clue what fixed it after so many days

Core cannot start. I only get to the command line. PrintScreen is via the core logs command. Can’t advise what to do next?

What kind of database are you using? How many days do you save your recorder database? Isn’t it not that big that just have to wait?

default database size approx. 10GB approx. 60 days but with limited entities. I enclose the record configuration. Thank you for the ideas restart takes about 6 hours.

purge_keep_days: 60
auto_purge: true
commit_interval: 1
include:
domains:
- switch
entities:
- sensor.processor_use
- sensor.memory_use
- sensor.disk_use_home
- sensor.r01_zas01_energy_power
- sensor.r01_zas01_energy_total
- sensor.r01_zas01_energy_voltage
- sensor.r01_zas02_energy_power
- sensor.r01_zas02_energy_total
- sensor.r01_zas02_energy_voltage
- sensor.r01_zas03_energy_power
- sensor.r01_zas03_energy_total
- sensor.r01_zas03_energy_voltage
- sensor.r01_zas04_energy_power
- sensor.r01_zas04_energy_total
- sensor.r01_zas04_energy_voltage
- sensor.r01_zas05_energy_power
- sensor.r01_zas05_energy_total
- sensor.r01_zas05_energy_voltage
- sensor.r01_zas06_energy_power
- sensor.r01_zas06_energy_total
- sensor.r01_zas06_energy_voltage
- sensor.r01_zas07_energy_power
- sensor.r01_zas07_energy_total
- sensor.r01_zas07_energy_voltage
- sensor.r01_zas08_energy_power
- sensor.r01_zas08_energy_total
- sensor.r01_zas08_energy_voltage
- sensor.r01_zas09_energy_power
- sensor.r01_zas09_energy_total
- sensor.r01_zas09_energy_voltage
- sensor.r01_zas10_energy_power
- sensor.r01_zas10_energy_total
- sensor.r01_zas10_energy_voltage
- sensor.r01_zas11_energy_power
- sensor.r01_zas11_energy_total
- sensor.r01_zas11_energy_voltage
- sensor.r01_zas12_energy_power
- sensor.r01_zas12_energy_total
- sensor.r01_zas12_energy_voltage
- sensor.r02_zas01_energy_power
- sensor.r02_zas01_energy_total
- sensor.r02_zas01_energy_voltage
- sensor.r02_zas02_energy_power
- sensor.r02_zas02_energy_total
- sensor.r02_zas02_energy_voltage
- sensor.r02_zas03_energy_power
- sensor.r02_zas03_energy_total
- sensor.r02_zas03_energy_voltage
- sensor.r02_zas04_energy_power
- sensor.r02_zas04_energy_total
- sensor.r02_zas04_energy_voltage
- sensor.r02_zas05_energy_power
- sensor.r02_zas05_energy_total
- sensor.r02_zas05_energy_voltage
- sensor.r02_zas06_energy_power
- sensor.r02_zas06_energy_total
- sensor.r02_zas06_energy_voltage
- sensor.r02_zas07_energy_power
- sensor.r02_zas07_energy_total
- sensor.r02_zas07_energy_voltage
- sensor.r02_zas08_energy_power
- sensor.r02_zas08_energy_total
- sensor.r02_zas08_energy_voltage
- sensor.r02_zas09_energy_power
- sensor.r02_zas09_energy_total
- sensor.r02_zas09_energy_voltage
- sensor.r02_zas10_energy_power
- sensor.r02_zas10_energy_total
- sensor.r02_zas10_energy_voltage
- sensor.r02_zas11_energy_power
- sensor.r02_zas11_energy_total
- sensor.r02_zas11_energy_voltage
- sensor.r02_zas12_energy_power
- sensor.r02_zas12_energy_total
- sensor.r02_zas12_energy_voltage
- sensor.mereni_01_si7021_temperature
- sensor.mereni_01_si7021_humidity
- sun.sun

Not sure if it the migration that fails. Maybe you can try with a top command to see if the sqllite process is very busy during your restart. Are those log messages preceded with a migration message of the recorder db?

If so, and when you can do without the recorder history you could try to limit the days in your configuration.
Another option might be that you manually truncate the sqllite tables.

Unfortunately, I do not know how to find out this condition. During the 6 hour start, only cmd is accessible via the CLI. The web interface only goes after that.
I enclose an abbreviated extract from the log.

I would try to turn off the SQLlite record completely but I don’t know how.

2021-02-27 14:25:11 WARNING (MainThread) [homeassistant.setup] Setup of recorder is taking over 10 seconds.
2021-02-27 14:26:00 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
2021-02-27 14:27:00 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
2021-02-27 14:28:00 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
/
2021-02-27 20:27:15 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
2021-02-27 20:28:15 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
2021-02-27 20:29:16 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
2021-02-27 20:30:16 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
2021-02-27 20:31:16 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
2021-02-27 20:32:16 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder

I’m still attaching my version of HASS

You are currently running version 2021.1.5
You are currently running version 5.11

So I deleted the database, then restart 60s instead of 7h. HA problems with sqllite should be solved natively, with reliable writing and working with the default database. Otherwise, it’s all HA a bit for the cat.