rails/rails — Issue #4914: SchemaDumper creates invalid MySQL syntax on multi-column indexes where some columns have index lengths and others do not
Author: jfiorato
Created: 2012-02-06 22:23:12+00:00
State: open

My environment is:
```Rails 3.2.1```
```ruby 1.9.3p0 (2011-10-30 revision 33570) [x86_64-darwin11.3.0]```
```mysql Ver 14.14 Distrib 5.5.15, for osx10.7 (i386) using readline 5.1```

I have an two-column index on a table where one of the columns has an length of 255 on the index and the other column has no length on the index. Here's what the migration looks like:

class CreateUsers < ActiveRecord::Migration
def change
create_table :users do |t|
t.string :username, :limit => 400
t.string :password
t.timestamps
end
add_index :users, [:username, :password]
end
end

This results in the following index (notice the ```Sub_part``` column is 255 for the ```username``` column and ```NULL``` for the password column:

bug_development> show indexes from users;
+-------+------------+--------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+--------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| users | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| users | 1 | index_users_on_username_and_password | 1 | username | A | 0 | 255 | NULL | YES | BTREE | | |
| users | 1 | index_users_on_username_and_password | 2 | password | A | 0 | NULL | NULL | YES | BTREE | | |
+-------+------------+--------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

This is causing SchemaDumper to create a an ```add_index``` syntax in ```db/schema.rb``` like this (note the ```nil``` value for the ```:password``` hash key in the ```:lengths``` option:

add_index "users", ["email", "password"], :name => "index_users_on_email_and_password", :length => {"email"=>255, "password"=>nil}

And finally, this in turn causes the following error (note the empty parens after ```password``` in the ```CREATE INDEX``` statement):

~/Rails/bug$ rake
rake aborted!
Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '))' at line 1: CREATE INDEX `index_users_on_username_and_password` ON `users` (`username`(255), `password`())

I believe it all boils down to this commit [here](https://github.com/rails/rails/commit/69dcd45aea770ca90bb3e1f8ce4e944dfb37e766), where the ```add_index``` method of ```activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb``` is only checking that the length hash has a key for that column, and not checking if the length for that column in the hash actually has a value.