Patch #6469

"Activity" doesn't work with oracle database

Added by Nelzin Alexander about 8 years ago. Updated 20 days ago.

Status:ClosedStart date:2010-09-23
Priority:NormalDue date:
Assignee:-% Done:

0%

Category:Database
Target version:-

Description

Here is error

ActiveRecord::StatementInvalid (OCIError: ORA-00932: inconsistent datatypes: expected - got CLOB: select * from (SELECT DISTINCT "JOURNALS".id, FIRST_VALUE(journals.id) OVER (PARTITION BY "JOURNALS".id ORDER BY journals.id DESC) AS alias_0__ FROM "JOURNALS"  LEFT OUTER JOIN "ISSUES" ON "ISSUES".id = "JOURNALS".journalized_id  LEFT OUTER JOIN "PROJECTS" ON "PROJECTS".id = "ISSUES".project_id  LEFT OUTER JOIN "JOURNAL_DETAILS" ON journal_details.journal_id = journals.id WHERE (journals.journalized_type = 'Issue' AND (journal_details.prop_key = 'status_id' OR journals.notes <> '')) AND (1=1 AND (journals.user_id = 514) AND (projects.status=1 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking')))  ORDER BY alias_0__ DESC) where rownum <= 10):
  /Library/Ruby/Gems/1.8/gems/activerecord-oracle_enhanced-adapter-1.3.0/lib/active_record/connection_adapters/oracle_enhanced_adapter.rb:1726:in `log'
  /Library/Ruby/Gems/1.8/gems/activerecord-oracle_enhanced-adapter-1.3.0/lib/active_record/connection_adapters/oracle_enhanced_adapter.rb:1609:in `select'
  lib/redmine/activity/fetcher.rb:76:in `events'
  lib/redmine/activity/fetcher.rb:75:in `each'
  lib/redmine/activity/fetcher.rb:75:in `events'
  lib/redmine/activity/fetcher.rb:74:in `each'
  lib/redmine/activity/fetcher.rb:74:in `events'
  app/controllers/users_controller.rb:59:in `show'

Rendered rescues/_trace (136.1ms)
Rendered rescues/_request_and_response (1.3ms)
Rendering rescues/layout (internal_server_error)

You cannot perform this operation ("<>") with clob field in oracle. The solution is to change this query.

Here is the patch to make it work with oracle:

Index: app/models/journal.rb
===================================================================
--- app/models/journal.rb    (revision 1538)
+++ app/models/journal.rb    (revision 1539)
@@ -36,7 +36,7 @@
                             :author_key => :user_id,
                             :find_options => {:include => [{:issue => :project}, :details, :user],
                                               :conditions => "#{Journal.table_name}.journalized_type = 'Issue' AND" +
-                                                             " (#{JournalDetail.table_name}.prop_key = 'status_id' OR #{Journal.table_name}.notes <> '')"}
+                                                             " (#{JournalDetail.table_name}.prop_key = 'status_id' OR LENGTH(#{Journal.table_name}.notes) > 0)"}

   def save(*args)
     # Do not save an empty journal

My environment:

Redmine 1.0.1.stable.1536 (OracleEnhanced)

~/Sites/redmine-1.0 $ ruby script/about
About your application's environment
Ruby version              1.8.7 (universal-darwin10.0)
RubyGems version          1.3.5
Rack version              1.0
Rails version             2.3.5
Active Record version     2.3.5
Active Resource version   2.3.5
Action Mailer version     2.3.5
Active Support version    2.3.5
Application root          /Users/alexander_nelzin/Sites/redmine-1.0
Environment               development
Database adapter          oracle_enhanced
Database schema version   20100819172912

journals_query.patch Magnifier (849 Bytes) Nelzin Alexander, 2010-09-23 07:56


Related issues

Related to Redmine - Feature #944: Oracle Support in Redmine Closed 2008-03-28

History

#1 Updated by Jean-Baptiste Barth about 8 years ago

Did you test your patch with Mysql, Postgresql and Sqlite3 ? Oracle is not officially supported, so we cannot apply your patch if we're not absolutely sure it won't break anything on supported architectures.

#2 Updated by Nelzin Alexander about 8 years ago

I have only PostgreSQL 8.4.4 available here. PostgreSQL 8.4.4 works fine with this patch. Nothing has broken.

I didn't test with Mysql and Sqlite3 databases.

Hope someone who uses other databases can test it.

#3 Updated by Nelzin Alexander about 8 years ago

My configuration I tested Redmine with Postgres:
gem postgres-pr (0.6.3)
PostgreSQL server 8.4.4

~/Sites/redmine-1.0-postgres $ ruby script/about
About your application's environment
Ruby version              1.8.7 (universal-darwin10.0)
RubyGems version          1.3.5
Rack version              1.0
Rails version             2.3.5
Active Record version     2.3.5
Active Resource version   2.3.5
Action Mailer version     2.3.5
Active Support version    2.3.5
Application root          /Users/alexander_nelzin/Sites/redmine-1.0-postgres
Environment               development
Database adapter          postgresql
Database schema version   20100819172912

#4 Updated by Toshi MARUYAMA over 7 years ago

  • Category set to Database

#5 Updated by Go MAEDA 20 days ago

  • Status changed from New to Closed

The latest version of Redmine does not support Oracle database. Please refer to RedmineInstall for supported databases.

Also available in: Atom PDF