mongrel unicorn - Jul 2011 - SQL Server errors when using unicorn

Lately I have been seeing a large volume of database errors in a couple of
applications at work.  This has proven difficult to troubleshoot: after some
restarts of unicorn, things seem fine.   Then, a restart as part of a new
deployment introduces these issues anew, with rollback to the previous version
of the app still producing errors.

One thing I have noticed is in my troubleshooting is that I seem to be able to
reproduce these errors only after a period of idleness or low activity on the
part of unicorn.  Running unicorn on my workstation, running ab with a
concurrency of 5 or so is enough to produce the issue reasonably often.

I bring this question to the unicorn discussion because I noticed we
don''t have this problem using Passenger.  I was wondering if there were
any particulars of our setup that seem problematic.

Here is an example of the error we see.  It is not always exactly this error. 
Sometimes we will see a bad token error from SQL Server.  Other times the error
manifests itself in ActiveRecord dynamic methods not being properly created:

[ WARN 11-07-08 11:42:29 25300:0x1001d6360 ] Internal Server Error ODBC::Error:
01000 (20017) [unixODBC][FreeTDS][SQL Server]Unexpected EOF from the server: 
SELECT columns.TABLE_NAME as table_name, columns.COLUMN_NAME as name,
columns.DATA_TYPE as type, columns.COLUMN_DEFAULT as default_value,
columns.NUMERIC_SCALE as numeric_scale, columns.NUMERIC_PRECISION as
numeric_precision, CASE WHEN columns.DATA_TYPE IN
(''nchar'',''nvarchar'') THEN
columns.CHARACTER_MAXIMUM_LENGTH ELSE
COL_LENGTH(columns.TABLE_SCHEMA+''.''+columns.TABLE_NAME,
columns.COLUMN_NAME) END as length, CASE WHEN columns.IS_NULLABLE =
''YES'' THEN 1 ELSE NULL END as is_nullable, CASE WHEN
COLUMNPROPERTY(OBJECT_ID(columns.TABLE_SCHEMA+''.''+columns.TABLE_NAME),
columns.COLUMN_NAME, ''IsIdentity'') = 0 THEN NULL ELSE 1 END as
is_identity FROM INFORMATION_SCHEMA.COLUMNS columns WHERE columns.TABLE_NAME =
''users'' AND columns.TABLE_SCHEMA = schema_name() ORDER BY
columns.ordinal_position


Our stack:
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 -- at a different site from our web servers, connected by WAN
CentOS 5.9

I appreciate any suggestions as to where the problem may lie. 

Thanks

Marc Sernatinger <marc at vailsys.com> wrote:
> I bring this question to the unicorn discussion because I noticed we
> don''t have this problem using Passenger.  I was wondering if there
> were any particulars of our setup that seem problematic. 
Do you have "preload_app" set to true?  Try using the default (false)
since it''s likely to cause issues for some/most database drivers or
anything else that opens a stream socket.

You can''t safely share stream sockets across multiple processes/threads
so you have to disconnect/reconnect them in the before/after_fork hooks.
> Our stack: 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 -- at a different site from our web servers, connected by
> WAN CentOS 5.9
Maybe somebody else can help you if disabling preload_app doesn''t help.
I have no idea about any of the database components you use.

-- 
Eric Wong