Project

General

Profile

Actions

Feature #12713

closed

Microsoft SQLServer support

Added by Jean-Philippe Lang about 11 years ago. Updated about 11 years ago.

Status:
Closed
Priority:
Normal
Category:
Database
Target version:
Start date:
Due date:
% Done:

100%

Estimated time:
(Total: 0.00 h)
Resolution:
Fixed

Files

patch (749 Bytes) patch remove index with confirmation Akiko Takano, 2013-01-05 20:07
patch.diff (749 Bytes) patch.diff Version 2 of Akiko's Patch Daniel Felix, 2013-01-05 21:22
database_gems.patch (2.09 KB) database_gems.patch Jean-Philippe Lang, 2013-01-13 11:55

Subtasks 1 (0 open1 closed)

Defect #10930: rake redmine:load_default_data error in 2.0 with SQLServerClosedEtienne Massip

Actions

Related issues

Related to Redmine - Defect #12693: MSSQL column quotationsClosed

Actions
Related to Redmine - Defect #12694: Dates in issue journalClosed

Actions
Related to Redmine - Defect #12692: MS SQL DB migrationClosed

Actions
Related to Redmine - Defect #11002: Duplicate field in query breaks Roadmap viewClosed

Actions
Related to Redmine - Defect #5822: Problem with Gantt and Calendar with SQL Server 2005Closed2010-07-05

Actions
Related to Redmine - Feature #13091: Task: Document MSSQL SupportClosed

Actions
Related to Redmine - Defect #13110: Double order by prevents documents from working in MSSQL Closed

Actions
Actions #1

Updated by Etienne Massip about 11 years ago

  • Target version set to 2.3.0
Actions #2

Updated by Jean-Philippe Lang about 11 years ago

r11116 passes the full test suite with Microsoft SQL Server 2012 using these gems:
  • activerecord-sqlserver-adapter (3.2.10)
  • tiny_tds (0.5.1)
Actions #3

Updated by Akiko Takano about 11 years ago

After checking out redmine from svn trunk, migration task for new database was failed with error like this.

Index name 'changesets_repos_rev' on table 'changesets' does not exist
/work/redmine/db/migrate/091_change_changesets_revision_to_string.rb:3:in `up'

I wonder this is the correct way or not, but this workaround works fine for me.
I hope I can give you any help.

--- db/migrate/091_change_changesets_revision_to_string.rb      (revision 11125)
+++ db/migrate/091_change_changesets_revision_to_string.rb      (working copy)
@@ -1,6 +1,8 @@
 class ChangeChangesetsRevisionToString < ActiveRecord::Migration
   def self.up
-    remove_index  :changesets, :name => :changesets_repos_rev
+    if index_exists? :chengesets, :changesets_repos_rev
+       remove_index  :changesets, :name => :changesets_repos_rev
+    end
     change_column :changesets, :revision, :string, :null => false
     add_index :changesets, [:repository_id, :revision], :unique => true, :name => :changesets_repos_rev
   end
Actions #4

Updated by Daniel Felix about 11 years ago

I attached a corrected version of the patch. You've got a typo in changesets.

Actions #5

Updated by Akiko Takano about 11 years ago

Daniel Felix wrote:

I attached a corrected version of the patch. You've got a typo in changesets.

I'm so sorry and thank you for making the revisions.

Actions #6

Updated by Jean-Philippe Lang about 11 years ago

Akiko Takano wrote:

After checking out redmine from svn trunk, migration task for new database was failed with error like this.

It works fine for me. Are you able to reproduce when running db:migrate on a new empty database? The index is created in a previous migration (034_create_changesets.rb) so I'd like to understand why you get this error before committing this patch.

Actions #7

Updated by Akiko Takano about 11 years ago

Jean-Philippe Lang wrote:

It works fine for me. Are you able to reproduce when running db:migrate on a new empty database? The index is created in a previous migration (034_create_changesets.rb) so I'd like to understand why you get this error before committing this patch.

I can reproduce the error when I use sqlite3.
Here is the part of error message.

.....
==  ChangeChangesetsRevisionToString: migrating ===============================
-- remove_index(:changesets, {:name=>:changesets_repos_rev})
rake aborted!
An error has occurred, this and all later migrations canceled:

Index name 'changesets_repos_rev' on table 'changesets' does not exist
/home/.../.rbenv/versions/1.9.2-p320/lib/ruby/gems/1.9.1/gems/activerecord-3.2.10/lib/active_record/connection_adapters/abstract/schema_statements.rb:587:in `index_name_for_remove'


Sorry I'm not sure this is because of my environment or not.

However when I use MySQL, migration to clean database is done without any problem. (Nothing to change from r11097)

Actions #8

Updated by Jean-Philippe Lang about 11 years ago

Thanks, I am able to reproduce with sqlite3. The index actually exists before migration 91 but is named altered_changesets_repos_rev. I guess it was silently renamed by a prior migration that affects the revision column, and it does not occur with all backends. The problem with the patch is that we end up with 2 identical unique indexes, which is suboptimal. I'll try to find a better way to workaround this issue.

Actions #9

Updated by Jean-Philippe Lang about 11 years ago

I've committed a fix in r11137. Note that the proposed patch was wrong (the index name was given as the column name and index_exists? was always returning false).

Actions #10

Updated by Akiko Takano about 11 years ago

Jean-Philippe Lang wrote:

I've committed a fix in r11137. Note that the proposed patch was wrong (the index name was given as the column name and index_exists? was always returning false).

Thank you so much for your time. Since r11137, migration task works fine.
I'm also looking forward to support SQL Server.

Actions #11

Updated by Rick Mason about 11 years ago

I'm really pleased to see this feature as we run Redmine on SQL Server 2005 at the moment. Here are some of my notes - I hope they're helpful.

Things I fix each time I install (updated for 2.2.0 on Ruby 1.8.7)

  • In Ruby1.8.7\lib\ruby\gems\1.8\specifications\tilt-1.3.3.gemspec, mail-2.4.4.gemspec and jquery-rails-2.0.3.gemspec change "s.date = %q{2011-05-21 00:00:00.000000000Z}" to "s.date = %q{2011-05-21}" (probably nothing to do with Redmine but included for completeness)
  • Fix line 34 of redmine-2.2.0\app\controllers\versions_controller.rb. Change: @trackers = @project.trackers.find(:all, :order => 'position') to @trackers = @project.trackers.find(:all, :order => "#{Tracker.table_name}.position") as described in http://www.redmine.org/issues/11002
  • Fix redmine-2.2.0\app\models\workflow_rule.rb. In the SQL query at the bottom change the fieldname “rule” to “[rule]” in two locations.

Known issues due to use of SQL server

  • Gantt charts don’t work
  • Can’t sort or group by a custom field
  • Dates for versions don’t work
  • Calendars don't show any events

Known issue which may or may not be SQL server

  • Log time feature is very slow on Ruby 1.8.7, and crashes on Ruby 1.9.x with "SystemStackError (stack level too deep):
    actionpack (3.2.9) lib/action_dispatch/middleware/reloader.rb:70"
Actions #12

Updated by Daniel Felix about 11 years ago

Hi Rick,

maybe you can try the trunk on a testserver?

Most of your reported bugs seem to be fixed in the current trunk. :-)

Best regards,
Daniel

Actions #13

Updated by Jean-Philippe Lang about 11 years ago

Rick Mason wrote:

  • In Ruby1.8.7\lib\ruby\gems\1.8\specifications\tilt-1.3.3.gemspec, mail-2.4.4.gemspec and jquery-rails-2.0.3.gemspec change "s.date = %q{2011-05-21 00:00:00.000000000Z}" to "s.date = %q{2011-05-21}" (probably nothing to do with Redmine but included for completeness)

I think this can be solved by upgrading your rubygems

  • Fix line 34 of redmine-2.2.0\app\controllers\versions_controller.rb. Change: @trackers = @project.trackers.find(:all, :order => 'position') to @trackers = @project.trackers.find(:all, :order => "#{Tracker.table_name}.position") as described in http://www.redmine.org/issues/11002
  • Fix redmine-2.2.0\app\models\workflow_rule.rb. In the SQL query at the bottom change the fieldname “rule” to “[rule]” in two locations.

Fixed

  • Gantt charts don’t work
  • Can’t sort or group by a custom field
  • Dates for versions don’t work
  • Calendars don't show any events

Fixed

  • Log time feature is very slow on Ruby 1.8.7, and crashes on Ruby 1.9.x with "SystemStackError (stack level too deep):
    actionpack (3.2.9) lib/action_dispatch/middleware/reloader.rb:70"

Logging time works fine for me with SQLServer and other databases, this should be reported in a specific issue.

Actions #14

Updated by Etienne Massip about 11 years ago

Any reason why the gem declarations haven't been added to Gemfile?

Actions #15

Updated by Jean-Philippe Lang about 11 years ago

Installing lots of unused gems when running a simple bundle install is a problem IMHO. For example, tiny_tds won't compile unless you have freetds installed, resulting in an error when bundling. And having to specify all the databases you're not using with the --without option is far from user-friendly.

I'm thinking of modifying the Gemfile so that only the gems required for the database adapters used in the database configuration file (database.yml) are actually declared. The install process would then be:
  1. configure database.yml
  2. run `bundle install`

Please, have a look at the attached patch. Feedback is welcome.

Actions #16

Updated by Jan Niggemann (redmine.org team member) about 11 years ago

having to specify all the databases you're not using with the --without option is far from user-friendly.

I agree, your patch is a good idea and will simplify the installation for novice users.

Actions #17

Updated by Etienne Massip about 11 years ago

Jean-Philippe Lang wrote:

Installing lots of unused gems when running a simple bundle install is a problem IMHO. For example, tiny_tds won't compile unless you have freetds installed, resulting in an error when bundling. And having to specify all the databases you're not using with the --without option is far from user-friendly.

Fully agree.

I'm thinking of modifying the Gemfile so that only the gems required for the database adapters used in the database configuration file (database.yml) are actually declared. The install process would then be:
  1. configure database.yml
  2. run `bundle install`

Looks fine.

Please, have a look at the attached patch. Feedback is welcome.

Nice patch. What about:

  • not aborting on unknown adapter, just warn? People should then be able to use their own adapter in Gemfile.local
  • handling and aborting if no adapter has been found
Actions #18

Updated by Etienne Massip about 11 years ago

FWIW I migrated a live PostgreSQL DB to SQL Server and everything is working like a charm right now.

Actions #19

Updated by Jean-Philippe Lang about 11 years ago

  • Status changed from New to Closed
  • Assignee set to Jean-Philippe Lang
  • Resolution set to Fixed

Etienne Massip wrote:

FWIW I migrated a live PostgreSQL DB to SQL Server and everything is working like a charm right now.

Thanks for the feedback. Tests are now running with SQL Server 2012 on the integration server.

For linux users: with freetds 0.82 that was packaged for the server distro, a few tests was breaking the build with lots of "ActiveRecord::StatementInvalid: TinyTds::Error: Attempt to initiate a new Adaptive Server operation with results pending" errors. I had to install freetds 0.91 to fix that.

Actions #20

Updated by Etienne Massip about 11 years ago

  • Status changed from Closed to Reopened

Might be worth it supporting SQL Server on JRuby too?

Index: Gemfile
===================================================================
--- Gemfile    (revision 11250)
+++ Gemfile    (working copy)
@@ -53,9 +53,10 @@
       when /sqlite3/
         gem "sqlite3", :platforms => [:mri, :mingw]
         gem "activerecord-jdbcsqlite3-adapter", :platforms => :jruby
-      when /sqlserver/
+      when /sqlserver/, /jdbcmssql/
         gem "tiny_tds", "~> 0.5.1", :platforms => [:mri, :mingw]
         gem "activerecord-sqlserver-adapter", :platforms => [:mri, :mingw]
+        gem "activerecord-jdbcmssql-adapter", :platforms => :jruby
       else
         warn("Unknown database adapter `#{adapter}`, use Gemfile.local to load your own database gems")
       end

I haven't tried yet but I plan to do so (some day).

Actions #21

Updated by Jean-Philippe Lang about 11 years ago

  • Assignee changed from Jean-Philippe Lang to Etienne Massip
I was not able to make it work with JRuby:
  • using jdbc-jtds: I'm getting this error on startup: ActiveRecord::JDBCError: The driver encountered an unknown error: cannot link Java class net.sourceforge.jtds.jdbc.Driver, probable missing dependency: net/sourceforge/jtds/jdbc/Driver : Unsupported major.minor version 51.0
  • using jdbc-mssql-azure: while it's incompatible according to the activerecord-jdbc-adapter documentation, the application starts. But still, a migration raises an error that I don't get when using mri+freetds

Good luck!

Actions #22

Updated by Etienne Massip about 11 years ago

I was able to have it running with JRuby 1.7.2 except I had to use gem "activerecord-jdbcmssql-adapter", :git => 'git://github.com/jruby/activerecord-jdbc-adapter.git', :branch => 'jdbc-jtds-1.2.x', :platform => :jruby because of this issue.

Then I had some issue with badly displayed accentuated characters but since it affects also translated labels it should be caused by some Puma incompatibility.

Edit: I haven't hit your issue :|

Actions #23

Updated by Etienne Massip about 11 years ago

Jean-Philippe Lang wrote:

  • using jdbc-mssql-azure: while it's incompatible according to the activerecord-jdbc-adapter documentation, the application starts. But still, a migration raises an error that I don't get when using mri+freetds

There is a note here about Azure: https://github.com/jruby/activerecord-jdbc-adapter/tree/master/activerecord-jdbcmssql-adapter.

Actions #24

Updated by Jean-Philippe Lang about 11 years ago

Etienne Massip wrote:

I was able to have it running with JRuby 1.7.2 except I had to use gem "activerecord-jdbcmssql-adapter", :git => 'git://github.com/jruby/activerecord-jdbc-adapter.git', :branch => 'jdbc-jtds-1.2.x', :platform => :jruby because of this issue.

Then we'll wait for the fixed gem.

Actions #25

Updated by Marcel Nadje about 11 years ago

tiny_tds 0.5.1 fails connecting with integrated security on Windows systems. v0.6.0.rc1 fixes this issue. Can you update the gem file?

Actions #26

Updated by Jean-Philippe Lang about 11 years ago

Marcel Nadje wrote:

v0.6.0.rc1 fixes this issue.

0.5.1 works fine for me on Windows. Do you have any reference of this issue?

Actions #27

Updated by Marcel Nadje about 11 years ago

Jean-Philippe Lang wrote:

0.5.1 works fine for me on Windows. Do you have any reference of this issue?

Any plan on supporting Windows Authentication?

Actions #28

Updated by Jean-Philippe Lang about 11 years ago

  • Status changed from Reopened to Closed

I'd like not to bundle RC gems in Redmine so I think you'll have to edit your Gemfile if you want to use this one.

Actions #29

Updated by Etienne Massip about 11 years ago

  • Status changed from Closed to Reopened

Sorry =)

AR-JDBC 1.2.6 incoming and should make SQLServer JDBC adapter usable.

Actions #30

Updated by Etienne Massip about 11 years ago

AR-JDBC 1.2.6 has been released.

Actions #31

Updated by Jean-Philippe Lang about 11 years ago

  • Status changed from Reopened to Closed
  • Assignee changed from Etienne Massip to Jean-Philippe Lang

Still doesn't work for me with JRuby1.7.2 and:

gem "activerecord-jdbc-adapter", "1.2.6" 
gem "jdbc-jtds", "~> 1.2.7", :platforms => :jruby
gem "activerecord-jdbcmssql-adapter", :platforms => :jruby

I get this on db:migrate:

==  SetLanguageLengthToFive: migrating ========================================
-- change_column(:users, :language, :string, {:limit=>5, :default=>""})
rake aborted!
An error has occurred, this and all later migrations canceled:

ActiveRecord::JDBCError: Table sysobjects does not exist: select def.name from s
ysobjects def, syscolumns col, sysobjects tab where col.cdefault = def.id and co
l.name = 'language' and tab.name = 'users' and col.id = tab.id

I'm closing it since it's no longer an issue about Redmine support for SQLServer. I'd prefer that we track this in a separate ticket.

Actions #32

Updated by Etienne Massip about 11 years ago

gem "activerecord-jdbcmssql-adapter", "~> 1.2.6" :platforms => :jruby is enough since jdbc-jtds and activerecord-jdbc-adapter are installed as its dependencies and it solved my precedent issue as expected.

I still have encoding issue translation and every server I try, it must be an environment issue unrelated to DB since every string fetched from DB is fine but I've no clue which, will try to have a look when I get some time.

I'm running it in Windows BTW.

Jean-Philippe Lang wrote:

I get this on db:migrate:
[...]

I think this is a misconfiguration issue and that every account should have read access to these sys* tables.

Actions #33

Updated by Jean-Philippe Lang about 11 years ago

Etienne Massip wrote:

gem "activerecord-jdbcmssql-adapter", "~> 1.2.6" :platforms => :jruby is enough since jdbc-jtds and activerecord-jdbc-adapter are installed as its dependencies and it solved my precedent issue as expected.

jdbc-jtds after 1.2.7 is not Java 6 compatible, that's why I used this version.

I still have encoding issue translation and every server I try, it must be an environment issue unrelated to DB since every string fetched from DB is fine but I've no clue which, will try to have a look when I get some time.

Try to set JRUBY_OPTION to -J-Dfile.encoding = UTF8

I think this is a misconfiguration issue and that every account should have read access to these sys* tables.

rake db:migrate runs just fine with ruby1.9+tiny_tds and the same database account

Actions #34

Updated by Etienne Massip about 11 years ago

Jean-Philippe Lang wrote:

Try to set JRUBY_OPTION to -J-Dfile.encoding = UTF8

We have a winner!

I think this is a misconfiguration issue and that every account should have read access to these sys* tables.

rake db:migrate runs just fine with ruby1.9+tiny_tds and the same database account

Right, I migrated a blank DB and got the same error as yours.

Actions #35

Updated by Etienne Massip about 11 years ago

FWIW I dug into these errors, eventually fixed all of them and went through the whole migration process but these are nasty bugs in AR-JDBC MSSQL adapter and there will be some time until Redmine will be able to work with JRuby and MSSQL.

Actions

Also available in: Atom PDF