Timeout error

[Neo]

Hi All,

I am using tinytds + activerecord-sqlserver-adapter combination for
Rails - SQLServer connectivity.
Its working fine but not all the time. Sometimes i am getting this
weird error.

"ActiveRecord::StatementInvalid: TinyTds::Error: Adaptive Server
connection timed out:"

Is it an error from our side (tiny_tds and sqlserver gem config) or
sqlserver side? I am getting confused.

After restarting the server, its working fine. But this behaviour
should not happen for production environments right?

In console also, I am facing the same issue.

My System Info:

OS - Ubuntu 10.04 LTS
Ruby - 1.8.7
Rails - 2.2.2
Tiny_tds - 0.2.3
activerecord-sqlserver-adapter - 2.3.12
SQLSERVER - 2008

Please help me to resolve this issue

Thanks in advance

Regards,
Neo

[Ken Collins]

Perhaps you have a query that is taking too long. What is your timeout set to? See this page for details.

https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/wiki/Using-TinyTds

Also, it is incorrect to say that timeouts are something that would/should not happen in production. The code is there in the adapter for it and every low level connection supports the notion. It is very valid to expect hoptoads or exception notifications if your queries are taking longer than your specified timeouts. Lemme know what you find out.

- Ken

[Neo]

Hi Ken,

Its just a simple select query like below

"select * from <tablename>" (no joins)

FYI, my table has 8000 records only (totally)

I agree that timeout may occur in production too. If timeout happens
because of query, then its ok. But here in my case, its a simple
select query right?
My Question is why db connectivity works well sometime and failing
sometime for the same (simple) select query? Is there any chance for
issues at the SQLServer side?

Please guide me

Thanks,
Neo

On Dec 22, 7:34 am, Ken Collins <k...@metaskills.net> wrote:
> Perhaps you have a query that is taking too long. What is your timeout set to? See this page for details.
>

> https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/wik...

[Scott Jacobsen]

I've found the 'Activity Monitor' in sql server is a good place to
start. Right click on your database server in the object explorer in
Management Studio and select 'Activity Monitor'. If your query is taking
many seconds to run on the sql server side it should show up in the
'Long Running Queries' section. While the query is running you should
see it in the 'Processes' section.

Also check your application event log on the db server for any errors
that might have been logged by sqlserver.

If you don't see you query running in the activity monitor, but it looks
like it is still running in your rails code then it is a good bet the
problem is network related (try and ping your db server from the rails
side while the query is running) or else there is a problem somewhere in
the stack on the rails side.

--
Scott Jacobsen
303-800-2711

[Ken Collins]

There are. In fact, I have an outstanding TinyTds issue around it not handling poor network connections well and recovering in a stable state for the next query. Do you think your connection falls under the "poor" category? If so, you'll have to wait a few day or so, maybe after the holidays till I find time to retool some things in the code.

- Ken

to do more work and test that its connection can recover from very poor network conditions.

> --
> You received this message because you are subscribed to the Google Groups "Rails SQLServer Adapter" group.
> 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.
>

[Neo]

Thanks for the reply.
I dont think my case is same. My network connections are fine. Because
the same connectivity is working fine with ruby-odbc layer without any
timeout issues.

Regards,
Neo

[Ken Collins]

Well, ruby-odbc would not suffer from a poor connection that would hurt TinyTds. It would be helpful if you could give me an assessment on your thoughts of the quality of the connection without using ruby-odbc as a benchmark. IE, what type of network latency do you have, if any? Are you going thru a VPN, to point A then point B? Is it a local connection that absolutely has no issues? Is it a public IP connection from one side of the country to the other? See what I'm getting at? Just trying to get an idea of the general quality of your connection and wondering if this is related to an already known issue or if I should try to help in some other way.

- Ken

[Neo]

Hi Ken,

I am not using any VPN.
My SQLServer has a static IP, I am using that IP address along with
SQLserver default port config (1433) in my freetds conf .

I am using Ubuntu 10.04 LTS and my ruby version is 1.8.7

Additional Info:

Rails - 2.2.2
Tiny_tds - 0.2.3
activerecord-sqlserver-adapter - 2.3.12
SQLSERVER - 2008

My Internet Speed is around 14Mbps Download speed / 5Mbps upload
speed.

I hope this info will help you for your R&D

Thanks,
Neo

[Ken Collins]

Is your static IP on a LAN along with your computer? What are your ping times and packet loss too?

- Ken

[Neo]

No, its not on LAN. My SQLServer is on a dedicated server box (with
static ip). When i ping that ipaddress from my machine, i am not
seeing any packet loss. Its pinging properly.

Thanks,
Neo

[Ken Collins]

So I saw a thread this morning on FreeTDS I think may be related to this. Inline below. So it seems if you are having a timeout issue, upgrade to the latest (unreleased) version of FreeTDS. FYI, these versions have been tested with TInyTDS too.

- Ken

On Feb 25, 2011, at 5:07 AM, Frediano Ziglio wrote:

> 2011/2/24 Doug <duck...@yahoo.com>:
>> Hi,
>>
>> Have been using freetds 0.64 on 64-bit red hat linux for a long time to connect to MS SQL Server 2000 and MS SQL Server 2005 and it worked fine.
>>
>> Recently tried to use it on MS SQL 2008 and SQL 2008 R2 server running Window Server 2008 R2 Enterprise (64 bit). The SQL servers do not have encryption turned on. In both cases, it was able to connect and could do simple short queries. However when tried to run a long running query (a simple update), the query would start and after a while, it would return an error stating "read from server failed" and the connection is dropped. Tried that with freetds version 0.82 and compiled with TDS version 8.0 and ran into the same issue.
>>
>> Any help is greatly appreciated! Thanks.
>> Doug
>
> It's a known problem related to a mis-detected timeout reported as a
> read error. Use post 0.82 patches (http://freetds.sourceforge.net/) or
> patched version.
>
> bye
> freddy77
> _______________________________________________
> FreeTDS mailing list
> Fre...@lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds

[twshelton]

I'm having a similar issue. Everything works fine until I run a big
query. The one in question is taking about 20 seconds (crazy report)
and I set the timeout value to 60 seconds just to test but I keep
getting these two errors:

TinyTds::Error: Adaptive Server connection timed out:

and
TinyTds::Error: Bad token from the server: Datastream processing out
of sync:

If I limit the query so that it runs quickly, it's good.

Not sure how to replicate this since the data is client data but would
be happy to provide as much detail as necessary to make it work.

Rails 2.3.11 (Linux) connecting to SQLServer 2000

ODBC works fine in all.

Thanks,
Thomas

On Feb 25, 7:12 am, Ken Collins <k...@metaskills.net> wrote:
> So I saw a thread this morning on FreeTDS I think may be related to this. Inline below. So it seems if you are having a timeout issue, upgrade to the latest (unreleased) version of FreeTDS. FYI, these versions have been tested with TInyTDS too.
>
>  - Ken
>
> On Feb 25, 2011, at 5:07 AM, Frediano Ziglio wrote:
>
>
>
>
>
>
>

> > 2011/2/24 Doug <duckd...@yahoo.com>:

> >> Hi,
>
> >> Have been using freetds 0.64 on 64-bit red hat linux for a long time to connect to MS SQL Server 2000 and MS SQL Server 2005 and it worked fine.
>
> >>  Recently tried to use it on MS SQL 2008 and SQL 2008 R2 server running Window Server 2008 R2 Enterprise (64 bit).  The SQL servers do not have encryption turned on.   In both cases, it was able to connect and could do simple short queries.  However when tried to run a long running query (a simple update), the query would start and after a while, it would return an error stating "read from server failed" and the connection is dropped.  Tried that with freetds version 0.82 and compiled with TDS version 8.0 and ran into the same issue.
>
> >> Any help is greatly appreciated!    Thanks.
> >> Doug
>
> > It's a known problem related to a mis-detected timeout reported as a
> > read error. Use post 0.82 patches (http://freetds.sourceforge.net/) or
> > patched version.
>
> > bye
> > freddy77
> > _______________________________________________
> > FreeTDS mailing list

> > Free...@lists.ibiblio.org
> >http://lists.ibiblio.org/mailman/listinfo/freetds

[Ken Collins]

What version of FreeTDS and/or have you tried the latest? FYI, 0.83.dev is supported by TinyTDS.

- Ken

[Thomas Shelton]

0.82 ... I can upgrade if you think that might do the trick.

Is this the correct version?

http://freetds.cvs.sourceforge.net/viewvc/freetds/freetds/

Or can it be found elsewhere?

Thanks

[Ken Collins]

I have used this one in the past.

http://ibiblio.org/pub/Linux/ALPHA/freetds/current/freetds-current.tgz

[Thomas Shelton]

I was able to build and install this version ... TDS is still 5 but freeTDS is 0.83dev ... I'm getting this error now though ...

ArgumentError: argument out of range: SELECT * FROM [tb_user] WHERE ([tb_user].[userid] = 4713) 

It's a very basic select and runs fine through TSQL ... I installed over the previous installation and did not reboot.  Is there something else that needs to be reset?

Thanks for your help.

Thomas

[Ken Collins]

Did you check your freetds.conf file and make sure that it kept your old settings like tds version = 8.0 

Remember, the compiled tds version is just a default.

 - Ken

[Thomas Shelton]

yep ...

here is the config output ...

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

                            Version: freetds v0.83.dev.20110306

             freetds.conf directory: /usr/local/etc

     MS db-lib source compatibility: yes

        Sybase binary compatibility: no

                      Thread safety: yes

                      iconv library: yes

                        TDS version: 7.1

                              iODBC: no

                           unixodbc: yes

              SSPI "trusted" logins: no

                            Keberos: no

I used this configure command: sudo ./configure --with-tdsver=7.1 --enable-msdblib  ... the config help looked like 7.1 was the latest it could build, when I tried 8, it stuck with the default of 5.0.

I've tried a couple of variations and I still get the same error with this code.

Any thoughts?

[Ken Collins]

Remember, the --with-tdsver and --enable-msdblib do NOT matter. Those are just compile time defaults and in some cases useless. The important part is making sure the conf file has 8.0, you may have that set, but I just wanted to make sure. 

This page explains why.

https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/wiki/Using-TinyTds

[Ken Collins]

Thomas,

Remember too, if you update FreeTDS, TinyTDS should be reinstalled too so that it builds against the latest FreeTDS.

- Ken

[Thomas Shelton]

tds version is set to 8.0 in freetds.conf.

I didn't think to rebuild tiny_tds ... I did that after installing 0.83dev and am now able to connect with basic queries but still rec'v this error on my long-running query.

TinyTds::Error: Bad token from the server: Datastream processing out of sync:

Thomas

[Ken Collins]

Thomas,

Have you verified that it is nothing with the data coming from the query. For instance, try doing something like this SQL to make it wait and test the connection lives for a certain duration long. Below is 2 seconds, change to what you need.

WaitFor Delay '00:00:02'

[Thomas Shelton]

I'm not familiar with this approach ... where would I place the command?  freetds.conf? It's a pretty extensive system and al other aspects work fine through tiny_tds except this one query ... could be others that simply haven't been hit yet but it's been going for a couple of weeks now.

Thanks again.

--

[Ken Collins]

Thomas,

My recommendation was to test a long query in a controlled manner. So let's say your problematic query always yields and exception at 30 seconds, even tho you have a timeout of 60 seconds. My first question when debugging this is to isolate the issue. The logical first step would be to test a long running query equal or greater to what was happening before. So in rails console.

ActiveRecord::Base.connection.execute "WaitFor Delay '00:00:40'"

That would take 40 seconds to run. Does it work? Do you get the same error at 30 seconds? Etc etc? See what I am saying? This would first isolate the problem. From here, all sorts of possible debugging options are on the table.

- Ken

[Thomas Shelton]

got it ... makes perfect sense.

here is the result and this happens at about 15 seconds consecutively.

ActiveRecord::StatementInvalid: TinyTds::Error: DBPROCESS is dead or not enabled: WaitFor Delay '00:00:40'

Helpful?

--

[Ken Collins]

Yea, but not toward a solution. Just for knowing what the real problem is. Basically that FreeTDS's DBLIB has network issues. This is a hunch. Is your connection across an open network? Have you tested that network for connectivity, packet loss, etc? These are not solutions, just info gathering. It's hard to find if this is FreeTDS or TinyTDS.

I guess you could do something in the tsql shell and find out too. For instance.

$ tsql -S <yourtdservername> - U <youruser> -P <yourpass>

Then when connected, give it that wait SQL then GO and see what happens. That would show if it is FreeTDS or TinyTDS. If it is FreeTDS, I would search their mail archives and/or open a bug reports. Lemme know.

- Ken

[Thomas Shelton]

I tried through tsql and here are the results:

1> WaitFor Delay '00:00:40';

2> GO

Error 20003 (severity 6): Adaptive Server connection timed out

The boxes are on the same network but I could certainly run some commands to gather packet info if you think that would be helpful ... this appears to be freetds though ... right?  I'll start a thread on the mailing list @ ibiblio unless you have some further thoughts ...

Really appreciate the help.

 - Ken

[Ken Collins]

Thomas,

I would agree this is FreeTDS and that list is the correct one. They are slow to respond though. I have never been able to reproduce these bad network connections. Maybe it is a bug in FreeTDS that is shared by everyone and see in odd cases. Maybe it is a bug when compiled on certain machines, etc. If you find out more, let us know.

 - Thanks,

    Ken

[Thomas Shelton]

Scratch the last post ...

I changed the timeout option in freetds.conf to 45 seconds and then ran the command again through tsql and it worked fine.  However, when I run through activerecord, I still get this error:

ActiveRecord::StatementInvalid: TinyTds::Error: DBPROCESS is dead or not enabled: WaitFor Delay '00:00:40'

Thoughts?

[Ken Collins]

Well for starters remove timeout from the conf file. Let TinyTDS do that via database.yml, so your setting in there would be 45000 for 45 seconds. I have noticed problems when there is a timeout in the freetds.conf file.

But if what you are saying is true, then this looks to be an issue with TinyTDS, what I am not sure. Can you make totally sure that ever time you run this in tsql it does work and that consistently it fails in TinyTDS? And tell me again what version DB, ruby, etc you have?

- Ken

[Thomas Shelton]

I ran a couple of more times thorugh tsql after removing the timeout from the conf file and it worked.  And yes it consistently fails when run through activerecord at about 15 seconds ... 

I'm running ruby 1.8.7 (2008-05-31 patchlevel 0) [i686-linux]

SQLServer 2000

Rails 2.3.11

freetds v0.83.dev.20110306

Thanks

[Ken Collins]

Super :(

Can you set timeout: 0 in your database.yml? I'm not sure where to go from here. I can make my TinyTDS in AR to 2000 wait for a LONG time and have no issues. So not sure where to look. Maybe connect to another DB instance and test. I am not sure, open to suggestions.

- Ken

[Clifford Heath]

It sounds to me that you are getting a server-side timeout,
rather than a TDS one. Perhaps SQL Server has an unusually
low timeout set? My experience using ADO leads me to believe
that a connection can request a longer server-side timeout (in
addition to enforcing its own client-side one), but I don't know
how this is done. The default will be settable in the SQL Server
admin interface however.

Have I missed something, and you are actually aware of both
possible timeout sources?

Clifford Heath.

[Ken Collins]

If the TinyTDS client times out, you will get a logical timeout exception. I understood that there could be a server timeout, but have not looked at it nor do I understand where it is set or read. Perhaps Thomas can investigate and let us know.

- Ken

[Thomas Shelton]

If the server were throwing the time-out wouldn't freetds also have problems holding the connection through 40 seconds?  We checked the network and all looks good.

Not sure where to go from here.

[Ken Collins]

> If the server were throwing the time-out wouldn't freetds also have problems holding the connection through 40 seconds? We checked the network and all looks good.

No. We are talking about a server config for different client connections. DBLIB is different than ODBC client connections. We were raising the point that in some odd case, the server could timeout DBLIB based clients from some server level setting on your end. Worth looking into since I can not reproduce here.

- Ken

[Ken Collins]

Can anyone affected by timeout issues with TinyTDS test the latest versions of both TinyTDS and the adapter and let me know if that helped?

- Ken

[Marlon Moyer]

Just upgraded my project this morning to tiny_tds 0.4.3 and AR-SQLServer 3.0.12. I'm still getting the timeout issues when seeding my db. Roughly about 1/3 of the time when I start spork, I get the timeout issue about 10 seconds after it starts seeding. Normally, the seed process takes about 2 seconds.

On Apr 3, 2011, at 2:33 PM, Ken Collins wrote:

>
> Can anyone affected by timeout issues with TinyTDS test the latest versions of both TinyTDS and the adapter and let me know if that helped?
>
> - Ken
>

[Marlon Moyer]

another odd thing is that I'm getting the warning "TindyTds v0.4.3 or higher required. Using 0.4.3"

On Apr 3, 2011, at 2:33 PM, Ken Collins wrote:

>
> Can anyone affected by timeout issues with TinyTDS test the latest versions of both TinyTDS and the adapter and let me know if that helped?
>
> - Ken
>

[Ken Collins]

That should not be able to happen. I did that check for both v2/3 versions and it correctly checks here just fine.

https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/blob/master/lib/active_record/connection_adapters/sqlserver_adapter.rb#L27
https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/blob/2-3-stable/lib/active_record/connection_adapters/sqlserver_adapter.rb#L18

Since the #active? method is key to helping with connection issues, I would make sure that your stack/bundle is loading things right before proceeding. Can you investigate further and let me know how that warning is firing on your end?

- Ken

[Ken Collins]

> Just upgraded my project this morning to tiny_tds 0.4.3 and AR-SQLServer 3.0.12. I'm still getting the timeout issues when seeding my db. Roughly about 1/3 of the time when I start spork, I get the timeout issue about 10 seconds after it starts seeding. Normally, the seed process takes about 2 seconds.

Marlon,

The TinyTDS version warning was my fault. I forgot that 1.9 returns symbols when using #instance_methods.

About your timeout issues. This just has to be something in your particular setup? I can confirm on my end that lost connections are being handled correctly by the connection pool and that indeed TinyTDS works very well over long distance connections (like to Azure) from here too.

What is "spork"?

- Ken

[Thomas Shelton]

Ken,

I'm still having connection problems and simply working around them when they come up at this point.  We're still in development and I'm hoping by the time we need to go live, these issues will be resolved.

I'm not a SQLServer DBA so it might help if you could point out specific configuration options that might be throwing these errors.

This is the error we're getting:

ActiveRecord::StatementInvalid (TinyTds::Error: DBPROCESS is dead or not enabled: BEGIN TRANSACTION):

  /usr/local/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.3.11/lib/active_record/connection_adapters/sqlserver_adapter.rb:1006:in `do_execute'

I'll try to update tonight and see if any progress can be made.

Thanks,

Thomas

 - Ken

[Marlon Moyer]

Spork is a background test runner. If you use 1.9.2 and Rails 3 you've gotta use spork to avoid wasting years of your life waiting for the spool up :)

It's a non-issue though....It times out when running the base rspec command too.

I might have a different issue regarding my timeouts as they happen sporadically in the middle of a lot of db activity.

Here's a log showing my timeout in the middle of seeding the db.

https://gist.github.com/dbbb3674580f050f5ad8

[Ken Collins]

Marlon,

I know that TinyTDS as of yet has no thread safety code. I do not know if spork hooks into that short-coming or not.

The trace is very helpful tho and this is exactly the info to help debug that I need. I doubt that one commit transaction should have taken more than a few milliseconds. The question is why it did. Looking at the error, one of 2 things happened. It did take more than 10 seconds (perhaps due to a deadlock in another process) and your normal timeout kicked in and gave you that error. Or it took 10 seconds to timeout on a lost connection.

- Ken

[Marlon Moyer]

Hey Ken,

I think I'm going to consider this a configuration/MS issue since I am unable to get it to happen with a sql trace running. Stop the trace and it reappears intermittently. Downloading latest SP's for SqlServer 2008 and I'll run more tests later.

[C Teng]

I may have a related issue with ActiveRecord. The query to my MS SQL 2008 is very slow. I change freetds.conf to use TDS Protocol Version 7.2 and tsql now works fine, but ActiveRecord-based apps are still very slow. I tried changing the database.yml and they are still very slow. What could I do? Thanks.

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