[Info-ingres] E_US1262 Your transaction has been externally aborted
Paul White
paul.white at shift7solutions.com.au
Thu Sep 24 05:21:34 UTC 2015
Hi Charaka,
You have an Ingres question here.
How big is your transaction log? I suggest 2GB is reasonable for a busy
production site.
The transaction log records a before and after image of every page affected
in an update.
So you will need at least twice the table size if you are going to update
all records.
The wo_history_det table is 2683 bytes wide. Each record uses 4K page for
storage.
71795 records is at least 294,072,320 bytes.
so you will need at least 588,144,640 bytes.
Also take into account any indexes using the field as they will also be
recorded in the transaction log.
Options you might consider.
• Increase size of your transaction log
• Break the update into smaller batches say 10,000 records at a time
• Use NOLOGGING (not for production)
• Modify the table to use compression to fit more records on a page
Paul
From: Charaka Wijerathne [mailto:charakaw at peercore.com.au]
Sent: Thursday, 24 September 2015 2:25 PM
To: openroad-developer at lists.ingres.com
Cc: paul.white at shift7solutions.com.au
Subject: E_US1262 Your transaction has been externally aborted
Hi All,
I’m continuously getting below error when I was trying execute below query.
• Query:
update wo_history_det set is_processed = 0;
• Error:
update wo_history_det set is_processed = 0 \g
Executing . . .
E_US1262 Your transaction has been externally aborted. See errlog.log for
more information.
(Thu Sep 24 10:04:51 2015)
Currently I’m having 71795 records in this table and above mentioned query
supposed to update the value in a field in all records.
Below I have mentioned table schema and error.log records.
Could you please help me on this to fix this error.
Greatly appreciate your help on this.
Thanks.
Error.log
GP-WS11 ::[II\INGRES\1264 , 4708 , 1e380100]: Thu Sep 24
10:02:48 2015 E_QE0022_QUERY_ABORTED The query has been aborted.
GP-WS11 ::[II\INGRES\1264 , 4708 , 1e380100]: Thu Sep 24
10:02:48 2015 E_DM9059_TRAN_FORCE_ABORT The transaction (000055E9,
56E8787A) in database delta is being force aborted.
GP-WS11 ::[II\INGRES\1264 , 4708 , 1ef1d200]: Thu Sep 24
10:04:27 2015 E_QE0022_QUERY_ABORTED The query has been aborted.
GP-WS11 ::[II\INGRES\1264 , 4708 , 1ef1d200]: Thu Sep 24
10:04:27 2015 E_DM9059_TRAN_FORCE_ABORT The transaction (000055E9,
56E8799B) in database delta is being force aborted.
GP-WS11 ::[II\INGRES\1264 , 4708 , 1e6fb280]: Thu Sep 24
10:04:51 2015 E_DM010C_TRAN_ABORTED The transaction has been aborted to
release transaction log space.
GP-WS11 ::[II\INGRES\1264 , 4708 , 1e6fb280]: Thu Sep 24
10:04:51 2015 E_QE0024_TRANSACTION_ABORTED The transaction log file is
full. The transaction will be aborted.
GP-WS11 ::[II\INGRES\1264 , 4708 , 1e6fb280]: Thu Sep 24
10:04:51 2015 E_DM9059_TRAN_FORCE_ABORT The transaction (000055E9,
56CC9B43) in database delta is being force aborted.
Table schema
/* SQL script created by COPYDB, version II 9.2.1 (int.w32/103). */
\sql
set autocommit on
\p\g
set nojournaling
\p\g
\sql
set session with privileges=all
\p\g
set decimal '.'
\p\g
set date_format 'multinational'
\p\g
set money_format 'l:$'
\p\g
set money_prec '2'
\p\g
/* TABLES */
\continue
drop table wo_history_det
\p\g
create table wo_history_det(
work_order_no varchar(12) not null default '',
work_title varchar(50) not null default '',
work_done varchar(1000) not null default '',
description varchar(250) not null default '',
priority varchar(10) not null default '',
super_code varchar(16) not null default '',
status varchar(10) not null default '',
contract_number varchar(12) not null default '',
on_charge_customer varchar(250) not null default '',
on_charge_type varchar(250) not null default '',
cust_ref varchar(30) not null default '',
site varchar(30) not null default '',
raised_date date not null default '',
start_date date not null default '',
finish_date date not null default '',
on_charge_flag integer not null default 0,
meter_reading integer not null default 0,
sorder_no integer not null default 0,
is_wo_template integer not null default 0,
equip_no integer not null default 0,
equip_code varchar(18) not null default '',
equip_desc varchar(100) not null default '',
company_code varchar(3) not null default '',
company_name varchar(40) not null default '',
cust_code varchar(8) not null default '',
name varchar(60) not null default '',
service_centre varchar(8) not null default '',
service_centre_desc varchar(80) not null default '',
type varchar(10) not null default '',
type_desc varchar(80) not null default '',
sub_type varchar(10) not null default '',
sub_type_desc varchar(80) not null default '',
sub_type2 varchar(10) not null default '',
sub_type2_desc varchar(80) not null default '',
issue_cost money not null default 0,
material_cost money not null default 0,
labour_cost money not null default 0,
misc_amt money not null default 0,
ivce_amt money not null default 0,
record_updated_on date not null default '',
history_refreshed_on date not null default '',
is_processed integer not null default 0
)
with duplicates,
nojournaling,
page_size = 4096,
location = (ii_database),
security_audit=(table,norow)
\p\g
modify wo_history_det to btree unique on
work_order_no
with nonleaffill = 80,
leaffill = 70,
fillfactor = 80,
extend = 16,
page_size = 4096
\p\g
set journaling on wo_history_det
\p\g
/* INDEXES */
create index idx_wo_history_det_1 on wo_history_det (
work_order_no)
with structure = btree,
page_size = 4096,
nocompression,
key = (work_order_no),
persistence,
nonleaffill = 80,
leaffill = 70,
fillfactor = 80,
location = (ii_database)
\p\g
/* PERMISSIONS */
grant select on table ingres.wo_history_det to public
\p\g
grant update on table ingres.wo_history_det to public
\p\g
grant delete on table ingres.wo_history_det to public
\p\g
grant insert on table ingres.wo_history_det to public
\p\g
grant references on table ingres.wo_history_det to public
\p\g
grant copy_into on table ingres.wo_history_det to public
\p\g
grant copy_from on table ingres.wo_history_det to public
\p\g
Best Regards,
Charaka Wijerathne
More information about the Info-ingres
mailing list