Since upgrading to 0.112 and now 0.112.1 besides even 0.112.1 taking forever to restart, anyone seeing no history being tracked?
Like I have an alert when the washer or dryer finish but it never goes off even though I can see the statuses are still reporting realtime.
When the system starts up I have a number of notifications including inability to start:
The tracking of death rates for Covid no longer reports No Status History found.
Any breaking changes that might have caused that? What does the log say?
Easiest first step is to go to Configuration-Server Control and hit “Check Configuration” (blue button on top)
No, configuration is fine and haven’t made a change for months other than updating to 0.112. I’ll check my logs specifically shortly.
For recorder seeing the below:
2020-07-05 09:01:24 WARNING (Recorder) [homeassistant.components.recorder.migration] Database is about to upgrade. Schema version: 7
2020-07-05 09:01:24 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding columns context_parent_id to table events. Note: this can take several minutes on large databases and slow computers. Please be patient!
2020-07-05 09:01:34 WARNING (MainThread) [homeassistant.setup] Setup of recorder is taking over 10 seconds.
2020-07-05 09:05:20 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: (MySQLdb._exceptions.OperationalError) (1114, "The table 'events' is full")
[SQL: ALTER TABLE events ADD context_parent_id CHARACTER(36)]
(Background on this error at: http://sqlalche.me/e/e3q8)
2020-07-05 09:05:20 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: (MySQLdb._exceptions.OperationalError) (1114, "The table 'events' is full")
[SQL: ALTER TABLE events ADD context_parent_id CHARACTER(36)]
table is full
This most likely means that your disk is full
No, 4.2GB free.
Thanks.
If you don’t mind losing your history data, you could re-install the MariaDB Add-On (which I guess you are using?) (copy the config into the freshly installed one)
The error usually means that MySQL doesn’t have space to write data, could be some other problem too.
Yeah I can do that. I did restore from a backup when I thought 0.112 failed the first time so not sure if that had something to do with it. Saw some threads about mariadb not handling backup and restores well.
By the way, do I need to remove mariadb or just the add-on?
I’d copy the Config of the MariaDB addon, paste it in a text editor, uninstall the addon, install it again from the Addon-Store, paste the configuration.
Make sure to enable autostart, start it and then restart HA
Of course now looking at my notes there is no plugin.
I install mysql or mariadb
created the user for HA
Migrated the data with some script homeassistant_v2.db
Then just added to configuration.yaml:
recorder:
db_url: mysql://hass:Pass1#[email protected]/hass?charset=utf8
This was the whole thing:
sudo apt install mariadb-server mariadb-client
sudo apt install libmariadb-dev sqlite3
sudo mysql_secure_installation
Enter root password
Say yes to remove anonymous users
Yes to disallow root login remotely
Yes to remove test DB
Yes to reload privilege table
Sudo mysql
CREATE USER 'hass' IDENTIFIED BY 'Pass1#word';
CREATE DATABASE hass;
GRANT ALL PRIVILEGES ON hass.* TO 'hass'@'localhost' identified by 'Pass1#word';
Go to the HA GUI > Configuration > Server > STOP
Cd /usr/share/hassio/homeassistant
Sudo sqlite3 home-assistant_v2.db
.output home-assistant_v2.sql
.dump
.exit
git clone https://github.com/athlite/sqlite3-to-mysql
chmod 777 sqllite3-to-mysql
cp home-assistant_v2.sql sqlite3-to-mysql
cd sqlite3-to-mysql
sudo bash ./sqlite3-to-mysql home-assistant_v2.sql > hadump.sql
sudo mysql -u hass -p -h localhost hass < hadump.sql
This can take a while
mysql -u hass -p hass
update events set event_data = REPLACE(event_data, '`', '"'); update states set attributes = REPLACE(attributes, '`', '"');
select max(run_id) from recorder_runs;
alter table recorder_runs modify column run_id INT NOT NULL AUTO_INCREMENT,
AUTO_INCREMENT=NNNNN; ## this is 1 more than the max above from table_recorder
alter table states drop foreign key states_ibfk_1;
select max(event_id) from events;
alter table events modify column event_id int(11) NOT NULL AUTO_INCREMENT,
AUTO_INCREMENT=NNNNN; ### this is 1 more than the max above from events
alter table states modify column state_id int(11) NOT NULL AUTO_INCREMENT,
AUTO_INCREMENT=NNNNN; ###### This is 1 more than the max above from states
alter table states add foreign key(event_id) references events (event_id);
#### modify configuration.yaml to include the following statements:
recorder:
db_url: mysql://hass:<password>@127.0.0.1/hass?charset=utf8
So you’re using HA on generic Linux? What’s your setup?
Basically an Ubuntu docker install.
So Ubuntu 18.04.and then
curl -sL https://raw.githubusercontent.com/home-assistant/hassio-installer/master/hassio_install.sh | bash -s
And you already know I have mariadb.
So i restored from backup again a different one and still no joy. Different issues but maybe that’s due to a upgrade from an “older” version. 0.110. Basically same outcome.
2020-07-06 05:47:52 WARNING (MainThread) [homeassistant.loader] You are using a custom integration for hacs which has not been tested by Home Assistant. This component might cause stability problems, be sure to disable it if you experience issues with Home Assistant.
2020-07-06 05:47:52 WARNING (MainThread) [homeassistant.loader] You are using a custom integration for alexa_media which has not been tested by Home Assistant. This component might cause stability problems, be sure to disable it if you experience issues with Home Assistant.
2020-07-06 05:47:52 WARNING (MainThread) [homeassistant.loader] You are using a custom integration for audiconnect which has not been tested by Home Assistant. This component might cause stability problems, be sure to disable it if you experience issues with Home Assistant.
2020-07-06 05:47:52 WARNING (MainThread) [homeassistant.components.http] The 'base_url' option is deprecated, please remove it from your configuration
2020-07-06 05:47:52 WARNING (Recorder) [homeassistant.components.recorder.migration] Database is about to upgrade. Schema version: 7
2020-07-06 05:47:52 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding columns context_parent_id to table events. Note: this can take several minutes on large databases and slow computers. Please be patient!
2020-07-06 05:47:52 WARNING (Recorder) [homeassistant.components.recorder.migration] Column context_parent_id already exists on events, continuing
2020-07-06 05:47:52 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding columns old_state_id to table states. Note: this can take several minutes on large databases and slow computers. Please be patient!
2020-07-06 05:47:52 WARNING (Recorder) [homeassistant.components.recorder.migration] Column old_state_id already exists on states, continuing
2020-07-06 05:47:52 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding index `ix_events_context_parent_id` to database. Note: this can take several minutes on large databases and slow computers. Please be patient!
2020-07-06 05:47:52 WARNING (Recorder) [homeassistant.components.recorder.migration] Index ix_events_context_parent_id already exists on events, continuing
2020-07-06 05:47:52 WARNING (Recorder) [homeassistant.components.recorder.migration] Failed to drop index ix_states_context_id from table states. Schema Migration will continue; this is not a critical operation.
2020-07-06 05:47:52 WARNING (Recorder) [homeassistant.components.recorder.migration] Failed to drop index ix_states_context_user_id from table states. Schema Migration will continue; this is not a critical operation.
2020-07-06 05:47:52 WARNING (Recorder) [homeassistant.components.recorder.migration] Failed to drop index ix_states_entity_id from table states. Schema Migration will continue; this is not a critical operation.
2020-07-06 05:47:52 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding index `ix_events_event_type_time_fired` to database. Note: this can take several minutes on large databases and slow computers. Please be patient!
2020-07-06 05:47:52 WARNING (Recorder) [homeassistant.components.recorder.migration] Index ix_events_event_type_time_fired already exists on events, continuing
2020-07-06 05:47:52 WARNING (Recorder) [homeassistant.components.recorder.migration] Failed to drop index ix_events_event_type from table events. Schema Migration will continue; this is not a critical operation.
2020-07-06 05:47:52 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: (MySQLdb._exceptions.IntegrityError) (1062, "Duplicate entry '0' for key 'PRIMARY'")
[SQL: INSERT INTO schema_changes (schema_version, changed) VALUES (%s, %s)]
[parameters: (9, datetime.datetime(2020, 7, 6, 10, 47, 52, 627688, tzinfo=<UTC>))]
(Background on this error at: http://sqlalche.me/e/gkpj)
2020-07-06 05:47:52 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: (MySQLdb._exceptions.IntegrityError) (1062, "Duplicate entry '0' for key 'PRIMARY'")
[SQL: INSERT INTO schema_changes (schema_version, changed) VALUES (%s, %s)]
[parameters: (9, datetime.datetime(2020, 7, 6, 10, 47, 52, 627688, tzinfo=<UTC>))]
(Background on this error at: http://sqlalche.me/e/gkpj) (retrying in 3 seconds)
Then it just keeps repeating that for a while. Should note this is the same error the first time I ran this upgrade after the restore and does this every time after that.
Should note before doing the upgrade after restore this time I increased the drive space in case it was trying to use more than 4GB of space to do the DB changes so have like 80GB free now.
Hm, that’s weird. I’d just purge the entire database if you don’t need the historic data…
From SO:
This error can indicate that the table’s PRIMARY KEY is not set to AUTO-INCREMENT, (and your insert query did not specify an ID value).
To resolve:
Check that there is a PRIMARY KEY set on your table, and that the PRIMARY KEY is set to AUTO-INCREMENT.
mysql - Error: Duplicate entry '0' for key 'PRIMARY' - Stack Overflow
But again, re-creating the database is probably faster and easier
Yeah i did that probably while you were writing. I did:
SET foreign_key_checks = 0;
TRUNCATE tablename; //do this for each table you want emptied
SET foreign_key_checks = 1;
Did that for the four tables. Didn’t see any commands run on the database at all after the restart. Figured there would still be some kind of setting this index or that but it came back now and seems to be writing data again.