Vote #68659: Migration from boolean to varchar fails on PostgreSQL 8.1 - redmineorg-copy202205 - Redmine

編集操作

リンクをコピー

Vote #68659

完了

Migration from boolean to varchar fails on PostgreSQL 8.1

Admin Redmine さんが3年以上前に追加. 3年以上前に更新.

ステータス:

Closed

優先度:

通常

担当者:

カテゴリ:

Database_21

対象バージョン:

1.1.0_20

開始日:

2010/11/19

期日:

進捗率:

予定工数:

Redmineorg_URL:

https://www.redmine.org/issues/6943

category_id:

version_id:

issue_org_id:

6943

author_id:

2207

assigned_to_id:

comments:

status_id:

tracker_id:

plus1:

affected_version:

closed_on:

affected_version_id:

ステータス-->[Closed]

引用

説明

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?

journals

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.
--------------------------------------------------------------------------------
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(255) 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.

--------------------------------------------------------------------------------
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(255) 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.
--------------------------------------------------------------------------------
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.
--------------------------------------------------------------------------------
Yep that seems to do it and the resulting data looks like this.
"all"
"only_my_events"
"only_my_events"
"only_my_events"

<pre>
== 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) ==========================
</pre>

--------------------------------------------------------------------------------
Thanks for your help.
--------------------------------------------------------------------------------
Committed in r4413.
--------------------------------------------------------------------------------