1205 error
John Adamski (IDS 10.00.FC9 on HP-UX) had one row in a 91-column table that could not be updated or deleted, always failing with error 1205 (invalid month in date), even though the row's date values looked fine on select and oncheck -cI/-cD reported no problems. Suggestions: a corrupt index key (drop/rebuild indexes and disable the audit triggers), a bug in trigger/stored-procedure code building dates on the fly, and using onmode -I 1205 to trap an AF file. The trace pointed to the live database rather than the audit table, and the poster planned downtime to rebuild indexes and drop audit triggers, but no outcome or confirmed fix is recorded in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
HPUX B.11.23 U ia64
IDS 10.00.FC9
I have a table with 91 columns about 334966 rows and we recently found 1 row
that can't be updated or deleted. If you try we get a 1205 error (Invalid
month in date). There are 3 rows that are data fields and the table has an
audit trigger on 15 of the columns which includes the 3 date fields.
SQL: New Run Modify Use-editor Output Choose Save Info Drop Exit
Modify the current SQL statements using the SQL editor.
----------------------- cars@carsitcp ---------- Press CTRL-W for Help --------
update stu_acad_rec
set prog="TEST"
where id=xxxxxx
and sess="FA" and yr=2010
1205: Invalid month in date
I have ran an oncheck -cI and oncheck -cD on both the 'live' and 'audit'
tables and it finds not problems. The audit table is getting other audits from
other rows.
Before I go through the obstacle course needed to get IBM support (I have to
go through our 3rd party provider). Is there anything else I can do? My
assumption is data corruption of some sorts. But sure don't see it. As doing a
select in dbaccess or an unload of the row the data looks good.
Part of the row in question
...
reg_upd_date 02/22/2010
no_adrp 0
cl_rank 0
cl_size 0
...
goal
wd_code WD
wd_date 02/22/2010
wd_hrs 0.00
last_attended_date 02/22/2010
...
I even tried to this which works:
select last_attended_date, last_attended_date + 5 fiveplus
from stu_acad_rec
where id=xxxxxx and sess="FA" and yr="2010"
last_attended_date fiveplus
02/22/2010 02/27/2010
1 row(s) retrieved.
Any suggestions would be welcomed.
John Adamski
Network Specialist
Graceland University
Any non-date fields that get converted into dates in your triggers, maybe
something like a fiscal month field? That would be my first guess given the
error you received.
--EEM
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> John Adamski
> Sent: Monday, April 19, 2010 9:48 AM
> To: ids@iiug.org
> Subject: 1205 error [19725]
>
> HPUX B.11.23 U ia64
> IDS 10.00.FC9
>
> I have a table with 91 columns about 334966 rows and we recently found
> 1 row
> that can't be updated or deleted. If you try we get a 1205 error
> (Invalid
> month in date). There are 3 rows that are data fields and the table has
> an
> audit trigger on 15 of the columns which includes the 3 date fields.
>
> SQL: New Run Modify Use-editor Output Choose Save Info Drop Exit
> Modify the current SQL statements using the SQL editor.
>
> ----------------------- cars@carsitcp ---------- Press CTRL-W for Help
> --------
>
> update stu_acad_rec
> set prog="TEST"
> where id=xxxxxx
> and sess="FA" and yr=2010
>
> 1205: Invalid month in date>
> I have ran an oncheck -cI and oncheck -cD on both the 'live' and
> 'audit'
> tables and it finds not problems. The audit table is getting other
> audits from
> other rows.
>
> Before I go through the obstacle course needed to get IBM support (I
> have to
> go through our 3rd party provider). Is there anything else I can do? My
> assumption is data corruption of some sorts. But sure don't see it. As
> doing a
> select in dbaccess or an unload of the row the data looks good.
>
> Part of the row in question
>
> ....
> reg_upd_date 02/22/2010
> no_adrp 0
> cl_rank 0
> cl_size 0
> ....
> goal
> wd_code WD
> wd_date 02/22/2010
> wd_hrs 0.00
> last_attended_date 02/22/2010
> ....
>
> I even tried to this which works:
>
> select last_attended_date, last_attended_date + 5 fiveplus
> from stu_acad_rec
> where id=xxxxxx and sess="FA" and yr="2010">
> last_attended_date fiveplus
>
> 02/22/2010 02/27/2010
>
> 1 row(s) retrieved.
>
> Any suggestions would be welcomed.
>
> John Adamski
> Network Specialist
> Graceland University
>
>
> ***********************************************************************
> ********
> Forum Note: Use "Reply" to post a response in the discussion forum.
The problem is likely a bad key in one of the indexes that contains the
datetime column the engine is complaining about. You can try dropping all
indexes and constraints (or at least any that reference this column),
stopping auditing then try to update this row. If you can, then rebuilding
the indexes should do the trick and you can reinstate the indexes,
constraints, and auditing.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf
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 Mon, Apr 19, 2010 at 10:48 AM, John Adamski <adamski@graceland.edu>wrote:
> HPUX B.11.23 U ia64
> IDS 10.00.FC9
>
> I have a table with 91 columns about 334966 rows and we recently found 1
> row
> that can't be updated or deleted. If you try we get a 1205 error (Invalid
> month in date). There are 3 rows that are data fields and the table has an
> audit trigger on 15 of the columns which includes the 3 date fields.
>
> SQL: New Run Modify Use-editor Output Choose Save Info Drop Exit
> Modify the current SQL statements using the SQL editor.
>
> ----------------------- cars@carsitcp ---------- Press CTRL-W for Help
> --------
>
> update stu_acad_rec
> set prog="TEST"
> where id=xxxxxx
> and sess="FA" and yr=2010
>
> 1205: Invalid month in date>
> I have ran an oncheck -cI and oncheck -cD on both the 'live' and 'audit'
> tables and it finds not problems. The audit table is getting other audits
> from
> other rows.
>
> Before I go through the obstacle course needed to get IBM support (I have
> to
> go through our 3rd party provider). Is there anything else I can do? My
> assumption is data corruption of some sorts. But sure don't see it. As
> doing a
> select in dbaccess or an unload of the row the data looks good.
>
> Part of the row in question
>
> ....
> reg_upd_date 02/22/2010
> no_adrp 0
> cl_rank 0
> cl_size 0
> ....
> goal
> wd_code WD
> wd_date 02/22/2010
> wd_hrs 0.00
> last_attended_date 02/22/2010
> ....
>
> I even tried to this which works:
>
> select last_attended_date, last_attended_date + 5 fiveplus
> from stu_acad_rec
> where id=xxxxxx and sess="FA" and yr="2010">
> last_attended_date fiveplus
>
> 02/22/2010 02/27/2010
>
> 1 row(s) retrieved.
>
> Any suggestions would be welcomed.
>
> John Adamski
> Network Specialist
> Graceland University
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001636e0a5e886ead4048498f3ea
Hi John,
I had the similar situation in past and in my case, what I found out was, the
table/record in question had no issue and had no direct relationship
with the given error. I had an UPDATE trigger which calls a Stored Procedure
and within stored procedure there were couple of SQL statements.
One of them was failing due to a "bug" in my SPL code that was creating a
date/time values on the fly.
In short, please also look into other "in-direct" attached objects that are
asscociated with the table in question, trigger/SP etc.
I hope it will help you in your analysis and resolve an issue quickly.
Regards,
Dharmendra
> To: ids@iiug.org
> From: adamski@graceland.edu
> Subject: 1205 error [19725]
> Date: Mon, 19 Apr 2010 10:48:08 -0400
>
> HPUX B.11.23 U ia64
> IDS 10.00.FC9
>
> I have a table with 91 columns about 334966 rows and we recently found 1 row
> that can't be updated or deleted. If you try we get a 1205 error (Invalid
> month in date). There are 3 rows that are data fields and the table has an
> audit trigger on 15 of the columns which includes the 3 date fields.
>
> SQL: New Run Modify Use-editor Output Choose Save Info Drop Exit
> Modify the current SQL statements using the SQL editor.
>
> ----------------------- cars@carsitcp ---------- Press CTRL-W for Help
> --------
>
> update stu_acad_rec
> set prog="TEST"
> where id=xxxxxx
> and sess="FA" and yr=2010
>
> 1205: Invalid month in date>
> I have ran an oncheck -cI and oncheck -cD on both the 'live' and 'audit'
> tables and it finds not problems. The audit table is getting other audits
from
> other rows.
>
> Before I go through the obstacle course needed to get IBM support (I have to
> go through our 3rd party provider). Is there anything else I can do? My
> assumption is data corruption of some sorts. But sure don't see it. As doing
a
> select in dbaccess or an unload of the row the data looks good.
>
> Part of the row in question
>
> ....
> reg_upd_date 02/22/2010
> no_adrp 0
> cl_rank 0
> cl_size 0
> ....
> goal
> wd_code WD
> wd_date 02/22/2010
> wd_hrs 0.00
> last_attended_date 02/22/2010
> ....
>
> I even tried to this which works:
>
> select last_attended_date, last_attended_date + 5 fiveplus
> from stu_acad_rec
> where id=xxxxxx and sess="FA" and yr="2010">
> last_attended_date fiveplus
>
> 02/22/2010 02/27/2010
>
> 1 row(s) retrieved.
>
> Any suggestions would be welcomed.
>
> John Adamski
> Network Specialist
> Graceland University
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
_________________________________________________________________
The New Busy is not the too busy. Combine all your e-mail accounts with
Hotmail.
http://www.windowslive.com/campaign/thenewbusy?tile=multiaccount&ocid=PID28326::
T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_4
EEM,
Well the only triggers are the audit ones and they are standard
insert/update/delete audit triggers. But I will double check them again.
John
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Everett
Mills
Sent: Monday, April 19, 2010 10:25 AM
To: ids@iiug.org
Subject: RE: 1205 error [19728]
Any non-date fields that get converted into dates in your triggers, maybe
something like a fiscal month field? That would be my first guess given the
error you received.
--EEM
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> John Adamski
> Sent: Monday, April 19, 2010 9:48 AM
> To: ids@iiug.org
> Subject: 1205 error [19725]
>
> HPUX B.11.23 U ia64
> IDS 10.00.FC9
>
> I have a table with 91 columns about 334966 rows and we recently found
> 1 row
> that can't be updated or deleted. If you try we get a 1205 error
> (Invalid
> month in date). There are 3 rows that are data fields and the table has
> an
> audit trigger on 15 of the columns which includes the 3 date fields.
>
> SQL: New Run Modify Use-editor Output Choose Save Info Drop Exit
> Modify the current SQL statements using the SQL editor.
>
> ----------------------- cars@carsitcp ---------- Press CTRL-W for Help
> --------
>
> update stu_acad_rec
> set prog="TEST"
> where id=xxxxxx
> and sess="FA" and yr=2010
>
> 1205: Invalid month in date>
> I have ran an oncheck -cI and oncheck -cD on both the 'live' and
> 'audit'
> tables and it finds not problems. The audit table is getting other
> audits from
> other rows.
>
> Before I go through the obstacle course needed to get IBM support (I
> have to
> go through our 3rd party provider). Is there anything else I can do? My
> assumption is data corruption of some sorts. But sure don't see it. As
> doing a
> select in dbaccess or an unload of the row the data looks good.
>
> Part of the row in question
>
> ....
> reg_upd_date 02/22/2010
> no_adrp 0
> cl_rank 0
> cl_size 0
> ....
> goal
> wd_code WD
> wd_date 02/22/2010
> wd_hrs 0.00
> last_attended_date 02/22/2010
> ....
>
> I even tried to this which works:
>
> select last_attended_date, last_attended_date + 5 fiveplus
> from stu_acad_rec
> where id=xxxxxx and sess="FA" and yr="2010">
> last_attended_date fiveplus
>
> 02/22/2010 02/27/2010
>
> 1 row(s) retrieved.
>
> Any suggestions would be welcomed.
>
> John Adamski
> Network Specialist
> Graceland University
>
>
> ***********************************************************************
> ********
> Forum Note: Use "Reply" to post a response in the discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Art,
Well I'm not seeing any date fields in indexes. From a dbschema:
id integer,
prog char(4),
site char(4),
sess char(4),
yr smallint,
sess_ord smallint
default 0 not null ,
create index "informix".stuac_id on "informix".stu_acad_rec (id)
using btree ;
create index "informix".stuac_key on "informix".stu_acad_rec (prog,
sess,yr) using btree ;
create index "informix".stuac_key2 on "informix".stu_acad_rec
(yr,sess,id) using btree ;
create index "informix".stuac_key4 on "informix".stu_acad_rec
(id,prog,site,yr,sess_ord) using btree ;
create unique index "informix".stuac_prim on "informix".stu_acad_rec
(id,prog,sess,yr,site) using btree ;
For the audit table
create index "informix".gu_stu_acadrua on "informix".stu_acad_rec
(id,yr,sess,prog) using btree ;
Art, do you know of a way to identify which table ('live' or 'audit') that is
giving the 1205 error.
John
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel
Sent: Monday, April 19, 2010 10:53 AM
To: ids@iiug.org
Subject: Re: 1205 error [19730]
The problem is likely a bad key in one of the indexes that contains the
datetime column the engine is complaining about. You can try dropping all
indexes and constraints (or at least any that reference this column),
stopping auditing then try to update this row. If you can, then rebuilding
the indexes should do the trick and you can reinstate the indexes,
constraints, and auditing.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf
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 Mon, Apr 19, 2010 at 10:48 AM, John Adamski <adamski@graceland.edu>wrote:
> HPUX B.11.23 U ia64
> IDS 10.00.FC9
>
> I have a table with 91 columns about 334966 rows and we recently found 1
> row
> that can't be updated or deleted. If you try we get a 1205 error (Invalid
> month in date). There are 3 rows that are data fields and the table has an
> audit trigger on 15 of the columns which includes the 3 date fields.
>
> SQL: New Run Modify Use-editor Output Choose Save Info Drop Exit
> Modify the current SQL statements using the SQL editor.
>
> ----------------------- cars@carsitcp ---------- Press CTRL-W for Help
> --------
>
> update stu_acad_rec
> set prog="TEST"
> where id=xxxxxx
> and sess="FA" and yr=2010
>
> 1205: Invalid month in date>
> I have ran an oncheck -cI and oncheck -cD on both the 'live' and 'audit'
> tables and it finds not problems. The audit table is getting other audits
> from
> other rows.
>
> Before I go through the obstacle course needed to get IBM support (I have
> to
> go through our 3rd party provider). Is there anything else I can do? My
> assumption is data corruption of some sorts. But sure don't see it. As
> doing a
> select in dbaccess or an unload of the row the data looks good.
>
> Part of the row in question
>
> ....
> reg_upd_date 02/22/2010
> no_adrp 0
> cl_rank 0
> cl_size 0
> ....
> goal
> wd_code WD
> wd_date 02/22/2010
> wd_hrs 0.00
> last_attended_date 02/22/2010
> ....
>
> I even tried to this which works:
>
> select last_attended_date, last_attended_date + 5 fiveplus
> from stu_acad_rec
> where id=xxxxxx and sess="FA" and yr="2010">
> last_attended_date fiveplus
>
> 02/22/2010 02/27/2010
>
> 1 row(s) retrieved.
>
> Any suggestions would be welcomed.
>
> John Adamski
> Network Specialist
> Graceland University
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001636e0a5e886ead4048498f3ea
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Db access usually reports whatever table or column name string the engine
reports back with on an error. If it's not showing in the error message,
likely the engine isn't reporting that information. You can try putting a
trace on the -1205 error and see what's captured (see onmode in the
Administrator's Reference).
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf
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 Mon, Apr 19, 2010 at 1:05 PM, John Adamski <adamski@graceland.edu> wrote:
> Art,
>
> Well I'm not seeing any date fields in indexes. From a dbschema:
>
> id integer,
>
> prog char(4),
>
> site char(4),
>
> sess char(4),
>
> yr smallint,
>
> sess_ord smallint
>
> default 0 not null ,
>
> create index "informix".stuac_id on "informix".stu_acad_rec (id)
>
> using btree ;
> create index "informix".stuac_key on "informix".stu_acad_rec (prog,
>
> sess,yr) using btree ;
> create index "informix".stuac_key2 on "informix".stu_acad_rec
>
> (yr,sess,id) using btree ;
> create index "informix".stuac_key4 on "informix".stu_acad_rec
>
> (id,prog,site,yr,sess_ord) using btree ;
> create unique index "informix".stuac_prim on "informix".stu_acad_rec
>
> (id,prog,sess,yr,site) using btree ;
>
> For the audit table
>
> create index "informix".gu_stu_acadrua on "informix".stu_acad_rec
>
> (id,yr,sess,prog) using btree ;
>
> Art, do you know of a way to identify which table ('live' or 'audit') that
> is
> giving the 1205 error.
>
> John
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
> Kagel
> Sent: Monday, April 19, 2010 10:53 AM
> To: ids@iiug.org
> Subject: Re: 1205 error [19730]
>
> The problem is likely a bad key in one of the indexes that contains the
> datetime column the engine is complaining about. You can try dropping all
> indexes and constraints (or at least any that reference this column),
> stopping auditing then try to update this row. If you can, then rebuilding
> the indexes should do the trick and you can reinstate the indexes,
> constraints, and auditing.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> IIUG Board of Directors (art@iiug.org)
>
> See you at the 2010 IIUG Informix Conference
> April 25-28, 2010
> Overland Park (Kansas City), KS
> www.iiug.org/conf
>
> 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 Mon, Apr 19, 2010 at 10:48 AM, John Adamski <adamski@graceland.edu
> >wrote:
>
> > HPUX B.11.23 U ia64
> > IDS 10.00.FC9
> >
> > I have a table with 91 columns about 334966 rows and we recently found 1
> > row
> > that can't be updated or deleted. If you try we get a 1205 error (Invalid
> > month in date). There are 3 rows that are data fields and the table has
> an
> > audit trigger on 15 of the columns which includes the 3 date fields.
> >
> > SQL: New Run Modify Use-editor Output Choose Save Info Drop Exit
> > Modify the current SQL statements using the SQL editor.
> >
> > ----------------------- cars@carsitcp ---------- Press CTRL-W for Help
> > --------
> >
> > update stu_acad_rec
> > set prog="TEST"
> > where id=xxxxxx
> > and sess="FA" and yr=2010
> >
> > 1205: Invalid month in date> >
> > I have ran an oncheck -cI and oncheck -cD on both the 'live' and 'audit'
> > tables and it finds not problems. The audit table is getting other audits
> > from
> > other rows.
> >
> > Before I go through the obstacle course needed to get IBM support (I have
> > to
> > go through our 3rd party provider). Is there anything else I can do? My
> > assumption is data corruption of some sorts. But sure don't see it. As
> > doing a
> > select in dbaccess or an unload of the row the data looks good.
> >
> > Part of the row in question
> >
> > ....
> > reg_upd_date 02/22/2010
> > no_adrp 0
> > cl_rank 0
> > cl_size 0
> > ....
> > goal
> > wd_code WD
> > wd_date 02/22/2010
> > wd_hrs 0.00
> > last_attended_date 02/22/2010
> > ....
> >
> > I even tried to this which works:
> >
> > select last_attended_date, last_attended_date + 5 fiveplus
> > from stu_acad_rec
> > where id=xxxxxx and sess="FA" and yr="2010"> >
> > last_attended_date fiveplus
> >
> > 02/22/2010 02/27/2010
> >
> > 1 row(s) retrieved.
> >
> > Any suggestions would be welcomed.
> >
> > John Adamski
> > Network Specialist
> > Graceland University
> >
> >
> >
> >
>
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --001636e0a5e886ead4048498f3ea
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--000e0cd32ef67f94e604849a0de2
Hello.
You could enable a trace on the error (I always use it here, but I´m on
11.50 version).
Try to start it with:
onmode -I 1205and monitor your online.log file, until it raises the AF file.
Then remember to trace it of, using
onmode -I
The af file always generates usefull informations.
I hope it works on IDS 10.X version.
Best regards.
Alexandre Marini
Tecnologia da Informação - DBA
msn: alexandre_marini@hotmail.com
SEFAZ-MS / SGI-UIMP / Sistemas IBM-Informix
See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf
<http://www.iiug.org>;
John Adamski escreveu:
> Art,
>
> Well I'm not seeing any date fields in indexes. From a dbschema:
>
> id integer,
>
> prog char(4),
>
> site char(4),
>
> sess char(4),
>
> yr smallint,
>
> sess_ord smallint
>
> default 0 not null ,
>
> create index "informix".stuac_id on "informix".stu_acad_rec (id)
>
> using btree ;
> create index "informix".stuac_key on "informix".stu_acad_rec (prog,
>
> sess,yr) using btree ;
> create index "informix".stuac_key2 on "informix".stu_acad_rec
>
> (yr,sess,id) using btree ;
> create index "informix".stuac_key4 on "informix".stu_acad_rec
>
> (id,prog,site,yr,sess_ord) using btree ;
> create unique index "informix".stuac_prim on "informix".stu_acad_rec
>
> (id,prog,sess,yr,site) using btree ;
>
> For the audit table
>
> create index "informix".gu_stu_acadrua on "informix".stu_acad_rec
>
> (id,yr,sess,prog) using btree ;
>
> Art, do you know of a way to identify which table ('live' or 'audit') that is
> giving the 1205 error.
>
> John
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
> Kagel
> Sent: Monday, April 19, 2010 10:53 AM
> To: ids@iiug.org
> Subject: Re: 1205 error [19730]
>
> The problem is likely a bad key in one of the indexes that contains the
> datetime column the engine is complaining about. You can try dropping all
> indexes and constraints (or at least any that reference this column),
> stopping auditing then try to update this row. If you can, then rebuilding
> the indexes should do the trick and you can reinstate the indexes,
> constraints, and auditing.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> IIUG Board of Directors (art@iiug.org)
>
> See you at the 2010 IIUG Informix Conference
> April 25-28, 2010
> Overland Park (Kansas City), KS
> www.iiug.org/conf
>
> 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 Mon, Apr 19, 2010 at 10:48 AM, John Adamski <adamski@graceland.edu>wrote:
>
>
>> HPUX B.11.23 U ia64
>> IDS 10.00.FC9
>>
>> I have a table with 91 columns about 334966 rows and we recently found 1
>> row
>> that can't be updated or deleted. If you try we get a 1205 error (Invalid
>> month in date). There are 3 rows that are data fields and the table has an
>> audit trigger on 15 of the columns which includes the 3 date fields.
>>
>> SQL: New Run Modify Use-editor Output Choose Save Info Drop Exit
>> Modify the current SQL statements using the SQL editor.
>>
>> ----------------------- cars@carsitcp ---------- Press CTRL-W for Help
>> --------
>>
>> update stu_acad_rec
>> set prog="TEST"
>> where id=xxxxxx
>> and sess="FA" and yr=2010
>>
>> 1205: Invalid month in date>>
>> I have ran an oncheck -cI and oncheck -cD on both the 'live' and 'audit'
>> tables and it finds not problems. The audit table is getting other audits
>> from
>> other rows.
>>
>> Before I go through the obstacle course needed to get IBM support (I have
>> to
>> go through our 3rd party provider). Is there anything else I can do? My
>> assumption is data corruption of some sorts. But sure don't see it. As
>> doing a
>> select in dbaccess or an unload of the row the data looks good.
>>
>> Part of the row in question
>>
>> ....
>> reg_upd_date 02/22/2010
>> no_adrp 0
>> cl_rank 0
>> cl_size 0
>> ....
>> goal
>> wd_code WD
>> wd_date 02/22/2010
>> wd_hrs 0.00
>> last_attended_date 02/22/2010
>> ....
>>
>> I even tried to this which works:
>>
>> select last_attended_date, last_attended_date + 5 fiveplus
>> from stu_acad_rec
>> where id=xxxxxx and sess="FA" and yr="2010">>
>> last_attended_date fiveplus
>>
>> 02/22/2010 02/27/2010
>>
>> 1 row(s) retrieved.
>>
>> Any suggestions would be welcomed.
>>
>> John Adamski
>> Network Specialist
>> Graceland University
>>
>>
>>
>>
>>
>
>
*******************************************************************************
>
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>>
>>
>
> --001636e0a5e886ead4048498f3ea
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
Also, it looks like the record in question is from Feb month. Please make sure
your code is not creating any calcuated date
for Feb 2010 that either ending as value "02/29/2010" or "02/30/2010" or
"02/31/2010"...As you know, all three dates are
invalid as the Feb' 2010 last date is 02/28/2010. (Just a wild guess as I saw
the Fed month on a record).
Dharmendra
From: dharmendrasharma@hotmail.com
To: ids@iiug.org
Subject: RE: 1205 error [19725]
Date: Mon, 19 Apr 2010 12:13:17 -0400
Hi John,
I had the similar situation in past and in my case, what I found out was, the
table/record in question had no issue and had no direct relationship
with the given error. I had an UPDATE trigger which calls a Stored Procedure
and within stored procedure there were couple of SQL statements.
One of them was failing due to a "bug" in my SPL code that was creating a
date/time values on the fly.
In short, please also look into other "in-direct" attached objects that are
asscociated with the table in question, trigger/SP etc.
I hope it will help you in your analysis and resolve an issue quickly.
Regards,
Dharmendra
> To: ids@iiug.org
> From: adamski@graceland.edu
> Subject: 1205 error [19725]
> Date: Mon, 19 Apr 2010 10:48:08 -0400
>
> HPUX B.11.23 U ia64
> IDS 10.00.FC9
>
> I have a table with 91 columns about 334966 rows and we recently found 1 row
> that can't be updated or deleted. If you try we get a 1205 error (Invalid
> month in date). There are 3 rows that are data fields and the table has an
> audit trigger on 15 of the columns which includes the 3 date fields.
>
> SQL: New Run Modify Use-editor Output Choose Save Info Drop Exit
> Modify the current SQL statements using the SQL editor.
>
> ----------------------- cars@carsitcp ---------- Press CTRL-W for Help
> --------
>
> update stu_acad_rec
> set prog="TEST"
> where id=xxxxxx
> and sess="FA" and yr=2010
>
> 1205: Invalid month in date>
> I have ran an oncheck -cI and oncheck -cD on both the 'live' and 'audit'
> tables and it finds not problems. The audit table is getting other audits
from
> other rows.
>
> Before I go through the obstacle course needed to get IBM support (I have to
> go through our 3rd party provider). Is there anything else I can do? My
> assumption is data corruption of some sorts. But sure don't see it. As doing
a
> select in dbaccess or an unload of the row the data looks good.
>
> Part of the row in question
>
> ....
> reg_upd_date 02/22/2010
> no_adrp 0
> cl_rank 0
> cl_size 0
> ....
> goal
> wd_code WD
> wd_date 02/22/2010
> wd_hrs 0.00
> last_attended_date 02/22/2010
> ....
>
> I even tried to this which works:
>
> select last_attended_date, last_attended_date + 5 fiveplus
> from stu_acad_rec
> where id=xxxxxx and sess="FA" and yr="2010">
> last_attended_date fiveplus
>
> 02/22/2010 02/27/2010
>
> 1 row(s) retrieved.
>
> Any suggestions would be welcomed.
>
> John Adamski
> Network Specialist
> Graceland University
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
The New Busy is not the too busy. Combine all your e-mail accounts with
Hotmail. Get busy.
_________________________________________________________________
The New Busy is not the too busy. Combine all your e-mail accounts with
Hotmail.
http://www.windowslive.com/campaign/thenewbusy?tile=multiaccount&ocid=PID28326::
T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_4
Art & Alexandre,
I did the onmode -I 1205, like you suggested and boy does that give you a lot
of data to look over in the af file.
From what I understand of the af files it looks like it is the 'live' db
(cars). I was hoping for the 'audit' table as I have more flexibility with
that, like drop/add indexes.
This is from the af file - which I think is saying it is the cars or 'live' db
that is having the 1205 error. I couldn't find any reference to our audit db
(cars_audit) in the af file.
I will request down time for the system to drop/re-add indexes to see if that
resolves the problem. I might also drop the audit triggers to see if that
makes any difference first.
/opt/informix/bin/onstat -g ses 45066:
IBM Informix Dynamic Server Version 10.00.FC9 -- On-Line -- Up 3 days 06:12:05
-- 1056768 Kbytes
session #RSAM total used dynamic
id user tty pid hostname threads memory memory explain
45066 adamski 1 9295 zoom 1 151552 146768 off
tid name rstcb flags curstk status
55594 sqlexec c000000033399068 --BP--- 726642144 running
Memory pools count 2
name class addr totalsize freesize #allocfrag #freefrag
45066 V c000000039563040 147456 3952 220 7
45066*O0 V c0000000390b9040 4096 832 1 1
name free used name free used
overhead 0 6528 mtmisc 0 96
scb 0 176 opentable 0 8672
filetable 0 2192 ru 0 608
log 0 16528 temprec 0 10128
keys 0 5120 ralloc 0 53616
gentcb 0 1680 ostcb 0 4080
sqscb 0 21440 sql 0 80
rdahead 0 256 hashfiletab 0 560
osenv 0 3648 sqtcb 0 6208
fragman 0 5152
sqscb info
scb sqscb optofc pdqpriority sqlstats optcompind directives
c0000000391af110 c000000039a12030 0 0 0 0 1
Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers Explain
45066 UPDATE cars CR Not Wait -1205 0 9.24 Off
Current SQL statement :
update stu_acad_rec set prog="TEST" where id=xxxxxx and sess="FA" and
yr=2010
Dharmendra,
Good idea,, but no coding I can find doing anything that could generate a bad
February date. And, I just been informed we have two other rows that have the
same problem. one has January dates and the other was from 2 days ago.
John
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
dharmendra sharma
Sent: Monday, April 19, 2010 12:20 PM
To: ids@iiug.org
Subject: RE: 1205 error [19736]
Also, it looks like the record in question is from Feb month. Please make sure
your code is not creating any calcuated date
for Feb 2010 that either ending as value "02/29/2010" or "02/30/2010" or
"02/31/2010"...As you know, all three dates are
invalid as the Feb' 2010 last date is 02/28/2010. (Just a wild guess as I saw
the Fed month on a record).
Dharmendra
From: dharmendrasharma@hotmail.com
To: ids@iiug.org
Subject: RE: 1205 error [19725]
Date: Mon, 19 Apr 2010 12:13:17 -0400
Hi John,
I had the similar situation in past and in my case, what I found out was, the
table/record in question had no issue and had no direct relationship
with the given error. I had an UPDATE trigger which calls a Stored Procedure
and within stored procedure there were couple of SQL statements.
One of them was failing due to a "bug" in my SPL code that was creating a
date/time values on the fly.
In short, please also look into other "in-direct" attached objects that are
asscociated with the table in question, trigger/SP etc.
I hope it will help you in your analysis and resolve an issue quickly.
Regards,
Dharmendra
> To: ids@iiug.org
> From: adamski@graceland.edu
> Subject: 1205 error [19725]
> Date: Mon, 19 Apr 2010 10:48:08 -0400
>
> HPUX B.11.23 U ia64
> IDS 10.00.FC9
>
> I have a table with 91 columns about 334966 rows and we recently found 1 row
> that can't be updated or deleted. If you try we get a 1205 error (Invalid
> month in date). There are 3 rows that are data fields and the table has an
> audit trigger on 15 of the columns which includes the 3 date fields.
>
> SQL: New Run Modify Use-editor Output Choose Save Info Drop Exit
> Modify the current SQL statements using the SQL editor.
>
> ----------------------- cars@carsitcp ---------- Press CTRL-W for Help
> --------
>
> update stu_acad_rec
> set prog="TEST"
> where id=xxxxxx
> and sess="FA" and yr=2010
>
> 1205: Invalid month in date>
> I have ran an oncheck -cI and oncheck -cD on both the 'live' and 'audit'
> tables and it finds not problems. The audit table is getting other audits
from
> other rows.
>
> Before I go through the obstacle course needed to get IBM support (I have to
> go through our 3rd party provider). Is there anything else I can do? My
> assumption is data corruption of some sorts. But sure don't see it. As doing
a
> select in dbaccess or an unload of the row the data looks good.
>
> Part of the row in question
>
> ....
> reg_upd_date 02/22/2010
> no_adrp 0
> cl_rank 0
> cl_size 0
> ....
> goal
> wd_code WD
> wd_date 02/22/2010
> wd_hrs 0.00
> last_attended_date 02/22/2010
> ....
>
> I even tried to this which works:
>
> select last_attended_date, last_attended_date + 5 fiveplus
> from stu_acad_rec
> where id=xxxxxx and sess="FA" and yr="2010">
> last_attended_date fiveplus
>
> 02/22/2010 02/27/2010
>
> 1 row(s) retrieved.
>
> Any suggestions would be welcomed.
>
> John Adamski
> Network Specialist
> Graceland University
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
The New Busy is not the too busy. Combine all your e-mail accounts with
Hotmail. Get busy.
_________________________________________________________________
The New Busy is not the too busy. Combine all your e-mail accounts with
Hotmail.
http://www.windowslive.com/campaign/thenewbusy?tile=multiaccount&ocid=PID28326::
T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_4
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Alexandre & Art were right as I manually dropped the 3 triggers and then could
update at least one of the records in question. Once I get management approval
will run the 3rd-party Make process to put the triggers back and see if we get
the problem again. My guess we will.
Thanks everyone for help and suggestions.
John
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Alexandre Marini
Sent: Monday, April 19, 2010 12:14 PM
To: ids@iiug.org
Subject: Re: 1205 error [19735]
Hello.
You could enable a trace on the error (I always use it here, but I´m on
11.50 version).
Try to start it with:
onmode -I 1205and monitor your online.log file, until it raises the AF file.
Then remember to trace it of, using
onmode -I
The af file always generates usefull informations.
I hope it works on IDS 10.X version.
Best regards.
Alexandre Marini
Tecnologia da Informação - DBA
msn: alexandre_marini@hotmail.com
SEFAZ-MS / SGI-UIMP / Sistemas IBM-Informix
See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf
<http://www.iiug.org>;
John Adamski escreveu:
> Art,
>
> Well I'm not seeing any date fields in indexes. From a dbschema:
>
> id integer,
>
> prog char(4),
>
> site char(4),
>
> sess char(4),
>
> yr smallint,
>
> sess_ord smallint
>
> default 0 not null ,
>
> create index "informix".stuac_id on "informix".stu_acad_rec (id)
>
> using btree ;
> create index "informix".stuac_key on "informix".stu_acad_rec (prog,
>
> sess,yr) using btree ;
> create index "informix".stuac_key2 on "informix".stu_acad_rec
>
> (yr,sess,id) using btree ;
> create index "informix".stuac_key4 on "informix".stu_acad_rec
>
> (id,prog,site,yr,sess_ord) using btree ;
> create unique index "informix".stuac_prim on "informix".stu_acad_rec
>
> (id,prog,sess,yr,site) using btree ;
>
> For the audit table
>
> create index "informix".gu_stu_acadrua on "informix".stu_acad_rec
>
> (id,yr,sess,prog) using btree ;
>
> Art, do you know of a way to identify which table ('live' or 'audit') that
is
> giving the 1205 error.
>
> John
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
> Kagel
> Sent: Monday, April 19, 2010 10:53 AM
> To: ids@iiug.org
> Subject: Re: 1205 error [19730]
>
> The problem is likely a bad key in one of the indexes that contains the
> datetime column the engine is complaining about. You can try dropping all
> indexes and constraints (or at least any that reference this column),
> stopping auditing then try to update this row. If you can, then rebuilding
> the indexes should do the trick and you can reinstate the indexes,
> constraints, and auditing.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> IIUG Board of Directors (art@iiug.org)
>
> See you at the 2010 IIUG Informix Conference
> April 25-28, 2010
> Overland Park (Kansas City), KS
> www.iiug.org/conf
>
> 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 Mon, Apr 19, 2010 at 10:48 AM, John Adamski <adamski@graceland.edu>wrote:
>
>
>> HPUX B.11.23 U ia64
>> IDS 10.00.FC9
>>
>> I have a table with 91 columns about 334966 rows and we recently found 1
>> row
>> that can't be updated or deleted. If you try we get a 1205 error (Invalid
>> month in date). There are 3 rows that are data fields and the table has an
>> audit trigger on 15 of the columns which includes the 3 date fields.
>>
>> SQL: New Run Modify Use-editor Output Choose Save Info Drop Exit
>> Modify the current SQL statements using the SQL editor.
>>
>> ----------------------- cars@carsitcp ---------- Press CTRL-W for Help
>> --------
>>
>> update stu_acad_rec
>> set prog="TEST"
>> where id=xxxxxx
>> and sess="FA" and yr=2010
>>
>> 1205: Invalid month in date>>
>> I have ran an oncheck -cI and oncheck -cD on both the 'live' and 'audit'
>> tables and it finds not problems. The audit table is getting other audits
>> from
>> other rows.
>>
>> Before I go through the obstacle course needed to get IBM support (I have
>> to
>> go through our 3rd party provider). Is there anything else I can do? My
>> assumption is data corruption of some sorts. But sure don't see it. As
>> doing a
>> select in dbaccess or an unload of the row the data looks good.
>>
>> Part of the row in question
>>
>> ....
>> reg_upd_date 02/22/2010
>> no_adrp 0
>> cl_rank 0
>> cl_size 0
>> ....
>> goal
>> wd_code WD
>> wd_date 02/22/2010
>> wd_hrs 0.00
>> last_attended_date 02/22/2010
>> ....
>>
>> I even tried to this which works:
>>
>> select last_attended_date, last_attended_date + 5 fiveplus
>> from stu_acad_rec
>> where id=xxxxxx and sess="FA" and yr="2010">>
>> last_attended_date fiveplus
>>
>> 02/22/2010 02/27/2010
>>
>> 1 row(s) retrieved.
>>
>> Any suggestions would be welcomed.
>>
>> John Adamski
>> Network Specialist
>> Graceland University
>>
>>
>>
>>
>>
>
>
*******************************************************************************
>
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>>
>>
>
> --001636e0a5e886ead4048498f3ea
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.