Horton

Thread

  1. Why the data changes it's value by itself!

    Ardian Xharra <axharra@boxxo.info> — 2006-11-16T15:55:47Z

    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

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

    Richard Huxton <dev@archonet.com> — 2006-11-16T17:30:02Z

    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

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

    Ardian Xharra <axharra@boxxo.info> — 2006-11-16T18:13:36Z

    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:
>> 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
>
>

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

    Richard Huxton <dev@archonet.com> — 2006-11-16T18:47:48Z

    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

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

    Ardian Xharra <axharra@boxxo.info> — 2006-11-16T19:15:26Z

     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:
>> 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
>
>

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

    Richard Huxton <dev@archonet.com> — 2006-11-17T10:44:12Z

    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

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

    Ardian Xharra <axharra@boxxo.info> — 2006-11-17T17:29:36Z

    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:
>> 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
>
>

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

    Richard Huxton <dev@archonet.com> — 2006-11-17T18:12:55Z

    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

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

    Magnus Hagander <mha@sollentuna.net> — 2006-11-17T18:15:47Z

    > > 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

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

    Ardian Xharra <axharra@boxxo.info> — 2007-01-15T16:36:16Z

    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!


> 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
>
>