8000 Use LONGTEXT column instead of TEXT for MySQL/MariaDB and migrate existing databases · Pull Request #47026 · home-assistant/core · GitHub
[go: up one dir, main page]
More Web Proxy on the site http://driver.im/
Skip to content

Use LONG 8000 TEXT column instead of TEXT for MySQL/MariaDB and migrate existing databases #47026

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Merged
merged 1 commit into from Mar 10, 2021
Merged

Use LONGTEXT column instead of TEXT for MySQL/MariaDB and migrate existing databases #47026

merged 1 commit into from Mar 10, 2021

Conversation

ghost
Copy link
@ghost ghost commented Feb 24, 2021 •
edited by bdraco
Loading

Proposed change

There are some issues reported with MySQL/MariaDB databases having SQL exceptions in the HA log like "Data too long for column".
The mentioned columns are attributes in the states table and event_data in the events table. Both columns are of type TEXT, which is pretty limited in maximum size for MySQL/MariaDB.

SQLAlchemy has the ability to force a specific column type for a particular DBMS.
If we force LONGTEXT in case of MySQL/MariaDB it will be more similar to other DBMSes TEXT-like columns.
The maximum length for LONGTEXT is 2^32-1, the maximum length for the current TEXT only 2^16-1.

A migration is necessary to change the column type for existing databases.

Changes:

  1. Set the attributes column (states table) and event_data column (events table) column type to LONGTEXT for new databases based on MySQL/MariaDB.
  2. Add a schema migration for existing MySQL/MariaDB databases to convert the attributes column (states table) and event_data column (events table) column type from TEXT to LONGTEXT.

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)
  • Breaking change (fix/feature causing existing functionality to break)
  • Code quality improvements to existing code or addition of tests

Additional information

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
  • 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:

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.
  • Untested files have been added to .coveragerc.

The integration reached or maintains the following Integration Quality Scale:

  • No score or internal
  • 🥈 Silver
  • 🥇 Gold
  • 🏆 Platinum

To help with the load of incoming pull requests:

@bdraco
Copy link
Member
bdraco commented Feb 27, 2021

Did you test handling the case where the alter happens twice because something goes wrong and the schema version doesn’t update ?

@ghost
Copy link
Author
ghost commented Feb 27, 2021

Yes, the query result is still OK (0 modified rows). It does not result in an exception and the schema version is still updated.

@bdraco
Copy link
Member
bdraco commented Feb 27, 2021

Thanks. I'll try to give this a shot later this week when I get back to working on recorder

@bdraco bdraco self-requested a review February 27, 2021 20:03
bdraco
bdraco approved these changes Mar 10, 2021
View reviewed changes
Copy link
Member
@bdraco bdraco left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

I've been running this for a week without issues. I verified the upgrade was successful on a mysql install as well.

@bdraco bdraco merged commit 7c88512 into home-assistant:dev Mar 10, 2021
This was referenced Mar 11, 2021
Closed
Merged
@github-actions github-actions bot locked and limited conversation to collaborators Mar 11, 2021
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Reviewers
No reviews
Assignees
No one assigned
Labels
bugfix cla-signed core integration: recorder
Projects
None yet
Milestone
No milestone
2 participants
@bdraco @homeassistant
0