Core: MariaDB doesn't purge

[drthanwho]

The problem

I recently moved from the default database to MariaDB. Everything seemed to be working good until I noticed errors occuring on my HA regarding database. It also resulted in history on individual entities not loading (so meant also graphs wouldn't load either ever or take a very long time). This also somehow made all zigbee devices to briefly loose connection.

So I thought perhaps the database might got corrupted so I attempted to delete it (I tried the recorder.purge service) however it wasn't possible. This is what made me realise that also even though my recorder settings are _keep_days: 7_ my history was going back 15 days (about when I moved databases). The only way to delete it (suggested in discord) was to uninstall the add-on completely and start again.

In order to reproduce the issue I moved _keep_days_ down to 1 day and I got the same error again at 4:21 (as expected).
Therefore, it seems the database will build up indefinitely and never get purged

Environment

• Add-on with the issue: MariaDB
• Add-on release with the issue: v2.2.1
• Last working add-on release (if known): N/A
• Operating environment (OS/Supervised): OS 5.1 and HA 116.2

Problem-relevant configuration

My recorder config is a lot bigger but I trimmed it down for simplicity

db_url: mysql://homeassistant:xxxxxxx@core-mariadb/homeassistant?charset=utf8
purge_keep_days: 1
include:
  domains:
    - light
    - input_select
    - device_tracker
    - person
  entity_globs:
    - sensor.*public_ip_address
    - sensor.*battery_level
    - sensor.*coronavirus*
    - sensor.*_light_sensor
    - sensor.*_pressure_sensor
    - sensor.*_steps_sensor
    - sensor.*_storage_sensor
  entities:
    - group.people_status
    - binary_sensor.inside_button
    - binary_sensor.kleidaria
    - binary_sensor.koudoni_ttgocam_pir
    - binary_sensor.kuria_eisodos
    - binary_sensor.openclose_11
    - binary_sensor.openclose_18
exclude:
  domains:
    - camera
    - switch
    - group
    - automation
    - media_player
    - binary_sensor
    - zone
    - input_text
    - input_number
  entity_globs:
    - sensor.*count
    - sensor.*_ip
    - sensor.chinese_*
    - sensor.home_echo_*
    - sensor.seventeentrack*
    - sensor.thanasis_dot_*
    - sensor.thanasis_flex_*
    - sensor.*wifi_bssid
    - sensor.*wifi_frequency
    - sensor.*wifi_link_speed
    - sensor.*wifi_signal_strength
    - sensor.u_s_*
  entities:
    - sensor.date
    - sensor.hacs
    - sensor.last_alexa
    - sensor.last_boot
    - sensor.last_tag_description
    - sun.sun
    - weather.dark_sky

MariaDB add-on configuration:

databases:
  - homeassistant
logins:
  - username: homeassistant
    password: xxxxx
rights:
  - username: homeassistant
    database: homeassistant

Traceback/Error logs

2020-10-26 04:12:00 ERROR (Recorder) [homeassistant.components.recorder.util] 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, 23, 18, 8, 3, tzinfo=<UTC>),)]
(Background on this error at: http://sqlalche.me/e/13/gkpj)
2020-10-26 04:12:00 WARNING (Recorder) [homeassistant.components.recorder.purge] 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, 23, 18, 8, 3, tzinfo=<UTC>),)]
(Background on this error at: http://sqlalche.me/e/13/gkpj)

Additional information

[probot-home-assistant[bot]]

recorder documentation
recorder source
_{^{(message by IssueLinks)}}

[degrashopper]

I'm getting the same errors in the logs. running supervised HA 0.116.4

[Funcy-dcm]

I'm getting the same errors in the logs

[drthanwho]

@Funcy-dcm it would help of you else mention what install method you are running and which versions of HA and OS you have

[fubar-coder]

I have a similar error.

OS: RaspberryPI OS Buster Arm64
PI: RPi 4 Model B r1.4
HA Installation method: docker
HA Version: 0.117.1

2020-11-01 12:17:02 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`))')
[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.power_21', '109', '{"on": true, "current": 559, "voltage": 224, "unit_of_measurement": "W", "friendly_name": "Server-Infrastruktur Stromverbrauch", "device_class": "power"}', 13790055, datetime.datetime(2020, 11, 1, 3, 12, 16, 993793, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 11, 1, 3, 12, 16, 993793, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 11, 1, 3, 12, 16, 993837, tzinfo=datetime.timezone.utc), 13673990)]
(Background on this error at: http://sqlalche.me/e/13/gkpj)
2020-11-01 12:17:02 ERROR (Recorder) [homeassistant.components.recorder] Error saving events: (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.power_21', '109', '{"on": true, "current": 559, "voltage": 224, "unit_of_measurement": "W", "friendly_name": "Server-Infrastruktur Stromverbrauch", "device_class": "power"}', 13790055, datetime.datetime(2020, 11, 1, 3, 12, 16, 993793, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 11, 1, 3, 12, 16, 993793, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 11, 1, 3, 12, 16, 993837, tzinfo=datetime.timezone.utc), 13673990)]
(Background on this error at: http://sqlalche.me/e/13/gkpj)
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 593, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/usr/local/lib/python3.8/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, 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`))')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/__init__.py", line 454, in _commit_event_session_or_retry
    self._commit_event_session()
  File "/usr/src/homeassistant/homeassistant/components/recorder/__init__.py", line 509, in _commit_event_session
    self.event_session.flush()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2536, in flush
    self._flush(objects)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2678, in _flush
    transaction.rollback(_capture_exception=True)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.raise_(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2638, in _flush
    flush_context.execute()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 419, in execute
    n.execute_aggregate(self, set_)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 697, in execute_aggregate
    persistence.save_obj(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 239, in save_obj
    _emit_insert_statements(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 1135, in _emit_insert_statements
    result = cached_connections[connection].execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1124, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1316, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapi_exception
    util.raise_(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 593, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/usr/local/lib/python3.8/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)
sqlalchemy.exc.IntegrityError: (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.power_21', '109', '{"on": true, "current": 559, "voltage": 224, "unit_of_measurement": "W", "friendly_name": "Server-Infrastruktur Stromverbrauch", "device_class": "power"}', 13790055, datetime.datetime(2020, 11, 1, 3, 12, 16, 993793, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 11, 1, 3, 12, 16, 993793, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 11, 1, 3, 12, 16, 993837, tzinfo=datetime.timezone.utc), 13673990)]
(Background on this error at: http://sqlalche.me/e/13/gkpj)

EDIT:

My current "workaround" is to execute the following command on the DB:

ALTER TABLE states DROP CONSTRAINT states_ibfk_2;

EDIT2: Be aware that this doesn't fix the underlying problem. It just makes the error message go away. Funny thing is, that after restarting Home Assistant, the error simply goes away and is returning after a couple of hours.

[jes1417]

Same here

System Health
arch    x86_64
chassis vm
dev false
docker  true
docker_version  19.03.11
hassio  true
host_os HassOS 4.15
installation_type   Home Assistant OS
os_name Linux
os_version  5.4.72
python_version  3.8.6
supervisor  2020.10.1
timezone    America/Chicago
version 0.117.1
virtualenv  false
Lovelace
dashboards  1
mode    storage
resources   4
views   3

Logger: homeassistant.components.recorder.util
Source: components/recorder/util.py:43
Integration: Recorder (documentation, issues)
First occurred: October 31, 2020, 4:12:00 AM (2 occurrences)
Last logged: 4:12:02 AM

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, 11, 20, 36, 19, tzinfo=<UTC>),)] (Background on this error at: http://sqlalche.me/e/13/gkpj)



md5-486be793a7326f5626a5b31fa8aa3baa



Logger: homeassistant.components.recorder
Source: components/recorder/__init__.py:518
Integration: Recorder (documentation, issues)
First occurred: October 31, 2020, 4:12:10 AM (13220 occurrences)
Last logged: 6:24:17 AM

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: ('light', 'light.downstairs_bath', 'on', '{"min_mireds": 153, "max_mireds": 500, "effect_list": ["0", "1", "2", "3", "4"], "brightness": 255, "color_temp": 190, "hs_color": [0.0, 0.0], "rgb_color": [255, 255, 255], "xy_color": [0.323, 0.329], "white_value": 255, "effect": "0", "friendly_name": "Downstairs Bath", "supported_features": 151}', 655487, datetime.datetime(2020, 10, 31, 16, 26, 39, 355261, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 10, 31, 16, 26, 39, 355261, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 10, 31, 16, 26, 39, 355282, tzinfo=datetime.timezone.utc), 638077)] (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.glances_ram_used_percent', '46.1', '{"unit_of_measurement": "%", "friendly_name": "Glances RAM used percent", "icon": "mdi:memory"}', 653957, datetime.datetime(2020, 10, 31, 15, 2, 17, 827066, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 10, 31, 15, 2, 17, 827066, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 10, 31, 15, 2, 17, 827096, tzinfo=datetime.timezone.utc), 637485)] (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.glances_ram_used', '909.3', '{"unit_of_measurement": "MiB", "friendly_name": "Glances RAM used", "icon": "mdi:memory"}', 653861, datetime.datetime(2020, 10, 31, 14, 57, 18, 794901, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 10, 31, 14, 57, 18, 794901, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 10, 31, 14, 57, 18, 794925, tzinfo=datetime.timezone.utc), 637486)] (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: ('media_player', 'media_player.hallway_mini', 'playing', '{"volume_level": 0.15000000596046448, "is_volume_muted": false, "media_content_id": "https://storage.googleapis.com/relaxation-sounds/ocean_3600.mp3" ... (474 characters truncated) ... ly_name": "Hallway mini", "entity_picture": "//www.gstatic.com/voice_delight/images/relaxation_sounds/icons/ocean.png", "supported_features": 152463}', 651004, datetime.datetime(2020, 10, 31, 11, 30, 19, 138326, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 10, 31, 12, 24, 52, 858678, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 10, 31, 12, 24, 52, 858701, tzinfo=datetime.timezone.utc), 634068)] (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: ('media_player', 'media_player.bdrm', 'playing', '{"media_content_id": "5707626", "media_content_type": "movie", "media_duration": 1800, "media_position": 724, "media_position_updated_at": "2020-10-3 ... (195 characters truncated) ... _recorded": false, "media_start_time": "2020-10-31T04:00:00-05:00", "friendly_name": "Bdrm", "supported_features": 21425, "device_class": "receiver"}', 648214, datetime.datetime(2020, 10, 31, 3, 55, 37, 716370, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 10, 31, 9, 12, 9, 288864, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 10, 31, 9, 12, 9, 288888, tzinfo=datetime.timezone.utc), 632056)] (Background on this error at: http://sqlalche.me/e/13/gkpj)



md5-486be793a7326f5626a5b31fa8aa3baa



Logger: homeassistant.components.recorder
Source: components/recorder/__init__.py:509
Integration: Recorder (documentation, issues)
First occurred: October 31, 2020, 4:12:10 AM (13220 occurrences)
Last logged: 6:24:17 AM

Error saving events: (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: ('light', 'light.downstairs_bath', 'on', '{"min_mireds": 153, "max_mireds": 500, "effect_list": ["0", "1", "2", "3", "4"], "brightness": 255, "color_temp": 190, "hs_color": [0.0, 0.0], "rgb_color": [255, 255, 255], "xy_color": [0.323, 0.329], "white_value": 255, "effect": "0", "friendly_name": "Downstairs Bath", "supported_features": 151}', 655487, datetime.datetime(2020, 10, 31, 16, 26, 39, 355261, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 10, 31, 16, 26, 39, 355261, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 10, 31, 16, 26, 39, 355282, tzinfo=datetime.timezone.utc), 638077)] (Background on this error at: http://sqlalche.me/e/13/gkpj)
Error saving events: (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.glances_ram_used_percent', '46.1', '{"unit_of_measurement": "%", "friendly_name": "Glances RAM used percent", "icon": "mdi:memory"}', 653957, datetime.datetime(2020, 10, 31, 15, 2, 17, 827066, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 10, 31, 15, 2, 17, 827066, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 10, 31, 15, 2, 17, 827096, tzinfo=datetime.timezone.utc), 637485)] (Background on this error at: http://sqlalche.me/e/13/gkpj)
Error saving events: (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.glances_ram_used', '909.3', '{"unit_of_measurement": "MiB", "friendly_name": "Glances RAM used", "icon": "mdi:memory"}', 653861, datetime.datetime(2020, 10, 31, 14, 57, 18, 794901, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 10, 31, 14, 57, 18, 794901, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 10, 31, 14, 57, 18, 794925, tzinfo=datetime.timezone.utc), 637486)] (Background on this error at: http://sqlalche.me/e/13/gkpj)
Error saving events: (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: ('media_player', 'media_player.hallway_mini', 'playing', '{"volume_level": 0.15000000596046448, "is_volume_muted": false, "media_content_id": "https://storage.googleapis.com/relaxation-sounds/ocean_3600.mp3" ... (474 characters truncated) ... ly_name": "Hallway mini", "entity_picture": "//www.gstatic.com/voice_delight/images/relaxation_sounds/icons/ocean.png", "supported_features": 152463}', 651004, datetime.datetime(2020, 10, 31, 11, 30, 19, 138326, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 10, 31, 12, 24, 52, 858678, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 10, 31, 12, 24, 52, 858701, tzinfo=datetime.timezone.utc), 634068)] (Background on this error at: http://sqlalche.me/e/13/gkpj)
Error saving events: (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: ('media_player', 'media_player.bdrm', 'playing', '{"media_content_id": "5707626", "media_content_type": "movie", "media_duration": 1800, "media_position": 724, "media_position_updated_at": "2020-10-3 ... (195 characters truncated) ... _recorded": false, "media_start_time": "2020-10-31T04:00:00-05:00", "friendly_name": "Bdrm", "supported_features": 21425, "device_class": "receiver"}', 648214, datetime.datetime(2020, 10, 31, 3, 55, 37, 716370, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 10, 31, 9, 12, 9, 288864, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 10, 31, 9, 12, 9, 288888, tzinfo=datetime.timezone.utc), 632056)] (Background on this error at: http://sqlalche.me/e/13/gkpj)
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 593, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/usr/local/lib/python3.8/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, 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`))')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/__init__.py", line 454, in _commit_event_session_or_retry
    self._commit_event_session()
  File "/usr/src/homeassistant/homeassistant/components/recorder/__init__.py", line 509, in _commit_event_session
    self.event_session.flush()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2536, in flush
    self._flush(objects)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2678, in _flush
    transaction.rollback(_capture_exception=True)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.raise_(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2638, in _flush
    flush_context.execute()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 422, in execute
    rec.execute(self)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 586, in execute
    persistence.save_obj(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 239, in save_obj
    _emit_insert_statements(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 1135, in _emit_insert_statements
    result = cached_connections[connection].execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1124, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1316, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapi_exception
    util.raise_(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 593, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/usr/local/lib/python3.8/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)
sqlalchemy.exc.IntegrityError: (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: ('media_player', 'media_player.bdrm', 'playing', '{"media_content_id": "5707626", "media_content_type": "movie", "media_duration": 1800, "media_position": 724, "media_position_updated_at": "2020-10-3 ... (195 characters truncated) ... _recorded": false, "media_start_time": "2020-10-31T04:00:00-05:00", "friendly_name": "Bdrm", "supported_features": 21425, "device_class": "receiver"}', 648214, datetime.datetime(2020, 10, 31, 3, 55, 37, 716370, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 10, 31, 9, 12, 9, 288864, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 10, 31, 9, 12, 9, 288888, tzinfo=datetime.timezone.utc), 632056)]
(Background on this error at: http://sqlalche.me/e/13/gkpj)

[Funcy-dcm]

@drthanwho

arch | armv7l
-- | --
chassis |  
dev | false
docker | true
docker_version | 19.03.13
hassio | true
host_os | Raspbian GNU/Linux 10 (buster)
installation_type | Home Assistant Supervised
os_name | Linux
os_version | 5.4.72-v7l+
python_version | 3.8.6
supervisor | 2020.10.0
timezone | Asia/Yekaterinburg
version | 0.117.1
virtualenv | false

[fubar-coder]

It seems that this problem already existed: #24721. The problem is even bigger, because it's not just the purge that doesn't work. HA also fails to log new sensor data.

[drthanwho]

In my setup it seems purge doesn't work at all, whereas logging does so far. But I have twice randomly ended up with the database unable to write any new data. Only solution for logging was to restart HA and for purging to remove the add-on and add it again.

[pbarvinko]

I have the same issue with the recorder using postgres database.

Purge procedure performs the following statement:

[SQL: DELETE FROM states WHERE states.last_updated < %(last_updated_1)s]
[parameters: {'last_updated_1': datetime.datetime(2020, 10, 23, 11, 48, 51, 663608, tzinfo=<UTC>)}]

But that cannot physically work, because states are linked to each other via old_state_id, which is a FK and all old records still have newer records, which are linked to them via old_state_id.
Before performing this statement the links from newer states to the states-to-be-purged have to be removed.

[fubar-coder]

@pbarvinko You can specify foreign keys as "SET NULL" which would automatically set the old_state_id column to NULL when the referenced row gets deleted, which would've been useful in this case.

[pbarvinko]

@fubar-coder Good one. I altered the constraints for both event_id and old_state_id. It resulted in (finally!) working purge.
For the record and for the others - what I have done:

1. Checked the confdeltype of the currently available constraints:

SELECT con.conname, con.confupdtype, confdeltype
       FROM pg_catalog.pg_constraint con
            INNER JOIN pg_catalog.pg_class rel
                       ON rel.oid = con.conrelid
            INNER JOIN pg_catalog.pg_namespace nsp
                       ON nsp.oid = connamespace
       WHERE nsp.nspname = 'public'
             AND rel.relname = 'states';

It was set to 'a' (meaning "no action") for both of them.

1. Executed

ALTER TABLE states DROP CONSTRAINT states_old_state_id_fkey;

ALTER TABLE states
ADD CONSTRAINT states_old_state_id_fkey
    FOREIGN KEY (old_state_id)
    REFERENCES states (state_id)
    ON DELETE SET NULL;

ALTER TABLE states DROP CONSTRAINT states_event_id_fkey;

ALTER TABLE states
ADD CONSTRAINT states_event_id_fkey
    FOREIGN KEY (event_id)
    REFERENCES events (event_id)
    ON DELETE SET NULL;

to set the constraints to "on delete set null"

1. Verified with the pg_constraint query above the results. confdeltype is now set to 'n', meaning set to null.

1. Created index on old_state_id column, which has speeded up the purge procedure factor 100x

CREATE INDEX ix_states_old_state_id
ON states (old_state_id);

The steps above are largely specific to Postgres. For those, who use MySQL/MariaDB, please check the following post for the solution.

[pbarvinko]

Well, it kind of works. It does not crash anymore, but the purge procedure hangs for a long time in the delete statement as I have about 6 mln states and the same number of events. I am going to purge the database manually and exclude more event/state types.

[pbarvinko]

@fubar-coder I have updated my post above to add index on old_state_id column.
Now (thanks to your advise) purge works properly. In the essence two things have to be changed/added to the current purge process:

• Adding "ON DELETE SET NULL" to the states_old_state_id_fkey and states_event_id_fkey constraints
• Adding index on old_state_id column

Does anyone know who from the developers shall be tagged here?..

[drthanwho]

@pbarvinko

Does anyone know who from the developers shall be tagged here?..

None. It is generally not appreciate to randomly tag/ping any developers into a chat. They will check open issues when they have the time to do so 👍

[Svrbinek]

Hi, I have the same problem. MariaDB on Synology Server and can't be deleted. Not even by hand. The error message refers to older keys. Here is some solution from pbarvinko, but I have no idea where to check or set the individual points. Will anyone try to guide me, please?

[fubar-coder]

@Svrbinek You have to find a way to execute SQL commands on the MariaDB database used for Home Assistant. I'm unable to help you with this, as I dont know/have a Synology server. The DB server usually accepts connections on port 3306, but a firewall on the Synology server might prevent access to that port. Please check that the firewall on the Synology server allows connections to this port (3306). Now you can install the MySQL Workbench on your desktop PC and connect to MariaDB on the Synology server.

[Mikle61]

Hi, I have the same problem with MariaDB:

---

arch armv7l
chassis
dev false
docker true
docker_version 19.03.13
hassio true
host_os Raspbian GNU/Linux 10 (buster)
installation_type Home Assistant Supervised
os_name Linux
os_version 5.4.72-v7l+
python_version 3.8.6
supervisor 2020.11.0
timezone Europe/Moscow
version 0.117.5
virtualenv false

Уровень: ERROR
Logger: homeassistant.components.recorder.util
Source: components/recorder/util.py:43
Integration: Recorder (documentation, issues)
First occurred: 0:11:00 (4 occurrences)
Last logged: 11:54:04

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, 11, 5, 17, 21, 28, tzinfo=),)] (Background on this error at: http://sqlalche.me/e/13/gkpj)

MariaDB
Configuration

databases:

• homeassistant
  logins:
• username: hass
  host: '%'
  password: hass_password
  rights:
• username: hass
  host: '%'
  database: homeassistant
  grant: ALL PRIVILEGES ON

---

i tried to apply solution from fubar-coder. Installed the program MySQL Workbench, connected to the database and tried to execute the query. However, it turned out that the user did not have enough privileges to execute the select command:

---

Error Code: 1142. SELECT command denied to user 'hass'@'192.168.1.47' for table 'pg_constraint'

---

rights are not enough even to view information about the database:

---

The current user does not have enough privileges to execute SELECT '' as Db,Host,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv,Grant_priv,References_priv,Index_priv,Alter_priv,Create_tmp_table_priv,Lock_tables_priv,Create_view_priv,Create_routine_priv,Alter_routine_priv,Execute_priv,Event_priv,Trigger_priv FROM mysql.user WHERE Select_priv = 'Y' OR Insert_priv = 'Y' OR Update_priv = 'Y' OR Delete_priv = 'Y' OR Create_priv = 'Y' OR Drop_priv = 'Y' OR Grant_priv = 'Y' OR References_priv = 'Y' OR Index_priv = 'Y' OR Alter_priv = 'Y' OR Create_tmp_table_priv = 'Y' OR Lock_tables_priv = 'Y' OR Create_view_priv = 'Y' OR Create_routine_priv = 'Y' OR Alter_routine_priv = 'Y' OR Execute_priv = 'Y' OR Event_priv = 'Y' OR Trigger_priv = 'Y'.

SELECT command denied to user 'hass'@'192.168.1.47' for table 'user'

---

Unfortunately I have no experience with databases. I ask you to suggest how to fix the lack of necessary privileges to fulfill the previously described requests?

[brucehvn]

I started a thread in the forums here with a similar problem. With the Mariadb add-on purge causes foreign key errors (logs are in the forum thread and below). It appears to be trying to execute the delete from the EVENTS table before doing it from STATES. I can do the purge manually from the MySQL CLI client by doing the two deletes, STATES, then EVENTS. Several people in the thread are having the same issue.

2020-11-04 21:11:58 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: (MySQLdb._exceptions.IntegrityError) (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`homeassistant`.`states`, CONSTRAINT `states_ibfk_1` FOREIGN KEY (`event_id`) REFERENCES `events` (`event_id`))')
[SQL: DELETE FROM events WHERE events.time_fired < %s]
[parameters: (datetime.datetime(2020, 7, 8, 7, 26, 53, tzinfo=<UTC>),)]
(Background on this error at: http://sqlalche.me/e/13/gkpj)
2020-11-04 21:11:59 WARNING (Recorder) [homeassistant.components.recorder.purge] Error purging history: (MySQLdb._exceptions.IntegrityError) (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`homeassistant`.`states`, CONSTRAINT `states_ibfk_1` FOREIGN KEY (`event_id`) REFERENCES `events` (`event_id`))')
[SQL: DELETE FROM events WHERE events.time_fired < %s]
[parameters: (datetime.datetime(2020, 7, 8, 7, 26, 53, tzinfo=<UTC>),)]
(Background on this error at: http://sqlalche.me/e/13/gkpj)

[ronaldtveen]

I've now reinstalled the MariaDB add-on 4 times in the last 10 days or so, which would correspondent with my 2 days purge setting in my recorder:

System: Raspbian GNU/Linux 10 (buster) (armv7 / raspberrypi4)
Home Assistant Core: 0.117.5
Home Assistant Supervisor: 2020.11.0

I'm running it on SSD, not a SD card.

It's not the exact same error, but close enough to think this is directly related:
home-assistant.log

 2020-11-10 20:08:19 ERROR (Recorder) [homeassistant.components.recorder] Error saving events: (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.power_consumption', '0.191', '{"unit_of_measurement": "kW", "friendly_name": "Power Consumption", "icon": "mdi:flash"}', 4985591, datetime.datetime(2020, 11, 10, 3, 12, 0, 726701, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 11, 10, 3, 12, 0, 726701, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 11, 10, 3, 12, 0, 726738, tzinfo=datetime.timezone.utc), 4950969)]
(Background on this error at: http://sqlalche.me/e/13/gkpj)
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 593, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/usr/local/lib/python3.8/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, 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`))')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/__init__.py", line 454, in _commit_event_session_or_retry
    self._commit_event_session()
  File "/usr/src/homeassistant/homeassistant/components/recorder/__init__.py", line 509, in _commit_event_session
    self.event_session.flush()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2536, in flush
    self._flush(objects)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2678, in _flush
    transaction.rollback(_capture_exception=True)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.raise_(
[..trimmed..]
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 593, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/usr/local/lib/python3.8/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)
sqlalchemy.exc.IntegrityError: (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.power_consumption', '0.191', '{"unit_of_measurement": "kW", "friendly_name": "Power Consumption", "icon": "mdi:flash"}', 4985591, datetime.datetime(2020, 11, 10, 3, 12, 0, 726701, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 11, 10, 3, 12, 0, 726701, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 11, 10, 3, 12, 0, 726738, tzinfo=datetime.timezone.utc), 4950969)]
(Background on this error at: http://sqlalche.me/e/13/gkpj)

My log is full of this and related errors, currently touching 120,000 rows in just over a day.

My recorder:

recorder:
  db_url: !secret mariadb_url
  purge_keep_days: 2
  include:
    domains:
      - sensor
      - binary_sensor
      - switch
      - light
      - media_player
      - climate

Default MariaDB config:

databases:
  - homeassistant
logins:
  - username: USER
    password: PASSWORD
rights:
  - username: USER
    database: homeassistant

[sagitt]

Same problem here

[groenmarsmannetje]

This explains why I had approx. a month of states in the MariaDB while purge days is set to 5. Dropped the database now and started with a fresh one.

[drthanwho]

This explains why I had approx. a month of states in the MariaDB while purge days is set to 5. Dropped the database now and started with a fresh one.

This was the case for me too. You probably are also getting the purging errors every night at 4:20am but haven't noticed them. As long as you have a good recorder includes/excludes you should be OK since mariadb can handle a long history. I have heard people with 1.5GB database with no issues.

[groenmarsmannetje]

I had 3 Gb of data and noticed performance issues when opening a 2 day history graph of a sensor with lot of events (every 5 seconds). Also manual purge service command was not working and querying the database with sql client was also very slow in responding.

[drthanwho]

Wow 3GB is quite a lot I guess. I would only get to 0.5GB if I'd leave it for a month. I assume you have quite a lot of sensors logging then

[adriaanh]

Same problem for me.

[tobbensoft]

I have the same issue. Following the above error, some sensors fail to get their values inserted in the db.

[duceduc]

I am having the same issue. Have tried dropping all tables and restarting of HA. Foreign key error reoccurs after 10 days after or so.

Config:

recorder:
  db_url: !secret MySQL_login
  purge_keep_days: 5
  exclude:
    domains:
     - group
     - automation
     - camera
     - media_player
     - scene
     - script
    entities:
     - sun.sun
     - sensor.date_time

System:

System Health
arch armv7l
chassis embedded
dev false
docker true
docker_version 19.03.12
hassio true
host_os HassOS 4.16
installation_type Home Assistant OS
os_name Linux
os_version 4.19.127-v7
python_version 3.8.6
supervisor 2020.11.0
timezone Asia/Tokyo
version 0.117.0
virtualenv false
Lovelace
dashboards 2
mode yaml
resources 6
views 8

Log Error:

Logger: homeassistant.components.recorder
Source: components/recorder/__init__.py:509
Integration: Recorder (documentation, issues)
First occurred: November 15, 2020, 7:01:02 PM (6678 occurrences)
Last logged: 5:51:20 AM

Error saving events: (MySQLdb._exceptions.IntegrityError) (1452, 'Cannot add or update a child row: a foreign key constraint fails (`jnzufwmy_hassio`.`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.livingroom_temperature', '23.9', '{"unit_of_measurement": "\\u00b0C", "friendly_name": "Living Room Temperature", "device_class": "temperature"}', 109324, datetime.datetime(2020, 11, 15, 10, 0, 58, 70320, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 11, 15, 10, 0, 58, 70320, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 11, 15, 10, 0, 58, 70383, tzinfo=datetime.timezone.utc), 104123)] (Background on this error at: http://sqlalche.me/e/13/gkpj)
Error saving events: (MySQLdb._exceptions.IntegrityError) (1452, 'Cannot add or update a child row: a foreign key constraint fails (`jnzufwmy_hassio`.`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.ps1_lastmessage', '2020-11-15T18:50:25.620536+09:00', '{"friendly_name": "ps1 LastMessage", "icon": "mdi:clock-check", "device_class": "timestamp"}', 108698, datetime.datetime(2020, 11, 15, 9, 50, 27, 626852, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 11, 15, 9, 50, 27, 626852, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 11, 15, 9, 50, 27, 626913, tzinfo=datetime.timezone.utc), 103766)] (Background on this error at: http://sqlalche.me/e/13/gkpj)
Error saving events: (MySQLdb._exceptions.IntegrityError) (1452, 'Cannot add or update a child row: a foreign key constraint fails (`jnzufwmy_hassio`.`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.shion_room_temperature', '19.6', '{"unit_of_measurement": "\\u00b0C", "friendly_name": "Shion Room Temperature", "icon": "mdi:thermometer"}', 112930, datetime.datetime(2020, 11, 15, 10, 42, 48, 884345, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 11, 15, 10, 42, 48, 884345, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 11, 15, 10, 42, 48, 884424, tzinfo=datetime.timezone.utc), 105963)] (Background on this error at: http://sqlalche.me/e/13/gkpj)
.
.
.
.

Log Error:

Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 593, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/usr/local/lib/python3.8/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.IntegrityError: (1452, 'Cannot add or update a child row: a foreign key constraint fails (`jnzufwmy_hassio`.`states`, CONSTRAINT `states_ibfk_2` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`))')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/__init__.py", line 454, in _commit_event_session_or_retry
    self._commit_event_session()
  File "/usr/src/homeassistant/homeassistant/components/recorder/__init__.py", line 509, in _commit_event_session
    self.event_session.flush()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2536, in flush
    self._flush(objects)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2678, in _flush
    transaction.rollback(_capture_exception=True)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.raise_(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2638, in _flush
    flush_context.execute()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 419, in execute
    n.execute_aggregate(self, set_)
.
.
.
  File "/usr/local/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/usr/local/lib/python3.8/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)
sqlalchemy.exc.IntegrityError: (MySQLdb._exceptions.IntegrityError) (1452, 'Cannot add or update a child row: a foreign key constraint fails (`jnzufwmy_hassio`.`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.tariff_31_power', '456.3', '{"unit_of_measurement": "W", "friendly_name": "Tariff 31 Power", "icon": "mdi:gauge"}', 106275, datetime.datetime(2020, 11, 15, 9, 4, 49, 7978, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 11, 15, 9, 4, 49, 7978, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 11, 15, 9, 4, 49, 8036, tzinfo=datetime.timezone.utc), 101990)]
(Background on this error at: http://sqlalche.me/e/13/gkpj)

[tobbensoft]

Just wonder if someone is looking in to this error? I can only see people try to repair the db after it's broken?

[fubar-coder]

@tobbensoft For now, I suggest that you modify the database as described by @pbarvinko in this comment.

[arjen-w]

Same issue here. Did run the DB modification, will report back in a couple of days to see if this works.

[pbarvinko]

Just to be clear: the current design of the purge process in home assistant when using postgres/mysql/mariadb physically cannot work. It's not a fault of your particular installation. It has a design flaw, which tries to delete rows that are protected by foreign key constraint.
Currently the only way to resolve it is to modify the database structure as described in the post above.

For users with mysql/mariadb, the syntax of command to change/add constraints is a bit different and described here.
You also still need to do also step 4.

[drthanwho]

Just to be clear: the current design of the purge process in home assistant when using postgres/mysql/mariadb physically cannot work. It's not a fault of your particular installation.

But then how does it only occur in some people, whereas others that use MariaDB have no issues with purging?

[pbarvinko]

@drthanwho I do not know. These are just observations from my own installation. I looked at the query and at the purge implementation and I see no way how it can work. But I am not that familiar with the code and maybe I have overlooked something. Maybe those others use a different version of the home assistant where it used to work.

[Svrbinek]

It started working for me about a week ago and since then it has been lubricating well. I didn't edit anything, I only deleted the whole db in phpmyadmin once and then it didn't work again. Finally, I tried the manual delete commands mentioned in this thread (which didn't work before) and now they worked and then it was deleted by HA itself.

[drthanwho]

@pbarvinko I know there are issues with this as far back as 6 months. But I equally know it's not the case for every installation otherwise it would be blowing up.

@Svrbinek I have only ended up deleting the db by uninstalling the add-on itself but the issue still occurs.

At the same time I'm on 21 days now with 300MB only (although surprised at the size since I have a significant amount of entities logging)

[elRadix]

I have the issue as the users shared above, so maybe not everyone is reporting the errors or looking at their logs. Some people assume that the purge is working

[Svrbinek]

Now I am sending from the db sensor. where you can see every night a drop of 100MB

[duceduc]

pbarvinko,
Hi. I am trying to patch fix this foreign key error. I am running mysql v.5.6.41-84.1 externally.
In step 2, when I add this query

ALTER TABLE states DROP CONSTRAINT states_old_state_id_fkey;

I get this error.

Error
SQL query:

ALTER TABLE states DROP CONSTRAINT states_old_state_id_fkey
MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONSTRAINT states_old_state_id_fkey' at line 1

[pbarvinko]

@duceduc Ah, sorry, my fault. I assumed that mysql syntax for constraints manipulation is the same in postgres and mysql.
It is different. Here is a nice article that shows the required mysql commands:
https://dev.to/mcgurkadam/how-to-change-a-foreign-key-constraint-in-mysql-1cma

And you still need step 4 to add index on old_state_id.

[Mikle61]

@duceduc Ah, sorry, my fault. I assumed that mysql syntax for constraints manipulation is the same in postgres and mysql.
It is different. Here is a nice article that shows the required mysql commands:
https://dev.to/mcgurkadam/how-to-change-a-foreign-key-constraint-in-mysql-1cma

And you still need step 4 to add index on old_state_id.

Thanks for the link!

The database has purge!
I Have Hass.IO on Raspberry witn MariaDB add-on.

mysql -u -p homeassistant
MariaDB [homeassistant]> SHOW CREATE TABLE statesG
******** 1. row ********
Table: states
Create Table: CREATE TABLE states (
state_id int(11) NOT NULL AUTO_INCREMENT,
domain varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
entity_id varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
state varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
attributes text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
event_id int(11) DEFAULT NULL,
last_changed datetime DEFAULT NULL,
last_updated datetime DEFAULT NULL,
created datetime DEFAULT NULL,
old_state_id int(11) DEFAULT NULL,
PRIMARY KEY (state_id),
KEY old_state_id (old_state_id),
KEY ix_states_last_updated (last_updated),
KEY ix_states_entity_id_last_updated (entity_id,last_updated),
KEY ix_states_event_id (event_id),
CONSTRAINT states_ibfk_1 FOREIGN KEY (event_id) REFERENCES events (event_id),
CONSTRAINT states_ibfk_2' FOREIGN KEY (old_state_id) REFERENCESstates(state_id`)
) ENGINE=InnoDB AUTO_INCREMENT=553 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

After the keyword "Constraint" the ngas have several keys. After that, we make the commands:

MariaDB [homeassistant]> ALTER TABLE states DROP FOREIGN KEY states_ibfk_1;
MariaDB [homeassistant]> ALTER TABLE states
-> ADD CONSTRAINT states_ibfk_1
-> FOREIGN KEY (event_id)
-> REFERENCES events (event_id)
-> ON DELETE SET NULL;
MariaDB [homeassistant]> ALTER TABLE states DROP FOREIGN KEY states_ibfk_2;
MariaDB [homeassistant]> ALTER TABLE states
-> ADD CONSTRAINT states_ibfk_2
-> FOREIGN KEY (old_state_id)
-> REFERENCES states (state_id)
-> ON DELETE SET NULL;
MariaDB [homeassistant]> CREATE INDEX ix_states_old_state_id
-> ON states (old_state_id);

after that, I called the recorder.purge service in НА and the data base was compressed!

[duceduc]

@pbarvinko Thank you much for the link and reply.
https://dev.to/mcgurkadam/how-to-change-a-foreign-key-constraint-in-mysql-1cma

I've used @Mikle61 queries above to patch fix the error. Now the wait time to see if the error comes back.

Note: Running the below query in phpmyadmin console will come back error. You can only run this in CLI to verify the foreign key constraint name. Mine just so happen to be the same as Mikle61 above.

SHOW CREATE TABLE statesG

After I found out the name(s), I went back to phpmyadmin console and paste in these blocks and hit go after each query. The connection via the CLI was flaky. That was the reason why I use phpmyadmin instead.

ALTER TABLE states DROP FOREIGN KEY states_ibfk_1;

ALTER TABLE states
ADD CONSTRAINT states_ibfk_1
FOREIGN KEY (event_id)
REFERENCES events (event_id)
ON DELETE SET NULL;

ALTER TABLE states DROP FOREIGN KEY states_ibfk_2;

ALTER TABLE states
ADD CONSTRAINT states_ibfk_2
FOREIGN KEY (old_state_id)
REFERENCES states (state_id)
ON DELETE SET NULL;

CREATE INDEX ix_states_old_state_id
ON states (old_state_id);

The last step was to manually purge in HA. Developer Tool -> Service

recorder.purge

[Mikle61]

@duceduc

... The connection via the CLI was flaky. That was the reason why I use phpmyadmin instead.

In my case, the database is running in a docker container. I use portainer.io to manage docker. It is very convenient to use the container console from the partner to manage the database.

[maxill1]

Same problem here with mariadb on docker.

recorder.purge didn't work and also deleting by hand produced and error on constraint.

Solved by dropping an recreating the constraint with ON DELETE SET NULL.

[drthanwho]

Solved by dropping an recreating the constraint with ON DELETE SET NULL.

Does this fix the issue so that recorder.purger now works normally or we have to "DELETE SET NULL" everytime the database needs to be purged?

[pbarvinko]

@drthanwho It just starts working normally. No need to repeat the sequence.

[ricreis394]

@pbarvinko Thank you much for the link and reply.
https://dev.to/mcgurkadam/how-to-change-a-foreign-key-constraint-in-mysql-1cma

I've used @Mikle61 queries above to patch fix the error. Now the wait time to see if the error comes back.

Note: Running the below query in phpmyadmin console will come back error. You can only run this in CLI to verify the foreign key constraint name. Mine just so happen to be the same as Mikle61 above.

SHOW CREATE TABLE statesG

After I found out the name(s), I went back to phpmyadmin console and paste in these blocks and hit go after each query. The connection via the CLI was flaky. That was the reason why I use phpmyadmin instead.

ALTER TABLE states DROP FOREIGN KEY states_ibfk_1;

ALTER TABLE states
ADD CONSTRAINT states_ibfk_1
FOREIGN KEY (event_id)
REFERENCES events (event_id)
ON DELETE SET NULL;

ALTER TABLE states DROP FOREIGN KEY states_ibfk_2;

ALTER TABLE states
ADD CONSTRAINT states_ibfk_2
FOREIGN KEY (old_state_id)
REFERENCES states (state_id)
ON DELETE SET NULL;

CREATE INDEX ix_states_old_state_id
ON states (old_state_id);

The last step was to manually purge in HA. Developer Tool -> Service

recorder.purge

this solved to me, thanks!

[moinmoin-sh]

I have the same purge issue as described above and I started to try some root cause analysis.

The first thing I did was checking the table creation of the 'states' table with and older version (0.115.1) and a more recent version (0.117.0). Here is what I got:

0115.1:

CREATE TABLE `states` (
  `state_id` int(11) NOT NULL AUTO_INCREMENT,
  `domain` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `entity_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `state` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `attributes` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `event_id` int(11) DEFAULT NULL,
  `last_changed` datetime DEFAULT NULL,
  `last_updated` datetime DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `old_state_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`state_id`),
  KEY `ix_states_event_id` (`event_id`),
  KEY `ix_states_last_updated` (`last_updated`),
  KEY `ix_states_entity_id_last_updated` (`entity_id`,`last_updated`),
  CONSTRAINT `states_ibfk_1` FOREIGN KEY (`event_id`) REFERENCES `events` (`event_id`)
) ENGINE=InnoDB AUTO_INCREMENT=39 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

0.117.0 :

CREATE TABLE `states` (
  `state_id` int(11) NOT NULL AUTO_INCREMENT,
  `domain` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `entity_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `state` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `attributes` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `event_id` int(11) DEFAULT NULL,
  `last_changed` datetime DEFAULT NULL,
  `last_updated` datetime DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `old_state_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`state_id`),
  KEY `old_state_id` (`old_state_id`),
  KEY `ix_states_event_id` (`event_id`),
  KEY `ix_states_last_updated` (`last_updated`),
  KEY `ix_states_entity_id_last_updated` (`entity_id`,`last_updated`),
  CONSTRAINT `states_ibfk_1` FOREIGN KEY (`event_id`) REFERENCES `events` (`event_id`),
  CONSTRAINT `states_ibfk_2` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`)
) ENGINE=InnoDB AUTO_INCREMENT=245984 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

As one can see from the two above there was something changed between the 2 versions -> there was a relationship added to the 'old_state_id' within the table itself (second row starting with 'CONSTRAINT').

I looked into the code of models.py which I found here :
https://github.com/home-assistant/core/tree/dev/homeassistant/components/recorder

in the diffs of the source code :
https://github.com/home-assistant/core/commit/d9ba32dc3fe5be8f8e242123a4109a98ce751446#diff-ecf535aee3c11066659a4a0a5f493ace168a8ee82372be63098a1b06896a4142

I found that the change was related to #40467 where it's explained why this change was made.

To me it looks like the negative impact on purge was overlooked.
May be it would help to expand the table creation in models.py by 'ON DELETE SET NULL' or apropriate.

Just my 5ct

[RefineryX]

I am also having this issue after installing MariaDB today. I am not too confident changing the tables etc and so will (hopefully) wait for an official fix.

[Mikle61]

I've been monitoring the database for 6 days. I confirm that after the above correction, the database starts functioning normally.

[RefineryX]

Is there step by step instructions I could use to correct? Or best to wait for the official fix? Is it going to cause a clash?

[fubar-coder]

@RefineryX It might cause a clash in the future, but I really hope, that the developer that fixes this problem tries to find a fix, reads the comments about the workaround and creates a patch that will not cause a clash. But for me (us) it's more more important to have a working recorder and event history than a possible conflicting DB migration in the future.

[ifajardoj]

Is there step by step instructions I could use to correct? Or best to wait for the official fix? Is it going to cause a clash?

+1