Re: Why the data changes it's value by itself!

Hello,

We been having a problem lately with some data changing their value without modifying them.
We used to backups to see the difference:
this was the first backup
INSERT INTO journal VALUES(28382,698,754,7116,7,0.01,'20060606','15415773',1,3,4,1,1,5,77,1,17,2,1,1,15,1,1,2,0,32252,0,14183,0,0,0,'A1',0,0);
INSERT INTO journal VALUES(28383,698,3201,26018,7,21.74,'20060606','15415773',1,3,4,1,1,7,175,1,33,2,1,1,15,1,1,1,0,71451,1,14183,0,0,0,'P1',0,0);
INSERT INTO journal VALUES(28384,698,3201,26018,9,1.52,'20060606','15415773',1,3,4,1,1,7,175,1,33,2,1,1,15,1,1,1,0,71451,0,14183,0,0,0,'T11',0,0);
INSERT INTO journal VALUES(28385,698,3201,26018,10,1.74,'20060606','15415773',1,3,4,1,1,7,175,1,33,2,1,1,15,1,1,1,0,71451,0,14183,0,0,0,'T12',0,0);

and the second backup

INSERT INTO jnlsale VALUES(28382,698,754,7116,7,0.01,'2000606','1541577',1,3,4,1,1,5,77,1,17,2,1,1,15,1,1,2,0,32252,0,14183,0,0,0,'A1',0,0);
INSERT INTO jnlsale VALUES(28383,698,3201,26018,7,1.20932764209866e-307,'20060606','15415773',1,3,4,1,1,7,175,1,33,2,1,1,15,1,1,1,0,71451,1,14183,0,0,0,'P1',0,0);
INSERT INTO jnlsale VALUES(28384,698,3201,26018,9,1.52,'2000606','1541577',1,3,4,1,1,7,175,1,33,2,1,1,15,1,1,1,0,71451,0,14183,0,0,0,'T11',0,0);
INSERT INTO jnlsale VALUES(28385,698,3201,26018,10,1.29775128722884e-154,'20060606','15415773',1,3,4,1,1,7,175,1,33,2,1,1,15,1,1,1,0,71451,0,14183,0,0,0,'T12',0,0);

The values that change are: '20060606' to '2000606'
21.74 to 1.20932764209866e-307
etc

Thanks in advance,

Version used PostgreSQl 8.1.1 on Windows box with Windows 2000 server

Ardian Xharra wrote:
> Hello,
>
> We been having a problem lately with some data changing their value without modifying them.
> We used to backups to see the difference:
> this was the first backup
> INSERT INTO journal VALUES

> and the second backup
>
> INSERT INTO jnlsale VALUES

These are different tables. Do you have an example which shows data
changing?
Does it change in the live database if you select data out?

> Version used PostgreSQl 8.1.1 on Windows box with Windows 2000 server

Upgrade to 8.1.5 as soon as is convenient.

--
Richard Huxton
Archonet Ltd

No, sorry about the name of the table it's the same table.
Yes, the change is in live database during a select

Regards, Ardian

----- Original Message -----
From: "Richard Huxton" <dev(at)archonet(dot)com>
To: "Ardian Xharra" <axharra(at)boxxo(dot)info>
Cc: "postgreSQL postgreSQL" <pgsql-general(at)postgresql(dot)org>
Sent: Thursday, November 16, 2006 12:30 PM
Subject: Re: [GENERAL] Why the data changes it's value by itself!

> Ardian Xharra wrote:
>> Hello,
>>
>> We been having a problem lately with some data changing their value
>> without modifying them.
>> We used to backups to see the difference:
>> this was the first backup
>> INSERT INTO journal VALUES
>
>> and the second backup
>>
>> INSERT INTO jnlsale VALUES
>
> These are different tables. Do you have an example which shows data
> changing?
> Does it change in the live database if you select data out?
>
>
>> Version used PostgreSQl 8.1.1 on Windows box with Windows 2000 server
>
> Upgrade to 8.1.5 as soon as is convenient.
>
> --
> Richard Huxton
> Archonet Ltd
>
>
>
> --
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.409 / Virus Database: 268.14.6/535 - Release Date: 15/11/2006
>
>

Ardian Xharra wrote:
> No, sorry about the name of the table it's the same table.
> Yes, the change is in live database during a select

OK, so it's definitely a data change. Can you get the numeric value of
the character in question, and compare it to the original value
SELECT ascii(substring(my_column,5,1)) WHERE ...;
SELECT ascii('6');
You might want an index other than 5 of course.

What I'm wondering is whether this is a single-bit error that has
occurred. If it is, I'd suspect hardware problems.

--
Richard Huxton
Archonet Ltd

For the column: date(varchar 8) '2000606' the
SELECT ascii(substring(date,4,1)) from jnlsale WHERE id_jnlsale=28384
gives: 22
SELECT ascii('6'); gives 54

For the column: amount(float 8) 1.20932764209866e-307 the
SELECT ascii(substring(amount,20,1)) from jnlsale WHERE id_jnlsale=28382
gives: 48

regards Ardian
----- Original Message -----
From: "Richard Huxton" <dev(at)archonet(dot)com>
To: "Ardian Xharra" <axharra(at)boxxo(dot)info>
Cc: "postgreSQL postgreSQL" <pgsql-general(at)postgresql(dot)org>
Sent: Thursday, November 16, 2006 1:47 PM
Subject: Re: [GENERAL] Why the data changes it's value by itself!

> Ardian Xharra wrote:
>> No, sorry about the name of the table it's the same table.
>> Yes, the change is in live database during a select
>
> OK, so it's definitely a data change. Can you get the numeric value of the
> character in question, and compare it to the original value
> SELECT ascii(substring(my_column,5,1)) WHERE ...;
> SELECT ascii('6');
> You might want an index other than 5 of course.
>
> What I'm wondering is whether this is a single-bit error that has
> occurred. If it is, I'd suspect hardware problems.
>
> --
> Richard Huxton
> Archonet Ltd
>
>
>
> --
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.409 / Virus Database: 268.14.6/535 - Release Date: 15/11/2006
>
>

Ardian Xharra wrote:
> For the column: date(varchar 8) '2000606' the
> SELECT ascii(substring(date,4,1)) from jnlsale WHERE id_jnlsale=28384
> gives: 22
> SELECT ascii('6'); gives 54

Hmm - a difference of 32, or a single bit (2^5).

> For the column: amount(float 8) 1.20932764209866e-307 the
> SELECT ascii(substring(amount,20,1)) from jnlsale WHERE id_jnlsale=28382
> gives: 48

Sorry - this only applies to the text. I'd need to figure out the
internal format of the float8 and see what happened.

Well, if all the textual differences are single-bit (1,2,4,8,16,32...)
I'd suspect hardware. I can't imagine the kind of bug in PostgreSQL that
would cause that kind of error.

I can imagine a faulty RAM chip or slowly failing hard-disk causing that
kind of error though. I'd find a Linux live CD that supports memtest86
(or one of its spin-offs) and leave it testing your RAM for 24 hours or so.

--
Richard Huxton
Archonet Ltd

Does this message in application.log can be the source of problem

wuaueng.dll (1204) Synchronous read page checksum error -1018 ((1:801 1:801)
(0-13218) (0-14642)) occurred. Please restore the databases from a previous
backup.

Regards Ardian,

----- Original Message -----
From: "Richard Huxton" <dev(at)archonet(dot)com>
To: "Ardian Xharra" <axharra(at)boxxo(dot)info>
Cc: "postgreSQL postgreSQL" <pgsql-general(at)postgresql(dot)org>
Sent: Friday, November 17, 2006 5:44 AM
Subject: Re: [GENERAL] Why the data changes it's value by itself!

> Ardian Xharra wrote:
>> For the column: date(varchar 8) '2000606' the
>> SELECT ascii(substring(date,4,1)) from jnlsale WHERE id_jnlsale=28384
>> gives: 22
>> SELECT ascii('6'); gives 54
>
> Hmm - a difference of 32, or a single bit (2^5).
>
>> For the column: amount(float 8) 1.20932764209866e-307 the
>> SELECT ascii(substring(amount,20,1)) from jnlsale WHERE id_jnlsale=28382
>> gives: 48
>
> Sorry - this only applies to the text. I'd need to figure out the internal
> format of the float8 and see what happened.
>
> Well, if all the textual differences are single-bit (1,2,4,8,16,32...) I'd
> suspect hardware. I can't imagine the kind of bug in PostgreSQL that would
> cause that kind of error.
>
> I can imagine a faulty RAM chip or slowly failing hard-disk causing that
> kind of error though. I'd find a Linux live CD that supports memtest86 (or
> one of its spin-offs) and leave it testing your RAM for 24 hours or so.
>
> --
> Richard Huxton
> Archonet Ltd
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
>
>
> --
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.409 / Virus Database: 268.14.6/536 - Release Date: 16/11/2006
>
>

Ardian Xharra wrote:
> Does this message in application.log can be the source of problem
>
> wuaueng.dll (1204) Synchronous read page checksum error -1018 ((1:801
> 1:801) (0-13218) (0-14642)) occurred. Please restore the databases from
> a previous backup.

I don't recognise this as a PostgreSQL error, although it could be part
of the Windows code I suppose.

Hmm... Googling a little, that appears to be an MS-Exchange error
http://support.microsoft.com/kb/314917

Looks like you might have problems with your disk hardware (or RAM or
motherboard, or drivers...)

Run some extensive hardware tests. If you can afford to, just replace
the machine. You'll be looking at a couple of days work to be sure
you've fixed whatever is causing this.

--
Richard Huxton
Archonet Ltd

> > Does this message in application.log can be the source of problem
> >
> > wuaueng.dll (1204) Synchronous read page checksum error
> -1018 ((1:801
> > 1:801) (0-13218) (0-14642)) occurred. Please restore the databases
> > from a previous backup.
>
> I don't recognise this as a PostgreSQL error, although it
> could be part of the Windows code I suppose.

wuaueng.dll sounds like the Windows Update engine. There should be a
bunch of articles around on how to reset the windows update database -
that should be your first try.

(It uses the same general database engine as Exchange, which is why you
get the same kind of error msg)

//Magnus

Yes, the problem was the memory (testing with memtest)
I took us long to have the machine check

Thanks,

----- Original Message -----
From: "Richard Huxton" <dev(at)archonet(dot)com>
To: "Ardian Xharra" <axharra(at)boxxo(dot)info>
Cc: "postgreSQL postgreSQL" <pgsql-general(at)postgresql(dot)org>
Sent: Friday, November 17, 2006 1:12 PM
Subject: Re: [GENERAL] Why the data changes it's value by itself!

> Ardian Xharra wrote:
>> Does this message in application.log can be the source of problem
>>
>> wuaueng.dll (1204) Synchronous read page checksum error -1018 ((1:801
>> 1:801) (0-13218) (0-14642)) occurred. Please restore the databases from a
>> previous backup.
>
> I don't recognise this as a PostgreSQL error, although it could be part of
> the Windows code I suppose.
>
> Hmm... Googling a little, that appears to be an MS-Exchange error
> http://support.microsoft.com/kb/314917
>
> Looks like you might have problems with your disk hardware (or RAM or
> motherboard, or drivers...)
>
> Run some extensive hardware tests. If you can afford to, just replace the
> machine. You'll be looking at a couple of days work to be sure you've
> fixed whatever is causing this.
>
> --
> Richard Huxton
> Archonet Ltd
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org/
>
>
>
> --
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.409 / Virus Database: 268.14.6/536 - Release Date: 16/11/2006
>
>