TinyTds::Error: Read/Write to the server failed (Azure)

[metaskills]

This issue's discussion was started by @TigerMint on TinyTDS' https://github.com/rails-sqlserver/tiny_tds/issues/249 issue tracker. But the core bug can be confirmed to something in the adapter or the Rails stack. I created a test/debug.rb script which did nothing but make an Azure test connection.

require 'tiny_tds'
c = TinyTds::Client.new(
  username: 'rails', database: 'activerecord_unittest', azure: true
  host: ENV['CI_AZURE_HOST'], password: ENV['CI_AZURE_PASS'],
)
puts c.execute("SELECT 1 AS [one]").each ;c.close

This will work just fine and I can watch the puts output on Appveyor. However, when adding these two lines to the top of the file, the write to server failed is encountered on WINDOWS only.

require 'bundler/setup'
Bundler.require :default, :development, :test

[metaskills]

OK, something is wrong with the Windows builds and I suspect it has something to do with memory, OpenSSL, or both. See the TDSDUMP logs below that I pulled form Appveyor. Things are good when we only require TinyTDS. However, when we require rails/all before requiring TinyTDS, things go south.

• GOOD: https://gist.github.com/metaskills/e5a29eca6ba88b16e871
• BAD: https://gist.github.com/metaskills/083986b1b1c0c6ad8769

Could it be that other gems, ruby stdlib, etc on Windows cause things to go south? For example, all the Rails tests on Appveyor work just fine on the local Appveyor DBs which do not use SSL. So this has something to do with SSL which Azure requires.

cc @larskanis

[dharmdip]

Hi @metaskills,

Based on conversation, stated looking into. Setup application on Linux box that seems working fine, then install on clean windows machine. It seems first can't connect and getting (TinyTds::Error (Write to the server failed (xxxxxxxxxx.database.windows.net:1433))) but from seconds request it seems working fine, every time restart server first time getting "write to server fail" and then able to connect from second request.

gem 'tiny_tds',' 0.9.5.rc.3', platforms: [:mingw, :mswin, :x64_mingw]
gem 'activerecord-sqlserver-adapter', '~> 4.2.0'

[metaskills]

Thanks. I am really going to need @larskanis help on this one. Maybe we should upgrade the dockers/compilation stuff in TinyTDS.

[dharmdip]

Okay, please let me know if i can be useful.

[larskanis]

Hm, sounds scary. Which Ruby version is it? Could I use the Azure test DB to reproduce and for diagnosis?

[metaskills]

@larskanis I think I tried a few different Ruby versions (via appveyor) and experienced the same issue. I can get you some credentials to our Azure DB. Can you shoot me a mail to ken[at]metaskills[dot]net and I can send them to you.

[YvesR]

Hello, same here, tried with

• ruby 2.0.0p598 (2014-11-13) [x64-mingw32]
• ruby 2.1.6p336 (2015-04-13 revision 50298) [x64-mingw32]

Same behavior. First data access results in "write failed". All other access works fine.

[metaskills]

@YvesR Can you see if you can track down why a Rails all require makes things go south? Like maybe do some tests requiring different things that rails requires that may load OpenSSL too. Maybe try requiring things like ActiveSupport then TinyTDS, etc?

[YvesR]

@metaskills

Ok I made a test script like yours:

require 'tiny_tds'

require 'bundler/setup'
#Bundler.require :default, :development, :test

c = TinyTds::Client.new(
  username: 'xxx', database: 'yyy', azure: true, 
  host: 'zzzz.database.windows.net',  password: 'secret',
)
puts c.execute("SELECT * FROM table").each ;c.close

It works in general against my azure DB and no CPU issues doing this. The moment I uncomment Bundler.require :default, :development, :test it crashes. So I went through my GEMFILE and commented all gems out, after I did activate each gem step by step.

Here is my result list (commented are causing error, not-commented makes no trouble):

gem 'rails', '4.2.5'

gem 'sass-rails'
gem 'uglifier'
gem 'coffee-rails'
gem 'yui-compressor'
gem 'sprockets', '2.12.3'
gem 'string-scrub' # scrub method for strings, will be default in ruby >=2.1.0
gem 'tzinfo-data', platforms: [:mingw, :mswin, :x64_mingw]
#gem 'activerecord-session_store'
gem 'net-ldap', '~> 0.9.0'
#gem  'tiny_tds', '~> 1.0.5'
gem  'tiny_tds', '~> 1.0.6'
#gem 'activerecord-sqlserver-adapter', '>=4.2' #'~> 3.2.12'
#gem 'composite_primary_keys' #, '=3.1.0'
#gem 'rubyzip'
gem "rake"
gem 'icalendar'
#gem 'nokogiri'
gem 'acts_as_list'
#gem 'will_paginate'
gem 'acts_as_tree', '~> 2.5.1'
#gem 'rufus-scheduler'
#gem 'sunspot', '~> 2.2.0'
#gem 'sunspot_rails', '~> 2.2.0'
#gem 'progress_bar'
gem 'spawnling', '=2.1.5'#, :git => 'git://github.com/rfc2822/spawn'
#gem 'i18n' #, '=0.5.0'
gem 'kramdown'
#gem 'bullet'
#gem 'ruby-prof'
#gem 'rails_db_objects'
#gem 'simple_calendar', '~> 2.0'
#gem 'write_xlsx'
gem 'thin', '=1.6.4'#'>=1.5'
gem 'eventmachine', '1.0.7'

Next step Dig into a gem that cause trouble to try find the cause :)

[YvesR]

Still need to find out why those active gems cause the problem. Anyone has tips how to track down / debug this? If not I have to try review all the code and find similarities between the gems that might cause the problem.

Is @larskanis watching this and have some ideas?

[YvesR]

@metaskills can you tell me how do I know that I use encrypted connection? I did setup a SQL server local and added a SSL certificate. Then I configured SQL server to use encrypted connection. I can connect via SSMS and a C# test script. Also via ruby console. What I do not know if the encryption is active or not using rails.

My goal was to make sure that our problem is Azure related and not general using encrypted connection.

[nicosuria]

Just curious if there was any resolution to this.

[YvesR]

@nicosuria It turned out that all the trouble was a problem within open-ssl implementation. Loading caused tremendous CPU usage time during startup. First data access often failed.

After lots of testing and awesome work from metakills and his maintainers the new version works perfect in all scenarios.

So just use the latest stable, works perfect with Azure, even with data access in initializing on a rails session.