7.31 UD1 can't delete or update row get 1214 error
A user on IDS 7.31 UD1 (AIX 5.2) had one row of a 'mail' table with a bogus postage value (5043000.0 instead of 25) and couldn't update or delete it, even by rowid, getting error -1214 "Value exceeds limit of smallint precision". Respondents noted -1214 refers to a smallint, not the decimal postage column, and suspected a trigger. The table indeed had update/delete triggers copying rows into an audit table (exfpv:exmail); the advice was to check the data types of exmail's postage/opostage columns, likely defined as smallint and too small for the corrupt value. No confirmation from the original poster is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
hi all I used IDS 7.31 UD1 with AIX 5.2. the table info as follow: column name type nulls mailcode char(10) no inwpno char(10) no outwpno char(10) no postage decimal(8,1) no weight smallint no maildate char(8) no commname varchar(50) no commadd varchar(80) no wpcode char(3) no seq char(6) no kind char(1) yes In one row it should be 25 in postage column, however it shows 5043000.0. So we try to delete and update it by its unique inwpno. But even by rowid it still can't be updated. we got the 1214 error. 1214 error shows "Value exceeds limit of smallint precision". we have tried to restart the server and the problem is still here. Please give me any idea about it. thanks in advance, fan
The -1214 error is complaining about the contents of the weight column not postage. Something is not right. How did you insert the data in the first place (statement)? What was the update statement you used to try to fix this? Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, May 14, 2010 at 5:26 AM, FAN YANG <fania.yang@gmail.com> wrote: > hi all > > I used IDS 7.31 UD1 with AIX 5.2. > > the table info as follow: > > column name type nulls > > mailcode char(10) no > > inwpno char(10) no > > outwpno char(10) no > > postage decimal(8,1) no > > weight smallint no > > maildate char(8) no > > commname varchar(50) no > > commadd varchar(80) no > > wpcode char(3) no > > seq char(6) no > > kind char(1) yes > > In one row it should be 25 in postage column, however it shows 5043000.0. > So > we try to delete and update it by its unique inwpno. But even by rowid it > still can't be updated. we got the 1214 error. 1214 error shows "Value > exceeds > limit of smallint precision". > > we have tried to restart the server and the problem is still here. Please > give > me any idea about it. > > thanks in advance, > fan > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636ed68ac60819804868b4fd4
On 14 May 2010 10:26, FAN YANG <fania.yang@gmail.com> wrote: > hi all > > I used IDS 7.31 UD1 with AIX 5.2. > > the table info as follow: > > column name type nulls > > mailcode char(10) no > > inwpno char(10) no > > outwpno char(10) no > > postage decimal(8,1) no > > weight smallint no > > maildate char(8) no > > commname varchar(50) no > > commadd varchar(80) no > > wpcode char(3) no > > seq char(6) no > > kind char(1) yes > > In one row it should be 25 in postage column, however it shows 5043000.0. So > we try to delete and update it by its unique inwpno. But even by rowid it > still can't be updated. we got the 1214 error. 1214 error shows "Value exceeds > limit of smallint precision". > > we have tried to restart the server and the problem is still here. Please give > me any idea about it. > > thanks in advance, > fan > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > Fan The error indicates exceeding a smallint however postage is a dec(8,1) field, are you sure this is the field causing the error, and not weight ?? Are you updating by name or field position?? Keith
Sounds like you have a trigger on that table trying to do something with that column. Best guess is that the 5043000.0 is what's giving you fits. Dbaccess should be able tell you if a trigger exists. --EEM > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > FAN YANG > Sent: Friday, May 14, 2010 4:27 AM > To: ids@iiug.org > Subject: 7.31 UD1 can't delete or update row get 1214 error [20155] > > hi all > > I used IDS 7.31 UD1 with AIX 5.2. > > the table info as follow: > > column name type nulls > > mailcode char(10) no > > inwpno char(10) no > > outwpno char(10) no > > postage decimal(8,1) no > > weight smallint no > > maildate char(8) no > > commname varchar(50) no > > commadd varchar(80) no > > wpcode char(3) no > > seq char(6) no > > kind char(1) yes > > In one row it should be 25 in postage column, however it shows > 5043000.0. So > we try to delete and update it by its unique inwpno. But even by rowid > it > still can't be updated. we got the 1214 error. 1214 error shows "Value > exceeds > limit of smallint precision". > > we have tried to restart the server and the problem is still here. > Please give > me any idea about it. > > thanks in advance, > fan > > > *********************************************************************** > ******** > Forum Note: Use "Reply" to post a response in the discussion forum.
Perhaps the issue is not with this table but another one being updated via a trigger/sp? -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of FAN YANG Sent: Friday, May 14, 2010 4:27 AM To: ids@iiug.org Subject: 7.31 UD1 can't delete or update row get 1214 error [20155] hi all I used IDS 7.31 UD1 with AIX 5.2. the table info as follow: column name type nulls mailcode char(10) no inwpno char(10) no outwpno char(10) no postage decimal(8,1) no weight smallint no maildate char(8) no commname varchar(50) no commadd varchar(80) no wpcode char(3) no seq char(6) no kind char(1) yes In one row it should be 25 in postage column, however it shows 5043000.0. So we try to delete and update it by its unique inwpno. But even by rowid it still can't be updated. we got the 1214 error. 1214 error shows "Value exceeds limit of smallint precision". we have tried to restart the server and the problem is still here. Please give me any idea about it. thanks in advance, fan ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
hi,
I am not sure if there is anthing wrong with weight column,however it shows
the right content there. Our problem is the wrong content of the postage
column. We used the AP to insert the data and update the date with dbaccess as
follow.
update mail set postage ='25'
where inwpno="xxxx";
I check the table schema as follow, it did have two trigger but will it cause
this problem?
create table mail
(
mailcode char(10) not null ,
inwpno char(10) not null ,
outwpno char(10) not null ,
postage decimal(8,1) not null ,
weight smallint not null ,
maildate char(8) not null ,
commname varchar(50) not null ,
commadd varchar(80) not null ,
wpcode char(3) not null ,
seq char(6) not null ,
kind char(1)
) ;
revoke all on mail from "public";
create index mail_idx2_c on mail (inwpno,wpcode,
seq);
create index mail_idx1_c on mail (mailcode);
create index mail_idx3_c on mail (outwpno);
create unique index mail_idx4_c on mail (mailcode,
inwpno,maildate,outwpno,seq);
create index mail_idx5_c on mail (maildate,mailcode,
kind);
create trigger upd_mail_c update on mailrec referencing
old as pre_upd new as post_upd
for each row
(
insert into exfpv:exmail (chng_id,chng_d,chng_time,
userid,mailcode,inwpno,outwpno,postage,weight,maildate,commname,commadd,
wpcode,seq,kind,omailcode,oinwpno,ooutwpno,opostage,oweight,omaildate,
ocommname,ocommadd,owpcode,oseq,okind,trandate) values ('U' ,TODAY
,CURRENT year to fraction(3) ,USER ,post_upd.mailcode ,post_upd.inwpno
,post_upd.outwpno ,post_upd.postage ,post_upd.weight ,post_upd.maildate
,post_upd.commname ,post_upd.commadd ,post_upd.wpcode ,post_upd.seq
,post_upd.kind ,pre_upd.mailcode ,pre_upd.inwpno ,pre_upd.outwpno
,pre_upd.postage ,pre_upd.weight ,pre_upd.maildate ,pre_upd.commname
,pre_upd.commadd ,pre_upd.wpcode ,pre_upd.seq ,pre_upd.kind ,NULL
));
create trigger del_mail_c delete on mailrec referencing
old as pre_del
for each row
(
insert into exfpv:exmail (chng_id,chng_d,chng_time,
userid,mailcode,inwpno,outwpno,postage,weight,maildate,commname,commadd,
wpcode,seq,kind,trandate) values ('D' ,TODAY ,CURRENT year to fraction(3)
,USER ,pre_del.mailcode ,pre_del.inwpno ,pre_del.outwpno ,pre_del.postage
,pre_del.weight ,pre_del.maildate ,pre_del.commname ,pre_del.commadd
,pre_del.wpcode ,pre_del.seq ,pre_del.kind ,NULL ));
On Fri, May 14, 2010 at 5:26 AM, FAN YANG <fania.yang@gmail.com> wrote:
> hi all
>
> I used IDS 7.31 UD1 with AIX 5.2.
>
> the table info as follow:
>
> column name type nulls
>
> mailcode char(10) no
>
> inwpno char(10) no
>
> outwpno char(10) no
>
> postage decimal(8,1) no
>
> weight smallint no
>
> maildate char(8) no
>
> commname varchar(50) no
>
> commadd varchar(80) no
>
> wpcode char(3) no
>
> seq char(6) no
>
> kind char(1) yes
>
> In one row it should be 25 in postage column, however it shows 5043000.0.
> So
> we try to delete and update it by its unique inwpno. But even by rowid it
> still can't be updated. we got the 1214 error. 1214 error shows "Value
> exceeds
> limit of smallint precision".
>
> we have tried to restart the server and the problem is still here. Please
> give
> me any idea about it.
>
> thanks in advance,
> fan
>
>
>
Yup, check the data type for the postage and opostage columns of exmail. I'd
wager one or both of them is/are a smallint.
--EEM
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> FAN YANG
> Sent: Monday, May 17, 2010 1:08 AM
> To: ids@iiug.org
> Subject: Re: 7.31 UD1 can't delete or update row get 1214 e [20176]
>
> hi,
>
> I am not sure if there is anthing wrong with weight column,however it
> shows
> the right content there. Our problem is the wrong content of the
> postage
> column. We used the AP to insert the data and update the date with
> dbaccess as
> follow.
>
> update mail set postage ='25'
> where inwpno="xxxx";>
> I check the table schema as follow, it did have two trigger but will it
> cause
> this problem?
>
> create table mail
> (>
> mailcode char(10) not null ,
>
> inwpno char(10) not null ,
>
> outwpno char(10) not null ,
>
> postage decimal(8,1) not null ,
>
> weight smallint not null ,
>
> maildate char(8) not null ,
>
> commname varchar(50) not null ,
>
> commadd varchar(80) not null ,
>
> wpcode char(3) not null ,
>
> seq char(6) not null ,
>
> kind char(1)
> ) ;
> revoke all on mail from "public";>
> create index mail_idx2_c on mail (inwpno,wpcode,>
> seq);
> create index mail_idx1_c on mail (mailcode);>
> create index mail_idx3_c on mail (outwpno);
> create unique index mail_idx4_c on mail (mailcode,>
> inwpno,maildate,outwpno,seq);
> create index mail_idx5_c on mail (maildate,mailcode,>
> kind);
>
> create trigger upd_mail_c update on mailrec referencing>
> old as pre_upd new as post_upd
>
> for each row
>
> (
>
> insert into exfpv:exmail (chng_id,chng_d,chng_time,>
> userid,mailcode,inwpno,outwpno,postage,weight,maildate,commname,commadd
> ,
>
> wpcode,seq,kind,omailcode,oinwpno,ooutwpno,opostage,oweight,omaildate,
>
> ocommname,ocommadd,owpcode,oseq,okind,trandate) values ('U' ,TODAY
>
> ,CURRENT year to fraction(3) ,USER ,post_upd.mailcode ,post_upd.inwpno
>
> ,post_upd.outwpno ,post_upd.postage ,post_upd.weight ,post_upd.maildate
>
> ,post_upd.commname ,post_upd.commadd ,post_upd.wpcode ,post_upd.seq
>
> ,post_upd.kind ,pre_upd.mailcode ,pre_upd.inwpno ,pre_upd.outwpno
>
> ,pre_upd.postage ,pre_upd.weight ,pre_upd.maildate ,pre_upd.commname
>
> ,pre_upd.commadd ,pre_upd.wpcode ,pre_upd.seq ,pre_upd.kind ,NULL
>
> ));
>
> create trigger del_mail_c delete on mailrec referencing>
> old as pre_del
>
> for each row
>
> (
>
> insert into exfpv:exmail (chng_id,chng_d,chng_time,>
> userid,mailcode,inwpno,outwpno,postage,weight,maildate,commname,commadd
> ,
>
> wpcode,seq,kind,trandate) values ('D' ,TODAY ,CURRENT year to
> fraction(3)
>
> ,USER ,pre_del.mailcode ,pre_del.inwpno ,pre_del.outwpno
> ,pre_del.postage
>
> ,pre_del.weight ,pre_del.maildate ,pre_del.commname ,pre_del.commadd
>
> ,pre_del.wpcode ,pre_del.seq ,pre_del.kind ,NULL ));
>
> On Fri, May 14, 2010 at 5:26 AM, FAN YANG <fania.yang@gmail.com> wrote:
>
> > hi all
> >
> > I used IDS 7.31 UD1 with AIX 5.2.
> >
> > the table info as follow:
> >
> > column name type nulls
> >
> > mailcode char(10) no
> >
> > inwpno char(10) no
> >
> > outwpno char(10) no
> >
> > postage decimal(8,1) no
> >
> > weight smallint no
> >
> > maildate char(8) no
> >
> > commname varchar(50) no
> >
> > commadd varchar(80) no
> >
> > wpcode char(3) no
> >
> > seq char(6) no
> >
> > kind char(1) yes
> >
> > In one row it should be 25 in postage column, however it shows
> 5043000.0.
> > So
> > we try to delete and update it by its unique inwpno. But even by
> rowid it
> > still can't be updated. we got the 1214 error. 1214 error shows
> "Value
> > exceeds
> > limit of smallint precision".
> >
> > we have tried to restart the server and the problem is still here.
> Please
> > give
> > me any idea about it.
> >
> > thanks in advance,
> > fan
> >
> >
> >
>
>
> ***********************************************************************
> ********
> Forum Note: Use "Reply" to post a response in the discussion forum.
Related threads
- Problem with SYS_CONNECT_BY_PATH i SPL
- Replication: Understanding CDRGeval# threads
- Transactions blocked in output of online log
- Alternative for cursor