Hi all,



Here's one to distract you on a Friday. I discovered corrupt data in
decimal fields when I used to unloaddb to copy production to test. New
records inserted are corrupt. Old data appears intact. I suspect the
problem is due to incorrectly compiled OpenROAD code but haven't been able
to prove this. It may well be that the bug is already fixed and I need to
go back to an older image to reproduce the problem. The users have not been
reporting errors in the application and copying data is error free.
OpenROAD displays the corrupt data but reports no error - see the attached
pic. There is an inconsistency somewhere and Ingres is not picking up.



Before I embark on the data fix, I'm interested in suggestions for further
debug.



Here, asset 13 is the new data.

* select * from ar_tax_depr_schedule

Executing . . .




....







unload from prod

* copy ar_tax_depr_schedule () into './ar_tax_depr_schedule.ingres'

Executing . . .

(104166 rows)



load to test

* copy ar_tax_depr_schedule () from './ar_tax_depr_schedule.ingres'

with allocation = 4, row_estimate = 104153

E_CO0032 COPY: Bad data value for column 'opening_value' encountered while
processing row 1.

E_CO002B COPY: 1 rows could not be processed.

E_CO002A COPY: Copy has been aborted.

- Terminated by Errors





Similar problem when doing a character unload/reload

* * copy ar_tax_depr_schedule(

asset_code= varchar(0)tab,

cost_year= c0tab,

cost_period= c0tab,

opening_value= varchar(0)tab,

tax_depreciation= varchar(0)tab,

closing_value= varchar(0)tab,

rolled= varchar(0)nl,

nl= d0nl)

into './ar_tax_depr_schedule.ingres'

Executing . . .

(104166 rows)



* * copy ar_tax_depr_schedule(

asset_code= varchar(0)tab,

cost_year= c0tab,

cost_period= c0tab,

opening_value= varchar(0)tab,

tax_depreciation= varchar(0)tab,

closing_value= varchar(0)tab,

rolled= varchar(0)nl,

nl= d0nl)

from './ar_tax_depr_schedule.ingres'

with allocation = 4,

row_estimate = 104166

Executing . . .



E_CO0039 COPY: Error processing row 1. Cannot convert column

'opening_value' to tuple format.



E_US100F String cannot be converted to numeric due to incorrect syntax.

E_CO002B COPY: 1 rows could not be processed.

E_CO002A COPY: Copy has been aborted.





auditdb shows mostly corrupt data going in, but there is an occasional good
one.

45824-Begin : Transaction Id 00004ced4ea132c2 19-Jul-2011 12:15:39.70
Username ingres

45825: Insert/Append : Transaction Id 00004ced4ea132c2 Id (3157,0)
Table [ar_tax_depr_schedule,ingres]

45826- Record: <15|2011|9|=<<.>4|:643;=?4?756.04|?1283<02:9:<.>4| >

45827: Insert/Append : Transaction Id 00004ced4ea132c2 Id (3157,0)
Table [ar_tax_depr_schedule,ingres]

45828- Record: <15|2011|12|?1283<02:9:<.>4|;07.84|?1283<0::=4<.=4 |>

45829: Insert/Append : Transaction Id 00004ced4ea132c2 Id (3157,0)
Table [ar_tax_depr_schedule,ingres]

45830- Record: <15|2012|3|?1283<0::=4<.=4|;07.84|?1283<02:0?<.=4| >

...

47290-Begin : Transaction Id 00004ced4ea14e51 19-Jul-2011 13:39:00.09
Username ingres

47291: Insert/Append : Transaction Id 00004ced4ea14e51 Id (3157,0)
Table [ar_tax_depr_schedule,ingres]

47292- Record: <27|2011|12|>89.<4|245;6?3?==42.>4|593180456::9.<4 |>

47293: Insert/Append : Transaction Id 00004ced4ea14e51 Id (3157,0)
Table [ar_tax_depr_schedule,ingres]

47294- Record: <27|2012|3|593180456::9.<4|204.74|593180456?19.;4| >

47295: Insert/Append : Transaction Id 00004ced4ea14e51 Id (3157,0)
Table [ar_tax_depr_schedule,ingres]

47296- Record: <27|2012|6|593180456?19.;4|204.74|593180456389.;4| >

...

47896-Begin : Transaction Id 00004ced4ea160c1 19-Jul-2011 14:08:09.80
Username ingres

47897: Insert/Append : Transaction Id 00004ced4ea160c1 Id (3157,0)
Table [ar_tax_depr_schedule,ingres]

47898- Record: <34|2011|12|809.64|2.84|509.64|>

47899: Insert/Append : Transaction Id 00004ced4ea160c1 Id (3157,0)
Table [ar_tax_depr_schedule,ingres]

47900- Record: <34|2012|3|509.64|4.24|<09.54|>

47901: Insert/Append : Transaction Id 00004ced4ea160c1 Id (3157,0)
Table [ar_tax_depr_schedule,ingres]

47902- Record: <34|2012|6|<09.54|4.24|309.54|>

...





There was a schema change prior to 19 July, unfortunately journals for the
event are gone. Float fields were changed to decimal(14,2). A similar
depreciation table was changed in the same way but does not have these
problems. OpenROAD user class and procedures were changed also, but there is
currently an inconsistency in the code. In one area the variables are
decimal(14,3) but another has decimal(14,2). There is a code fix pending.



I can't reproduce the problem on an identically defined table in the test
database on the same machine, but I'm not yet sure what sequence of steps in
the application is generating the corrupt data or if I am testing with the
right version of the application image.



I can't see anything unusual in iicolumns. Help table and copydb generate
normal looking schema.



OpenROAD 5.0

Ingres 9.2.1 P13824



Inserting using interactive SQL works ok.

insert into ar_tax_depr_schedule values (' 001', 2011,1,123,456,789,'')

...

insert into ar_tax_depr_schedule values (' 001',
2011,6,12345678,45678901,78901234,'')

select * from ar_tax_depr_schedule

Executing . . .

+------------------+-------------+-------------+----------------+-----------
-----+----------------+------+

+------------------+-------------+-------------+----------------+-----------
-----+----------------+------+














Copying to another identical table copies the corrupt data without errors.

* insert into pw_decimals select * from ar_tax_depr_schedule

Executing . . .

(104589 rows)

continue

* select * from pw_decimals

Executing . . .

+------------------+-------------+-------------+----------------+-----------
-----+----------------+------+

+------------------+-------------+-------------+----------------+-----------
-----+----------------+------+












...














Copying to a table defined with floats looks like it worked but those
numbers are wrong

* insert into pw_floats select * from ar_tax_depr_schedule

Executing . . .

(104589 rows)

continue

* select * from pw_floats

Executing . . .

+------------------+-------------+-------------+-----------+-----------+----
-------+------+

+------------------+-------------+-------------+-----------+-----------+----
-------+------+












...









...





I think can restore the corrupt records by reloading into a table defined
with float fields. Then I plan to extract the good data from the bad,
hopefully leaving out the ugly.



* copy pw_float() from 'ar_tax_depr_schedule.ingres'

Executing . . .

(104583 rows)

continue

* select * from pw_float

Executing . . .

+------------------+-------------+-------------+-----------+-----------+----
-------+------+

+------------------+-------------+-------------+-----------+-----------+----
-------+------+









...














With depreciation like that, no wonder the accountant is cranky. J



Before I rebuild the table, I thought I should collect some more evidence
for Ingres Support and try out suggestions from the experts. I plan to play
this weekend.





Paul White

&

Shift Seven Solutions