Datastream processing out of sync
Marc
Recently, we upgraded our Rails apps from 2.x to 3.0 and are seeing
some very curious errors in our logs.
#<ActiveRecord::StatementInvalid: ODBC::Error: 08S01 (20020) [unixODBC]
[FreeTDS][SQL Server]Bad token from the server: Datastream processing
out of sync: SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE
TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME <> 'dtproperties' AND
TABLE_SCHEMA = schema_name()>>)
These occur seemingly at random, with no single query being the
cause. We're at a loss to find an explanation for these errors. At
this point, I'm just trying to rule out potential causes. Our stack
is as follows:
Rails 3.0.7
activerecord-sqlserver-adapter 3.0.15 using ruby-odbc and FreeTDS 0.82
unicorn 3.5.0 with nginx in front
SQL Server 2005
CentOS 5.9
Has anyone with a similar stack seen these errors?
Ken Collins
Sent from my iPhone
> --
> 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.
>
Marc
> I have never seen that error before. Possible a network issue between the app and db servers? Aside from the normal network troubleshooting, try TinyTDS as an alternative connection mode vs Ruby ODBC.
>
> Sent from my iPhone
>
>
>
>
>
>
>
>
> > Hi all,
>
> > Recently, we upgraded our Rails apps from 2.x to 3.0 and are seeing
> > some very curious errors in our logs.
>
> > #<ActiveRecord::StatementInvalid: ODBC::Error: 08S01 (20020) [unixODBC]
> > [FreeTDS][SQL Server]Bad token from the server: Datastream processing
> > out of sync: SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE
> > TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME <> 'dtproperties' AND
> > TABLE_SCHEMA = schema_name()>>)
>
> > These occur seemingly at random, with no single query being the
> > cause. We're at a loss to find an explanation for these errors. At
> > this point, I'm just trying to rule out potential causes. Our stack
> > is as follows:
>
> > Rails 3.0.7
> > activerecord-sqlserver-adapter 3.0.15 using ruby-odbc and FreeTDS 0.82
> > unicorn 3.5.0 with nginx in front
> > SQL Server 2005
> > CentOS 5.9
>
> > Has anyone with a similar stack seen these errors?
>
> > --
> > 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 athttp://groups.google.com/group/rails-sqlserver-adapter?hl=en.
say that one idiosyncrasy of our setup is that our DBs are at a
different site from our web servers so they have to cross a WAN to
talk to one another. The latency is pretty low and the connections
are good but obviously not as good as being on-site together.
We tried TinyTDS and saw numerous "undefined method" errors on
ActiveRecord classes. I imagine this is simply a different
manifestation of the same issue and probably has to do with bad
responses when SQL Server is queried to determine table schemas.
Moving to Phusion Passenger seems to resolve the issue so we're trying
that for now. I'll reply to this thread if I learn anything more
definitive.
Matt K
adapter 2.3.15, tiny_tds 0.3.2) and seen plenty of similar errors.
They occur regularly but don't seem to be predictable. They also
appear to come in bursts, where we'll see five or ten occurrences of
the error in the span of a few minutes, and then nothing again for a
day or two. I originally thought it was the result of a particular
long-running query, but I've seen the same error with very simple
queries as well.
There have been two different flavors of what seems to be the same
problem:
1. ActiveRecord::StatementInvalid: TinyTds::Error: DBPROCESS is dead
or not enabled: SELECT <some query here>
2. ActiveRecord::StatementInvalid: TinyTds::Error: Bad token from the
server: Datastream processing out of sync: SELECT <some query here>
Many of these queries are simple like "SELECT TOP 1 * FROM table WHERE
table.id = 1234"
The only unusual messages I see in the SQL Server log are messages
such as "SQL Server has encountered 1 occurrence(s) of I/O requests
taking longer than 15 seconds..." but these messages don't neatly
correspond to the client-side errors. I've also found articles [1]
which claim that these messages are false warnings caused by CPU
sleeping.
Our deployment consists of a handful of EC2 instances. This article
[2] says that these errors are often caused by "an abruptly terminated
network connection", and with no evidence in the SQL Server logs to
suggest application errors, my best explanation is random network
hiccups that TinyTDS (or some other library in the stack) doesn't
handle very well.
Any ideas about how to improve resilience to network instability?
[1] http://blogs.msdn.com/b/chrissk/archive/2008/06/19/i-o-requests-taking-longer-than-15-seconds-to-complete-on-file.aspx
[2] http://support.microsoft.com/kb/109787
Ken Collins
Hey Matt
> We're running a similar stack (Rails 2.3.11, unicorn 1.1.3, sqlserver-
> adapter 2.3.15, tiny_tds 0.3.2) and seen plenty of similar errors.
> They occur regularly but don't seem to be predictable. They also
> appear to come in bursts, where we'll see five or ten occurrences of
> the error in the span of a few minutes, and then nothing again for a
> day or two. I originally thought it was the result of a particular
> long-running query, but I've seen the same error with very simple
> queries as well.
>
> There have been two different flavors of what seems to be the same
> problem:
>
> 1. ActiveRecord::StatementInvalid: TinyTds::Error: DBPROCESS is dead
> or not enabled: SELECT <some query here>
> 2. ActiveRecord::StatementInvalid: TinyTds::Error: Bad token from the
> server: Datastream processing out of sync: SELECT <some query here>
>
> Many of these queries are simple like "SELECT TOP 1 * FROM table WHERE
> table.id = 1234"
Good info. FYI, for these types of errors, it is not the query that you see in the exception that was the source of the error. It is typically the query or handle right before that caused it.
> The only unusual messages I see in the SQL Server log are messages
> such as "SQL Server has encountered 1 occurrence(s) of I/O requests
> taking longer than 15 seconds..." but these messages don't neatly
> correspond to the client-side errors. I've also found articles [1]
> which claim that these messages are false warnings caused by CPU
> sleeping.
>
> Our deployment consists of a handful of EC2 instances. This article
> [2] says that these errors are often caused by "an abruptly terminated
> network connection", and with no evidence in the SQL Server logs to
> suggest application errors, my best explanation is random network
> hiccups that TinyTDS (or some other library in the stack) doesn't
> handle very well.
>
> Any ideas about how to improve resilience to network instability?
Yes. Try different version of FreeTDS and/or TinyTDS. I believe in 4.x we put in better error handling for network conditions that both the latest 2.x/3.x adapters are trying to hook into. Specifically with the TinyTds::Connection#active? and us checking for closed? or dead?, see docs here [1]. If trying both versions of FreeTDS (0.82, 0.91RC2) with the latest TinyTDS does not yield any changes, dive into the test suite and run the should eventually method that helps check for a dead connection being handled gracefully. If not, let me know and patches welcome.
[1] https://github.com/rails-sqlserver/tiny_tds
- Ken
Matt Kent
Thanks for the tips. I upgraded tiny_tds and let the app run for a couple weeks. The bad news is that we're still seeing the errors.
The good news is that I think it's an internal problem. We have some batch processes that apparently nearly DDOS our SQL Server, and these batches conveniently coincide with the times of the errors. So, false alarm. Thanks anyway.
-Matt
Konstantin Krauss
> Thanks for the tips. I upgraded tiny_tds and let the app run for a couple weeks. The bad news is that we're still seeing the errors.
>
> The good news is that I think it's an internal problem. We have some batch processes that apparently nearly DDOS our SQL Server, and these batches conveniently coincide with the times of the errors. So, false alarm. Thanks anyway.
I don't think this is an internal problem. We have exactly the same issues using TinyTDS directly raw without the ORM.
We get randomly:
Bad token from the server: Datastream processing out of sync
AND
DBPROCESS is dead or not enabled
Very frustrating. I'll give other FreeTDS versions a try.
Best,
Konstantin