Tim,

The thing is that at this point we have switched the connection from our T1 lines to a cable modem line for users experiencing the issue and the problem disappeared.  Applications and databases are unchanged at either end.  

In my mind that eliminates the possiblity of a coding error and leaves only the communication layer.  Since the cable modem does not follow the same route as the T1 lines it seems to me that it has to be something in the route.

When we switched we were unable to initially connect because our router on the cable modem had the MTU set to the defualt 1500.  Once we changed that to 1492 everything was peachy.  Personally I don't think that was related to the issue - we had never used the cable modem for incoming traffic - but I could be wrong.  Does that MTU setting mean anything to DBISAM generally?  Is there something that some web routing company could have switched on that suddenly causes this behavior in our application?

As an additional note we have one customer on our IIS driven website (not DBISAM) that has been unable to load the login page for the site since Monday (the same time the DBISAM applications started failing).  This customer is actually getting their ISP service from Time Warner and is routing through the same servers that our DBISAM users that are having trouble are routing through.  Seems to be an awfully lot of coincidence here.

Thanks,

Mike

NOTE: 3/8/10

It appears that we are getting the duplicate table error after about 5 minutes when trying to download data from our server to the client and we are getting "DBISAM Engine Error # 10031 The table 'TableName' is already in use" after about 5 minutes when trying to upload data from the client to our server.

Mike

Michael,

<< In my mind that eliminates the possiblity of a coding error and leaves
only the communication layer.  Since the cable modem does not follow the
same route as the T1 lines it seems to me that it has to be something in the
route. >>

I understand, but I still find it fairly hard to believe that the comms
layer is screwing the SQL text up just perfectly so that the message headers
in the DBISAM session comms layer are all still just fine, but the SQL
issues an invalid error.  Do you see what I'm alluding to here ?  When a
TCP/IP connection fails, it should just fail, not change things in a subtle
way that changes the behavior of the endpoint software.

Are you building this SQL dynamically or loading it from disk ?  If so, did
you check to make sure that there aren't any embedded NULLs being placed
into the SQL that would mask out any trailing SQL from being visible.

<< When we switched we were unable to initially connect because our router
on the cable modem had the MTU set to the defualt 1500.  Once we changed
that to 1492 everything was peachy.  Personally I don't think that was
related to the issue - we had never used the cable modem for incoming
traffic - but I could be wrong.  Does that MTU setting mean anything to
DBISAM generally?  Is there something that some web routing company could
have switched on that suddenly causes this behavior in our application? >>

No, and no, none that I can think of.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< I understand, but I still find it fairly hard to believe that the comms
layer is screwing the SQL text up just perfectly so that the message headers
in the DBISAM session comms layer are all still just fine, but the SQL
issues an invalid error.  Do you see what I'm alluding to here ?  When a
TCP/IP connection fails, it should just fail, not change things in a subtle
way that changes the behavior of the endpoint software. >>

I do get you.  I'm not sure how the comms work within DBISAM.  I was assuming that it might also be a size thing as I'd guess establishing the session does not requires as much information be passed between the client and server as actually getting data.

<< Are you building this SQL dynamically or loading it from disk ?  If so, did
you check to make sure that there aren't any embedded NULLs being placed
into the SQL that would mask out any trailing SQL from being visible. >>

All SQL in this case is being built in code.  Basically a straight assignment.

Doesn't that consistent 5 minute delay between request and failure tell us something?

Here's a sample application log that is showing the failures and timestamps:

03/08 03:08:51.171 pm=Connecting to Powerfield Remote Server at xxx.xxx.xxx.xxx
03/08 03:08:52.734 pm=Connecting to Powerfield Local Server
03/08 03:08:52.750 pm=Synchronizing pfForms table
03/08 03:08:53.453 pm=Copying data from remote query to pfForms
03/08 03:08:53.734 pm=Synchronizing InspReq table
03/08 03:08:55.046 pm=Copying data from remote query to InspReq
03/08 03:08:55.750 pm=Synchronizing InslReq table
03/08 03:08:57.093 pm=Copying data from remote query to InslReq
03/08 03:08:57.421 pm=Synchronizing RecutReq table
03/08 03:08:59.468 pm=No records were retrieved in 0.63 seconds
03/08 03:08:59.625 pm=Searching for Previously Scheduled Recuts
03/08 03:09:00.687 pm=Searching for Recuts that might need to be Scheduled
---
03/08 04:03:48.703 pm=Connecting to Powerfield Remote Server at xxx.xxx.xxx.xxx
03/08 04:03:49.875 pm=Connecting to Powerfield Local Server
03/08 04:03:49.890 pm=Searching for New Requests
03/08 04:08:50.421 pm=ERROR: (TfrmPFRemote.DoRequests) DBISAM Engine Error # 11949 SQL parsing error - Duplicate table or table correlation name ReqBatch in SELECT SQL statement at line 1, column 26
03/08 04:08:50.453 pm=Searching for Results
03/08 04:08:50.656 pm=No Response records to upload
03/08 04:08:50.703 pm=1 Inspection result is available for upload.
03/08 04:13:51.171 pm=ERROR: (TfrmPFRemote.DoResults) DBISAM Engine Error # 10031 The table 'InspRes' is already in use
03/08 04:13:51.187 pm=No Insurance Loss results to upload
03/08 04:13:51.218 pm=No Recut results to upload
03/08 04:13:51.234 pm=Searching for Images records to upload
03/08 04:14:00.093 pm=122 Images records are available for upload.
03/08 04:19:01.078 pm=ERROR: (TfrmPFRemote.DoUploadTable) DBISAM Engine Error # 10031 The table 'Images' is already in use

This is from one user - you can see that at 3pm everything went swimmingly - then when they connected again just after 4pm everything failed.  The time between request and failure is always right at 5 minutes plus a few milliseconds (does that correlate to a default TCP/IP timeout on Windows after the retries?)

Thanks,

Mike

Michael,

<< Doesn't that consistent 5 minute delay between request and failure tell
us something? >>

Yes, I don't think that it tells us anything about what is going on with the
SQL.

Would it be possible for you to send me the connection information and SQL
query text so that I can do a test here using DBSYS ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

I can run it by the upper-ups.  I might be able to send you a modified dbsys application that can connect to our server (the default one in 4.16 can not since we're using the encryption) and has access to one or two tables.  Unfortunately the database contains private information and giving a non-contractor any kind of access would violate our security agreements with our customers.

If I get a chance I'm going to connect to a user PC remotely and run it against both good and bad IP's.

Thanks,

Mike

Michael,

<< I can run it by the upper-ups.  I might be able to send you a modified
dbsys application that can connect to our server (the default one in 4.16
can not since we're using the encryption) and has access to one or two
tables.  Unfortunately the database contains private information and giving
a non-contractor any kind of access would violate our security agreements
with our customers.

If I get a chance I'm going to connect to a user PC remotely and run it
against both good and bad IP's. >>

Okay, just let me know.

BTW, as far as I can tell your DBISAM support plan expired about 2 years
ago, so you might want to let someone know.

Thanks,

--
Tim Young
Elevate Software
www.elevatesoft.com

UPDATE (Final - I think): Our network guys switched the MTU setting on our main routers to 1492 and the problem disppeared on the primary T1 lines as well.  So whatever this is it is related to the MTU setting on the router.

Mike

Michael,

<< UPDATE (Final - I think): Our network guys switched the MTU setting on
our main routers to 1492 and the problem disppeared on the primary T1 lines
as well.  So whatever this is it is related to the MTU setting on the
router. >>

Wow, something to add to the knowledge base.

Thanks for the update,

--
Tim Young
Elevate Software
www.elevatesoft.com