OCIError: ORA-22858: invalid alteration of datatype: ALTER TABLE "CHANGES" MODIFY "PATH" CLOB DEFAULT NULL NULL
Replies (1)
RE: OCIError: ORA-22858: invalid alteration of datatype: ALTER TABLE "CHANGES" MODIFY "PATH" CLOB DEFAULT NULL NULL
-
Added by Matthew Rupert over 13 years ago
Well, I have Redmine working with Oracle 11.2. I'll put up a blog post, but for now here's this:
Here are the “gotchas” when setting up Redmine to run on Oracle. Once you have it all figured out, it really isn’t all that difficult (it just took a while to get there). (I’m not going to discuss setting up the Oracle adapter here, only Redmine).
Here goes.
1. Oracle sees ‘’ and “null” as the same thing.
The biggest issue is the fact that Oracle see ‘’ and “null” as the same thing. That said, there are a few setup scripts that will need modified. I went through db/migrate/001_setup.rb and looked for everything that sets a default value of ‘’ and told it to allow nulls for any such column.
For example, the users table is created like this:
create_table "users", :force => true do |t|
t.column "login", :string, :limit => 30, :default => "", :null => true
t.column "hashed_password", :string, :limit => 40, :default => "", :null => true
t.column "firstname", :string, :limit => 30, :default => "", :null => true
t.column "lastname", :string, :limit => 30, :default => "", :null => true
t.column "mail", :string, :limit => 60, :default => "", :null => true
t.column "mail_notification", :boolean, :default => true, :null => false
t.column "admin", :boolean, :default => false, :null => false
t.column "status", :integer, :default => 1, :null => false
t.column "last_login_on", :datetime
t.column "language", :string, :limit => 2, :default => "", :null => true
t.column "auth_source_id", :integer
t.column "created_on", :timestamp
t.column "updated_on", :timestamp
endcreate_table "versions", :force => true do |t|
t.column "project_id", :integer, :default => 0, :null => true
t.column "name", :string, :limit => 30, :default => "", :null => true
t.column "description", :string, :default => ""
t.column "effective_date", :date, :null => true
t.column "created_on", :timestamp
t.column "updated_on", :timestamp
endI’m simply allowing nulls where before null was no longer accepted. This shouldn’t be a problem, its just a lacking database constraint. I’ll definitely follow up if it does become a problem.
There are a few other places where you’ll have to make similar changes:
• 074_add_auth_sources_tls.rb
• 091_change_changesets_revision_to_string.rb
• 108_add_identity_url_to_users.rb
• 20091017214336_add_missing_indexes_to_users.rb
2. 048_allow_null_version_effective_date.rb
Oracle won’t like the syntax of 048_allow_null_version_effective_date.rb. I simply removed this file (it appears that this was a later change in Redmine) and made the version effective data column nullable in 001_setup.rb like this:
3. The UTF-8 Problem
Assuming your Oracle database uses AL32UTF8, you’ll want to do something like this in environment.rb:
ENV['NLS_LANG']='american_america.AL32UTF8' 8.
4. Oracle 30-character limitation on table names
Oracle limits table names to 30 characters in length. This is a problem in one particular Redmine db migration script:
107_add_open_id_authentication_tables.rb
I changed this file to use smaller table names:
class AddOpenIdAuthenticationTables < ActiveRecord::Migration
def self.up
create_table :open_id_auth_associations, :force => true do |t|
t.integer :issued, :lifetime
t.string :handle, :assoc_type
t.binary :server_url, :secret
end
create_table :open_id_auth_nonces, :force => true do |t|
t.integer :timestamp, :null => false
t.string :server_url, :null => true
t.string :salt, :null => false
end
enddef self.down
drop_table :open_id_authentication_associations
drop_table :open_id_authentication_nonces
end
end
~
5. Database connection
Finally, database.yml will end up looking something like this:production:
adapter: oracle_enhanced
database: <database>
host: <database host>
port: <port>
username: redmine_db_user
password: redmine_db_pass
These changes are very important to note should you ever have to upgrade Redmine.