Deleting records from a table
A user in dbaccess (IDS 11.50.FC8 on Solaris 10) ran a DELETE without a WHERE clause and aborted it mid-execution; Larry asked whether rows were actually removed. Replies: if the database is logged, the interrupted transaction is rolled back, and you can confirm this with onlog by locating the transaction (by time/table partnum) and checking for a commit versus rollback record — a rolled-back transaction shows one rollback record plus CLR records matching each delete. Reconstructing the original SQL (e.g. the WHERE clause) from the logs is considered impractical. No explicit confirmation of what happened in Larry's case is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
IDS 11.50.FC8
Solaris 10
If I have a user that logged into Informix and connected to a database via
dbaccess.......
A user was deleting records from a table using SQL. She forgot to put a
"WHERE" clause in, ran the command, but she hit delete before the command
completed because she saw her mistake.
Would some of the records have been deleted, or will it treat the command as a
transaction and do a "rollback"?
Larry
Original post:
IDS 11.50.FC8
Solaris 10
If I have a user that logged into Informix and connected to a database via
dbaccess.......
A user was deleting records from a table using SQL. She forgot to put a
"WHERE" clause in, ran the command, but she hit delete before the command
completed because she saw her mistake.
Would some of the records have been deleted, or will it treat the command as a
transaction and do a "rollback"?
Larry
Response:
Well, regardless of what dbaccess should/would do...assuming you have logging
on the database, you can determine exactly what happened by looking at onlog
output. You should be able to find the transaction based on the time and the
table's partnum and see if the transaction was compled in the onlog by either
the presence of a committed record or rolledback record.
Jacques Renaut
IBM Informix Advanced Support
If the database is logged it will be rolled back....
On Mon, Oct 17, 2016 at 4:34 PM, LARRY SORENSEN <LSORENSEN25@msn.com> wrote:
> IDS 11.50.FC8
>
> Solaris 10
>
> If I have a user that logged into Informix and connected to a database via
> dbaccess.......
>
> A user was deleting records from a table using SQL. She forgot to put a
> "WHERE" clause in, ran the command, but she hit delete before the command
> completed because she saw her mistake.
>
> Would some of the records have been deleted, or will it treat the command
> as a
> transaction and do a "rollback"?
>
> Larry
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--94eb2c048e5a810dd7053f11b04d
Is there a way to get the SQL statement that was run from the onlog output?
________________________________
From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of JACQUES RENAUT
<jrenaut@us.ibm.com>
Sent: Monday, October 17, 2016 10:01 AM
To: ids@iiug.org
Subject: Re: Deleting records from a table [37993]
Original post:
IDS 11.50.FC8
Solaris 10
If I have a user that logged into Informix and connected to a database via
dbaccess.......
A user was deleting records from a table using SQL. She forgot to put a
"WHERE" clause in, ran the command, but she hit delete before the command
completed because she saw her mistake.
Would some of the records have been deleted, or will it treat the command as a
transaction and do a "rollback"?
Larry
Response:
Well, regardless of what dbaccess should/would do...assuming you have logging
on the database, you can determine exactly what happened by looking at onlog
output. You should be able to find the transaction based on the time and the
table's partnum and see if the transaction was compled in the onlog by either
the presence of a committed record or rolledback record.
Jacques Renaut
IBM Informix Advanced Support
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
original post:
Is there a way to get the SQL statement that was run from the onlog output?
Response:
If you are referring to where clause specifics, I would say from a
practicality stand point, no. The server itself has to do something somewhat
like that when you have ER configured but that isn't externalized and I
imagine it would be very difficult to try and generate as a user from just the
onlog data.
Jacques Renaut
There are people trying this, but it's hard, and the best you can get is=20
an SQL reflecting a particular single row INSERT/UPDATE/DELETE operation=20
visible in the logs which not necessarily is the actual SQL that had been=20
ran.
You'd have to differentiate at least three cases
- log records contain full row images - for INSERTs, DELETES and=20
full-row-logged UPDATEs
Here you could try applying appropriate table schema (take care of=20
in-place alters!)
- log records contain only changed bytes of an update - for=20
non-full-row-logged UPDATEs
You first had to determine a row image reflecting either before or=20
after image of such update, then you could apply the changes and proceed=20
as above.
- anything out-of-row, i.e. blobs or sblobs being part of a row.
That's going to be real complicated, but of course all the bits needed=20
are there - how else would the server recover such log records :-D
Has this delete rolled back finally? If so, I'd say there's nothing to=20
worry about.
Andreas
From: "LARRY SORENSEN" <LSORENSEN25@msn.com>
To: ids@iiug.org
Date: 17.10.2016 20:56
Subject: Re: Deleting records from a table [37995]
Sent by: ids-bounces@iiug.org
Is there a way to get the SQL statement that was run from the onlog=20
output?=20
=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=
=5F=5F=5F=5F=5F=5F=5F=20
From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of JACQUES=20
RENAUT=20
<jrenaut@us.ibm.com>=20
Sent: Monday, October 17, 2016 10:01 AM=20
To: ids@iiug.org=20
Subject: Re: Deleting records from a table [37993]=20
Original post:=20
IDS 11.50.FC8=20
Solaris 10=20
If I have a user that logged into Informix and connected to a database via =
dbaccess.......=20
A user was deleting records from a table using SQL. She forgot to put a=20
"WHERE" clause in, ran the command, but she hit delete before the command=20
completed because she saw her mistake.=20
Would some of the records have been deleted, or will it treat the command=20
as a=20
transaction and do a "rollback"?=20
Larry=20
Response:=20
Well, regardless of what dbaccess should/would do...assuming you have=20
logging=20
on the database, you can determine exactly what happened by looking at=20
onlog=20
output. You should be able to find the transaction based on the time and=20
the=20
table's partnum and see if the transaction was compled in the onlog by=20
either=20
the presence of a committed record or rolledback record.=20
Jacques Renaut=20
IBM Informix Advanced Support=20
***************************************************************************=
****=20
Forum Note: Use "Reply" to post a response in the discussion forum.=20
***************************************************************************=
****=20
Forum Note: Use "Reply" to post a response in the discussion forum.=20
Thank you.
I see a lot of ROLLBACKs in the log, but I can't tell which, if any, apply to
that I am looking for. I ran onlog specifying the table that I am worried
about and there were still too many around the right time to know which may
apply.
Larry
________________________________
From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of Andreas Legner
<andreas.legner@de.ibm.com>
Sent: Tuesday, October 18, 2016 3:30 AM
To: ids@iiug.org
Subject: Re: Deleting records from a table [37997]
There are people trying this, but it's hard, and the best you can get is=20
an SQL reflecting a particular single row INSERT/UPDATE/DELETE operation=20
visible in the logs which not necessarily is the actual SQL that had been=20
ran.
You'd have to differentiate at least three cases
- log records contain full row images - for INSERTs, DELETES and=20
full-row-logged UPDATEs
Here you could try applying appropriate table schema (take care of=20
in-place alters!)
- log records contain only changed bytes of an update - for=20
non-full-row-logged UPDATEs
You first had to determine a row image reflecting either before or=20
after image of such update, then you could apply the changes and proceed=20
as above.
- anything out-of-row, i.e. blobs or sblobs being part of a row.
That's going to be real complicated, but of course all the bits needed=20
are there - how else would the server recover such log records :-D
Has this delete rolled back finally? If so, I'd say there's nothing to=20
worry about.
Andreas
From: "LARRY SORENSEN" <LSORENSEN25@msn.com>
To: ids@iiug.org
Date: 17.10.2016 20:56
Subject: Re: Deleting records from a table [37995]
Sent by: ids-bounces@iiug.org
Is there a way to get the SQL statement that was run from the onlog=20
output?=20
=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=
=5F=5F=5F=5F=5F=5F=5F=20
From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of JACQUES=20
RENAUT=20
<jrenaut@us.ibm.com>=20
Sent: Monday, October 17, 2016 10:01 AM=20
To: ids@iiug.org=20
Subject: Re: Deleting records from a table [37993]=20
Original post:=20
IDS 11.50.FC8=20
Solaris 10=20
If I have a user that logged into Informix and connected to a database via =
dbaccess.......=20
A user was deleting records from a table using SQL. She forgot to put a=20
"WHERE" clause in, ran the command, but she hit delete before the command=20
completed because she saw her mistake.=20
Would some of the records have been deleted, or will it treat the command=20
as a=20
transaction and do a "rollback"?=20
Larry=20
Response:=20
Well, regardless of what dbaccess should/would do...assuming you have=20
logging=20
on the database, you can determine exactly what happened by looking at=20
onlog=20
output. You should be able to find the transaction based on the time and=20
the=20
table's partnum and see if the transaction was compled in the onlog by=20
either=20
the presence of a committed record or rolledback record.=20
Jacques Renaut=20
IBM Informix Advanced Support=20
***************************************************************************=
****=20
Forum Note: Use "Reply" to post a response in the discussion forum.=20
***************************************************************************=
****=20
Forum Note: Use "Reply" to post a response in the discussion forum.=20
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
There will be only one rollback record. There will be a whole bunch of CLR log
records for the transaction which rolled back. Each CLR log record corresponds
to a single delete log record.
Madison Pruet
Retired and Loving it
On Tuesday, October 18, 2016 7:39 AM, LARRY SORENSEN <LSORENSEN25@msn.com>
wrote:
Thank you.
I see a lot of ROLLBACKs in the log, but I can't tell which, if any, apply to
that I am looking for. I ran onlog specifying the table that I am worried
about and there were still too many around the right time to know which may
apply.
Larry
________________________________
From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of Andreas Legner
<andreas.legner@de.ibm.com>
Sent: Tuesday, October 18, 2016 3:30 AM
To: ids@iiug.org
Subject: Re: Deleting records from a table [37997]
There are people trying this, but it's hard, and the best you can get is=20
an SQL reflecting a particular single row INSERT/UPDATE/DELETE operation=20
visible in the logs which not necessarily is the actual SQL that had been=20
ran.
You'd have to differentiate at least three cases
- log records contain full row images - for INSERTs, DELETES and=20
full-row-logged UPDATEs
Here you could try applying appropriate table schema (take care of=20
in-place alters!)
- log records contain only changed bytes of an update - for=20
non-full-row-logged UPDATEs
You first had to determine a row image reflecting either before or=20
after image of such update, then you could apply the changes and proceed=20
as above.
- anything out-of-row, i.e. blobs or sblobs being part of a row.
That's going to be real complicated, but of course all the bits needed=20
are there - how else would the server recover such log records :-D
Has this delete rolled back finally? If so, I'd say there's nothing to=20
worry about.
Andreas
From: "LARRY SORENSEN" <LSORENSEN25@msn.com>
To: ids@iiug.org
Date: 17.10.2016 20:56
Subject: Re: Deleting records from a table [37995]
Sent by: ids-bounces@iiug.org
Is there a way to get the SQL statement that was run from the onlog=20
output?=20
=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=
=5F=5F=5F=5F=5F=5F=5F=20
From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of JACQUES=20
RENAUT=20
<jrenaut@us.ibm.com>=20
Sent: Monday, October 17, 2016 10:01 AM=20
To: ids@iiug.org=20
Subject: Re: Deleting records from a table [37993]=20
Original post:=20
IDS 11.50.FC8=20
Solaris 10=20
If I have a user that logged into Informix and connected to a database via =
dbaccess.......=20
A user was deleting records from a table using SQL. She forgot to put a=20
"WHERE" clause in, ran the command, but she hit delete before the command=20
completed because she saw her mistake.=20
Would some of the records have been deleted, or will it treat the command=20
as a=20
transaction and do a "rollback"?=20
Larry=20
Response:=20
Well, regardless of what dbaccess should/would do...assuming you have=20
logging=20
on the database, you can determine exactly what happened by looking at=20
onlog=20
output. You should be able to find the transaction based on the time and=20
the=20
table's partnum and see if the transaction was compled in the onlog by=20
either=20
the presence of a committed record or rolledback record.=20
Jacques Renaut=20
IBM Informix Advanced Support=20
***************************************************************************=
****=20
Forum Note: Use "Reply" to post a response in the discussion forum.=20
***************************************************************************=
****=20
Forum Note: Use "Reply" to post a response in the discussion forum.=20
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.