Trying to convert existing production database table columns from enum to VARCHAR

I have a problem that needs me to convert my existing live production (I've duplicated the schema on my local development box, don't worry) table column types from enums to a string.

Background:

Basically, a previous developer left my codebase in absolute disarray: migration versions are extremely out of date, and apparently he never used it after a certain point of time in development and now that I'm tasked with migrating a Rails 1.2.6 app to 2.3.5.

I can't get the tests to run properly on 2.3.5 because my table columns have ENUM column types and they convert to :string, :limit => 0 on my schema.rb which creates the problem of an invalid default value when doing a rake db:test:prepare, like in the case of:

Mysql::Error: Invalid default value for 'own_vehicle': CREATE TABLE `lifestyles` (`id` int(11) DEFAULT NULL auto_increment PRIMARY KEY, `member_id` int(11) DEFAULT 0 NOT NULL, `own_vehicle` varchar(0) DEFAULT 'Y' NOT NULL, `hobbies` text, `sports` text, `AStar_activities` text, `how_know_IRC` varchar(100), `IRC_referral` varchar(200), `IRC_others` varchar(100), `IRC_rdrive` varchar(30)) ENGINE=InnoDB

I'm thinking of writing a migration task that looks through all the database tables for columns with enum and replace it with VARCHAR and I'm wondering if this is the right way to approach this problem. Or better yet if there is a way to fix this without having to modify the database, even better!

I'm also not sure how to write it so that it would loop through my database tables and replace all ENUM colum_types with a VARCHAR.

References

• https://rails.lighthouseapp.com/projects/8994/tickets/997-dbschemadump-saves-enum-columns-as-varchar0-on-mysql

• http://dev.rubyonrails.org/ticket/2832

I was also looking for a solution to fix the schema.rb file for enums too, since RoR (version 3.1.3 at the moment) calls them string with limit 0.

It looks like there's a plugin/gem for handling enum types without changing the database. On Rails 1 and 2 a plugin: http://enum-column.rubyforge.org/ On Rails 3 there's a gem similar to that one: https://github.com/electronick/enum_column

After you add enum_column3 to your Gemfile (and run bundle update), or install the plugin, then run the rake task db:schema:dump to regenerate the schema.rb file.

The only downside of this plugin/gem that I see is you have to change code in your app that uses those fields (so you have to find it all) from strings to symbols, and the validates_inclusion_of to validates_columns. Still, it didn't take me too long to do this with grep and vi.

Alternatively you could do as Robert said above and alter the MySQL directly so it matches what RoR expects. But I personally prefer to keep using Enum types. (I have a LAMP background.) Treating enum as a varchar, using validates_inclusion_of in the model, works great for everything that doesn't use schema.rb. Just tests and migrations have a problem.

Or alternatively, you could keep manually editing the schema.rb file to fix the limit. (That's what we've been doing so far, but it's annoying.)

(I guess I don't have enough rep points to add comments? Steve's answer irks me. You definitely DO want to use migrations, because then you guarantee doing the same alterations on dev as you do in production. It's too easy to make a mistake even in copy-paste of the SQL during deployment. Believe me, I know this by experience. Plus the migrations are a standard place to keep the alterations. And you might need to re-apply them if you restore from an older backup.)