Link to home
Start Free TrialLog in
Avatar of hmstechsupport
hmstechsupport

asked on

General SQL error. Error reading data from the connection.

Hello all.

We have a problem that we've been working on with a client for several months that we cannot resolve.  The environment is as follows.

Server (not sure of server details, but an  adequate box I'm told) is running Interbase 6.0.1.6 with 1 user and 1 database (ours) which is used to track timesheets.
On the same machine we have a Delphi 5 application running as a service which we call a 'Transaction Server' which is the layer that interacts with the Interbase database using the BDE (version 5.2) and the clients which are ActiveX controls running in IE.

The problem we see from our logs is that the connection from our 'Transaction Server' to the Interbase Server seems to fail during heavy periods of use with messages like "General SQL error.'#13#10'Error reading data from the connection."  or our own generated errors such as 'Unable to Process Request' which we display when we get an unexpected response from the database server.  During the week when there is less activity, things seem to go along without interuption and the service is not restarted, but on Friday or Monday when timesheets are due and the volume is up it's as if the IB Server cannot handle the requests fast enough.

We have taken their GDB file and run several tests here with it and it always behaves properly although we cannot really simulate the traffic this client has, but we have literally hundreds of other clients who have literally hundreds more users than this client does and the other clients do not display the same problems AT ALL.  Note that 'most' other clients are using either SQL Server as their back-end or Oracle with the same software and version, but we do have other Interbase database clients who do not encounter these issues.  

All that to say we think it's environmental but don't know where else to look.
We've checked and optimized the BDE, we've made sure we're all using the same version of the application, we've run their database and heavy processes here.  All without issue.

We've looked at the Interbase log, but it doesn't really tell us anything.

Are there better tools to examine the Interbase database?  

Does Interbase keep any better statistics about it's failures, or being overloaded or anything liek that that might help us?

Any suggestions are VERY welcome.


Thanks

HMSTechSupport
Avatar of Nick Upson
Nick Upson
Flag of United Kingdom of Great Britain and Northern Ireland image

what is the sweep interval set to? It could be the sweep is starting in the heavy load periods and it's that extra load that causes the failure.

The interbase log should say something in the relevant time period.

ib6 was known to have several bugs, can you upgrade to either ib7 (purchase from borland) or firebird (free download from www.ibphoenix.com)
Avatar of hmstechsupport
hmstechsupport

ASKER


The sweep interval on the database is set to 20,000 and the database has 6433 allocated pages, and a page size of 4096.

We use the free version of Interbase so we can bundle it with our software.  I have looked at Firebird, but we had issues with reserved words we use throughout our application so we made the choice to stick to IB 6.0.1.6 and not have to rewrite our application.

Which char set do you use?
Try to do an interbase back up and interbase restore.
The database dialect is set to 1.

I am currently reading a bunch of articles that seem to point to this being a network or NIC problem (URL below).  Have you seen this before?  The articles appear to be several years old, so I am not sure if they are accurate or not.

http://groups.google.ca/groups?q=%2B%22Error+reading+data+from+the+connection.%22+%2Binterbase&start=20&hl=en&
1. In which intervals do you make the restore? I suggest to make a backup/restore in/before the problematic periods This can help by complete sweeping (and cleaning) of the database.

2. I'll look at the articles you propose.
Thanks Kacor.  I appreciate the assistance.

I do not think though that the client has the skill (sorry to say that) to perform a sweep or a backup of the database.  Our bundling of the free version database was partly an effort to hide the complexities of a database from the client.  

Here are the highlights that I've come across.

------------------------------------------ Interbase log from client

OGCAL-MWTS01 (Client)      Mon Jul 18 07:13:04 2005
      INET/inet_error: send errno = 10054
 
OGCAL-MWTS01 (Client)      Mon Jul 18 07:13:04 2005
      INET/inet_error: send errno = 10054
 
OGCAL-MWTS01 (Client)      Mon Jul 18 07:13:04 2005
      REMOTE INTERFACE/gds__detach: Unsuccesful detach from database.  
      Uncommitted work may have been lost
 
OGCAL-MWTS01 (Client)      Mon Jul 18 07:13:04 2005
      INET/inet_error: send errno = 10054
 
OGCAL-MWTS01 (Client)      Mon Jul 18 07:13:04 2005
      INET/inet_error: send errno = 10054
 
OGCAL-MWTS01 (Client)      Mon Jul 18 07:13:04 2005
      REMOTE INTERFACE/gds__detach: Unsuccesful detach from database.  
      Uncommitted work may have been lost
 
OGCAL-MWTS01 (Client)      Mon Jul 18 07:13:04 2005
      INET/inet_error: send errno = 10054
----
---- From newsgroups
The interbase log is getting a few 10054 errors.  I cannot reproduce them.
the error code is ISC335544726 Error Reading data from the connection:

Does anybody know what could be causing this?


 Jeff Overcash (TeamB)   Oct 23 2001, 9:17 am     show options

Newsgroups: mers.interbase.list
From: "Jeff Overcash (TeamB)" <overc...@onramp.net> - Find messages by this author  
Date: Mon, 22 Oct 2001 22:25:23 -0400
Local: Mon, Oct 22 2001 10:25 pm  
Subject: Re: 10054 errors in interbase log
Reply to Author | Forward | Print | Individual Message | Show original | Report Abuse  

10054 tend to be a physical problem with the network.  Usually a bad NIC.
----
> server using BDE (in 1-5 threads).
> From time to time "Error reading data from the connection" occurs.
> I don't know why.

My first guess would be one of the following:
1) Network problem
2) Threading problem
In general, when multithreading using IB you must have a separate
connection to the DB for each thread, and the connection must be
"remote" (use "localhost" as the host name if the server is running on
the same machine as the client -- using "localhost" is considered a
"remote" connection).
The BDE in particular seems to have problems with multithreading on IB,
especially if three or more threads access the server concurrently. The
BDE requires a separate TSession for each thread, but even when this is
done you sometimes see problems. The IBX components do not have this
problem.
----
---- More
This usually means one of two things:
o Your network is having serious problems
o The IB server has crashed.
You can figure out which one is the problem by looking at interbase.log
on the server. If you see messages about IBGuardian restarting the
server, then the server has crashed. If you don't see any messages at
the time you saw this message, then you're having network problems.
If the server is crashing on a simple SELECT, then your DB is probably
corrupt. You should do a database validation in IBConsole and then
figure out what made your DB corrupt in the first place. In particular,
make sure you have IB 6.0.1.6 and that Forced Writes are ON.
----

All connections lost at once with error 10054 - by Borland Developer Support Staff

Rating: \l "rating" \l "rating"Ratings: 6 Rate it
Abstract: Some sort of network error occured
Problem:
All connections lost at once with error 10054 in interbase.log.  It can
happen with both Service pack 2 & 3 for NT 4.0.  InterBase Server is
still running on the server.  


Solution:
Error 10054 is a winsock error for connection reset.  You can find the
list of winsock errors in the winsock.h file.

Since the InterBase Server is up and running at the time of the problem,
it's an indication that it's not an InterBase issue but rather a network
issue.  Check the following:

 *  At the time of the problem, what's the traffic load?  Check for
    traffic loads that can flood the network. Is there anything scheduled
    to flood the network at certain time?

 *  Check for network hardware problems such as bad switch, router,
     hub, etc.

*   Another possible scenario involves having more than one protocol
    bound to the same network card. In one case a customer had TCP/IP,
    IPX/SPX, and RIP for IPX bound to the same ethernet card. When he
    unbound IPX/SPX and RIP for IPX from the card, the errors disappeared.

----
You must connect as "remote" (use localhost as the host name if
the IB server and client are running on the same machine) and you must
not attempt to concurrently access the same connection to IB from two
different threads, except in the case of asynchronous query cancellation
----
this was a great job! my congratulation. unfortunately I have no ideas :-))

wbr
Janos
Thanks Kacor.

I appreciate the time you took to examine the case for me.   I have convinced the client to change the network card about 10 minutes ago, so now we watch and see.

Do you know of better logs than the interbase.log or a tool to get better logs from Interbase?

Are there ways to tweak the server to use more RAM, or less RAM or any configuration capabilities within the Server part of Interbase?


Thanks
As I know using IB6 you can't control the RAM area but I try to search something about
SOLUTION
Avatar of kacor
kacor
Flag of Hungary image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks I appreciate the input, but we are not using any UDF's, TRIGGERS, or Stored Procedures on the database itself.  All business logic is within the application as we support many databases.

Thanks
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks BAlexander.
What do you by a direct path in the BDE.  We are using something similiar to SERVERNAME:C:\path\file.gdb as I think is recommended.

Is there a better way?
Yes this is recomended but you can try with "local connection" that is different type to connect to database - thru directly accessing file. This way you can have only one user connection but you bypass the network layer.
How does that get configured then in the BDE?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Ok.  Thanks.  We'll give it a try here first.
What happened?
Could not get it to work locally.  When I try to change the BDE setting from "SERVERNAME:C:\path\file.gdb" to just "C:\path\file.gdb", I get an unknown user name or password error from the BDE.  I think it is because Interbase is installed as a remote and not a local server (I am guessing).

We're still working with the client (long distance) to try to help, but so far have been unsuccessful.

Hello all.  

No. This case was never solved, and the client is still not happy with us.  On occasion we perform the task that they cannot do for them and send them back an updated Interbase database.  Not a real solution but a workaround.

Thanks for all of your assistances.  We will close this case.

Hello all.

Thank you for your persistance and answers.  I have split closed the case and split the points as fairly as I saw fit.