Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Index corruption/out of sync with data in table

5 posts in General Discussion Last posting was on 2009-11-25 14:49:09.0Z
Ignacio Vera Posted on 2009-11-25 11:33:58.0Z
Sender: 552b.4b0d15b6.1804289383@sybase.com
From: Ignacio Vera
Newsgroups: sybase.public.ase.general
Subject: Index corruption/out of sync with data in table
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4b0d1626.5534.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 25 Nov 2009 03:33:58 -0800
X-Trace: forums-1-dub 1259148838 10.22.241.41 (25 Nov 2009 03:33:58 -0800)
X-Original-Trace: 25 Nov 2009 03:33:58 -0800, 10.22.241.41
Lines: 49
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28693
Article PK: 77934

Dear Gurus,

We have a table which is highly used and in the last months
we have twice the following error. For some reason which we
don't understand the indexes of the table get out of sync
with the actual data on the table.

The last time, the error affected the primary key of the
table. We found out one 'ghost' entry in the index and one
missing entry in the index.

Explaining in more detail, imaging you have the following
table called test with the following population:

id description

1 item 1
2 item 2
3 item 3
5 item 5

where id is the primary key but it is not generated by an
identity column but using the 'key table' algorithm.

What we found is that if we performed the following query:

select * from test where id=4

was returning:

5 item 5 (Ghost entry in the index)

But on the other hand if we performed the following query:

select * from test where id=2

No rows were returned (missing entry in the index).

One thing suspicious to me is that if there is a Gap in the
primary keys, it means that for some reason there was an
error creating that entry and a roll back.

Of course we rebuild the index and the problem went away but
I would like to know if someone has seen this before and
what are the possible causes?

Regards,

Ignacio


Rob V [ Sybase ] Posted on 2009-11-25 12:03:53.0Z
Reply-To: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
From: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Newsgroups: sybase.public.ase.general
References: <4b0d1626.5534.1681692777@sybase.com>
Subject: Re: Index corruption/out of sync with data in table
Lines: 97
Organization: Sypron BV / TeamSybase / Sybase
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b0d1d29$1@forums-1-dub>
Date: 25 Nov 2009 04:03:53 -0800
X-Trace: forums-1-dub 1259150633 10.22.241.152 (25 Nov 2009 04:03:53 -0800)
X-Original-Trace: 25 Nov 2009 04:03:53 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28694
Article PK: 77936

If it is indeed the case that the index rows were not corresponding to the
data rows, then that would be a severe case of data corruption. This would
be extremely rare and there are no immediate obvious causes for this. If
this has occurred, then I would expect to find error messages in your ASE
errorlog (most likely with error numbers in ranges 600-699 and 800-899
though others could occur too). Do you see any of these?

About the index being corrupted: you cannot conclude that this was the case
just on the basis of the query results. What would need to be done here is
dump the contents of the index pages and verify their correspondence to the
data pages. Only when that shows a mismatch then we have certainty that
there is index corruption. This isn't straightforward, as it requires
messing with dbcc page() and interpreting the results, but it can be done.
Depending on the table's lock scheme, this can also be verified with certain
dbcc commands.

It is unfortunate that you rebuilt the index since it is not possible to
verify things anymore. Should this happen again, make a database dump first
so that the original problem can be investigated afterwards.

When reporitng issues like these, it is always a good idea to include your
precise ASE version as well as the lock scheme and size of the table, the
type of index (unqiue? clustered?), and the datatypes of the indexed
columns.

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., Amersfoort, The Netherlands
Chamber of Commerce 27138666
-----------------------------------------------------------------

<Ignacio Vera> wrote in message news:4b0d1626.5534.1681692777@sybase.com...
> Dear Gurus,
>
> We have a table which is highly used and in the last months
> we have twice the following error. For some reason which we
> don't understand the indexes of the table get out of sync
> with the actual data on the table.
>
> The last time, the error affected the primary key of the
> table. We found out one 'ghost' entry in the index and one
> missing entry in the index.
>
> Explaining in more detail, imaging you have the following
> table called test with the following population:
>
> id description
>
> 1 item 1
> 2 item 2
> 3 item 3
> 5 item 5
>
> where id is the primary key but it is not generated by an
> identity column but using the 'key table' algorithm.
>
> What we found is that if we performed the following query:
>
> select * from test where id=4
>
> was returning:
>
> 5 item 5 (Ghost entry in the index)
>
> But on the other hand if we performed the following query:
>
> select * from test where id=2
>
> No rows were returned (missing entry in the index).
>
> One thing suspicious to me is that if there is a Gap in the
> primary keys, it means that for some reason there was an
> error creating that entry and a roll back.
>
> Of course we rebuild the index and the problem went away but
> I would like to know if someone has seen this before and
> what are the possible causes?
>
> Regards,
>
> Ignacio


Ignacio Vera Posted on 2009-11-25 12:31:51.0Z
Sender: 5758.4b0d2134.1804289383@sybase.com
From: Ignacio Vera
Newsgroups: sybase.public.ase.general
Subject: Re: Index corruption/out of sync with data in table
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4b0d23b7.5783.1681692777@sybase.com>
References: <4b0d1d29$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 25 Nov 2009 04:31:51 -0800
X-Trace: forums-1-dub 1259152311 10.22.241.41 (25 Nov 2009 04:31:51 -0800)
X-Original-Trace: 25 Nov 2009 04:31:51 -0800, 10.22.241.41
Lines: 136
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28695
Article PK: 77937


> If it is indeed the case that the index rows were not
> corresponding to the data rows, then that would be a
> severe case of data corruption. This would be extremely
> rare and there are no immediate obvious causes for this.
> If this has occurred, then I would expect to find error
> messages in your ASE errorlog (most likely with error
> numbers in ranges 600-699 and 800-899 though others could
> occur too). Do you see any of these?

I have no administrator privileges so I need to wait an
answer for the administrators.
>
> About the index being corrupted: you cannot conclude that
> this was the case just on the basis of the query results.
> What would need to be done here is dump the contents of
> the index pages and verify their correspondence to the
> data pages. Only when that shows a mismatch then we have
> certainty that there is index corruption. This isn't
> straightforward, as it requires messing with dbcc page()
> and interpreting the results, but it can be done.
> Depending on the table's lock scheme, this can also be
> verified with certain dbcc commands.

One this we did was to bcp out the data and check that the
data were/not were there. You are right this procedure will
check all Sybase interanls but looking to query plans and
answer to the queries it was obvious something was very sick
and it was related with the index not being on sync with the
data.

> It is unfortunate that you rebuilt the index since it is
> not possible to verify things anymore. Should this happen
> again, make a database dump first so that the original
> problem can be investigated afterwards.

Yep, in this case we could have open a case with Sybase as
we cannot reproduce the error again. My fault, if there is a
next time (hope not ;-) we will follow this procedure.

> When reporitng issues like these, it is always a good idea
> to include your precise ASE version as well as the lock
> scheme and size of the table, the type of index (unqiue?
> clustered?), and the datatypes of the indexed columns.

ASE Version:

'Adaptive Server Enterprise/12.5.3/EBF 12897
ESD#3/P/x86_64/Enterprise Linux/ase1253/1916/64-bit/OPT/Wed
Aug 3 18:14:45 2005'

lock scheme: data rows
size of the table:
space reserverd: 1369 KB
Used by Data: 800 KB
Used by index: 480 KB
Unused: 116 KB

Type of index: Primary key index therefore unique and non
clustered. Integer data type (as in the example).

>
> HTH,
>
> Rob V.
> ----------------------------------------------------------
> ------- Rob Verschoor
>
> Certified Sybase Professional DBA for ASE
> 15.0/12.5/12.0/11.5/11.0 and Replication Server
> 15.0.1/12.5 // TeamSybase
>
> Author of Sybase books (order online at
> www.sypron.nl/shop): "Tips, Tricks & Recipes for Sybase
> ASE" (ASE 15 edition) "The Complete Sybase ASE Quick
> Reference Guide" "The Complete Sybase Replication Server
> Quick Reference Guide"
>
> mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
> http://www.sypron.nl
> Sypron B.V., Amersfoort, The Netherlands
> Chamber of Commerce 27138666
> ----------------------------------------------------------
> -------
>
> <Ignacio Vera> wrote in message
> > news:4b0d1626.5534.1681692777@sybase.com... Dear Gurus,
> >
> > We have a table which is highly used and in the last
> > months we have twice the following error. For some
> > reason which we don't understand the indexes of the
> > table get out of sync with the actual data on the table.
> >
> > The last time, the error affected the primary key of the
> > table. We found out one 'ghost' entry in the index and
> > one missing entry in the index.
> >
> > Explaining in more detail, imaging you have the
> > following table called test with the following
> population: >
> > id description
> >
> > 1 item 1
> > 2 item 2
> > 3 item 3
> > 5 item 5
> >
> > where id is the primary key but it is not generated by
> > an identity column but using the 'key table' algorithm.
> >
> > What we found is that if we performed the following
> query: >
> > select * from test where id=4
> >
> > was returning:
> >
> > 5 item 5 (Ghost entry in the index)
> >
> > But on the other hand if we performed the following
> query: >
> > select * from test where id=2
> >
> > No rows were returned (missing entry in the index).
> >
> > One thing suspicious to me is that if there is a Gap in
> > the primary keys, it means that for some reason there
> > was an error creating that entry and a roll back.
> >
> > Of course we rebuild the index and the problem went away
> > but I would like to know if someone has seen this before
> > and what are the possible causes?
> >
> > Regards,
> >
> > Ignacio
>
>


Rob V [ Sybase ] Posted on 2009-11-25 13:39:30.0Z
Reply-To: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
From: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Newsgroups: sybase.public.ase.general
References: <4b0d1d29$1@forums-1-dub> <4b0d23b7.5783.1681692777@sybase.com>
Subject: Re: Index corruption/out of sync with data in table
Lines: 162
Organization: Sypron BV / TeamSybase / Sybase
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b0d3392$1@forums-1-dub>
Date: 25 Nov 2009 05:39:30 -0800
X-Trace: forums-1-dub 1259156370 10.22.241.152 (25 Nov 2009 05:39:30 -0800)
X-Original-Trace: 25 Nov 2009 05:39:30 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28696
Article PK: 77938

Keep in mind that especially with datarows locking there are certain
optimziations that may look funny if you don't expect them. For example,
uncommitted inserts will not be block queries when selecting from the table,
so it looks as if the row is not there until it is committed. I'm not
saying this is your situation but

Since you're on 12.5.3, you should use dbcc checkindex to verify the
consistency of index rows vs. data rows. In 12.5.3 this is only done by
checking that a data row exists for an index row; in 15.0, you can also do
it the other way around. When this check tells you there are corruptions,
then you know you have an issue but if not, something else is going on.
BCP'ing the data out will not help to answer this problem, since it will
only copy the data rows.
One thing you could do is run a query that extracts the IDs from the index
by forcing the use of that particular index (and making it a covered query
by avoiding touching the data pages), but I would still be reluctant to see
that as evidence for a corruption unless dbcc checkindex says so.

HTH,

Rob V.

<Ignacio Vera> wrote in message news:4b0d23b7.5783.1681692777@sybase.com...
>> If it is indeed the case that the index rows were not
>> corresponding to the data rows, then that would be a
>> severe case of data corruption. This would be extremely
>> rare and there are no immediate obvious causes for this.
>> If this has occurred, then I would expect to find error
>> messages in your ASE errorlog (most likely with error
>> numbers in ranges 600-699 and 800-899 though others could
>> occur too). Do you see any of these?
>
> I have no administrator privileges so I need to wait an
> answer for the administrators.
>>
>> About the index being corrupted: you cannot conclude that
>> this was the case just on the basis of the query results.
>> What would need to be done here is dump the contents of
>> the index pages and verify their correspondence to the
>> data pages. Only when that shows a mismatch then we have
>> certainty that there is index corruption. This isn't
>> straightforward, as it requires messing with dbcc page()
>> and interpreting the results, but it can be done.
>> Depending on the table's lock scheme, this can also be
>> verified with certain dbcc commands.
>
> One this we did was to bcp out the data and check that the
> data were/not were there. You are right this procedure will
> check all Sybase interanls but looking to query plans and
> answer to the queries it was obvious something was very sick
> and it was related with the index not being on sync with the
> data.
>
>> It is unfortunate that you rebuilt the index since it is
>> not possible to verify things anymore. Should this happen
>> again, make a database dump first so that the original
>> problem can be investigated afterwards.
>
> Yep, in this case we could have open a case with Sybase as
> we cannot reproduce the error again. My fault, if there is a
> next time (hope not ;-) we will follow this procedure.
>
>> When reporitng issues like these, it is always a good idea
>> to include your precise ASE version as well as the lock
>> scheme and size of the table, the type of index (unqiue?
>> clustered?), and the datatypes of the indexed columns.
>
> ASE Version:
>
> 'Adaptive Server Enterprise/12.5.3/EBF 12897
> ESD#3/P/x86_64/Enterprise Linux/ase1253/1916/64-bit/OPT/Wed
> Aug 3 18:14:45 2005'
>
> lock scheme: data rows
> size of the table:
> space reserverd: 1369 KB
> Used by Data: 800 KB
> Used by index: 480 KB
> Unused: 116 KB
>
> Type of index: Primary key index therefore unique and non
> clustered. Integer data type (as in the example).
>
>>
>> HTH,
>>
>> Rob V.
>> ----------------------------------------------------------
>> ------- Rob Verschoor
>>
>> Certified Sybase Professional DBA for ASE
>> 15.0/12.5/12.0/11.5/11.0 and Replication Server
>> 15.0.1/12.5 // TeamSybase
>>
>> Author of Sybase books (order online at
>> www.sypron.nl/shop): "Tips, Tricks & Recipes for Sybase
>> ASE" (ASE 15 edition) "The Complete Sybase ASE Quick
>> Reference Guide" "The Complete Sybase Replication Server
>> Quick Reference Guide"
>>
>> mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
>> http://www.sypron.nl
>> Sypron B.V., Amersfoort, The Netherlands
>> Chamber of Commerce 27138666
>> ----------------------------------------------------------
>> -------
>>
>> <Ignacio Vera> wrote in message
>> > news:4b0d1626.5534.1681692777@sybase.com... Dear Gurus,
>> >
>> > We have a table which is highly used and in the last
>> > months we have twice the following error. For some
>> > reason which we don't understand the indexes of the
>> > table get out of sync with the actual data on the table.
>> >
>> > The last time, the error affected the primary key of the
>> > table. We found out one 'ghost' entry in the index and
>> > one missing entry in the index.
>> >
>> > Explaining in more detail, imaging you have the
>> > following table called test with the following
>> population: >
>> > id description
>> >
>> > 1 item 1
>> > 2 item 2
>> > 3 item 3
>> > 5 item 5
>> >
>> > where id is the primary key but it is not generated by
>> > an identity column but using the 'key table' algorithm.
>> >
>> > What we found is that if we performed the following
>> query: >
>> > select * from test where id=4
>> >
>> > was returning:
>> >
>> > 5 item 5 (Ghost entry in the index)
>> >
>> > But on the other hand if we performed the following
>> query: >
>> > select * from test where id=2
>> >
>> > No rows were returned (missing entry in the index).
>> >
>> > One thing suspicious to me is that if there is a Gap in
>> > the primary keys, it means that for some reason there
>> > was an error creating that entry and a roll back.
>> >
>> > Of course we rebuild the index and the problem went away
>> > but I would like to know if someone has seen this before
>> > and what are the possible causes?
>> >
>> > Regards,
>> >
>> > Ignacio
>>
>>


Luc Van der Veurst Posted on 2009-11-25 14:49:09.0Z
From: "Luc Van der Veurst" <dba_azvub@hotmail.com>
Newsgroups: sybase.public.ase.general
References: <4b0d1d29$1@forums-1-dub> <4b0d23b7.5783.1681692777@sybase.com>
Subject: Re: Index corruption/out of sync with data in table
Lines: 21
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Original
X-Forwarded: by - (DeleGate/5.8.7)
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b0d43e5@forums-1-dub>
Date: 25 Nov 2009 06:49:09 -0800
X-Trace: forums-1-dub 1259160549 10.22.241.152 (25 Nov 2009 06:49:09 -0800)
X-Original-Trace: 25 Nov 2009 06:49:09 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28697
Article PK: 77939

<Ignacio Vera> wrote in message news:4b0d23b7.5783.1681692777@sybase.com...
...

> Yep, in this case we could have open a case with Sybase as
> we cannot reproduce the error again. My fault, if there is a
> next time (hope not ;-) we will follow this procedure.

Depending on the degree of fear you have that it will happen
again ... :-) :
I suppose it happened not that long ago, so most likely you still
have database and transaction log dumps. If so, you can
load the database into a test system and recover until the time
of which you know the behavior was not what you expected
and before the time you rebuild the index. Then run dbcc
checkindex.

Luc.