Project

General

Profile

Actions

Defect #6943

closed

Migration from boolean to varchar fails on PostgreSQL 8.1

Added by Jakob Skjerning over 13 years ago. Updated over 13 years ago.

Status:
Closed
Priority:
Normal
Assignee:
-
Category:
Database
Target version:
Start date:
2010-11-19
Due date:
% Done:

0%

Estimated time:
Resolution:
Fixed
Affected version:

Description

After upgrading to trunk revision 4411, I went ahead and attempted to migrate the database:

$ rake db:migrate
(in /var/www/apps/redmine)
==  ChangeUsersMailNotificationToString: migrating ============================
-- change_column(:users, :mail_notification, :string, {:null=>false, :default=>""})
rake aborted!
An error has occurred, this and all later migrations canceled:

PGError: ERROR:  column "mail_notification" cannot be cast to type "pg_catalog.varchar" 
: ALTER TABLE "users" ALTER COLUMN "mail_notification" TYPE character varying(255)

(See full trace by running task with --trace)

I am running:

  • Rails 2.3.5
  • Ruby 1.8.7
  • PostgreSQL 8.1
  • Redmine r4411

Looks like PostgreSQL doesn't know how to cast a boolean to a varchar so db/migrate/20100129193402_change_users_mail_notification_to_string.rb isn't going to work on PostgreSQL as far as I can tell.

What values are the booleans expected to be cast to? I am guessing MySQL will cast false and true to '0' and '1' seeing how it doesn't have a boolean type.

The process for getting that in PostgreSQL (and probably other DBMS with boolean types) is a bit more complex than simply changing the column type. We're looking at something like:

ALTER TABLE users RENAME COLUMN mail_notification TO boolean_mail_notification;
ALTER TABLE users ADD COLUMN mail_notification VARCHAR(255) NOT NULL DEFAULT '';
UPDATE users SET mail_notification = '1' WHERE boolean_mail_notification = true;
UPDATE users SET mail_notification = '0' WHERE boolean_mail_notification = false;
ALTER TABLE users DROP COLUMN boolean_mail_notification;

Is that the expected behavior?


Files

mail_notification_migration.patch (2.55 KB) mail_notification_migration.patch Jean-Philippe Lang, 2010-11-19 19:39
Actions #1

Updated by Jean-Philippe Lang over 13 years ago

  • Subject changed from Migration fails on PostgreSQL 8.1 to Migration from boolean to varchar fails on PostgreSQL 8.1
  • Target version set to 1.1.0

Changing a boolean column to varchar was a bad idea. I'll have to fix it before it goes in stable branch.
Anyway, what you describe is pretty much what was expected. You should be able to run the migrations after running these alter/update.

Actions #2

Updated by Erik Ordway over 13 years ago

I think it is closer to
ALTER TABLE users RENAME COLUMN mail_notification TO boolean_mail_notification;
ALTER TABLE users ADD COLUMN mail_notification VARCHAR NOT NULL DEFAULT '';
UPDATE users SET mail_notification = 'selected' WHERE boolean_mail_notification = true;

Note the last line. Your question help me thanks. It is annoying when mysql'ism sneak in like this.

Actions #3

Updated by Jean-Philippe Lang over 13 years ago

Erik Ordway wrote:

I think it is closer to
ALTER TABLE users RENAME COLUMN mail_notification TO boolean_mail_notification;
ALTER TABLE users ADD COLUMN mail_notification VARCHAR NOT NULL DEFAULT '';
UPDATE users SET mail_notification = 'selected' WHERE boolean_mail_notification = true;

Note the last line. Your question help me thanks. It is annoying when mysql'ism sneak in like this.

Actually, values are updated by another migration, see r4216 that added the 2 migrations.
Your UPDATE is not really what is expected, see the second migration.

I'll fix the migration asap so that it runs more smoothly.

Actions #4

Updated by Jean-Philippe Lang over 13 years ago

I don't have a postgresql 8.1 running, can you give the attached patch a try?
It was tested with postgresql 8.3, mysql 5.1 and sqlite 3.

Actions #5

Updated by Erik Ordway over 13 years ago

Yep that seems to do it and the resulting data looks like this.
"all"
"only_my_events"
"only_my_events"
"only_my_events"

==  ChangeUsersMailNotificationToString: migrating ============================
-- rename_column(:users, :mail_notification, :mail_notification_bool)
   -> 0.0013s
-- add_column(:users, :mail_notification, :string, {:default=>"", :null=>false})
   -> 0.0043s
-- remove_column(:users, :mail_notification_bool)
   -> 0.0017s
==  ChangeUsersMailNotificationToString: migrated (0.0318s) ===================

==  UpdateMailNotificationValues: migrating ===================================
==  UpdateMailNotificationValues: migrated (0.0000s) ==========================
Actions #6

Updated by Jean-Philippe Lang over 13 years ago

Thanks for your help.

Actions #7

Updated by Jean-Philippe Lang over 13 years ago

  • Status changed from New to Closed
  • Resolution set to Fixed

Committed in r4413.

Actions

Also available in: Atom PDF