Trying to convert existing production database table columns from enum to VARCHAR (Rails)

Question

Hi everyone,

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 shit, 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 very sure how to write it such that it would loop through my database tables and replace all ENUM colum_types with a VARCHAR.

References

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

[2] http://dev.rubyonrails.org/ticket/2832

Answer 1

Seriously though, migrations are just an over-complicated way of going about something that would otherwise be very straightforward. It's called backing up and updating. Everyone's been doing it for decades... and it handles all data types, and is readable across skill sets, and if anything at all goes wrong - you have a backup!