SQL Anywhere database - validating database : lots of errors

Author

validating database : lots of errors

Ton van den Broek

2005-10-28, 7:23 am

Hi all,

For fun (!) just tried running a validate db process from within Sybase
Central
on a 6.04 (latest EBF) remote db. Horrifying to notice that most (80%) of
the
tables report errors "Runtime SQL error - Primary key for table xxx has
missing index entries"

Database seems to run without problems. Is this serious, are there other
things to check for
and how to recover this easily (without having to drop and recreate all
foreign keys)

Thanks in advance
and best regards

Ton van den Broek

Breck Carter [TeamSybase]

2005-10-28, 7:23 am

It is extremely serious. Extensive corruption may point to failing
hardware, possibly the disk, but also bad RAM can lead to database
corruption. The database is in extreme jeopardy; stop the engine and
take a full "file copy" backup immediately, because further damage may
cause it to be completely unusable.

Then try to recover: unload reload is one approach. But not until you
verify the hardware is OK. Or just move to another machine now,
investigate later.

You could also run dbvalid against an old backup to see if the problem
is new or old... if it is new, you could restore the old backup and
apply logs, presuming you do have a backup and recovery strategy that
works like that.

And when all is said and done, running dbvalid against a copy of each
night's full backup is a way to ensure that recovery is possible.

Breck

On 28 Oct 2005 03:07:35 -0700, "Ton van den Broek"
< REMOVEtonTHE@CAPITAL
Stvdb.nl> wrote:

>Hi all,
>
>For fun (!) just tried running a validate db process from within Sybase
>Central
>on a 6.04 (latest EBF) remote db. Horrifying to notice that most (80%) of
>the
>tables report errors "Runtime SQL error - Primary key for table xxx has
>missing index entries"
>
>Database seems to run without problems. Is this serious, are there other
>things to check for
>and how to recover this easily (without having to drop and recreate all
>foreign keys)
>
>Thanks in advance
>and best regards
>
>Ton van den Broek
>

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

Ton van den Broek

2005-10-28, 11:23 am

Breck,

Thanks for your info. Additional question: This machine is running
a dual processor (P4) and seems to corrupt indexes (the reload
from an unload coming from a validated db, even fails ...)
Is 6.04 suited for running on a dual processor machine?

TIA
Ton

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> schreef in
bericht news:7514m1dqope02tb
kolfvi3j8npgk57mcg3@
4ax.com...
> It is extremely serious. Extensive corruption may point to failing
> hardware, possibly the disk, but also bad RAM can lead to database
> corruption. The database is in extreme jeopardy; stop the engine and
> take a full "file copy" backup immediately, because further damage may
> cause it to be completely unusable.
>
> Then try to recover: unload reload is one approach. But not until you
> verify the hardware is OK. Or just move to another machine now,
> investigate later.
>
> You could also run dbvalid against an old backup to see if the problem
> is new or old... if it is new, you could restore the old backup and
> apply logs, presuming you do have a backup and recovery strategy that
> works like that.
>
> And when all is said and done, running dbvalid against a copy of each
> night's full backup is a way to ensure that recovery is possible.
>
> Breck
>
>
>
>
> On 28 Oct 2005 03:07:35 -0700, "Ton van den Broek"
> < REMOVEtonTHE@CAPITAL
Stvdb.nl> 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

Joshua Savill

2005-10-28, 8:23 pm

What OS are you running?

--
Joshua Savill
iAnywhere Solutions - Product Support Analyst

"Ton van den Broek" < REMOVEtonTHE@CAPITAL
Stvdb.nl> wrote in message
news:43624516@forums
-1-dub...
> Breck,
>
> Thanks for your info. Additional question: This machine is running
> a dual processor (P4) and seems to corrupt indexes (the reload
> from an unload coming from a validated db, even fails ...)
> Is 6.04 suited for running on a dual processor machine?
>
> TIA
> Ton
>
> "Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> schreef in
> bericht news:7514m1dqope02tb
kolfvi3j8npgk57mcg3@
4ax.com...
> http://www.amazon.com/exec/obidos/A...7/risingroad-20
>
>

Stephen Rice

2005-10-28, 8:23 pm

6.0.4 was tested on multi-processor boxes so that should not be the
source of your problem.

I did a quick scan of the fix logs did not find any know/fixed issues
that match your circumstances

Some thoughts (in no particular order):
- To eliminate the hardware can you try reloading on a different
machine/hard drive/etc?
- make sure you are using a fresh database (dbinit) Try a different
page size
- try reloading with a client/external reload (i.e the one that uses
input not load table) I'm getting old I forget the switch in v6 it's -xx
in v9)

/steve

Ton van den Broek wrote:
> Breck,
>
> Thanks for your info. Additional question: This machine is running
> a dual processor (P4) and seems to corrupt indexes (the reload
> from an unload coming from a validated db, even fails ...)
> Is 6.04 suited for running on a dual processor machine?
>
> TIA
> Ton
>
> "Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> schreef in
> bericht news:7514m1dqope02tb
kolfvi3j8npgk57mcg3@
4ax.com...
>
>
> http://www.amazon.com/exec/obidos/A...7/risingroad-20
>
>
>
>

--
Stephen Rice
Technical Services Manager
iAnywhere Solutions

--- Please Post ---
Whitepapers, Tech Docs, Solved Cases, Bug Fixes and
"Report a bug" are all available on www.ianywhere.com

Ton van den Broek

2005-10-28, 8:23 pm

OS is Win XP (SP2)

I'm currently trying to reload this db on another machine
with the same data and see what comes out of it ...

Best regards,
Ton

"Joshua Savill" <jsavill@ianywhere.com> schreef in bericht
news:43628759$1@foru
ms-2-dub...
> What OS are you running?
>
> --
> Joshua Savill
> iAnywhere Solutions - Product Support Analyst
>
>
> "Ton van den Broek" < REMOVEtonTHE@CAPITAL
Stvdb.nl> wrote in message
> news:43624516@forums
-1-dub...
Sybase[color=darkred
]
has[color=darkred]
other[color=darkred]

all[color=darkred]
>
>

Joshua Savill

2005-10-28, 8:23 pm

Please make sure that you are running at least EBF 3763 or higher. We did
not test on ASA 6.0.4 prior to this build. Should not be the cause of all of
the corruption, but is worth ruling out.

--
Joshua Savill
iAnywhere Solutions - Product Support Analyst

"Ton van den Broek" < REMOVEtonTHE@CAPITAL
Stvdb.nl> wrote in message
news:4362b250$1@foru
ms-1-dub...
> OS is Win XP (SP2)
>
> I'm currently trying to reload this db on another machine
> with the same data and see what comes out of it ...
>
> Best regards,
> Ton
>
> "Joshua Savill" <jsavill@ianywhere.com> schreef in bericht
> news:43628759$1@foru
ms-2-dub...
> Sybase
> has
> other
> all
>
>

Joshua Savill

2005-10-28, 8:23 pm

It's getting late. That should read 'we didn't test on Windows XP with ASA
6.0.4 until EBF 3763'.

--
Joshua Savill
iAnywhere Solutions - Product Support Analyst

"Joshua Savill" <jsavill@ianywhere.com> wrote in message
news:4362b603$1@foru
ms-1-dub...
> Please make sure that you are running at least EBF 3763 or higher. We did
> not test on ASA 6.0.4 prior to this build. Should not be the cause of all
> of the corruption, but is worth ruling out.
>
> --
> Joshua Savill
> iAnywhere Solutions - Product Support Analyst
>
>
> "Ton van den Broek" < REMOVEtonTHE@CAPITAL
Stvdb.nl> wrote in message
> news:4362b250$1@foru
ms-1-dub...
>
>

Ton van den Broek

2005-10-29, 7:23 am

Joshua,

(Although it was late you answered before I asked <g> )

Latest EBF (3799) is applied so that shouldn't be the problem. When
running SQLRemote against it crashed with the remarks shown below
There are some comments below. Maybe this is of more help.

Best regards,
Ton

I. 10/27 16:29:15. UPDATE DBA.gewas
SET fk_kleurid=367,
fk_gwblwid=41,
mdat=74812,
mtijd=5550930,
musnr=23
VERIFY (fk_kleurid,fk_gwblw
id,mdat,mtijd,musnr)

VALUES (NULL,NULL,73845,338
6321,2)
WHERE siteid=999999
AND id=12856
I. 10/27 16:29:16. COMMIT
E. 10/27 16:29:16. SQL statement failed: (-194) No primary key value for
foreign key 'vkbe_idkey' in table 'vkbepr'
I. 10/27 16:29:16. ROLLBACK
E. 10/27 16:29:16. SQL statement failed: (-301) Internal database error ***
ERROR *** Assertion failed: 100702
Unable to modify indexes for a row referenced in rollback
log -- transaction rolled back
E. 10/27 16:29:16. SQL statement failed: (-301) Internal database error --
transaction rolled back
E. 10/27 16:29:16. Error connecting to database
E. 10/27 16:29:16. SQL statement failed: (-101) Not connected to a database
E. 10/27 16:29:16. SQL statement failed: (-301) Internal database error --
transaction rolled back
E. 10/27 16:29:16. Error connecting to database
I. 10/27 16:29:16. Execution completed

- I cannot figure out what sql-statement causes the "No primary key..."
message, so I cannot
validate this (All other remotes do run ok and have passed this offset point
with succes)
- The recovery of the database fails too (I don't have the assertion at hand
for this)
- While doing the reload is also complains about a missing primary key
value. This value and
the primary key were definately added to the db (ISQL also shows the row,
that ought to be
missing)

"Joshua Savill" <jsavill@ianywhere.com> schreef in bericht
news:4362b647@forums
-1-dub...
> It's getting late. That should read 'we didn't test on Windows XP with ASA
> 6.0.4 until EBF 3763'.
>
> --
> Joshua Savill
> iAnywhere Solutions - Product Support Analyst
>
> "Joshua Savill" <jsavill@ianywhere.com> wrote in message
> news:4362b603$1@foru
ms-1-dub...
did[color=darkred]
all[color=darkred]
and[color=darkred]
you[color=darkred]
that[color=darkred]
each[color=darkred]
xxx[color=darkred]
recreate[color=darkr
ed]
>
>

Sponsored Links