core: Historic data migration fails and leads to wipe of the database

The problem

Originally I tried to replace a Shelly measuring device for my solar panels and wanted to keep the historic data. I followed the steps listed in the energy dashboard faq.

Error executing query: (sqlite3.IntegrityError) UNIQUE constraint failed: statistics_meta.statistic_id [SQL: UPDATE statistics_meta SET statistic_id=? WHERE statistics_meta.statistic_id = ? AND statistics_meta.source = ?] [parameters: ('sensor.shelly_pv_sandkiste_energy', 'sensor.shelly_pv_sandkiste_switch_0_energy', 'recorder')] (Background on this error at: https://sqlalche.me/e/20/gkpj)
Error executing query: (sqlite3.IntegrityError) UNIQUE constraint failed: statistics_meta.statistic_id [SQL: UPDATE statistics_meta SET statistic_id=? WHERE statistics_meta.statistic_id = ? AND statistics_meta.source = ?] [parameters: ('sensor.shelly_pv_sandkiste_power', 'sensor.shelly_pv_sandkiste_switch_0_power', 'recorder')] (Background on this error at: https://sqlalche.me/e/20/gkpj)
Error executing query: (sqlite3.IntegrityError) UNIQUE constraint failed: statistics_meta.statistic_id [SQL: UPDATE statistics_meta SET statistic_id=? WHERE statistics_meta.statistic_id = ? AND statistics_meta.source = ?] [parameters: ('sensor.shelly_pv_sandkiste_current', 'sensor.shelly_pv_sandkiste_switch_0_current', 'recorder')] (Background on this error at: https://sqlalche.me/e/20/gkpj)
Error executing query: (sqlite3.IntegrityError) UNIQUE constraint failed: statistics_meta.statistic_id [SQL: UPDATE statistics_meta SET statistic_id=? WHERE statistics_meta.statistic_id = ? AND statistics_meta.source = ?] [parameters: ('sensor.shelly_pv_sandkiste_voltage', 'sensor.shelly_pv_sandkiste_switch_0_voltage', 'recorder')] (Background on this error at: https://sqlalche.me/e/20/gkpj)
Error executing query: (sqlite3.IntegrityError) UNIQUE constraint failed: statistics_meta.statistic_id [SQL: UPDATE statistics_meta SET statistic_id=? WHERE statistics_meta.statistic_id = ? AND statistics_meta.source = ?] [parameters: ('sensor.shelly_pv_sandkiste_device_temperature', 'sensor.shelly_pv_sandkiste_switch_0_device_temperature', 'recorder')] (Background on this error at: https://sqlalche.me/e/20/gkpj)

sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: statistics_meta.statistic_id
[SQL: UPDATE statistics_meta SET statistic_id=? WHERE statistics_meta.statistic_id = ? AND statistics_meta.source = ?]
[parameters: ('sensor.shelly_pv_sandkiste_energy', 'sensor.shelly_pv_sandkiste_switch_0_energy', 'recorder')]
(Background on this error at: https://sqlalche.me/e/20/gkpj)

After a while energy dashboard is empty and full backup size decrease dramatically.

The system will rename the corrupt database file //config/.data/home-assistant_v2.db to //config/.data/home-assistant_v2.db.corrupt.2024-02-21T10:40:39.660703+00:00 in order to allow startup to proceed

Unrecoverable sqlite3 database corruption detected: (sqlite3.IntegrityError) FOREIGN KEY constraint failed [SQL: INSERT INTO states (entity_id, state, attributes, event_id, last_changed, last_changed_ts, last_updated, last_updated_ts, old_state_id, attributes_id, context_id, context_user_id, context_parent_id, origin_idx, context_id_bin, context_user_id_bin, context_parent_id_bin, metadata_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING state_id] [parameters: (None, '419507912', None, None, None, None, None, 1708512039.086579, 253121509, 9317138, None, None, None, 0, <memory at 0x7f76256d40>, None, None, 63)] (Background on this error at: https://sqlalche.me/e/20/gkpj)

What concerns me is that this did not came to my attention earlier as there was no warning about the renaming. I think this should be raised to the user immediately to avoid data loss. I noticed only after 4 days.

What version of Home Assistant Core has the issue?

core-24-02

What was the last working version of Home Assistant Core?

No response

What type of installation are you running?

Home Assistant OS

Integration causing the issue

No response

Link to integration documentation on our website

No response

Diagnostics information

home-assistant_2024-02-21T11-16-04.631Z.log

Example YAML snippet

No response

Anything in the logs that might be useful for us?

No response

Additional information

No response