Have you ever run a rake task such as
to be presented with an error about key length?
Mysql::Error: BLOB/TEXT column 'column-name' used in key specification without a key length: CREATE INDEX index-name ON table-name (column-name)
Here are some examples I’ve encountered:
Mysql::Error: BLOB/TEXT column 'keywords' used in key specification without a key length: CREATE INDEX fulltext_keywords ON article_search (keywords) Mysql::Error: BLOB/TEXT column 'mbr' used in key specification without a key length: CREATE INDEX spatial_mbr ON client_locations (mbr)
These errors are the result of the ActiveRecord::SchemaDumper class not being able to generate a schema.rb file with proper commands for creating FULLTEXT and SPATIAL MySQL indices. Fortunately Ruby is a dynamic language, and it’s easy to adjust the way the schema.rb file is generated.
NOTE: This code assumes your FULLTEXT indices have “fulltext” in their name and that your SPATIAL indices have “spatial” in their name. If you don’t follow this convention then you’ll need to write your own regular expressions into the code.
- Create a file called `sd_indexes.rb` in your `config/initializers` directory, and add the following code. This code overrides the basic index creation of the `ActiveRecord::SchemaDumper` class. When the schema dumper encounters an index, before issuing the standard `add_index` command, it checks if the index name matches **/fulltext/i** or **/spatial/i**. If there is a match, the schema dumper issues a MySQL [CREATE INDEX](http://dev.mysql.com/doc/refman/5.5/en/create-index.html) command to create the FULLTEXT or SPATIAL index manually.
- Generate the schema.rb file so that your changes take effect.
- Create your test database from the newly generated schema.rb file.
The added benefit of overriding the
function is that the schema.rb file will be properly generated from now on. That
means that the next time you run a migration you won’t have to make any
adjustments to the schema.rb file, and you won’t have to re-run the
db:schema:dump rake task.