Error migrating data from sql server to mongo

[elenaaralla]

Hi, I'm trying to migrate data from sql server to mongodb, and I have this error during data transfer...

the error is:

Copying t_messages (54/59): (20000/20000) 100% |ooooo| Time: 00:00:03 /Users/elena/.rbenv/versions/2.3.0/lib/ruby/gems/2.3.0/gems/activerecord-sqlserver-adapter-3.2.0/lib/active_record/connection_adapters/sqlserver/database_statements.rb:414:in `each': TinyTds::Error: Adaptive Server connection timed out: EXEC sp_executesql N'SELECT * FROM (ActiveRecord::StatementInvalid) ( SELECT TOP 1100000 *, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rnum FROM t_messages ) t_messages WHERE rnum > 1080000'

the table t_messages has 1.174.000 record...

Can you help me? Thank you and sorry for my poor english... Elena.

[anlek]

Try changing the timeout time, more information: https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/wiki/Using-TinyTds#advanced-tinytds

[elenaaralla]

Hi, unfortunately I'm not able to find the database.yml file on my Mac...

I've changed timeout on sql server configuration and in freetds.conf with no success.

If I write timeout: 5000 in database.config, when I check the file with:

mongify check database.config

this error occurs:

... bash-3.2$ mongify check database.config (eval):9:in `block in parse': Object#timeout is deprecated, use Timeout.timeout instead. ...

and writing Timeout.timeout 5000 this other error occurs

... bash-3.2$ mongify check database.config /Users/elena/.rbenv/versions/2.3.0/lib/ruby/gems/2.3.0/gems/mongify-1.2.4/lib/mongify/configuration.rb:14:in instance_eval': no block given (yield) (LocalJumpError) from /Users/elena/.rbenv/versions/2.3.0/lib/ruby/2.3.0/timeout.rb:33:inblock in catch' ...

Any ideas? Thanks for your attention, Elena.

[anlek]

Setting Timeout is only deprecated (it will stop working at some point, but it still works). Have you tried to run process after you added timeout to database.config?

[elenaaralla]

Hi, yes, I tried and got errors

if I write "timeout" in database.config

sql_connection do
  adapter   "sqlserver"
  host      "<my host IP>"
  port      3408
  username  "<my un>"
  password  "<my pw>"
  database  "ASM_DB"
  timeout 10000
end

mongodb_connection do
  host      "localhost"
  database  "ASM_BIG_MODB"
end

and run: mongify process database.config translation.rb I get this error:

bash-3.2$ mongify process database.config translation.rb
(eval):8:in `block in parse': Object#timeout is deprecated, use Timeout.timeout instead.
/Users/elena/.rbenv/versions/2.3.0/lib/ruby/gems/2.3.0/gems/mongify-1.2.4/lib/mongify/configuration.rb:14:in `instance_eval': no block given (yield) (LocalJumpError)...

if I write Timeout.timeout, as suggested by the above error

sql_connection do
  adapter   "sqlserver"
  host      "<my host IP>"
  port      3408
  username  "<my un>"
  password  "<my pw>"
  database  "ASM_DB"
  Timeout.timeout 10000
end

mongodb_connection do
  host      "localhost"
  database  "ASM_BIG_MODB"
end

and run: mongify process database.config translation.rb I get this error:

bash-3.2$  mongify process database.config translation.rb
/Users/elena/.rbenv/versions/2.3.0/lib/ruby/gems/2.3.0/gems/mongify-1.2.4/lib/mongify/configuration.rb:14:in `instance_eval': no block given (yield) (LocalJumpError)
    from /Users/elena/.rbenv/versions/2.3.0/lib/ruby/2.3.0/timeout.rb:33:in `block in catch'...

What can I try? Any other ideas? Thank you, Elena.

[anlek]

I'm sorry Elena, I don't have access to a MS Sql Server to test with. Is this server remote? If so, can you run it locally (clone the information), network speeds will affect how long things take and cause timeout issues.

[dboune]

I've just experienced this. I have to first admit general ignorance in regard to ruby. I know enough to be dangerous. Please forgive me if I get all the nomenclature, syntax, and everything else ruby specific, completely wrong.

Some part of the code that deals with configuration somehow executes the value "timeout" in the config file as a method instead of just passing it on to be used in the ActiveRecord connection. Ruby happens to have one such method, and I suppose it is somehow available to run in this context, so it does, but the call is incorrect, and shouldn't have happened anyhow.

I fixed this locally by using a different name entirely, "dbtimeout", and then in BaseConnection->to_hash I added a gsub to change dbtimeout into timeout. I am acutely aware that this is the wrong way to do this.

There is a larger problem here. ActiveRecord plugins don't all use 100% standard variable names. One may use timeout, another may be query_timeout. The way the configuration handling for mongify is written doesn't account for that.

[n-raghu]

May I know if the above issue is resolved? I was trying to pull data from SQL Server to MongoDB and sees the same error.

lrsa@SERVER5:~$ mongify check database.config /home/lrsa/.rvm/gems/ruby-2.4.0/gems/mongify-1.3.1/lib/mongify/configuration.rb:14:in instance_eval': no block given (yield) (LocalJumpError) from /home/lrsa/.rvm/rubies/ruby-2.4.0/lib/ruby/2.4.0/timeout.rb:33:in block in catch' from /home/lrsa/.rvm/rubies/ruby-2.4.0/lib/ruby/2.4.0/timeout.rb:33:in catch' from /home/lrsa/.rvm/rubies/ruby-2.4.0/lib/ruby/2.4.0/timeout.rb:33:in catch' from /home/lrsa/.rvm/rubies/ruby-2.4.0/lib/ruby/2.4.0/timeout.rb:108:in timeout' from (eval):8:in block in parse' from /home/lrsa/.rvm/gems/ruby-2.4.0/gems/mongify-1.3.1/lib/mongify/configuration.rb:34:in instance_exec' from /home/lrsa/.rvm/gems/ruby-2.4.0/gems/mongify-1.3.1/lib/mongify/configuration.rb:34:in sql_connection' from (eval):1:in parse' from /home/lrsa/.rvm/gems/ruby-2.4.0/gems/mongify-1.3.1/lib/mongify/configuration.rb:14:in instance_eval' from /home/lrsa/.rvm/gems/ruby-2.4.0/gems/mongify-1.3.1/lib/mongify/configuration.rb:14:in parse' from /home/lrsa/.rvm/gems/ruby-2.4.0/gems/mongify-1.3.1/lib/mongify/cli/options.rb:78:in config_file' from /home/lrsa/.rvm/gems/ruby-2.4.0/gems/mongify-1.3.1/lib/mongify/cli/options.rb:58:in parse' from /home/lrsa/.rvm/gems/ruby-2.4.0/gems/mongify-1.3.1/lib/mongify/cli/application.rb:27:in execute!' from /home/lrsa/.rvm/gems/ruby-2.4.0/gems/mongify-1.3.1/bin/mongify:15:in <top (required)>' from /home/lrsa/.rvm/gems/ruby-2.4.0/bin/mongify:22:in load' from /home/lrsa/.rvm/gems/ruby-2.4.0/bin/mongify:22:in <main>' from /home/lrsa/.rvm/gems/ruby-2.4.0/bin/ruby_executable_hooks:15:in eval' from /home/lrsa/.rvm/gems/ruby-2.4.0/bin/ruby_executable_hooks:15:in `

'

[atodicebear]

Same here as above. Need to Increase Timeout on MsSql but it throws Error : block in catch,catch,and so on as seen above

[davian-007]

Hi everybody, I'm trying to import sql server database to mongo with mongify, I'm using Windows 10, I dont know where to put or where to find database configuration file, I run mongify check datbase.config and I always get this, Error: Database Configuration file is missing or cannot be found, could anybody give me some help, thanks

[anlek]

@davian-007 You must create the database config file. Please be sure to read the getting started section.

[davian-007]

I do that now I am getting error when check the database.config it says sqlserver-adapter gem is found, I am trying to migrate ms SQL server database to mongodb

[arboks]

Hello there I have the same problem than @n-raghu an @atodicebear, trying to use mongify with sqlserver but i miss to simply check my database.config I saw on issue #152 someone with a similar error who solved it by adding a runtime dependency in a gemfile.

However i'm new in Ruby and i don't really understand what to modify. I suppose i have to use add_runtime_dependency(gem, *requirements) but don't know neither where and how

if someone could help, that would be great

[devabdicant]

the very same problem here. Please help

[anlek]

@devabdicant and @arboks, I'm sorry I'm unable to help with a given problem as I unable to test any solution I provide. You do NOT require hey runtime dependency. Are you working on a local setup or remote? Have you tried tweaking the timeout in tinyTDS? What is the actual error you're getting?

[devabdicant]

Hi, The funny point here is that you don't require a SQL server to test this because the problem is while loading the adapter for 'sqlserver'. I suspect something with gem installation stuff. I'll answer your questions. When I check database.config:

mongify check database.config
/var/lib/gems/2.3.0/gems/activerecord-4.2.11.1/lib/active_record/connection_adapters/connection_specification.rb:177:in `rescue in spec': Specified 'sqlserver' for database adapter, but the gem is not loaded. Add `gem 'activerecord-sqlserver-adapter'` to your Gemfile (and ensure its version is at the minimum required by ActiveRecord). (Gem::LoadError)
	from /var/lib/gems/2.3.0/gems/activerecord-4.2.11.1/lib/active_record/connection_adapters/connection_specification.rb:174:in `spec'
	from /var/lib/gems/2.3.0/gems/activerecord-4.2.11.1/lib/active_record/connection_handling.rb:50:in `establish_connection'
	from /var/lib/gems/2.3.0/gems/mongify-1.3.2/lib/mongify/database/sql_connection.rb:40:in `setup_connection_adapter'
	from /var/lib/gems/2.3.0/gems/mongify-1.3.2/lib/mongify/database/sql_connection.rb:56:in `has_connection?'
	from /var/lib/gems/2.3.0/gems/mongify-1.3.2/lib/mongify/cli/command/worker.rb:99:in `check_sql_connection'
	from /var/lib/gems/2.3.0/gems/mongify-1.3.2/lib/mongify/cli/command/worker.rb:68:in `execute'
	from /var/lib/gems/2.3.0/gems/mongify-1.3.2/lib/mongify/cli/application.rb:28:in `execute!'
	from /var/lib/gems/2.3.0/gems/mongify-1.3.2/bin/mongify:17:in `<top (required)>'
	from /usr/local/bin/mongify:23:in `load'
	from /usr/local/bin/mongify:23:in `<main>'

when I check my gems with 'gem list':

*** LOCAL GEMS ***

activemodel (5.2.2.1, 4.2.11.1)
activerecord (5.2.2.1, 4.2.11.1)
activerecord-sqlserver-adapter (5.2.0)
activesupport (5.2.2.1, 4.2.11.1)
arel (9.0.0, 6.0.4)
bigdecimal (1.2.8)
bson (1.12.5)
bson_ext (1.12.5)
builder (3.2.3)
bundler (1.11.2)
concurrent-ruby (1.1.5)
did_you_mean (1.0.0)
gist (4.5.0)
highline (1.7.8)
i18n (0.9.5)
io-console (0.4.5)
json (1.8.3)
mini_portile2 (2.4.0)
minitest (5.8.4)
molinillo (0.4.3)
mongify (1.3.2)
mongo (1.12.5)
mysql (2.9.1)
net-http-persistent (2.9.4)
net-telnet (0.1.1)
power_assert (0.2.7)
psych (2.0.17)
rake (10.5.0)
rdoc (4.2.1)
test-unit (3.1.7)
thor (0.19.1)
thread_safe (0.3.6)
tiny_tds (1.3.0)
tzinfo (1.2.5)

It is installed buy I suspect it is not 'loaded'. It says 'Add (...) to your Gemfile' but I don't know how. Can you help us there? Everything in local.

About the tweaking...what do I have to do? This is my config file in case you are wondering:

sql_connection do
  adapter   "sqlserver"
  host      "localhost"
  username  "admin"
  password  "admin"
  database  "test"
  batch_size  10000
end

mongodb_connection do
  host      "localhost"
  database  "mongify_test"
end

Thanks so much for the support!!

[anlek]

I'm not sure Mongify can work with sqlserver adaptor, (this was before it's time), try setting up a tinyTDS to see if you can get it working with that (see https://github.com/anlek/mongify/issues/96#issuecomment-220718098)

[davidquintard]

How to increase timeout ?

mongify process database.config translation.rb update references take hours and fall into timeout. My Mysql database is about 1Go.