recorder corrupts database after purge

The problem

After recorder run the Database is corrupted. The error has already occurred twice. I deleted the database. Exactly after 7 days the error occurs again. purge_keep_days is set to 7d

What version of Home Assistant Core has the issue?

core-2021.12.10

What was the last working version of Home Assistant Core?

unknown

What type of installation are you running?

Home Assistant OS

Integration causing the issue

Recorder

Link to integration documentation on our website

https://www.home-assistant.io/integrations/recorder/

Example YAML snippet

service: recorder.purge
data:
  keep_days: 7
  repack: true
  apply_filter: true

Anything in the logs that might be useful for us?

[SQL: SELECT states.domain AS states_domain, states.entity_id AS states_entity_id, states.state AS states_state, states.attributes AS states_attributes, states.last_changed AS states_last_changed, states.last_updated AS states_last_updated 
FROM states 
WHERE (states.domain IN (%s, %s, %s, %s, %s) OR states.last_changed = states.last_updated) AND states.last_updated > %s AND states.entity_id IN (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) AND states.last_updated < %s ORDER BY states.entity_id, states.last_updated]
[parameters: ('climate', 'device_tracker', 'humidifier', 'thermostat', 'water_heater', datetime.datetime(2022, 1, 24, 22, 44, 59, 999999, tzinfo=datetime.timezone.utc), 'sensor.average_cpu_load_raspberry_pi_hass', 'sensor.average_cpu_load_raspberry_pi_pihole', 'sensor.average_cpu_load_qnap_ts453', 'sensor.average_cpu_load_camsrvstj', 'sensor.openweathermap75378_feels_like_temperature', 'sensor.openweathermap75378_cloud_coverage', 'sensor.openweathermap75378_rain', 'sensor.openweathermap75378_snow', 'sensor.openweathermap75378_uv_index', 'sensor.openweathermap77740_feels_like_temperature', 'sensor.openweathermap77740_cloud_coverage', 'sensor.openweathermap77740_rain', 'sensor.openweathermap77740_snow', 'sensor.openweathermap77740_uv_index', 'sensor.e_niro_ev_battery', 'sensor.e_niro_range_by_ev', 'sensor.e_niro_range_total', 'sensor.schlafzimmer_humidity', 'sensor.badezimmer_humidity', 'sensor.power_production_now_2', 'sensor.current_ozone_level', 'sensor.current_uv_index', 'sensor.max_uv_index', 'sensor.skin_type_1_safe_exposure_time', 'sensor.skin_type_2_safe_exposure_time', 'sensor.skin_type_3_safe_exposure_time', 'sensor.skin_type_4_safe_exposure_time', 'sensor.skin_type_5_safe_exposure_time', 'sensor.skin_type_6_safe_exposure_time', 'sensor.kuche_temperature', 'sensor.kuche_power_consumption', 'sensor.badezimmer_temperature', 'sensor.badezimmer_power_consumption', 'sensor.infrarotheizung_temperature', 'sensor.infrarotheizung_power_consumption', 'sensor.luftentfeuchter_temperature', 'sensor.luftentfeuchter_power_consumption', 'sensor.waschmaschine_temperature', 'sensor.waschmaschine_power_consumption', 'sensor.trockner_temperature', 'sensor.trockner_power_consumption', 'sensor.mfc_7360n_page_counter', 'sensor.mfc_7360n_drum_remaining_life', 'sensor.mfc_7360n_black_toner_remaining', 'sensor.aqara_tur_wohnzimmer_battery_level', 'sensor.tur_wohnzimmer_temperature', 'sensor.aqara_tur_schlafzimmer_battery_level', 'sensor.tur_schlafzimmer_temperature', 'sensor.aqara_tur_kuche_battery_level', 'sensor.tur_kuche_temperature', 'sensor.aqara_tur_bad_battery_level', 'sensor.tur_bad_temperature', 'sensor.zauberwurfel_battery_level', 'sensor.zauberwurfel_temperature', 'sensor.tradfri_shortcut_button_01_battery_level', 'sensor.tradfri_shortcut_button_02_battery_level', 'sensor.tradfri_shortcut_button_03_battery_level', 'sensor.tradfri_shortcut_button_04_battery_level', 'sensor.tradfri_shortcut_button_05_battery_level', 'sensor.tradfri_shortcut_button_07_battery_level', 'sensor.disk_use_percent', 'sensor.disk_free', 'sensor.memory_use_percent', 'sensor.memory_free', 'sensor.load_1m', 'sensor.load_5m', 'sensor.load_15m', 'sensor.processor_use', 'sensor.rpi_hass_cpu_use', 'sensor.rpi_pihole_cpu_use', 'sensor.qnap_ts453_cpu_use', 'sensor.temperatur_balkon_stat', 'sensor.chinese_air_quality_index', 'sensor.u_s_air_quality_index', 'sensor.chinese_air_quality_index_2', 'sensor.u_s_air_quality_index_2', 'sensor.qnapups_load', 'sensor.qnapups_current_apparent_power', 'sensor.qnapups_current_real_power', 'sensor.qnapups_battery_charge', 'sensor.qnapups_input_voltage', 'sensor.qnapups_input_line_frequency', 'sensor.qnapups_output_voltage', 'sensor.qnapups_output_frequency', 'sensor.speedtest_ping', 'sensor.speedtest_download', 'sensor.speedtest_upload', 'sensor.lenovo_tb_x606f_bluetooth_verbindung', 'sensor.lenovo_tb_x606f_wlan_signalstarke', 'sensor.lenovo_tb_x606f_interner_speicher', 'sensor.lenovo_tb_x606f_externer_speicher', 'sensor.lenovo_tb_x606f_helligkeitssensor', 'sensor.lenovo_tb_x606f_app_speicher', 'sensor.mi_10_akkufullstand', 'sensor.mi_10_bluetooth_verbindung', 'sensor.mi_10_wlan_signalstarke', 'sensor.mi_10_wlan_frequenz', 'sensor.mi_10_wlan_signalstarke_2', 'sensor.mi_10_interner_speicher', 'sensor.mi_10_externer_speicher', 'sensor.mi_10_helligkeitssensor', 'sensor.mi_10_drucksensor', 'sensor.mi_10_app_speicher', 'sensor.mi_10_anzahl_aktivier_benachrichtigungen', 'sensor.mi_10_sleep_confidence', 'sensor.mi_10_batterietemperatur', 'sensor.taq_10g_batterietemperatur', 'sensor.taq_10g_app_speicher', 'sensor.taq_10g_bluetooth_verbindung', 'sensor.taq_10g_wlan_geschwindigkeit', 'sensor.taq_10g_wlan_frequenz', 'sensor.taq_10g_wlan_signalstarke', 'sensor.taq_10g_externer_speicher', 'sensor.in2023_app_speicher', 'sensor.in2023_akkufullstand', 'sensor.in2023_batterietemperatur', 'sensor.in2023_helligkeitssensor', 'sensor.in2023_wlan_geschwindigkeit', 'sensor.in2023_wlan_frequenz', 'sensor.in2023_wlan_signalstarke', 'sensor.in2023_anzahl_aktiver_benachrichtigungen', 'sensor.in2023_interner_speicher', 'sensor.in2023_externer_speicher', 'sensor.wohnzimmer_iaq_index', 'sensor.wohnzimmer_iaq_level', 'sensor.temperatur_wohnzimmer', 'sensor.luftfeuchtigkeit_wohnzimmer', 'sensor.wifi_signal_esp8266_wohnzimmer', 'sensor.temperatur_kuche', 'sensor.luftfeuchtigkeit_kuche', 'sensor.wifi_signal_esp8266_kuche', 'sensor.temperatur_bad', 'sensor.luftfeuchtigkeit_bad', 'sensor.wifi_signal_esp8266_bad', 'sensor.temperatur_schlafzimmer', 'sensor.luftfeuchtigkeit_schlafzimmer', 'sensor.wifi_signal_esp8266_schlafzimmer', 'sensor.eco2_value_schlafzimmer', 'sensor.total_volatile_organic_compound_schlafzimmer', 'sensor.temperatur_keller', 'sensor.luftfeuchtigkeit_keller', 'sensor.wifi_signal_esp8266_keller', 'sensor.wifi_signal_esp8266_esszimmer', 'sensor.eco2_value_esszimmer', 'sensor.total_volatile_organic_compound_esszimmer', 'sensor.bme680_esszimmer_temperatur', 'sensor.bme680_esszimmer_luftdruck', 'sensor.bme680_esszimmer_luftfeuchtigkeit', 'sensor.bme680_esszimmer_gas_resistance', 'sensor.bme680_esszimmer_iaq', 'sensor.bme680_esszimmer_co2_equivalent', 'sensor.bme680_esszimmer_breath_voc_equivalent', 'sensor.wifi_signal_bme680_esszimmer', 'sensor.temperatur_balkon', 'sensor.luftdruck_balkon', 'sensor.luftfeuchtigkeit_balkon', 'sensor.kanal_1_rot_tcs34725_balkon', 'sensor.kanal_2_grun_tcs34725_balkon', 'sensor.kanal_3_blau_tcs34725_balkon', 'sensor.kanal_4_klar_tcs34725_balkon', 'sensor.helligkeit_tcs34725_balkon', 'sensor.farbtemperatur_tcs34725_balkon', 'sensor.wifi_signal_esp8266_balkon', 'sensor.wifi_signal_esp8266_oled_display_schlafzimmer', 'sensor.feinstaub_1_0um_pms5003st_wohnzimmer', 'sensor.feinstaub_2_5um_pms5003st_wohnzimmer', 'sensor.feinstaub_10_0um_pms5003st_wohnzimmer', 'sensor.temperatur_pms5003st_wohnzimmer', 'sensor.luftfeuchtigkeit_pms5003st_wohnzimmer', 'sensor.formaldehyd_pms5003st_wohnzimmer', 'sensor.wifi_signal_esp8266_pms5003st_wohnzimmer', 'sensor.helligkeit_balkon', 'sensor.wifi_signal_esp8266_balkon_2', 'sensor.wifi_signal_esp8266_neopixel_light_wohnzimmer', 'sensor.wifi_signal_esp8266_oled_display_wohnzimmer', 'sensor.wifi_signal_esp8266_neopixel_light_schlafzimmer', 'sensor.feinstaubsensor_balkon_temperature', 'sensor.fritz_box_7590_upload_throughput', 'sensor.feinstaubsensor_balkon_humidity', 'sensor.fritz_box_7590_download_throughput', 'sensor.shelly_flood_c8b3cd_temperature', 'sensor.shelly_flood_c8b3cd_battery', 'sensor.shelly_flood_c8aa26_temperature', 'sensor.shelly_flood_c8aa26_battery', 'sensor.wifi_signal_thdm01', 'sensor.temperatur_thdm01', 'sensor.luftfeuchtigkeit_thdm01', 'sensor.wifi_signal_thdm02', 'sensor.temperatur_thdm02', 'sensor.luftfeuchtigkeit_thdm02', 'sensor.shelly_1_84cca8a8a4c7_rssi', 'sensor.shelly_1_c45bbe5ff2fe_rssi', 'sensor.wifi_signal_thm_02_77740_2', 'sensor.vcc_spannung_thm_02_77740_2', 'sensor.temperatur_thm_02_77740', 'sensor.luftfeuchtigkeit_thm_02_77740', 'sensor.temperatur_thm_01_bhstj', 'sensor.luftfeuchtigkeit_thm_01_bhstj', 'sensor.wifi_signal_thm_01_bhstj', 'sensor.vcc_spannung_thm_01_bhstj', 'sensor.temperatur_thm_02_bhstj', 'sensor.luftfeuchtigkeit_thm_02_bhstj', 'sensor.wifi_signal_thm_02_bhstj', 'sensor.vcc_spannung_thm_02_bhstj', 'sensor.temperatur_thm_03_bhstj', 'sensor.luftfeuchtigkeit_thm_03_bhstj', 'sensor.wifi_signal_thm_03_bhstj', 'sensor.vcc_spannung_thm_03_bhstj', 'sensor.wifi_signal_thm_01_77740', 'sensor.temperatur_thm_01_77740', 'sensor.luftfeuchtigkeit_thm_01_77740', 'sensor.vcc_spannung_thm_01_77740', 'sensor.temperatur_dallas_thdvcm_01_77740', 'sensor.temperatur_thdvcm_01_77740', 'sensor.luftfeuchtigkeit_thdvcm_01_77740', 'sensor.bus_spannung_thdvcm_01_77740', 'sensor.wifi_signal_thdvcm_01_77740', 'sensor.shunt_spannung_thdvcm_01_77740', 'sensor.stromverbruch_thdvcm_01_77740', 'sensor.leistung_thdvcm_01_77740', 'sensor.vcc_spannung_thdvcm_01_77740', 'sensor.unavailable_entities', 'sensor.entities_with_state_unavailable', 'sensor.entities_with_state_unknown', 'sensor.entities_with_state_none', 'sensor.tradfri_shortcut_button_06_battery_level', 'sensor.co2_intensity', 'sensor.grid_fossil_fuel_percentage', datetime.datetime(2022, 1, 24, 22, 50, tzinfo=datetime.timezone.utc))]
(Background on this error at: https://sqlalche.me/e/14/e3q8)
2022-01-24 23:50:12 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: Can't reconnect until invalid transaction is rolled back. (Background on this error at: https://sqlalche.me/e/14/8s2b)
2022-01-24 23:50:12 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: Can't reconnect until invalid transaction is rolled back. (Background on this error at: https://sqlalche.me/e/14/8s2b)
2022-01-24 23:50:12 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: Can't reconnect until invalid transaction is rolled back. (Background on this error at: https://sqlalche.me/e/14/8s2b)
2022-01-24 23:50:12 ERROR (Recorder) [homeassistant.components.recorder] SQLAlchemyError error processing event StatisticsTask(start=datetime.datetime(2022, 1, 24, 22, 45, tzinfo=datetime.timezone.utc)): Can't reconnect until invalid transaction is rolled back. (Background on this error at: https://sqlalche.me/e/14/8s2b)
Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/__init__.py", line 705, in _process_one_event_or_recover
    if self._process_one_task(event):
  File "/usr/src/homeassistant/homeassistant/components/recorder/__init__.py", line 843, in _process_one_task
    self._run_statistics(event.start)
  File "/usr/src/homeassistant/homeassistant/components/recorder/__init__.py", line 796, in _run_statistics
    if statistics.compile_statistics(self, start):
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 424, in wrapper
    return job(instance, *args, **kwargs)
  File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 407, in compile_statistics
    platform_stat = platform.compile_statistics(instance.hass, start, end)
  File "/usr/src/homeassistant/homeassistant/components/sensor/recorder.py", line 409, in compile_statistics
    result = _compile_statistics(hass, session, start, end)
  File "/usr/src/homeassistant/homeassistant/components/sensor/recorder.py", line 448, in _compile_statistics
    _history_list = history.get_significant_states_with_session(  # type: ignore
  File "/usr/src/homeassistant/homeassistant/components/recorder/history.py", line 123, in get_significant_states_with_session
    states = execute(
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 129, in execute
    result = list(qry)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/ext/baked.py", line 382, in __iter__
    return self._iter().__iter__()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/ext/baked.py", line 412, in _iter
    result = self.session.execute(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1689, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1614, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 325, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1481, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1700, in _execute_context
    conn = self._revalidate_connection()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 565, in _revalidate_connection
    self._invalid_transaction()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 537, in _invalid_transaction
    raise exc.PendingRollbackError(
sqlalchemy.exc.PendingRollbackError: Can't reconnect until invalid transaction is rolled back. (Background on this error at: https://sqlalche.me/e/14/8s2b)

Additional information

After that error, all Statistics Entities show empty values. Log is full of errors like that:

`MySQLdb._exceptions.OperationalError: (2002, "Can't connect to MySQL server on 'core-mariadb' (115)")

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 707, in _process_one_event_or_recover self._process_one_event(event) File "/usr/src/homeassistant/homeassistant/components/recorder/init.py", line 869, in _process_one_event self._send_keep_alive() File "/usr/src/homeassistant/homeassistant/components/recorder/init.py", line 1008, in _send_keep_alive self.event_session.connection().scalar(select([1])) File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1519, in connection return self._connection_for_bind( File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1529, in _connection_for_bind return self._transaction._connection_for_bind( File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 747, in _connection_for_bind conn = bind.connect() File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3197, in connect return self._connection_cls(self, close_with_result=close_with_result) File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 96, in init else engine.raw_connection() File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3276, in raw_connection return self._wrap_pool_connect(self.pool.connect, _connection) File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3246, in _wrap_pool_connect Connection._handle_dbapi_exception_noconnection( File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2100, in _handle_dbapi_exceptionnoconnection util.raise( File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_ raise exception File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3243, in _wrap_pool_connect return fn() File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 310, in connect return _ConnectionFairy._checkout(self) File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 868, in _checkout fairy = _ConnectionRecord.checkout(pool) File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 481, in checkout rec._checkin_failed(err, _fairy_wascreated=False) File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 70, in exit compat.raise( File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_ raise exception File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 478, in checkout dbapi_connection = rec.get_connection() File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 636, in getconnection self.connect() File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 666, in connect pool.logger.debug("Error on connect(): %s", e) File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 70, in exit compat.raise( File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_ raise exception File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 661, in __connect self.dbapi_connection = connection = pool._invoke_creator(self) File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/create.py", line 590, in connect return dialect.connect(*cargs, cparams) File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 584, in connect return self.dbapi.connect(*cargs, *cparams) File "/usr/local/lib/python3.9/site-packages/MySQLdb/init.py", line 130, in Connect return Connection(args, kwargs) File "/usr/local/lib/python3.9/site-packages/MySQLdb/connections.py", line 185, in init super().init(*args, **kwargs2) sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (2002, "Can't connect to MySQL server on 'core-mariadb' (115)") (Background on this error at: https://sqlalche.me/e/14/e3q8)`

A database check with Heidi SQL says Database is corrupted (Table events) I deleted the DB and restart HA.

home-assistant / core