This project is archived and is in readonly mode.
Limit MySQL index name length
Reported by Nathaniel Bibler | November 24th, 2009 @ 02:49 PM
The current MySQL adapter allows you to define new indices on a table with names that are generated beyond the allowed 50 byte length constraint:
add_index :posts, [:user_id, :a_long_column_name_that_concats_beyond_50]
# => "posts_on_user_id_and_a_long_column_name_that_concats_beyond_50"
This is an issue in a an environment where multiple databases are used in development vs production (sqlite vs postgresql vs mysql). Sqlite3 and PostgreSQL allow indices with longer names and therefore do not cause issue. But when MySQL is added, this problem may easily - and suddenly - show up.
Perhaps a logical truncation of the generated index name is appropriate in this case? Either way, since it is still around and hasn't been raised in Lighthouse, I thought it might be worth a revisit.
This issue was originally raised as ticket #10954 in Trac, two years ago by lesgrumels. It appears to still be an issue up to and through Rails 2.3.4.
Comments and changes to this ticket
-
Nathaniel Bibler November 24th, 2009 @ 03:04 PM
Hrm.. the PostgreSQL adapter seems to have similar truncation logic to what I mentioned. See this ticket. It seems to truncate at 63 bytes (vs the 50 limit on MySQL).
Perhaps it would be wise to just truncate all Rails-generated indices to 50 characters? Thereby making all ActiveRecord adapters act identically..
-
Michael Sheakoski January 9th, 2010 @ 05:42 PM
I was able to create an index name 64 characters long on MySQL 5.0.51. Just letting you know so a fix for this issue doesn't make the name shorter than it needs to be.
-
Nate Wiger January 10th, 2010 @ 12:14 AM
@Bibler Any limit needs to remain database dependent as Oracle's limit is 30 chars
-
Santiago Pastorino April 11th, 2010 @ 09:08 AM
- State changed from new to duplicate
duplicate #1852
-
Jeremy Kemper April 22nd, 2010 @ 08:26 PM
- State changed from duplicate to open
Santiago, this is a limit on the length of the index name.
-
Santiago Pastorino April 23rd, 2010 @ 01:09 AM
Sorry my bad here, i should read a bit slowly the next time.
-
Jeremy Kemper April 23rd, 2010 @ 06:04 PM
- State changed from open to duplicate
-
Gustavo Delfino July 24th, 2010 @ 04:10 PM
- Importance changed from to
I was just affected my this bug. As this has been marked as duplicate, I would like to know the new ticket number.
-
rcrogers November 22nd, 2010 @ 06:20 PM
I just ran into this in Rails 2.3.8. It took me quite a while to figure out what was going on.
Create your profile
Help contribute to this project by taking a few moments to create your personal profile. Create your profile »
<h2 style="font-size: 14px">Tickets have moved to Github</h2>
The new ticket tracker is available at <a href="https://github.com/rails/rails/issues">https://github.com/rails/rails/issues</a>
People watching this ticket
Tags
Referenced by
- 4319 MySQL index name is too long duplicate of #3508 which is duplicate to #1852 which is ....
- 4319 MySQL index name is too long Just a duplicate of #3508 only. sorry :/
- 4319 MySQL index name is too long Duplicate of #3508.
- 3452 [PATCH] Improve DB index handling Ok so I read #3252 and #3508 which made me realize Postgr...
- 3452 [PATCH] Improve DB index handling In #3508 the idea of truncating indexes is entertained an...