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, 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@archonet.com>
To: "Ardian Xharra" <axharra@boxxo.info>
Cc: "postgreSQL postgreSQL" <pgsql-general@postgresql.org>
Sent: Thursday, November 16, 2006 12:30 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

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@archonet.com>
To: "Ardian Xharra" <axharra@boxxo.info>
Cc: "postgreSQL postgreSQL" <pgsql-general@postgresql.org>
Sent: Thursday, November 16, 2006 1:47 PM
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

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@archonet.com>
To: "Ardian Xharra" <axharra@boxxo.info>
Cc: "postgreSQL postgreSQL" <pgsql-general@postgresql.org>
Sent: Friday, November 17, 2006 5:44 AM
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

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@archonet.com>
To: "Ardian Xharra" <axharra@boxxo.info>
Cc: "postgreSQL postgreSQL" <pgsql-general@postgresql.org>
Sent: Friday, November 17, 2006 1:12 PM
Subject: Re: [GENERAL] Why the data changes it's value by itself!