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.
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.
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.
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
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.
