Defect #6943: Migration from boolean to varchar fails on PostgreSQL 8.1 - Redmine

Actions

Copy link

Defect #6943

closed

Migration from boolean to varchar fails on PostgreSQL 8.1

Added by Jakob Skjerning about 14 years ago. Updated about 14 years ago.

Status:

Closed

Priority:

Normal

Assignee:

Category:

Database

Target version:

1.1.0

Start date:

2010-11-19

Due date:

% Done:

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

Copy link

Updated by Jean-Philippe Lang about 14 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

Copy link

Updated by Erik Ordway about 14 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

Copy link

Updated by Jean-Philippe Lang about 14 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

Copy link

Updated by Jean-Philippe Lang about 14 years ago

File mail_notification_migration.patch mail_notification_migration.patch added

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

Copy link

Updated by Erik Ordway about 14 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

Copy link