SQL Anywhere database - Could an Invalid Index be due to missing primary key?

Author

Could an Invalid Index be due to missing primary key?

Victor ORear

2005-05-16, 8:23 pm

I know see that GoogleGroup postings don't make it to this group.. So I'm
trying again before I open up a case on this seemingly simple question..

I have a few DBs with about 3 million entries in one table and no primary
key, but two indexes. And a frequent apperance of "Index has invalid or
duplicate index entries"

Error remains after rebuilding. Sybase ASA 9.0.2.3044 - WinNT Could the
problem be the lack of a primary key on this table?

Breck Carter [TeamSybase]

2005-05-16, 8:23 pm

Not likely.

Question: Are either of the indexes a *unique* index? If not, you
could have serious performance problems with certain queries, and with
database recovery if that was required (e.g., when the server shut
down hard and had to go through recovery on startup).

Have you tried dropping and recreating the indexes?

Unloading and recreating the database from scratch is probably a good
idea as well. As is checking the drive for problems. Errors like this
should be attended to as soon as possible.

Are you taking regular backups, and maintaining a transaction log?

Breck

On 16 May 2005 12:07:29 -0700, "Victor ORear" <victoro@mailinator.com>
wrote:

>I know see that GoogleGroup postings don't make it to this group.. So I'm
>trying again before I open up a case on this seemingly simple question..
>
>I have a few DBs with about 3 million entries in one table and no primary
>key, but two indexes. And a frequent apperance of "Index has invalid or
>duplicate index entries"
>
>Error remains after rebuilding. Sybase ASA 9.0.2.3044 - WinNT Could the
>problem be the lack of a primary key on this table?
>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Victor ORear

2005-05-16, 8:23 pm

Neither of the indexes are unique, and I didn't design the DB but I am
fixing it. I'm also aware that having no unique index also impacts the
transaction log since each transaction must be recorded in full.

I have dropped and recreated the problem indexes. I've also dropped the
indexes, re-run DBVAILD to confirm no other problems and then recreated the
indexes and passed DBVALID. Then, the next day, the problem returns.

The DB was converted from 7.0.4 to 9.0.2 with an unload and reload process
and no errors were seen during the dbunload process.

We do have a backup process that also validates the DB and Log (working on
copies :-) and that's how we found the error since immediatly after the
conversion there was no indication of a problem. We've seen this with two
customers after conversion and so I suspected some change with the number of
records handled by the indexes.

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:mjsh8191lv341fj
nt6g48o2ak950ob36m9@
4ax.com...
> Not likely.
>
> Question: Are either of the indexes a *unique* index? If not, you
> could have serious performance problems with certain queries, and with
> database recovery if that was required (e.g., when the server shut
> down hard and had to go through recovery on startup).
>
> Have you tried dropping and recreating the indexes?
>
> Unloading and recreating the database from scratch is probably a good
> idea as well. As is checking the drive for problems. Errors like this
> should be attended to as soon as possible.
>
> Are you taking regular backups, and maintaining a transaction log?
>
> Breck
>
> On 16 May 2005 12:07:29 -0700, "Victor ORear" <victoro@mailinator.com>
> wrote:
>
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com

Breck Carter [TeamSybase]

2005-05-17, 9:23 am

This forum is operated by volunteers... that means folks have limited
time to work on hard-to-reproduce symptoms that appear to be bugs
(especially since the problem *reappears* after a rebuild). You should
contact Technical Support http://www.sybase.com/contactus/support#tech

Breck

On 16 May 2005 15:04:11 -0700, "Victor ORear" <victoro@mailinator.com>
wrote:

>Neither of the indexes are unique, and I didn't design the DB but I am
>fixing it. I'm also aware that having no unique index also impacts the
>transaction log since each transaction must be recorded in full.
>
>I have dropped and recreated the problem indexes. I've also dropped the
>indexes, re-run DBVAILD to confirm no other problems and then recreated the
>indexes and passed DBVALID. Then, the next day, the problem returns.
>
>The DB was converted from 7.0.4 to 9.0.2 with an unload and reload process
>and no errors were seen during the dbunload process.
>
>We do have a backup process that also validates the DB and Log (working on
>copies :-) and that's how we found the error since immediatly after the
>conversion there was no indication of a problem. We've seen this with two
>customers after conversion and so I suspected some change with the number of
>records handled by the indexes.
>
>"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
>message news:mjsh8191lv341fj
nt6g48o2ak950ob36m9@
4ax.com...
>http://www.amazon.com/exec/obidos/A...7/risingroad-20
>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Victor ORear

2005-05-17, 1:23 pm

Thanks, just wanted to see if it was a simple problem not avoinding opening
up a case, by they way - I'm enjoying your book.

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:idsj8193ceu4f1o
ffq7edn5p311ai0nc6f@
4ax.com...
> This forum is operated by volunteers... that means folks have limited
> time to work on hard-to-reproduce symptoms that appear to be bugs
> (especially since the problem *reappears* after a rebuild). You should
> contact Technical Support http://www.sybase.com/contactus/support#tech
>
> Breck
>
> On 16 May 2005 15:04:11 -0700, "Victor ORear" <victoro@mailinator.com>
> wrote:
>
the[color=darkred]
process[color=darkre
d]
on[color=darkred]
two[color=darkred]
of[color=darkred]
I'm[color=darkred]
question..[color=darkred]
primary[color=darkre
d]
or[color=darkred]
the[color=darkred]
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com

Sponsored Links