table being deleted

I have a hell of a strange problem.
This has happened twice at first thought I must have done something but now
again happened so am thinking something more sinister....
I have a table called sales which has twice been totally deleted.
Have checked the transaction log which previously I didn't have and it shows the
delete happened during the last person being logged on and the whole table was
deleted row by row. Therefore it is impossible for the user to have done it so
must assume either the db is at fault or an outside hacker???? No other tables
are affected.
Any ideas????

Am using ASA8.02 4308
---== Posted via the PFCGuide Web Newsreader ==---
http://www.pfcguide.com/_newsgroups/group_list.asp 
0
matt
10/22/2003 4:21:30 AM
sybase.sqlanywhere.general 32637 articles. 4 followers. Follow

6 Replies
290 Views

Similar Articles

[PageSpeed] 10

matt wrote:
> Have checked the transaction log which previously I didn't have and it shows the
> delete happened during the last person being logged on and the whole table was
> deleted row by row. Therefore it is impossible for the user to have done it so
> must assume either the db is at fault or an outside hacker???? 

First, you should *always* run with a transaction log for both 
performance and data recovery purposes.  See the online ASA 8.x docs:

   Adaptive Server Anywhere Database Administration Guide
     11. Backup and Data Recovery
       Understanding backups
         - The transaction log

   and

   Adaptive Server Anywhere SQL User's Guide
     5. Monitoring and Improving Performance
       Top performance tips
         - Always use a transaction log

(or just look up "transaction log" in the index).

Second, the transaction log is not a log of the exact SQL statements 
that are sent to the engine.  It is (basically) a log of the *results* 
of executing those SQL statements.  To get a log of the SQL statements 
sent to the engine, see the "-zr" option for the dbeng8/dbsrv8 commands.

I suspect that there is a bug in your program, and this is causing all 
of the rows to be deleted.  Here are steps I did from an MS-Windows 
command prompt to get the same behaviour you are describing:

1. cd \
2. copy %ASANY8%\asademo.*
2. dbisql -c "uid=dba;pwd=sql;dbf=asademo.db" DELETE FROM 
dba.sales_order_items
3. dbtran -c "uid=dba;pwd=sql;dbf=asademo.db" asademo.out

Looking in asademo.out, I see 1097 DELETEs.

This highlights some things about "DELETE FROM <table>":

- deletes all rows in the table (note: there is no WHERE clause!)
- logs all rows that are deleted (note: TRUNCATE TABLE does not,
   so it is a more efficient algorithm for removing all data from a
   table)

Hope this helps,
greg.fenton
-- 
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

0
Greg
10/22/2003 6:00:25 AM
But in the transaction log the delete statement does provide a where
clause...
See below.
I have cascade deletes set for sales table so therefore I loose the payments
as well.
How do I debug this to find the problem as it has only happened twice in 3
months. But those times were only a week apart.
Any other ideas as I can't replicate this behaviour to debug??



--BEGIN TRANSACTION-1005-0000387665
BEGIN TRANSACTION
go
--DELETE-1005-0000387666
DELETE FROM DBA.SALES
 WHERE Sales_Id=202329
go
--BEGIN TRIGGER-1005-0000387675
--DELETE-1005-0000387676
DELETE FROM DBA.PAYMENT
 WHERE PAYMENT_NUMBER=199395
go
--END TRIGGER-1005-0000387685
--DELETE-1005-0000387686
DELETE FROM DBA.SALES
 WHERE Sales_Id=202330
go
--BEGIN TRIGGER-1005-0000387695
--DELETE-1005-0000387696
DELETE FROM DBA.PAYMENT
 WHERE PAYMENT_NUMBER=199396
go


0
MJN
10/22/2003 6:51:54 AM
Two possibilities:

First, to help recovery, you could create a "shadow table" and delete
trigger to make a backup copy of all deleted rows. The shadow table
looks like the base table but has an extra TIMESTAMP DEFAULT CURRENT
TIMESTAMP column in the primary key. The delete trigger is on the base
table, for each row, and it simply inserts the base table row into the
shadow table, plus the timestamp (which is necessary if the same
primary key is re-used in the base table and deleted again).

That is only necessary if you don't want to muck about with the
transaction log to research the problem, restore rows, etcetera.

Second, consider turning on request level logging on the server side.
This will slow performance somewhat, *and* use up disk space, but it
will allow you to see exactly what kind of SQL commands crossed the
client server boundary, with dates and times you can match to the
timestamps in the shadow table.

   dbsrv8 -zr SQL -zo filespec -zs 1024

That logs SQL statements, to a separate text filespec, and starts a
new file every 1024K so the files don't get too massive to read with,
say, notepad.

Request level logging doesn't have a good writeup in the V8 Help but
you can find some by doing a Help "Search" (not "Index"):

   request and level and logging

Breck

On 21 Oct 2003 23:51:54 -0700, "MJN" <NOkink_jpSPAM@hotmail.com>
wrote:

>But in the transaction log the delete statement does provide a where
>clause...
>See below.
>I have cascade deletes set for sales table so therefore I loose the payments
>as well.
>How do I debug this to find the problem as it has only happened twice in 3
>months. But those times were only a week apart.
>Any other ideas as I can't replicate this behaviour to debug??
>
>
>
>--BEGIN TRANSACTION-1005-0000387665
>BEGIN TRANSACTION
>go
>--DELETE-1005-0000387666
>DELETE FROM DBA.SALES
> WHERE Sales_Id=202329
>go
>--BEGIN TRIGGER-1005-0000387675
>--DELETE-1005-0000387676
>DELETE FROM DBA.PAYMENT
> WHERE PAYMENT_NUMBER=199395
>go
>--END TRIGGER-1005-0000387685
>--DELETE-1005-0000387686
>DELETE FROM DBA.SALES
> WHERE Sales_Id=202330
>go
>--BEGIN TRIGGER-1005-0000387695
>--DELETE-1005-0000387696
>DELETE FROM DBA.PAYMENT
> WHERE PAYMENT_NUMBER=199396
>go
>

--
bcarter@risingroad.com
Mobile and Distributed Enterprise Database Applications
www.risingroad.com
0
Breck
10/22/2003 10:26:15 AM
Thanks Breck will do.
Also in my payment table I have a fk (sales_id) that allows cascade deletes.
Do u think this may have anything to do with it???
Although I doubt it for the obvious reasons but also the same db structure
is in 3 other db's and have no problems with them like this.

"Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com> wrote in
message news:qolcpvsmvk3liku0q7vb6pijtdcv0r4uvv@4ax.com...
> Two possibilities:
>
> First, to help recovery, you could create a "shadow table" and delete
> trigger to make a backup copy of all deleted rows. The shadow table
> looks like the base table but has an extra TIMESTAMP DEFAULT CURRENT
> TIMESTAMP column in the primary key. The delete trigger is on the base
> table, for each row, and it simply inserts the base table row into the
> shadow table, plus the timestamp (which is necessary if the same
> primary key is re-used in the base table and deleted again).
>
> That is only necessary if you don't want to muck about with the
> transaction log to research the problem, restore rows, etcetera.
>
> Second, consider turning on request level logging on the server side.
> This will slow performance somewhat, *and* use up disk space, but it
> will allow you to see exactly what kind of SQL commands crossed the
> client server boundary, with dates and times you can match to the
> timestamps in the shadow table.
>
>    dbsrv8 -zr SQL -zo filespec -zs 1024
>
> That logs SQL statements, to a separate text filespec, and starts a
> new file every 1024K so the files don't get too massive to read with,
> say, notepad.
>
> Request level logging doesn't have a good writeup in the V8 Help but
> you can find some by doing a Help "Search" (not "Index"):
>
>    request and level and logging
>
> Breck
>
> On 21 Oct 2003 23:51:54 -0700, "MJN" <NOkink_jpSPAM@hotmail.com>
> wrote:
>
> >But in the transaction log the delete statement does provide a where
> >clause...
> >See below.
> >I have cascade deletes set for sales table so therefore I loose the
payments
> >as well.
> >How do I debug this to find the problem as it has only happened twice in
3
> >months. But those times were only a week apart.
> >Any other ideas as I can't replicate this behaviour to debug??
> >
> >
> >
> >--BEGIN TRANSACTION-1005-0000387665
> >BEGIN TRANSACTION
> >go
> >--DELETE-1005-0000387666
> >DELETE FROM DBA.SALES
> > WHERE Sales_Id=202329
> >go
> >--BEGIN TRIGGER-1005-0000387675
> >--DELETE-1005-0000387676
> >DELETE FROM DBA.PAYMENT
> > WHERE PAYMENT_NUMBER=199395
> >go
> >--END TRIGGER-1005-0000387685
> >--DELETE-1005-0000387686
> >DELETE FROM DBA.SALES
> > WHERE Sales_Id=202330
> >go
> >--BEGIN TRIGGER-1005-0000387695
> >--DELETE-1005-0000387696
> >DELETE FROM DBA.PAYMENT
> > WHERE PAYMENT_NUMBER=199396
> >go
> >
>
> --
> bcarter@risingroad.com
> Mobile and Distributed Enterprise Database Applications
> www.risingroad.com


0
MJN
10/22/2003 10:54:26 AM
Make shadow tables for them both if you want. The request level log
will show which table is getting deleted from the application point of
view.

Breck

On 22 Oct 2003 03:54:26 -0700, "MJN" <NOkink_jpSPAM@hotmail.com>
wrote:

>Thanks Breck will do.
>Also in my payment table I have a fk (sales_id) that allows cascade deletes.
>Do u think this may have anything to do with it???
>Although I doubt it for the obvious reasons but also the same db structure
>is in 3 other db's and have no problems with them like this.
>
>"Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com> wrote in
>message news:qolcpvsmvk3liku0q7vb6pijtdcv0r4uvv@4ax.com...
>> Two possibilities:
>>
>> First, to help recovery, you could create a "shadow table" and delete
>> trigger to make a backup copy of all deleted rows. The shadow table
>> looks like the base table but has an extra TIMESTAMP DEFAULT CURRENT
>> TIMESTAMP column in the primary key. The delete trigger is on the base
>> table, for each row, and it simply inserts the base table row into the
>> shadow table, plus the timestamp (which is necessary if the same
>> primary key is re-used in the base table and deleted again).
>>
>> That is only necessary if you don't want to muck about with the
>> transaction log to research the problem, restore rows, etcetera.
>>
>> Second, consider turning on request level logging on the server side.
>> This will slow performance somewhat, *and* use up disk space, but it
>> will allow you to see exactly what kind of SQL commands crossed the
>> client server boundary, with dates and times you can match to the
>> timestamps in the shadow table.
>>
>>    dbsrv8 -zr SQL -zo filespec -zs 1024
>>
>> That logs SQL statements, to a separate text filespec, and starts a
>> new file every 1024K so the files don't get too massive to read with,
>> say, notepad.
>>
>> Request level logging doesn't have a good writeup in the V8 Help but
>> you can find some by doing a Help "Search" (not "Index"):
>>
>>    request and level and logging
>>
>> Breck
>>
>> On 21 Oct 2003 23:51:54 -0700, "MJN" <NOkink_jpSPAM@hotmail.com>
>> wrote:
>>
>> >But in the transaction log the delete statement does provide a where
>> >clause...
>> >See below.
>> >I have cascade deletes set for sales table so therefore I loose the
>payments
>> >as well.
>> >How do I debug this to find the problem as it has only happened twice in
>3
>> >months. But those times were only a week apart.
>> >Any other ideas as I can't replicate this behaviour to debug??
>> >
>> >
>> >
>> >--BEGIN TRANSACTION-1005-0000387665
>> >BEGIN TRANSACTION
>> >go
>> >--DELETE-1005-0000387666
>> >DELETE FROM DBA.SALES
>> > WHERE Sales_Id=202329
>> >go
>> >--BEGIN TRIGGER-1005-0000387675
>> >--DELETE-1005-0000387676
>> >DELETE FROM DBA.PAYMENT
>> > WHERE PAYMENT_NUMBER=199395
>> >go
>> >--END TRIGGER-1005-0000387685
>> >--DELETE-1005-0000387686
>> >DELETE FROM DBA.SALES
>> > WHERE Sales_Id=202330
>> >go
>> >--BEGIN TRIGGER-1005-0000387695
>> >--DELETE-1005-0000387696
>> >DELETE FROM DBA.PAYMENT
>> > WHERE PAYMENT_NUMBER=199396
>> >go
>> >
>>
>> --
>> bcarter@risingroad.com
>> Mobile and Distributed Enterprise Database Applications
>> www.risingroad.com
>

--
bcarter@risingroad.com
Mobile and Distributed Enterprise Database Applications
www.risingroad.com
0
Breck
10/22/2003 11:14:56 AM
MJN wrote:

> But in the transaction log the delete statement does provide a where
> clause...

Yes, because that's the way that ASA can identify the row.  If you 
follow the exact steps I posted, you'll see that the DELETE statements 
in my translated log also have a WHERE clause even though I don't 
specify one.

Basically think of it this way:  the user issues "DELETE FROM <table>" 
and ASA internally convert that to a loop that deletes each individual 
row, thus the WHERE clause gets added to the transaction log.

> Any other ideas as I can't replicate this behaviour to debug??

As I suggested in my previous post, use the "-zr" flag on the server 
startup to log the SQL being sent to the engine from the client.

Hope this helps,
greg.fenton
-- 
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

0
Greg
10/22/2003 2:46:23 PM
Reply:

Similar Artilces:

Deleting the master table withour deleting the child tables
Hi i have to delete the master table data without deleting the child table records,is there any solution for this,  parent table has relation with the child table. regards vinod.t.vSoftware EngineerUshustechTechnoparkTrivandrum India You need to temporarily disable constraints on the table. Disable constraints: ALTER TABLE MyTable NOCHECK CONSTRAINT ALL Enable constraints: ALTER TABLE MyTable CHECK CONSTRAINT ALL    If this post was useful to you, please mark it as answer. Thank you! Thanks johram i can try this solution, this is usefullSoftware Enginee...

Problem with Entity Model after deleteing table: cannot delete in Store, there can't add same table again
Moved by moderator XIII: This question's more related to the designer of Visual Studio 2008. Questions about Entity Framework itself should be posted into the Data Access forum:  Hi,  I am having a problem creating an Entity Data Model using Visual Studio 2008. To reproduce... Create a new data model. Added some tables by selecting Update Model from Database. Look in the Model Browser, tables are added under Entity Types. Delete one of the tables, the table is gone from Entity Types but is still in the Model Store under Tables/Views Now, when I select Update Mo...

Delete row and delete from table
Hi I need, after delete a grid row, execute a delete statmentProtected Sub GridView1_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles GridView1.RowCommand Dim advogado As StringIf (e.CommandName = "Apagar") Thenadvogado = RTrim(e.CommandArgument.ToString)Apaga_Processos(advogado)End Ifend subPrivate Sub Apaga_Processos(ByVal advogado As String)Dim conn As SqlConnectionDim comm As SqlCommandDim connectionString As String = ConfigurationManager.ConnectionStrings("mssql2.tuganet.com").ConnectionStringconn = New Sql...

how to delete from Table A based on Table B
lets say i have 100 employees on Table A and I have 10 employees on Table B. I want to delete all the employees in Table A that are in Table B. Delete From TableA Where EmpNum = (Select EmpNum From TableB)The above SQL wont work but i am looking for something similar. any ideas? You should use IN as your subquery is returning multiple records.Delete From TableA Where EmpNum IN (Select EmpNum From TableB)***********************Dinakar NethiLife is short. Enjoy it.***********************...

Showing table columns from multiple tables in Formview, but insert/update/delete rows of Master table
I have a page on which I have GridView and FomView control. On selection of a row in GridView the row gets displayed in the Item template of the FormVIew. The values are the result of inner join from multiple tables i.e. table Stock,Product and Workshop.  Please let me know how I could insert/update/delete data for the base table i.e. Stock table, but be able to see the column values from the associated tables on the Itemtemplate,Edittemplate and Inserttemplate of the FormView. Thanks,Anita Hi:   Take a look at this tutorial. It's for DetailsView and same ...

LINQ: How to delete automatically on delete from association table
Hi, I'll try to explain my problem with an example:  In DB(ms swl server 2005) I've 2 tables:A(ID, SomeAText)B(AID, SomeBText) In LINQ TO SQL (in mdbd file) I've association:A.ID --- Association ---> B.AIDIn Table B we can have many rows is same AID valueAlso, I've GridView which represents all rows from A table with LinqDataSource.Is there way how to set in LINQ that when delete a row from A table,AUTOMATICALLY all rows from B with AID(which points to ID of the deleting row from A) to bedeleted too ? Thanks in advance! asp.netWHAT ARE YOU CODING RIGHT NOW?G...

Tables in SQLAnywhere
Hello. I need fill a table with information of a text file. I'm using a DataWindow with the option dw_1.ImportFile(file.txt) for do it. The first time I have not problem because the table is empty, but in others ocasions I have to delete all information in these table and after fill the table with the function ImportFile again. I wish fill the table without delete the information in the table. Does anybody know any manner for do it? Thanks in advance. >Hello. I need fill a table with information of a text file. I'm using a >DataWindow with the option dw_1.Impor...

Tables?! Why Tables?!
Bit random...but why does everything have to be rendered as a table?  It's starting to get quite annoying.  Formview - does this need to be a table?  the Menu??  Wouldn't it make sense to allow the option to...say...not render a Menu as a table?  Am I the only one that thinks this or are there others?  www.internetgeeks.co.ukwww.norwichbookclub.co.uk You can use CSS Adapters to create your own adapters to override how the controls render it self. In the next version of ASP.Net, you will probably have more flexibility to specify the surrounding elements of th...

Cannot delete table
hi, i'm trying to delete a table (suspected to be corrupted or with errors) into my database using Sybase Central but then it just hung. while trying to view the corresponding columns of the said table, again my server just hung and remains to be idle. my .db is in Sybase ASE 11.9.2 running in Window NT 4.0 kindly help. thanks. ������������������������� Rodelio R. Ampon Management Advisory Service DEPT. OF HEALTH rra@doh.gov.ph ������������������������� ...

select from one table, put into another, delete from original table
Hi there, i am writing a stock control system for a small company. Most of it has been done apart from the picking part. When the user (staff member in the shop) places an order for a customer, they type in the item ID they want + the qty and there is a DDL to say collection, delivery etc. There are 3 main tables involved tbl_stock - where stock information + pics are held tbl_location - where the location of stock is held, each row has 11 columns e.g. Item Name  Qty1  Location1  Qty2  Location2 etc up to 5. Each row is for one item, so for example if there is stockID 2...

From Table to Table
I am working in Delphi 4 and ran into a roadblock I am unfamiliar with. I am trying to search and find values in one table based off of matching values in another. For example a table of objects with fields like name, description, type, ect; and a table of object properties in which each object can be represented multiple times based on the settings on the page at the time. So if i search the object property table (and eventually loop through to repeat for all objects) to find all cases of visible objects when a certain criteria is met. It stops on the first object with "Yes" in th...

Deleting a table
I 'm using ASA 7.03.2046 and I have dropped all publications and I went to drop a table but I keep getting a message stating that this table has publications. There are NO publications in this database. Is there something I'm missing here. Thanks, Kevin Please Ignore this posting , I deleted ALL publications for SQL Remote but there was a Mobilink template that had the table in it that I was trying to drop. "Kevin C. Dunn" <no_spam_kevin.dunn@spsinc.com> wrote in message news:GfAGjg5QCHA.245@forums.sybase.com... > I 'm using ASA 7.03.2046 and I...

delete all tables
Hi folk, whats the SQL syntax of deleting all user tables  of a specific database on a Microsoft SQL server?thanks in advance,mulata  use yourTable;goEXEC sp_MSforeachtable @command1 = "DROP TABLE ?"--"sp_MSforeachtable" is one of the undocumented SQL Server system stored procedures.Limno hm sorry I don"t understand your solution.Is there a way that you do that in a "one line SQL statement" , something like: "drop table (select * from usertables) " thanks  I agree with limon's solution, which is really straight and easy to use. It will go through each user tabl...

deleting table?
I have a dw, if i destruct this while start retrieving rows, will it delete the table or affect it somehow?? thnaks in advance The actual data is in no way affected until dw.Update() is called. The only way I know of to delete is a table is to issue Drop Table, which can't be done via dw.Update() "ajay" <ajayvarier@yahoo.com> wrote in message news:wFq7GmBkCHA.198@forums.sybase.com... > I have a dw, if i destruct this while start retrieving rows, will it delete > the table or affect it somehow?? > thnaks in advance > > >>The a...

Web resources about - table being deleted - sybase.sqlanywhere.general

Category:Wikipedia files moved to Wikimedia Commons which could not be deleted - Wikipedia, the free ...
Category:Wikipedia files moved to Wikimedia Commons which could not be deleted - Wikipedia, the free ...

Deleted Facebook Timeline Nominated For Award
A timeline page commissioned by the Israel Anti-Drug Authority was nominated for a Webby Award in the “best use of social media” category, even ...

Facebook tests option for page owners to give users feedback about why comments were deleted
Facebook appears to be testing a new option for page owners to send users a private message about why their comments were removed from the page, ...

Why I deleted Google+
Explore scriptingnews' photos on Flickr. scriptingnews has uploaded 5920 photos to Flickr.

Mrs Doubtfire - Deleted Scenes - YouTube
With the sad, and tragic loss of Robin Williams, here are all the deleted scenes from his 1993 movie, Mrs Doubtfire. Robin is my comedy hero. ...

Rape charges dropped after deleted messages recovered from iPhone
Deleted but decoded text messages undermined a schoolgirl's claims against a northern beaches businessman, writes Joel Gibson.

Read A Deleted Scene From 'The Dark Knight Rises'
Picture: Warner Bros. / The Dark Knight Rises Though it was nearly a three hour film, it’s no surprise that Christopher Nolan‘s “The...

Carly McBride Facebook account deleted hours after she vanished
It is the mysterious deletion of a social media account that could help detectives investigating the suspected murder of mother-of-two Carly ...

Yet again, expletive is deleted: Mike Carlton resigns from the Sydney Morning Herald
PROUD hater Mike Carlton has resigned from The Sydney Morning Herald but the controversial columnist is certainly no stranger to the unemployment ...

Madeline McCann Twitter account deleted over abuse Ben Needham
THE parents of missing child Madeline McCann have been forced to delete their Twitter account due to a torrent of “hatred and sheer viscousness” ...

Resources last updated: 1/6/2016 9:39:54 AM