Recorder error with MySQL 2024.4.0 - Specified key was too long

[astrosteve0]

The problem

I updated from what was the latest version to 2024.4.0 this morning, and the recorder fails. This was working great before, and the only update I did was to 2024.4.0, using HA on W11 inside a VM VirtualBox. I run the database on MySQL 8.3.0.

What version of Home Assistant Core has the issue?

2024.4.0

What was the last working version of Home Assistant Core?

2024.3.3

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

Diagnostics information

No response

Example YAML snippet

No response

Anything in the logs that might be useful for us?

2024-04-04 10:16:32.033 ERROR (Recorder) [homeassistant.components.recorder.core] Error during connection setup: (MySQLdb.OperationalError) (1071, 'Specified key was too long; max key length is 1000 bytes')
[SQL:
CREATE TABLE migration_changes (
migration_id VARCHAR(255) NOT NULL,
version SMALLINT NOT NULL,
PRIMARY KEY (migration_id)
)
]
(Background on this error at: https://sqlalche.me/e/20/e3q8) (retrying in 3 seconds)
Traceback (most recent call last):
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1971, in _exec_single_context
self.dialect.do_execute(
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 919, in do_execute
cursor.execute(statement, parameters)
File "/usr/local/lib/python3.12/site-packages/MySQLdb/cursors.py", line 179, in execute
res = self._query(mogrified_query)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/MySQLdb/cursors.py", line 330, in _query
db.query(q)
File "/usr/local/lib/python3.12/site-packages/MySQLdb/connections.py", line 261, in query
_mysql.connection.query(self, query)
MySQLdb.OperationalError: (1071, 'Specified key was too long; max key length is 1000 bytes')
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 940, in _setup_recorder
self._setup_connection()
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 1443, in _setup_connection
Base.metadata.create_all(self.engine)
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/schema.py", line 5825, in create_all
bind._run_ddl_visitor(
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 3255, in _run_ddl_visitor
conn._run_ddl_visitor(visitorcallable, element, **kwargs)
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2461, in _run_ddl_visitor
visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/visitors.py", line 664, in traverse_single
return meth(obj, **kw)
^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/ddl.py", line 918, in visit_metadata
self.traverse_single(
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/visitors.py", line 664, in traverse_single
return meth(obj, **kw)
^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/ddl.py", line 956, in visit_table
)._invoke_with(self.connection)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/ddl.py", line 314, in _invoke_with
return bind.execute(self)
^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1422, in execute
return meth(
^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/ddl.py", line 180, in _execute_on_connection
return connection._execute_ddl(
^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1533, in _execute_ddl
ret = self._execute_context(
^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1850, in _execute_context
return self._exec_single_context(
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1990, in _exec_single_context
self._handle_dbapi_exception(
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2357, in _handle_dbapi_exception
raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1971, in _exec_single_context
self.dialect.do_execute(
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 919, in do_execute
cursor.execute(statement, parameters)
File "/usr/local/lib/python3.12/site-packages/MySQLdb/cursors.py", line 179, in execute
res = self._query(mogrified_query)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/MySQLdb/cursors.py", line 330, in _query
db.query(q)
File "/usr/local/lib/python3.12/site-packages/MySQLdb/connections.py", line 261, in query
_mysql.connection.query(self, query)
sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1071, 'Specified key was too long; max key length is 1000 bytes')

Additional information

No response

[home-assistant]

Hey there @home-assistant/core, mind taking a look at this issue 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 issue.
• @home-assistant rename Awesome new title Renames the issue.
• @home-assistant reopen Reopen the issue.
• @home-assistant unassign recorder Removes the current integration label and assignees on the issue, add the integration domain after the command.
• @home-assistant add-label needs-more-information Add a label (needs-more-information, problem in dependency, problem in custom component) to the issue.
• @home-assistant remove-label needs-more-information Remove a label (needs-more-information, problem in dependency, problem in custom component) on the issue.

_{^{(message by CodeOwnersMention)}}

---

recorder documentation
recorder source
_{^{(message by IssueLinks)}}

[bdraco]

https://www.home-assistant.io/integrations/recorder#mariadb-and-mysql

Make sure you are using innodb

myiasm is not supporyed

[astrosteve0]

When I switched to MySQL, I hadn't seen those instructions in your link, so did none of that. I checked several of the tables in phpMyAdmin, and they are innodb already. Then I thought I'd run the create table statement HA was failing on right inside phpMyAdmin, only I added the ENGINE and CHARSET statements as below:

CREATE TABLE migration_changes (
migration_id VARCHAR(255) NOT NULL,
version SMALLINT NOT NULL,
PRIMARY KEY (migration_id)
)
ENGINE=INNODB DEFAULT CHARSET=utf8mb4

The above worked fine with no errors. I rebooted HA, and it also added a field to the table "states". One more thing to check, I changed the name of the table to create to something else, and removed the ENGINE and CHARSET statements, and then it failed with the same error I got in HA. I think you just need to add the ENGINE and CHARSET statements to your create table code, and the bug would go away.

[astrosteve0]

One more note: running the statement:

SET GLOBAL default_storage_engine = 'InnoDB';

Then running the create table statement without the ENGINE and CHARSET statements does work. I found the setting under MySQL/MySQL Settings default_storage_engine, and changed it to InnoDB, so this should be permanent. Hopefully this helps someone else that may have this issue.