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.

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

Long keys for MySQL older than 5.7 #120

Closed
dbek opened this Issue Oct 2, 2016 · 11 comments

Comments

Projects
None yet
2 participants
@dbek
Copy link

dbek commented Oct 2, 2016

The table creation process fails on MySQL 5.5.52 with the following error:

Migration V1__Initial_Setup.sql failed

SQL State : 42000
Error Code : 1071
Message : Specified key was too long; max key length is 767 bytes
Location : db/migration/V1__Initial_Setup.sql (/usr/local/bin/file:/usr/local/bin/mojito-webapp-0.57.jar!/db/migration/V1__Initial_Setup.sql)
Line : 32
Statement : alter table asset add constraint UK__ASSET__REPOSITORY_ID__PATH unique (repository_id, path)

As fas as I can see, the "asset" table column "path" is 255 characters long, which would already exceed the 767 bytes key limit, since utf8mb4 is 4 bytes per character.

A possible workaround might be turning "innodb_large_prefix" on, but still doesnt' help on my Ubuntu 14.04 and MySQL 5.5.52

@aurambaj

This comment has been minimized.

Copy link
Collaborator

aurambaj commented Oct 2, 2016

@dbek , Mysql doc also mention innodb_file_format could you check this config as well?

innodb_large_prefix is enabled by default in MySQL 5.7.7. This change coincides with the default value change for innodb_file_format, which is set to Barracuda by default in MySQL 5.7.7. Together, these default value changes allow larger index key prefixes to be created when using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED. If either option is set to a non-default value, index key prefixes larger than 767 bytes are silently truncated.

@dbek

This comment has been minimized.

Copy link

dbek commented Oct 3, 2016

@aurambaj , changed it also in my.cnf, so this query:
select @@innodb_large_prefix, @@innodb_file_format;
now returns
1; Barracuda
Didn't help though, still got the same error. This definitely lies within the MySQL configuration, not an app issue, since I've tried it on MySQL 5.7.9 and it works just fine (btw, these globals have the same values of 1 and Barracuda on a newer version).

@dbek

This comment has been minimized.

Copy link

dbek commented Oct 3, 2016

@aurambaj , I think a possible solution might be to add ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED to table creation, this actually helps. After setting the innodb_large_prefix=1 and innodb_file_format=Barracuda, of course.

@aurambaj

This comment has been minimized.

Copy link
Collaborator

aurambaj commented Oct 3, 2016

Great that makes sense based on the documentation.

@aurambaj aurambaj closed this Oct 3, 2016

@dbek

This comment has been minimized.

Copy link

dbek commented Oct 4, 2016

@aurambaj , well, shouldn't it be actually added to source code? :) I can't find another way to fix this for MySQL older than 5.7.

@aurambaj

This comment has been minimized.

Copy link
Collaborator

aurambaj commented Oct 4, 2016

@dbek What do you mean by added to source code?

@dbek

This comment has been minimized.

Copy link

dbek commented Oct 4, 2016

@aurambaj The database structure is created on first app start, right? And there is no way to specify the use of ROW_FORMAT=DYNAMIC on a table except for its create statement. This should be added as a hint for entities (which might be affected by the 767 byte key limit), or a global hibernate configuration (not sure if hibernate has this fine tuning option, quick searching shows this stackoverflow question, not an elegant solution though).

@dbek

This comment has been minimized.

Copy link

dbek commented Oct 4, 2016

And yes - MySQL has the 'innodb_default_row_format' global variable, but - only since 5.7 :)

@aurambaj

This comment has been minimized.

Copy link
Collaborator

aurambaj commented Oct 4, 2016

@dbek hm, I was expecting innodb_default_row_format to be available. I need to do some more digging ( not fan of the Hibernate hack). it might be possible to restrict the part that is indexed via hibernate annotation or change the type but I think there is other tables that would be impacted.

@dbek

This comment has been minimized.

Copy link

dbek commented Oct 4, 2016

@aurambaj , in fact, I've seen that there is an option to specify the indexed length of a varchar field like so: (repository_id(186), path). Might be an acceptable option as well, if supported by previous MySQL releases.

@aurambaj aurambaj reopened this Oct 5, 2016

@aurambaj

This comment has been minimized.

Copy link
Collaborator

aurambaj commented Jun 13, 2017

Won't fix, assuming MySQL 5.7 is a requirement. Possible workaround is to create the schema manually.

@aurambaj aurambaj closed this Jun 13, 2017

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment