tinytds "server name not found" error, maybe related to connection limits

[baseliner]

Hi - I have a Rails 3.2.3 app that uses the SQL server adapter 3.2.4 along with the Octopus gem to connect to multiple SQL Server 2008 databases on two DB servers. My app collects metrics from data spread out across the DBs on both DB servers. When I run the app and try to collect data from all the DBs on both DB servers, I get the following error from tinytds when it tries to connect to a seemingly-random DB (i.e. it doesn't choke on the same DB each time):

TinyTds::Error (Server name not found in configuration files):

I do know that the problem is not with "server name" config in database.yml or in the Octopus shards since if I run the app to only collect data from databases on one DB server at a time, things work fine. i.e. the same DB at which the above error happens when I'm collecting data from all DBs does not error when I limit data collection to just the DBs on the server that this DB is on.

Does this have something to do with the number of connections allowed either by tinytds or on SQL Server? My search came up with the pool.conf file of freetds for the former, and a rather large limit for the latter. I tried bumping up the "max pool conn" value in pool.conf but that did not help. I'm wondering if the problem occurs due to the fact that I'm connecting multiple times to the same DB server to access data from different DBs on that server. Maybe there's a better way to do this so that connections are reused? Any help would be much appreciated! Also, is there a log file for tinytds that may provide some insight? Thanks.

[Ken Collins]

This sounds like a configuration issue. First, you should share what your environment and configs are. Like what version of TinyTDS and FreeTDS do you have installed? What does your database.yml look like? That will show how you are passing connection info down to TinyTDS which if your using things like a `dataserver` vs host and port, you would then have to show us what your freetds.conf file looks like. BTW, it is really easy to debug the connection properties at the command line with TinyTDS. So take any args that were being passed to the ActiveRecord abstract adapter and use them in TinyTDS like so.

client = TinyTds::Clinet.new ...

See here for the basic connection options. https://github.com/rails-sqlserver/tiny_tds

 - Ken

 

--
You received this message because you are subscribed to the Google Groups "Rails SQLServer Adapter" group.
To view this discussion on the web visit https://groups.google.com/d/msg/rails-sqlserver-adapter/-/J9Jgt7M22RQJ.
To post to this group, send email to rails-sqlse...@googlegroups.com.
To unsubscribe from this group, send email to rails-sqlserver-a...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/rails-sqlserver-adapter?hl=en.

[baseliner]

Hi Ken - the reason I don't think it's config related is because the exact same config works if I limit the app to only look at DBs on one server at a time. But maybe there's more that I'm missing. Here's my environment:

My dev machine that hosts the Rails app: Mac OS X 10.7.4

Ruby: ruby 1.9.3p0 (2011-10-30 revision 33570) [x86_64-darwin11.2.0]

Rails: 3.2.3

Output of tsql -C:

   Version: freetds v0.91

             freetds.conf directory: /usr/local/etc

     MS db-lib source compatibility: no

        Sybase binary compatibility: no

                      Thread safety: yes

                      iconv library: yes

                        TDS version: 5.0

                              iODBC: yes

                           unixodbc: no

              SSPI "trusted" logins: no

                           Kerberos: no

tiny_tds: 0.5.1

database.yml (and all the shards in Octopus also look similar with just the database name and host being different):

production:

  adapter: sqlserver

  mode: dblib

  host: test

  database: Customer

  username: sa

  password: secret

  verify_connection: true

Relevant Gemfile entries: 

gem 'rails', '3.2.3'

gem 'ar-octopus', '0.4.0', :require => "octopus", :git => 'git://github.com/baseliners/octopus.git'

gem 'tiny_tds'

gem 'activerecord-sqlserver-adapter'

In database.yml, I've tried specifying port as 1433 explicitly in addition to the host but same result. I turned on logging for freetds and also tried various freetds config options (from http://www.freetds.org/userguide/freetdsconf.htm) to dump out various levels of logs but I don't see anything suspicious (maybe I just don't know how to read the file). I am attaching the zipped freetds.log file here - I cleared out the file and made a run for this app where the error occurs. Please let me know if you need any other info, and thanks for your help!

Norm

To post to this group, send email to rails-sqlserver-adapter@googlegroups.com.
To unsubscribe from this group, send email to rails-sqlserver-adapter+unsub...@googlegroups.com.

[Ken Collins]

Can you show me your freetds.conf file?

Also, the `verify_connection` has no meaning in database.yml and hence TinyTDS's config options.


- Ken

[baseliner]

Sure.. pasting contents below. I commented out the timeout settings based on a previous thread where you'd suggested not using any timeout settings. Also modified the debug flags. None of the servers defined in this file are being used in the Rails app's host field (in database.yml or in Octopus shards). Btw, I just tried running the app using DBs on another server (fewer DBs in total than both my earlier DB servers combined) and it works fine.

#   $Id: freetds.conf,v 1.12 2007/12/25 06:02:36 jklowden Exp $

#

# This file is installed by FreeTDS if no file by the same 

# name is found in the installation directory.  

#

# For information about the layout of this file and its settings, 

# see the freetds.conf manpage "man freetds.conf".  

# Global settings are overridden by those in a database

# server specific section

[global]

        # TDS protocol version

;       tds version = 4.2

        # Whether to write a TDSDUMP file for diagnostic purposes

        # (setting this to /tmp is insecure on a multi-user system)

        dump file = /tmp/freetds.log

        #debug flags = 0xffff

        debug flags = 0x04

        # Command and connection timeouts

#;      timeout = 10

#;      connect timeout = 10

        

        # If you get out-of-memory errors, it may mean that your client

        # is trying to allocate a huge buffer for a TEXT field.  

        # Try setting 'text size' to a more reasonable limit 

        text size = 64512

# A typical Sybase server

[egServer50]

        host = symachine.domain.com

        port = 5000

        tds version = 5.0

# A typical Microsoft server

[test]

        host = test

        port = 1433

        tds version = 7.1

[heraclitus]

        host = heraclitus

        port = 1433

        tds version = 8.0

[pegasus]

        host = pegasus

        port = 1433

        tds version = 8.0

#Win2k8x64 SQLServer on VM setup by port fwding berkeley:1433

[euclid]

        host = berkeley

        port = 1433

        tds version = 7.1

[baseliner]

Oh and thanks for pointing out the verify_connection issue. I thought this had an effect while using Octopus shards but sounds like this is not the case.

[Ken Collins]

Looking at the conf file, I think you might be suffering from a creepy tds version issue. One thing about TinyTDS is that you can move away from conf file entries and named `dataserver` configurations that match to entries in freetds.conf to just a nice `host` like most other DBs. You should read this.

https://github.com/rails-sqlserver/tiny_tds/#freetds-compatibility--configuration

Specifically the bullet points about --with-tdsver configuration for FreeTDS. In the TinyTDS repo, when we install FreeTDS locally via miniportile and distribute native gems, we configure FreeTDS with a logical default of `--with-tdsver=7.1`, which you should do too because anything lower is technically not SQL Server. Like the README says, this should not be necessary, but I have see it cause problems. FYI, I do not use homebrew, but I did do a patch that changed this so people would not get caught up in it.

This whole tds version things gets more confusing when people think that 7.2 is the right one to use for 2005 and upward along with the fact that FreeTDS had set years back 8.0 to be the next MS version but MS did 7.2 which means 8.0 is the same as 7.1 which is what we should all be using. More confusion sets in when people try to set things in a conf file which pwns the runtime configuration the client (ruby) can pass down. TL;DR, people should really ditch freetds.conf files and just let TinyTDS handle it (minus the rare occasions when needed, then you have to be smart about this stuff).

So, reinstall FreeTDS using `--with-tdsver=7.1` and then uninstall and re-install the TinyTDS gem. Then, if you use a conf file, I always use 8.0, but if you do not use a conf file, then TinyTDS will take care of the version and set it.

Now, moving past that, you need to break down your debugging and start attacking your connection issues from a low level and move up. I think the fact you are sitting on top of so many things is confusing thing. Post the re-install of FreeTDS and TinyTDS, just do the basics in IRB and test some simple connections. Refer to the TinyTDS README, but it would go something like this.

client = TinyTds::Client.new :username => 'sa', :password => 'secret', :host => 'mydb.host.net'
client.execute("SELECT 1 AS [one]").each

If you are using things like `test` for the host, then I assume you have made an entry in your /etc/hosts so that would technically work. But an IP will work for the `host`config too, so it is up to you how you wire things up. Once you have made sure that basic connections work, then you can move to the adapter and then octopus. If then things do not work, then you need to put your debugger hat on and edit the adapter connect method and QA the args being passed down to TinyTDS. Once you can see those args, you can QA them again in IRB using simple TinyTDS. It really does not get more complicated than that. When you break it down, all the adapter is doing (octopus or not) is just passing some connection args down to TinyTDS, find out what those are and debug the configs.


- Ken

[baseliner]

Ken - thanks for the detailed info. I will try recompiling freetds with the tdsver option and reinstall tinytds. Btw, one thing I forgot to mention in my original post is that the app first connects to every DB on both DB servers to do a check for a certain parameter, then builds a shorter list of DBs to collect the more intensive metrics.. i.e. the DBs on which I'm getting this tinytds error were successfully connected to and queried by the Rails app during the first pass. During the second pass (the shards are not re-initialized before the second pass so they're exactly the same as what was used in the first pass), the app gets through some DBs before failing on a random DB.

Looks like the freetds website is down.. any ideas?

[baseliner]

I was able to find a previously downloaded tarball of freetds 0.91 on my machine so I compiled/installed it with the tdsver flag as you suggested and then reinstalled tiny_tds. I then removed the freetds.conf file. tsql -C now says:

Compile-time settings (established with the "configure" script)

                            Version: freetds v0.91

             freetds.conf directory: /usr/local/etc

     MS db-lib source compatibility: no

        Sybase binary compatibility: no

                      Thread safety: yes

                      iconv library: yes

                        TDS version: 7.1

                              iODBC: yes

                           unixodbc: no

              SSPI "trusted" logins: no

                           Kerberos: no

So tdsver looks good. Reran and still having the same issue. I took the DB that threw the error on one of my runs and manually connected to it in a Rails console like you'd suggested. No problem there. So I then went into the adapter's connect method and printed out the "config" variable. Here's what it looks like for the DB that the app got stuck at:

adapter config = {:mode=>:dblib, :host=>"SQLSERVER2", :adapter=>"sqlserver", :database=>"WCRA", :username=>"sa", :password=>"secret", :octopus_shard=>"WCRA"}

And here's the "WCRA" shard:

shard info = {"mode"=>"dblib", "host"=>"SQLSERVER2", "adapter"=>"sqlserver", "database"=>"WCRA", "username"=>"sa", "password"=>"secret"}

Everything looks good to me but tinytds still gives the same error. I tried looking at the tiny_tds gem folder to try to add some debug output but did not get very far there. Tried a bundle open on the .bundle file but got an error about bench_press being absent (after installing that, it now says another gem is missing). Before I go further down that path, based on the above, do you think the issue might be in tiny_tds and would debugging that be the right direction? If yes, please let me know if there's a better way to do it than via bundle open.. Thanks! 

[Ken Collins]

Doing a bundle open for tiny_tds wont give you much, most of the code is written in C and at most you can only confirm the same options that are being passed down to it via the ruby connect method. At this point, you may have to start digging into how octopus hooks into ActiveRecord and hence the adapters. Perhaps it is method chaining or monkey patching something that is causing issues?

- Ken

[baseliner]

Ken - I've been able to narrow down the issue to within the connect method in TinyTDS after adding a bunch of logging to that method. Specifically, the error is being thrown within the TinyTds::Client.new call in the connect method. Given this and the fact that the first pass of the app was able to successfully connect to all the DBs makes me wonder if it has something to do with how the connection to the DB is opened and/or reused by TinyTDS/FreeTDS. So the question is, if the "config" parameters I posted in the last message look OK (and I'm guessing they're OK given that the exact same configs worked during the first pass), what's causing TinyTDS's Client.new method to fail the second time around? Since the app is successively connecting to several DBs on the same DB server, is there a limit on the number of connections that TinyTDS/FreeTDS can open to the same server? Some searching led me to pool.conf and I've tried increasing the "max pool conn" parameter in pool.conf's "[global]" section to 400 but that isn't helping. Here's another datapoint that might support this connection limit theory: in the first pass, the app connects to 73 DBs on SQLSERVER2. In the second pass, it consistently gets stuck between the 25th and 28th DB on this server. Almost seems like the magic number 100 may have something to do here.. any thoughts?

[Ken Collins]

On Jun 24, 2012, at 1:43 AM, baseliner wrote:

> is there a limit on the number of connections that TinyTDS/FreeTDS can open to the same server?

Not that I am aware of. I would ask that question of SQL Server itself before TinyTDS or ODBC.

> Some searching led me to pool.conf

Keep away from that, ActiveRecord manages it's own pool.

Here is a easy way to test your connections. https://gist.github.com/2982826
I was able to easily go up to 1000. Perhaps your server instance is limited to a certain amount of connections?


- Ken

[baseliner]

Hi Ken - I had to drop this last year as the problem went away when I moved to a production server. However, it's back again and this time I had a chance to try out the test you have below. I am able to go up to 83 connections and after that I get a SystemStackError:

➜ ~/Downloads $ ruby max.rb 83

Tested 83 connections.

Process IDs: [56, 58, 59, 60, 61, 62, 63, 64, 65, 66, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140]

➜ ~/Downloads $ ruby max.rb 84

/opt/ruby/lib/ruby/gems/1.9.1/gems/tiny_tds-0.6.0.rc1/lib/tiny_tds/client.rb:70: stack level too deep (SystemStackError)

I tried with the 0.5.1 version and also the 0.6.0rc1 version and same result. Any ideas on where I'd go from here? I'm on a Mac OS X 10.8.3 with ruby 1.9.3p0 (2011-10-30 revision 33570) [x86_64-darwin11.2.0]. Thanks!