![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 6 of 6 total |
![]() |
Wed, Oct 25 2006 10:40 AM | Permanent 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 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | 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 AM | Permanent 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 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | 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 AM | Permanent 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 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | 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 |
This web page was last updated on Wednesday, August 14, 2024 at 07:31 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |