probot-home-assistant[bot]
commented
3 years ago recorder documentation recorder source (message by IssueLinks)
Closed embcla closed 3 years ago
probot-home-assistant[bot]
commented
3 years ago recorder documentation recorder source (message by IssueLinks)
embcla
commented
3 years ago Here is one of the non repetitive types of recorder errors, time is the same. And it's always both an error and a warning
Source: components/recorder/util.py:43
Integration: Recorder (documentation, issues)
First occurred: 23 November 2020, 4:12:00 (2 occurrences)
Last logged: 4:12:00
Error executing query: (MySQLdb._exceptions.IntegrityError) (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`homeassistant`.`states`, CONSTRAINT `states_ibfk_2` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`))') [SQL: DELETE FROM states WHERE states.last_updated < %s] [parameters: (datetime.datetime(2020, 10, 21, 12, 56, 13, tzinfo=<UTC>),)] (Background on this error at: http://sqlalche.me/e/13/gkpj)
Logger: homeassistant.components.recorder.purge
Source: components/recorder/purge.py:101
Integration: Recorder (documentation, issues)
First occurred: 23 November 2020, 4:12:00 (2 occurrences)
Last logged: 4:12:00
Error purging history: (MySQLdb._exceptions.IntegrityError) (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`homeassistant`.`states`, CONSTRAINT `states_ibfk_2` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`))') [SQL: DELETE FROM states WHERE states.last_updated < %s] [parameters: (datetime.datetime(2020, 10, 21, 12, 56, 13, tzinfo=<UTC>),)] (Background on this error at: http://sqlalche.me/e/13/gkpj)```
bdraco
commented
3 years ago Since these all start at 4:12 it looks like a problem with the auto purge.
slipx06
commented
3 years ago I am seeing the same error in my logs every couple of days. Recorder stops working. I have to restart home assistant to fix. I'm running 0.118.3 with the MariaDB addon
2020-11-25 10:25:26 ERROR (Recorder) [homeassistant.components.recorder] Error executing query: (MySQLdb._exceptions.IntegrityError) (1452, 'Cannot add or update a child row: a foreign key constraint fails (homeassistant.states, CONSTRAINT states_ibfk_2 FOREIGN KEY (old_state_id) REFERENCES states (state_id))')
embcla
commented
3 years ago Today's log
Logger: homeassistant.components.recorder
Source: components/recorder/__init__.py:518
Integration: Recorder (documentation, issues)
First occurred: 4:12:05 (2508 occurrences)
Last logged: 14:03:38
Error executing query: (MySQLdb._exceptions.IntegrityError) (1452, 'Cannot add or update a child row: a foreign key constraint fails (`homeassistant`.`states`, CONSTRAINT `states_ibfk_2` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`))') [SQL: INSERT INTO states (domain, entity_id, state, attributes, event_id, last_changed, last_updated, created, old_state_id) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)] [parameters: ('sensor', 'sensor.memory_use_percent', '43.6', '{"unit_of_measurement": "%", "friendly_name": "Memory use (percent)", "icon": "mdi:memory"}', 12611851, datetime.datetime(2020, 11, 26, 3, 42, 18, 239796, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 11, 26, 3, 42, 18, 239796, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 11, 26, 3, 42, 18, 239796, tzinfo=datetime.timezone.utc), 12344754)] (Background on this error at: http://sqlalche.me/e/13/gkpj)
Error executing query: (MySQLdb._exceptions.IntegrityError) (1452, 'Cannot add or update a child row: a foreign key constraint fails (`homeassistant`.`states`, CONSTRAINT `states_ibfk_2` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`))') [SQL: INSERT INTO states (domain, entity_id, state, attributes, event_id, last_changed, last_updated, created, old_state_id) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)] [parameters: ('sensor', 'sensor.unifi_dream_machine_b_sent', '85884692872', '{"unit_of_measurement": "B", "friendly_name": "UniFi Dream Machine B sent", "icon": "mdi:server-network"}', 12604736, datetime.datetime(2020, 11, 26, 3, 16, 50, 25074, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 11, 26, 3, 16, 50, 25074, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 11, 26, 3, 16, 50, 25074, tzinfo=datetime.timezone.utc), 12343926)] (Background on this error at: http://sqlalche.me/e/13/gkpj)
Error executing query: (MySQLdb._exceptions.IntegrityError) (1452, 'Cannot add or update a child row: a foreign key constraint fails (`homeassistant`.`states`, CONSTRAINT `states_ibfk_2` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`))') [SQL: INSERT INTO states (domain, entity_id, state, attributes, event_id, last_changed, last_updated, created, old_state_id) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)] [parameters: ('sensor', 'sensor.nas_bo2_status', 'good', '{"name": "NAS-BO2", "Model": "TS-473", "Serial #": "Q205B43808", "Uptime": "33d 14h 29m", "friendly_name": "NAS-BO2 Status", "icon": "mdi:checkbox-marked-circle-outline"}', 12621541, datetime.datetime(2020, 11, 24, 20, 51, 12, 140078, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 11, 26, 4, 17, 57, 268079, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 11, 26, 4, 17, 57, 268079, tzinfo=datetime.timezone.utc), 12345670)] (Background on this error at: http://sqlalche.me/e/13/gkpj)
Error executing query: (MySQLdb._exceptions.IntegrityError) (1452, 'Cannot add or update a child row: a foreign key constraint fails (`homeassistant`.`states`, CONSTRAINT `states_ibfk_2` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`))') [SQL: INSERT INTO states (domain, entity_id, state, attributes, event_id, last_changed, last_updated, created, old_state_id) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)] [parameters: ('sensor', 'sensor.iotawatt', '230.4908', '{"inputs": [{"channel": 0, "Vrms": 230.4908, "Hz": 50.00903, "phase": 1.48}, {"channel": 1, "Watts": " 0", "Pf": 0, "phase": 1.4, "lastphase": -0.08} ... (1514 characters truncated) ... s": "Watts", "value": 229.4962}, {"name": "voltaggio", "units": "Volts", "value": 230.4908}], "friendly_name": "IoTaWatt", "device_class": "voltage"}', 12603421, datetime.datetime(2020, 11, 26, 3, 12, 4, 853139, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 11, 26, 3, 12, 4, 853139, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 11, 26, 3, 12, 4, 853139, tzinfo=datetime.timezone.utc), 12343713)] (Background on this error at: http://sqlalche.me/e/13/gkpj)
Error executing query: (MySQLdb._exceptions.IntegrityError) (1452, 'Cannot add or update a child row: a foreign key constraint fails (`homeassistant`.`states`, CONSTRAINT `states_ibfk_2` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`))') [SQL: INSERT INTO states (domain, entity_id, state, attributes, event_id, last_changed, last_updated, created, old_state_id) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)] [parameters: ('sensor', 'sensor.cucina_temperature', '16.59', '{"time": "2020-11-26T05:19:31.425Z", "setting": 0, "unit_of_measurement": "\\u00b0C", "friendly_name": "Smart TRV Cucina temperature", "icon": "mdi:thermometer"}', 12638479, datetime.datetime(2020, 11, 26, 5, 19, 37, 874522, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 11, 26, 5, 19, 37, 874522, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 11, 26, 5, 19, 37, 874522, tzinfo=datetime.timezone.utc), 12346921)] (Background on this error at: http://sqlalche.me/e/13/gkpj)@bdraco has the fix been merged into 118.5? I had the same issue last night as usual
The problem
Sometimes the logger gets stuck basically tearing down the entire home assistant core. I notice it because I hear the harddrives churning, and when I get to HA the UI is extremely slow (to the point of being almost unresponsive). It always starts at 4:12, though I don't run services at that specific time. It almost never recovers by itself, in fact this has happened a number of times since I moved to mariadb, and only once I saw this error in the logs appearing only once, all other times the system gets stuck generating it in loop thousands of times until I catch it.
HA is running in supervised on a debian VM on my NAS, and mariaDB in a container on the same NAS. NAS is an AMD based quad core x86 based on linux. When the error is going on, the NAS shows increased cpu and I/O activity by debian as shown in these pictures
Environment
Problem-relevant
configuration.yamlTraceback/Error logs
mariadb shows no error in the container logs but the resets when rebooting HA
and the processlist before the reset looks like this
and after the reset looks like this:
Additional information