Oracle error ORA-01451 in migration

Added by Guilherme Schneider over 9 years ago

I tried to install redmine using oracle and when I did

rake db:migrate RAILS_ENV="production"

I got

== 48 AllowNullVersionEffectiveDate: migrating ================================
-- change_column(:versions, :effective_date, :date, {:default=>nil, :null=>true})
rake aborted!
OCIError: ORA-01451: column to be modified to NULL cannot be modified to NULL: ALTER TABLE versions MODIFY effective_date DATE DEFAULT NULL NULL

(formated by tslh)

Replies (8)

RE: Oracle error ORA-01451 in migration - Added by Thomas Lecavelier over 9 years ago

Even if it's rather strange that nobody meet your error on that quite old migration. According to http://ora-01451.ora-code.com/ I think it's because effective_date is already nullable in your very case (why? that's an other question). Try by commenting the change_column line of that migration (db/migrations/48_...)

RE: Oracle error ORA-01451 in migration - Added by Guilherme Schneider over 9 years ago

OK. I commented change_column of migration 48.

I have the same problem on migration 76:

76 AllowNullPosition: migrating ==========================================
-- change_column(:issue_statuses, :position, :integer, {:default=>1, :null=>true})
rake aborted!
OCIError: ORA-01451: column to be modified to NULL cannot be modified to NULL: ALTER TABLE issue_statuses MODIFY position NUMBER DEFAULT 1 NULL 76 AllowNullPosition: migrating ==========================================
-- change_column(:roles, :position, :integer, {:default=>1, :null=>true})
rake aborted!
OCIError: ORA-01451: column to be modified to NULL cannot be modified to NULL: ALTER TABLE roles MODIFY position NUMBER DEFAULT 1 NULL 76 AllowNullPosition: migrating ==========================================
-- change_column(:trackers, :position, :integer, {:default=>1, :null=>true})
rake aborted!
OCIError: ORA-01451: column to be modified to NULL cannot be modified to NULL: ALTER TABLE trackers MODIFY position NUMBER DEFAULT 1 NULL 76 AllowNullPosition: migrating ==========================================
-- change_column(:boards, :position, :integer, {:default=>1, :null=>true})
rake aborted!
OCIError: ORA-01451: column to be modified to NULL cannot be modified to NULL: ALTER TABLE boards MODIFY position NUMBER DEFAULT 1 NULL 76 AllowNullPosition: migrating ==========================================
-- change_column(:enumerations, :position, :integer, {:default=>1, :null=>true})
rake aborted!
OCIError: ORA-01451: column to be modified to NULL cannot be modified to NULL: ALTER TABLE enumerations MODIFY position NUMBER DEFAULT 1 NULL

I commented all of them. Then I have some more problems:

On migration 87:

87 ChangeProjectsDescriptionToText: migrating ============================
-- change_column(:projects, :description, :text, {:default=>nil, :null=>true})
rake aborted!
OCIError: ORA-22858: invalid alteration of datatype: ALTER TABLE projects MODIFY description CLOB DEFAULT NULL NULL

This one I had to modify 001_setup.rb to use :text from the beggining (and commented migration 87)

On migration 91:

91 ChangeChangesetsRevisionToString: migrating ===========================
-- change_column(:changesets, :revision, :string, {:null=>false})
rake aborted!
OCIError: ORA-01442: column to be modified to NOT NULL is already NOT NULL: ALTER TABLE changesets MODIFY revision VARCHAR2 NOT NULL

For this one I had to remove the :null => false parameter.

Then I finnaly could run all the migrations from the beggining in Oracle with no errors.

But when I tried to access the aplication I got these errors:

ActiveRecord::StatementInvalid (OCIError: ORA-01400: cannot insert NULL into ("REDMINE"."USERS"."LOGIN"): INSERT INTO users (status, created_on, last_login_on, hashed_password, language, updated_on, admin, type, firstname, lastname, mail, id, mail_notification, auth_source_id, login) VALUES):

ActiveRecord::StatementInvalid (OCIError: ORA-01400: cannot insert NULL into ("REDMINE"."USERS"."HASHED_PASSWORD"): INSERT INTO users (status, created_on, last_login_on, hashed_password, language, updated_on, admin, type, firstname, lastname, mail, id, mail_notification, auth_source_id, login) VALUES):

ActiveRecord::StatementInvalid (OCIError: ORA-01400: cannot insert NULL into ("REDMINE"."USERS"."FIRSTNAME"): INSERT INTO users (status, created_on, last_login_on, hashed_password, language, updated_on, admin, type, firstname, lastname, mail, id, mail_notification, auth_source_id, login) VALUES)

Those are because, for Oracle, null and the empty string ('') are the same. So I had to modify 001_setup.rb to allow null values in those columns.

Now I could access Redmine!!

But then, when I tried to "load the default configuration" I got:

Default configuration could not be loaded: You have a nil object when you didn't expect it! The error occurred while evaluating nil.write

The same happens when I run:
rake redmine:load_default_data RAILS_ENV="production"
(in /home/notroot/rails_apps/redmine)

Select language: bg, cs, da, de, en, es, fi, fr, he, hu, it, ja, ko, lt, nl, no, pl, pt, pt-br, ro, ru, sr, sv, th, uk, zh, zh-tw [en] pt-br ====================================
Error: undefined method `write' for nil:NilClass
Default configuration data was not loaded.

Any clue?

Guilherme

RE: Oracle error ORA-01451 in migration - Added by Guilherme Schneider over 9 years ago

I had to put
ENV['NLS_LANG']='american_america.AL32UTF8'
on environment.rb to correct problems with charset.

Also, when I tried to save an workflow I got this:

Processing RolesController#workflow (for 192.168.175.1 at 2008-07-22 13:26:15) [POST]
Session ID: a2050828099aa069b161ccc1ff31135b
Parameters: {"commit"=>"Save", "issue_status"=>{"10001"=>["10007"], "10002"=>["10006"], "10007"=>["10002"], "10000"=>["10001"]}, "action"=>"workflow", "role_id"=>"10004", "controller"=>"roles", "tracker_id"=>"10001"}

OCIError (ORA-22920: row containing the LOB value is not locked):
lob.c:181:in oci8lib.so
/usr/local/lib/site_ruby/1.8/oci8.rb:1005:in `write'
/usr/lib/ruby/gems/1.8/gems/activerecord-2.1.0/lib/active_record/connection_adapters/oracle_adapter.rb:289:in `write_lobs'
...etc
/app/controllers/roles_controller.rb:94:in `workflow'
...etc

Guilherme

RE: Oracle error ORA-01451 in migration - Added by Guilherme Schneider over 9 years ago

Another one, when I clik on a project (projects/show):

Processing ProjectsController#show (for 192.168.175.1 at 2008-07-22 14:19:06) [GET]
  Session ID: a2050828099aa069b161ccc1ff31135b
  Parameters: {"action"=>"show", "id"=>"sandbox", "controller"=>"projects"}

ActiveRecord::StatementInvalid (OCIError: ORA-00904: "TRACKER_ID": invalid identifier: SELECT COUNT(*) AS count_all, tracker_id AS tracker_id FROM (SELECT DISTINCT issues.id FROM issues  LEFT OUTER JOIN projects ON projects.id = issues.project_id  LEFT OUTER JOIN issue_statuses ON issue_statuses.id = issues.status_id  LEFT OUTER JOIN trackers ON trackers.id = issues.tracker_id     WHERE (((projects.id = 10000 OR projects.parent_id = 10000)) AND issue_statuses.is_closed=0) AND (projects.status=1)  GROUP BY tracker_id )):
    /usr/lib/ruby/gems/1.8/gems/activerecord-2.1.0/lib/active_record/connection_adapters/abstract_adapter.rb:147:in `log'
    ...etc
    /app/controllers/projects_controller.rb:94:in `show'
    /app/models/issue.rb:250:in `visible_by'
    /usr/lib/ruby/gems/1.8/gems/activerecord-2.1.0/lib/active_record/base.rb:1852:in `with_scope'
    /app/models/issue.rb:249:in `visible_by'
    /app/controllers/projects_controller.rb:93:in `show'
    ...etc

RE: Oracle error ORA-01451 in migration - Added by Guilherme Schneider over 9 years ago

I've solved the problem of saving workflows (ORA-22920: row containing the LOB value is not locked) using

ActiveRecord::Base.partial_updates = false

in environment.rb

But the problem with projects/show remains...

RE: Oracle error ORA-01451 in migration - Added by Rodrigo Mesquita almost 9 years ago

Hi!

Based upon this thread i could configurate the redmine 0.7.3 to work with Oracle XE Database.

The entire explanation of how i did this can be viewed on the PDF file attached to this post.

I didn't test the whole application, so, fails can be arised.

Till this time, it worked fine for me.

See ya.

Redmine_on_Oracle.pdf - Instalação do Redmine on Oracle Database (315 KB)

RE: Oracle error ORA-01451 in migration - Added by Edson Oliveira almost 9 years ago

Hi, just to you know... I'm maintaining a git repository at Github (http://github.com/edsono/redmine-jruby-oracle/tree/0.7-jruby-oracle) with needed patchs to run Redmin 0.7.3 with JRuby on Oracle. It works with Ruby/Oracle combo too.

I'm fixing bugs as they show up.

cya.

RE: Oracle error ORA-01451 in migration - Added by Mélanie Gault almost 8 years ago

hello

Another bug with oracle (it seems to be a bug in activerecord-oracle-adapter) and redmine 0.8.6.
I'm new on ruby and rails so the solution may not be the best.

The problem :
while usine ldap authentification, the test button always return "Unable to connect ()".
With no errors in logs nor console.

After lots of search : in app/mofels/auth_source_ldap.rb

  def initialize_ldap_con(ldap_user, ldap_password)
    options = { :host => self.host,
                :port => self.port,
                :encryption => (self.tls ? :simple_tls : nil)
              }

encryption is always :simple_tls because in oracle database the tls column is an integer not null initialized by 0, and the ActiveRecord::ConnectionAdapters::OracleAdapter.emulate_booleans is true.

Solution
change the code into :

  def initialize_ldap_con(ldap_user, ldap_password)
    options = { :host => self.host,
                :port => self.port,
                :encryption => (self.tls>0 ? :simple_tls : nil)
              }

(tested OK)

Or perhaps change the migration in 074_add_auth_sources_tls.rb
(but I don't know how at the moment)

thanks.

(1-8/8)