Reduce space needed to store data for MySQL/MariaDB

[bdraco]

Proposed change

These are the same improvements as #90246 and #91150 but for existing databases instead of only new databases

This PR takes care of all the ones that are done as part of the schema migration. The ones that rely on background migration or purge over time will need to be done with a different solution in future PRs:

After all legacy events are gone from the db, we can do the following:

• states.attributes
• states.event_id
• events.event_data
• events.origin

Can be altered when migration task finishes, but not before

• events.event_type
• events.context_id
• events.context_user_id
• events.context_parent_id
• states.context_id
• states.context_user_id
• states.context_parent_id
• states.entity_id

Type of change

• [ ] Dependency upgrade
• [ ] Bugfix (non-breaking change which fixes an issue)
• [ ] New integration (thank you!)
• [ ] New feature (which adds functionality to an existing integration)
• [ ] Deprecation (breaking change to happen in the future)
• [ ] Breaking change (fix/feature causing existing functionality to break)
• [x] Code quality improvements to existing code or addition of tests

Additional information

• This PR fixes or closes issue: fixes #
• This PR is related to issue:
• Link to documentation pull request:

Checklist

• [ ] The code change is tested and works locally.
• [ ] Local tests pass. Your PR cannot be merged unless tests pass
• [ ] There is no commented out code in this PR.
• [ ] I have followed the development checklist
• [ ] I have followed the perfect PR recommendations
• [ ] The code has been formatted using Black (black --fast homeassistant tests)
• [ ] Tests have been added to verify that the new code works.

If user exposed functionality or configuration variables are added/changed:

• [ ] Documentation added/updated for www.home-assistant.io

If the code communicates with devices, web services, or third-party tools:

• [ ] The manifest file has all fields filled out correctly.
  Updated and included derived files by running: python3 -m script.hassfest.
• [ ] New or updated dependencies have been added to requirements_all.txt.
  Updated by running python3 -m script.gen_requirements_all.
• [ ] For the updated dependencies - a link to the changelog, or at minimum a diff between library versions is added to the PR description.
• [ ] Untested files have been added to .coveragerc.

To help with the load of incoming pull requests:

• [ ] I have reviewed two other open pull requests in this repository.

[home-assistant[bot]]

Hey there @home-assistant/core, mind taking a look at this pull request as it has been labeled with an integration (recorder) you are listed as a code owner for? Thanks!

Code owner commands

Code owners of recorder can trigger bot actions by commenting:

• @home-assistant close Closes the pull request.
• @home-assistant rename Awesome new title Renames the pull request.
• @home-assistant reopen Reopen the pull request.
• @home-assistant unassign recorder Removes the current integration label and assignees on the pull request, add the integration domain after the command.

[bdraco]

It looks like we can't ignore on postgresql so we may need to not do them now

[bdraco]

Glad we added MySQL to the ci.

Will do testing with local MySQL

This one is going to take a bit longer so will come back to it

[bdraco]

sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'created' in 'states'")
[SQL: ALTER IGNORE TABLE states MODIFY created CHAR(0)]
(Background on this error at: https://sqlalche.me/e/20/e3q8)

Need to make these as non-critical like we do with index

[bdraco]

with bookworm out the door and supervisor support dropping old versions with old SQLite we can finally bump the minimum SQLite version and than drop all the old columns which would negate the need for this pr. That’s assuming that raising the minimum SQLite version is viable.

Keeping this warm in the mean time as its a good canary to show when we access a column we shouldn’t be accessing.

[bdraco]

I think the best path forward is going to have a check at startup that deletes unused columns after we drop old SQLite versions

[github-actions[bot]]

There hasn't been any activity on this pull request recently. This pull request has been automatically marked as stale because of that and will be closed if no further activity occurs within 7 days. Thank you for your contributions.

[github-actions[bot]]

There hasn't been any activity on this pull request recently. This pull request has been automatically marked as stale because of that and will be closed if no further activity occurs within 7 days. If you are the author of this PR, please leave a comment if you want to keep it open. Also, please rebase your PR onto the latest dev branch to ensure that it's up to date with the latest changes. Thank you for your contribution!

[bdraco]

still need to come back to this.

[github-actions[bot]]

There hasn't been any activity on this pull request recently. This pull request has been automatically marked as stale because of that and will be closed if no further activity occurs within 7 days. If you are the author of this PR, please leave a comment if you want to keep it open. Also, please rebase your PR onto the latest dev branch to ensure that it's up to date with the latest changes. Thank you for your contribution!