search

home-assistant / core

:house_with_garden: Open source home automation that puts local control and privacy first.
https://www.home-assistant.io
Apache License 2.0
73.43k stars 30.67k forks source link

Database Foreign Keys, heavily duplicated after upgrade to 2024.8. #124502

Open BenDirectPromos opened 2 months ago

BenDirectPromos commented 2 months ago

The problem

After upgrade, I got notifications about database upgrade, every few hours home assistant would reboot and I would get message again.

I looked in database at it seems the forienkey constraints that are getting added after upgrade are duplicating

SELECT DISTINCT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = (SELECT DATABASE())

TABLE_NAME|COLUMN_NAME |CONSTRAINT_NAME|REFERENCED_TABLE_NAME|REFERENCED_COLUMN_NAME| ----------+-------------+---------------+---------------------+----------------------+ events |data_id |events_ibfk_1 |event_data |data_id | events |event_type_id|events_ibfk_10 |event_types |event_type_id | events |data_id |events_ibfk_11 |event_data |data_id | events |event_type_id|events_ibfk_12 |event_types |event_type_id | events |data_id |events_ibfk_13 |event_data |data_id | events |event_type_id|events_ibfk_14 |event_types |event_type_id | events |data_id |events_ibfk_15 |event_data |data_id | events |event_type_id|events_ibfk_16 |event_types |event_type_id | events |data_id |events_ibfk_17 |event_data |data_id | events |event_type_id|events_ibfk_18 |event_types |event_type_id | events |data_id |events_ibfk_19 |event_data |data_id | events |event_type_id|events_ibfk_2 |event_types |event_type_id | events |event_type_id|events_ibfk_20 |event_types |event_type_id | events |data_id |events_ibfk_21 |event_data |data_id | events |event_type_id|events_ibfk_22 |event_types |event_type_id | events |data_id |events_ibfk_23 |event_data |data_id | events |event_type_id|events_ibfk_24 |event_types |event_type_id | events |data_id |events_ibfk_25 |event_data |data_id | events |event_type_id|events_ibfk_26 |event_types |event_type_id | events |data_id |events_ibfk_27 |event_data |data_id | events |event_type_id|events_ibfk_28 |event_types |event_type_id | events |data_id |events_ibfk_29 |event_data |data_id | events |data_id |events_ibfk_3 |event_data |data_id | events |event_type_id|events_ibfk_30 |event_types |event_type_id | events |data_id |events_ibfk_31 |event_data |data_id | events |event_type_id|events_ibfk_32 |event_types |event_type_id | events |data_id |events_ibfk_33 |event_data |data_id | events |event_type_id|events_ibfk_34 |event_types |event_type_id | events |data_id |events_ibfk_35 |event_data |data_id | events |event_type_id|events_ibfk_36 |event_types |event_type_id | events |data_id |events_ibfk_37 |event_data |data_id | events |event_type_id|events_ibfk_38 |event_types |event_type_id | events |data_id |events_ibfk_39 |event_data |data_id | events |event_type_id|events_ibfk_4 |event_types |event_type_id | events |event_type_id|events_ibfk_40 |event_types |event_type_id | events |data_id |events_ibfk_41 |event_data |data_id | events |event_type_id|events_ibfk_42 |event_types |event_type_id | events |data_id |events_ibfk_43 |event_data |data_id | events |event_type_id|events_ibfk_44 |event_types |event_type_id | events |data_id |events_ibfk_45 |event_data |data_id | events |event_type_id|events_ibfk_46 |event_types |event_type_id | events |data_id |events_ibfk_47 |event_data |data_id | events |event_type_id|events_ibfk_48 |event_types |event_type_id | events |data_id |events_ibfk_49 |event_data |data_id | events |data_id |events_ibfk_5 |event_data |data_id | events |event_type_id|events_ibfk_50 |event_types |event_type_id | events |data_id |events_ibfk_51 |event_data |data_id | events |event_type_id|events_ibfk_52 |event_types |event_type_id | events |data_id |events_ibfk_53 |event_data |data_id | events |event_type_id|events_ibfk_54 |event_types |event_type_id | events |data_id |events_ibfk_55 |event_data |data_id | events |event_type_id|events_ibfk_56 |event_types |event_type_id | events |data_id |events_ibfk_57 |event_data |data_id | events |event_type_id|events_ibfk_58 |event_types |event_type_id | events |data_id |events_ibfk_59 |event_data |data_id | events |event_type_id|events_ibfk_6 |event_types |event_type_id | events |event_type_id|events_ibfk_60 |event_types |event_type_id | events |data_id |events_ibfk_61 |event_data |data_id | events |event_type_id|events_ibfk_62 |event_types |event_type_id | events |data_id |events_ibfk_63 |event_data |data_id | events |event_type_id|events_ibfk_64 |event_types |event_type_id | events |data_id |events_ibfk_65 |event_data |data_id | events |event_type_id|events_ibfk_66 |event_types |event_type_id | events |data_id |events_ibfk_67 |event_data |data_id | events |event_type_id|events_ibfk_68 |event_types |event_type_id | events |data_id |events_ibfk_69 |event_data |data_id | events |data_id |events_ibfk_7 |event_data |data_id | events |event_type_id|events_ibfk_70 |event_types |event_type_id | events |data_id |events_ibfk_71 |event_data |data_id | events |event_type_id|events_ibfk_72 |event_types |event_type_id | events |data_id |events_ibfk_73 |event_data |data_id | events |event_type_id|events_ibfk_74 |event_types |event_type_id | events |event_type_id|events_ibfk_8 |event_types |event_type_id | events |data_id |events_ibfk_9 |event_data |data_id | states |old_state_id |states_ibfk_1 |states |state_id | states |metadata_id |states_ibfk_10 |states_meta |metadata_id | states |old_state_id |states_ibfk_11 |states |state_id | states |attributes_id|states_ibfk_12 |state_attributes |attributes_id | states |old_state_id |states_ibfk_13 |states |state_id | states |old_state_id |states_ibfk_14 |states |state_id | states |attributes_id|states_ibfk_15 |state_attributes |attributes_id | states |old_state_id |states_ibfk_16 |states |state_id | states |old_state_id |states_ibfk_17 |states |state_id | states |attributes_id|states_ibfk_18 |state_attributes |attributes_id | states |old_state_id |states_ibfk_19 |states |state_id | states |attributes_id|states_ibfk_2 |state_attributes |attributes_id | states |attributes_id|states_ibfk_20 |state_attributes |attributes_id | states |old_state_id |states_ibfk_21 |states |state_id | states |attributes_id|states_ibfk_22 |state_attributes |attributes_id | states |old_state_id |states_ibfk_23 |states |state_id | states |attributes_id|states_ibfk_24 |state_attributes |attributes_id | states |old_state_id |states_ibfk_25 |states |state_id | states |attributes_id|states_ibfk_26 |state_attributes |attributes_id | states |old_state_id |states_ibfk_27 |states |state_id | states |old_state_id |states_ibfk_28 |states |state_id | states |attributes_id|states_ibfk_29 |state_attributes |attributes_id | states |old_state_id |states_ibfk_3 |states |state_id | states |old_state_id |states_ibfk_30 |states |state_id | states |old_state_id |states_ibfk_31 |states |state_id | states |attributes_id|states_ibfk_32 |state_attributes |attributes_id | states |old_state_id |states_ibfk_33 |states |state_id | states |attributes_id|states_ibfk_34 |state_attributes |attributes_id | states |old_state_id |states_ibfk_35 |states |state_id | states |attributes_id|states_ibfk_36 |state_attributes |attributes_id | states |old_state_id |states_ibfk_37 |states |state_id | states |old_state_id |states_ibfk_38 |states |state_id | states |attributes_id|states_ibfk_39 |state_attributes |attributes_id | states |attributes_id|states_ibfk_4 |state_attributes |attributes_id | states |old_state_id |states_ibfk_40 |states |state_id | states |old_state_id |states_ibfk_41 |states |state_id | states |attributes_id|states_ibfk_42 |state_attributes |attributes_id | states |old_state_id |states_ibfk_43 |states |state_id | states |attributes_id|states_ibfk_44 |state_attributes |attributes_id | states |old_state_id |states_ibfk_45 |states |state_id | states |old_state_id |states_ibfk_46 |states |state_id | states |old_state_id |states_ibfk_47 |states |state_id | states |attributes_id|states_ibfk_48 |state_attributes |attributes_id | states |old_state_id |states_ibfk_49 |states |state_id | states |old_state_id |states_ibfk_5 |states |state_id | states |attributes_id|states_ibfk_50 |state_attributes |attributes_id | states |old_state_id |states_ibfk_51 |states |state_id | states |attributes_id|states_ibfk_52 |state_attributes |attributes_id | states |old_state_id |states_ibfk_53 |states |state_id | states |old_state_id |states_ibfk_54 |states |state_id | states |attributes_id|states_ibfk_55 |state_attributes |attributes_id | states |old_state_id |states_ibfk_56 |states |state_id | states |attributes_id|states_ibfk_57 |state_attributes |attributes_id | states |old_state_id |states_ibfk_58 |states |state_id | states |attributes_id|states_ibfk_59 |state_attributes |attributes_id | states |attributes_id|states_ibfk_6 |state_attributes |attributes_id | states |old_state_id |states_ibfk_60 |states |state_id | states |old_state_id |states_ibfk_7 |states |state_id | states |old_state_id |states_ibfk_8 |states |state_id | states |attributes_id|states_ibfk_9 |state_attributes |attributes_id |

What version of Home Assistant Core has the issue?

2024.8.2

What was the last working version of Home Assistant Core?

2024.7.x

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

No response

Example YAML snippet

No response

Anything in the logs that might be useful for us?

No response

Additional information

No response

BenDirectPromos commented 2 months ago

Have some more info. It seems to restart HA during the ALTER TABLE states ADD FOREIGN KEY(attributes_id) REFERENCES state_attributes (attributes_id) command, and then starts over again and reruns all foreign key adds again, and then gets stuck in this loop

BenDirectPromos commented 2 months ago

Log from last reboot home-assistant_log.txt

gabest11 commented 2 months ago

I've been upgrading from 44 to 45 since yesterday. It restarted a couple of times and I also restarted the container a few times. 125 million rows in states. It will never be done...

"TABLE_NAME"    "COLUMN_NAME"   "CONSTRAINT_NAME"   "REFERENCED_TABLE_NAME" "REFERENCED_COLUMN_NAME"
"events"    "data_id"   "events_ibfk_1" "event_data"    "data_id"
"events"    "event_type_id" "events_ibfk_2" "event_types"   "event_type_id"
"events"    "data_id"   "events_ibfk_3" "event_data"    "data_id"
"events"    "event_type_id" "events_ibfk_4" "event_types"   "event_type_id"
"events"    "data_id"   "events_ibfk_5" "event_data"    "data_id"
"events"    "event_type_id" "events_ibfk_6" "event_types"   "event_type_id"
"events"    "data_id"   "events_ibfk_7" "event_data"    "data_id"
"events"    "event_type_id" "events_ibfk_8" "event_types"   "event_type_id"
"states"    "old_state_id"  "states_ibfk_1" "states"    "state_id"
"states"    "attributes_id" "states_ibfk_2" "state_attributes"  "attributes_id"
"states"    "metadata_id"   "states_ibfk_3" "states_meta"   "metadata_id"

Finally done, ended up with a couple of extra keys on the states table, too. If only these were not executed separately (alter table can do more keys in a single statement), it would not have to copy the whole table three times.

ALTER TABLE states ADD FOREIGN KEY(old_state_id) REFERENCES states (state_id);
ALTER TABLE states ADD FOREIGN KEY(attributes_id) REFERENCES state_attributes (attributes_id);
ALTER TABLE states ADD FOREIGN KEY(metadata_id) REFERENCES states_meta (metadata_id);
chemelli74 commented 2 months ago

which database are you using ?