Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 6 of 6 total
Thread Table Mangling as a result of "Stack overflow" error
Wed, Oct 25 2006 10:40 AMPermanent Link

adam
Dear All,

I attach a ZIP file. It is of a single table, but a number within my DB have been
similarly effected.

DBISAM 3.3 C/S Run from a Delphi 6 app.

--

This happened at a Client site when one of the local users wrote a piece of SQL to
generate a report.

Standard SELECT statement, but badly formed.

(I don't know exactly what was done in terms of the form of the SQL!)

The user attempted to run it ... there was a pause, then an error message "DBISAM Error
12XXXX Stack Overflow" (my user isn't exactly sure what the error number was).

... The DB Server crashed, requiring a re-start (not too terrible) ... but the strange
thing is that a number of the tables involved in the SQL were badly mangled in the process.

I attach a ZIP with a typical table.

You will see that the content is OK up to a point, then BAM its basically garbage.
Interestingly, all the "real rows" are present ( 1 - 256) the garbage is "new rows" ...

One other feature is that the AutoInc field for the table is massively incremented.

--

Perhaps I am wrong to give my users freedom to write their own reports (???) ... but
honestly it is usually not a problem. I have now set in place a system where new SQL is
written in a "sand-box" on local data prior to being run on the Server.

However, I am at a loss as to how a mis-written piece of SELECT SQL (that is definitely
all it was) could have damaged tables!!!

Does anyone else have experience of this?








Attachments: DB.zip
Wed, Oct 25 2006 6:25 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Adam,

<< This happened at a Client site when one of the local users wrote a piece
of SQL to generate a report.

Standard SELECT statement, but badly formed.

(I don't know exactly what was done in terms of the form of the SQL!) >>

So, is this just what the user said they did, or do you have any idea of
what the SQL looked like ?

<< The user attempted to run it ... there was a pause, then an error message
"DBISAM Error 12XXXX Stack Overflow" (my user isn't exactly sure what the
error number was). >>

The only thing I've seen that can cause such an error is a very large series
of string concatentations.

<< .. The DB Server crashed, requiring a re-start (not too terrible) ... but
the strange thing is that a number of the tables involved in the SQL were
badly mangled in the process. >>

Did the user kill the database server process after the stack overflow
exception, or did it shut down completely on its own ?  Can you send me the
server log (dbsrvr.lgb) from the site so I can check and see exactly what
the error was ?

<< You will see that the content is OK up to a point, then BAM its basically
garbage. Interestingly, all the "real rows" are present ( 1 - 256) the
garbage is "new rows" ...

One other feature is that the AutoInc field for the table is massively
incremented. >>

I've never seen a situation where a shutdown that doesn't entail powering
down the machine or application in an uncontrolled fashion has corrupted
tables, especially in the case of an operation that AVs, etc. while only
reading data from a table or tables.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tue, Oct 31 2006 7:30 AMPermanent Link

adam
Dear Tim,

Thank you for this extensive reply, sorry to have been slow coming back to you, I have
been working in Tanzania for 5 days.


>>So, is this just what the user said they did, or do you have any idea of
>>what the SQL looked like ?

... I only have information the user gave me over the phone. I don't have any kind of real
detail of the SQL they wrote (unfortunately).

The system is a multi-user DB App (about 14 users in 1 office) the boss can write his own
SQL on the Reporting Application, while the staff use the Data Entry Application.

There is a SQL-building tool (QMS QBuilder I think) but the boss regularly rolls his own.
The database is fairly strongly relational so there are a good number of joins, which can
make getting the SQL right a bit tricky!

--

What seems to have happened is SQL containing some kind of recursive join was written &
actioned by the Reporting Application, i.e. Table A joined to B joined back to A, perhaps
CARTESIAN ... so generating a huge result set & soaking up resources on the server.

At the same time the system was in use by the other 12 or so users ... still going about
their business viewing & entering, data etc.

At some point the Server actually crashed, i.e. the DBSRVR.exe ceased to respond & the
server machine had to be switched off to restart the DBSRVR (I don't have the LOG file to
hand ... I will try to get it) & it seems that at this point something bad happened!

When the Server machine was restarted a couple of the tables required Repair ... and after
the Repair the AutoInc fields were "out of sync" ... i.e. new records were given an ID
that jumped massively compared with previous numbers.

--

The event wasn't catastrophic ... a few Table Repairs & 15 minutes later they were up &
running again. Something which I think I really have to thank DBISAM for!

I have put all Report-SQL writing into a "Sand box" now ... new SQL is written & tested on
a local copy of the DB & only run on "real" data once it has been tested as OK.

If anyone out there is doing anything as silly as I was (allowing users to write their own
SQL) I would strongly recommend that they follow a similar procedure ... whatever DB
engine they are using!

--

However, I am still a bit puzzled by the whole process of what went on & next time I am at
the user's site (in about 10 days) I will spend a bit of time trying to get to the bottom
of it!

Adam Brett

Tue, Oct 31 2006 8:49 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Adam,

<< What seems to have happened is SQL containing some kind of recursive join
was written & actioned by the Reporting Application, i.e. Table A joined to
B joined back to A, perhaps CARTESIAN ... so generating a huge result set &
soaking up resources on the server.  >>

A cartesian join would certainly do the trick.  Even very small tables can
generate huge tables.

<< At some point the Server actually crashed, i.e. the DBSRVR.exe ceased to
respond & the server machine had to be switched off to restart the DBSRVR (I
don't have the LOG file to hand ... I will try to get it) & it seems that at
this point something bad happened! >>

Is the database server running as a service or as a regular application ?

<< When the Server machine was restarted a couple of the tables required
Repair ... and after the Repair the AutoInc fields were "out of sync" ...
i.e. new records were given an ID that jumped massively compared with
previous numbers. >>

Yes, a reboot of the server while the dbsrvr process was running would most
likely cause some corruption.

<< If anyone out there is doing anything as silly as I was (allowing users
to write their own SQL) I would strongly recommend that they follow a
similar procedure ... whatever DB engine they are using! >>

What you should consider doing is using the TDBISAMQuery.MaxRowCount
property to govern these types of issues.  Just set the sucker to something
like 100,000 records, and it will prevent any result sets with a greater
number of records.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tue, Oct 31 2006 9:43 AMPermanent Link

adam
What you should consider doing is using the TDBISAMQuery.MaxRowCount
property to govern these types of issues.  Just set the sucker to something
like 100,000 records, and it will prevent any result sets with a greater
number of records.

--

Brilliant idea Tim!! I will implement it immediately. I can even put in a "continue or
cancel" dialogue at 100,000 records so if the query really should be that massive it will
carry on.

Adam
Wed, Nov 1 2006 7:33 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Adam,

<< Brilliant idea Tim!! I will implement it immediately. I can even put in a
"continue or cancel" dialogue at 100,000 records so if the query really
should be that massive it will carry on. >>

That might be a little harder since there's really no way to intercept the
MaxRowCount governor action.  It will simply stop the query at 100,000 rows
in the result set.

--
Tim Young
Elevate Software
www.elevatesoft.com

Image