add_index allows too long of a index name for mysql #3602

Closed
daveheitzman opened this Issue Nov 10, 2011 · 5 comments

Comments

Projects
None yet
6 participants

add_index(:my_super_long_table_name, [:my_way_way_too_long_table_name, :my_ridiculous_and_humiliatingly_long_table_name]

This results in mysql complaining "index names can only by 64 chars "

Since the helper is the one creating the name, it would be nice if it protected us from our own verbosity.

Contributor

tylercoville commented Nov 11, 2011

I looked at the code and found where this is handled but it seems to be operating properly for me. When I call it ruby checks the length and sees that it is too long before it gets to the db.

rake db:migrate
== CreateLongTable: migrating ================================================
-- create_table(:my_super_long_table_name)
-> 0.0010s
-- add_index(:my_super_long_table_name, [:my_way_way_too_long_variable_name, :my_ridiculous_and_humiliatingly_long_variable_name])
rake aborted!
An error has occurred, this and all later migrations canceled:

Index name >'index_my_super_long_table_name_on_my_way_way_too_long_variable_name_and_my_ridiculous_and_humiliatingly_long_variable_name' on table 'my_super_long_table_name' is too long; the limit is 64 characters

Is this the error your getting?

If you are getting this I think the best solution is just to manually add a name
:name => "my_index"
The generator could be changed to check the length after generating and before passing it to the sql execution then truncate it if it is too long (this is how postgres handles too long of indexes) in the sample case we would end up with an index named:

`````` index_my_super_long_table_name_on_my_way_way_too_long_variable_but I think that would be bad because if you then ranadd_index(:my_super_long_table_name, [:my_way_way_too_long_variable_name, :name])```
you would run into another error for duplicate index names.

The only way I could potentially see it working is if you truncated the table/variable names before generating the index name but I think that may be extreme for such a fringe issue.

Hopefully I am recreating this error correctly.

Thanks for the response. Yes, that is the error I'm seeing. I assume mysql needs you to have unique index names? Maybe not. If not, just truncate sensibly, I think after creating the name. If uniques are necessary, then I guess you have to have a numbering system you track for each schema.rb. This is a pain, but since you're generating schema each migration, really not so bad. You could get ugly, and take the long name, and replace half its characters with the first 32 bytes of some sha1 hash. Think of it as punishment for people who index against 3 long column names :-)

The only importance of this, I think, is that the db abstraction layer is picking the index name for you. So it bears the responsibility of picking a name that is suitable for the given DB engine. Maybe some engines take 256 char index names? And so from the point of view of delivering db abstraction, the implementation should take care of that for you.

Thanks again,
Dave Heitzman


From: Tyler Coville <reply@reply.github.com>
To: David Heitzman <daveheitzman@yahoo.com>
Sent: Thursday, November 10, 2011 6:43 PM
Subject: Re: [rails] add_index allows too long of a index name for mysql (#3602)

I looked at the code and found where this is handled but it seems to be operating properly for me. When I call it ruby checks the length and sees that it is too long before it gets to the db.

>rake db:migrate
>== CreateLongTable: migrating ================================================
>-- create_table(:my_super_long_table_name)
> -> 0.0010s
>-- add_index(:my_super_long_table_name, [:my_way_way_too_long_variable_name, :my_ridiculous_and_humiliatingly_long_variable_name])
>rake aborted!
>An error has occurred, this and all later migrations canceled:

>Index name >'index_my_super_long_table_name_on_my_way_way_too_long_variable_name_and_my_ridiculous_and_humiliatingly_long_variable_name' on table 'my_super_long_table_name' is too long; the limit is 64 characters

Is this the error your getting?

If you are getting this I think the best solution is just to manually add a name
:name =&gt; &quot;my_index&quot;
The generator could be changed to check the length after generating and before passing it to the sql execution then truncate it if it is too long (this is how postgres handles too long of indexes) in the sample case we would end up with an index named:

`````` index_my_super_long_table_name_on_my_way_way_too_long_variable_but I think that would be bad because if you then ranadd_index(:my_super_long_table_name, [:my_way_way_too_long_variable_name, :name])```
you would run into another error for duplicate index names.

The only way I could potentially see it working is if you truncated the table/variable names before generating the index name but I think that may be extreme for such a fringe issue.

Hopefully I am recreating this error correctly.


Reply to this email directly or view it on GitHub:
#3602 (comment)

Contributor

henrikhodne commented Nov 11, 2011

I think that in this case, specifying name: 'my_index' would be the best thing, unless someone can come up with a super-elegant way to truncate that name.

Member

vijaydev commented Nov 12, 2011

Related: #1993

@daveheitzman seems this isn't really a problem. There is a known work around: pass your own :name to add_index. The only other option is to truncate index names in the adapter itself. As @vijaydev dev said, see: #1993 for further discussion on implementing something you approved.

@steveklabnik can you close this?

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