SQL Anywhere database - Truncate table problem

Author

Truncate table problem

Alexey Danilov

2005-04-19, 9:23 am

Hello, All!

We have ASA v 8.0.3.5212

Some times after calling truncate table of various tables in
different stored procedures .... become ERROR:

[Sybase][ODBC Driver][Adaptive Server Anywhere]Integrity constraint
violation: No primary key value for foreign key 'SYSATTRIBUTENAME' in table
'SYSATTRIBUTE'

What is it?

With best regards, Alexey Danilov. E-mail: dan@pharm.mos.ru

anil k goel

2005-04-19, 9:23 am

This sound like a bug. Are you able to generate a reproducible case? Having
a repro will greatly assist us in being able to find the problem.

Which version of the server created the database?

Can you do 'select * from sysattributename' and post the result?

--
-anil
Research and Development, Query Processing
iAnywhere Solutions Engineering

-------------------------------------------------------------------------
** Whitepapers, TechDocs, bug fixes are all available through the **
** iAnywhere Developer Community at http://www.ianywhere.com/developer **
-------------------------------------------------------------------------

"Alexey Danilov" <dan@pharm.mos.ru> wrote in message
news:364059910@domai
n...
> Hello, All!
>
> We have ASA v 8.0.3.5212
>
> Some times after calling truncate table of various tables in
> different stored procedures .... become ERROR:
>
> [Sybase][ODBC Driver][Adaptive Server Anywhere]Integrity constraint
> violation: No primary key value for foreign key 'SYSATTRIBUTENAME' in
> table 'SYSATTRIBUTE'
>
> What is it?
>
> With best regards, Alexey Danilov. E-mail: dan@pharm.mos.ru
>

Bruce Hay

2005-04-20, 9:23 am

Looks like the database was created with an early version of ASA 8.0.2.
Rebuilding the database with your current version of ASA 8.0.3 should fix
the problem.

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer

"Alexey Danilov" <dan@pharm.mos.ru> wrote in message
news:437895470@domai
n...
> Hello, anil!
> You wrote in conference sybase.public.sqlanywhere.general on 19 Apr 2005
> 06:43:39 -0700:
> ak> This sound like a bug. Are you able to generate a reproducible
> case?
>
>
> Having
> ak> a repro will greatly assist us in being able to find the
problem.
>
>
> It's problem appear from time to time and I can't make a "case", but I can
> send stored procedures where this error has raised.
>
> ak> Which version of the server created the database?
>
>
> dbeng8 v 8.03.5212
> ak> Can you do 'select * from sysattributename' and post the
result?
>
>
> 2,'Clustered index'
> 1,'Table Page Percent Free'
> 3,'Number of distinct key values'
>
> ak> --
> -anil
> Research and Development, Query Processing
> iAnywhere Solutions Engineering
> --------------------------------------------------------------------
---
> ** Whitepapers, TechDocs, bug fixes are all available through the
> ** ** iAnywhere Developer Community at
> http://www.ianywhere.com/developer **
> --------------------------------------------------------------------
---
>
>
> With best regards, Alexey Danilov. E-mail: dan@pharm.mos.ru
>
>
>

anil k goel

2005-04-20, 9:23 am

From the output of SYSATTRIBUTENAME below, it would appear that the database
was created with 8.0.2 and not 8.0.3 (perhaps it was "UPGRADED" with
8.0.3?). As Bruce suggested, you can work around the problem by rebuilding
the database with 8.0.3.

In the meanwhile, I'll try to see if I can reproduce the problem.

--
-anil
Research and Development, Query Processing
iAnywhere Solutions Engineering

-------------------------------------------------------------------------
** Whitepapers, TechDocs, bug fixes are all available through the **
** iAnywhere Developer Community at http://www.ianywhere.com/developer **
-------------------------------------------------------------------------

"Alexey Danilov" <dan@pharm.mos.ru> wrote in message
news:437895470@domai
n...
> Hello, anil!
> You wrote in conference sybase.public.sqlanywhere.general on 19 Apr 2005
> 06:43:39 -0700:
> ak> This sound like a bug. Are you able to generate a reproducible
> case?
>
>
> Having
> ak> a repro will greatly assist us in being able to find the
> problem.
>
>
> It's problem appear from time to time and I can't make a "case", but I can
> send stored procedures where this error has raised.
>
> ak> Which version of the server created the database?
>
>
> dbeng8 v 8.03.5212
> ak> Can you do 'select * from sysattributename' and post the result?
>
>
> 2,'Clustered index'
> 1,'Table Page Percent Free'
> 3,'Number of distinct key values'
>
> ak> --
> -anil
> Research and Development, Query Processing
> iAnywhere Solutions Engineering
> -----------------------------------------------------------------------
> ** Whitepapers, TechDocs, bug fixes are all available through the
> ** ** iAnywhere Developer Community at
> http://www.ianywhere.com/developer **
> -----------------------------------------------------------------------
>
>
> With best regards, Alexey Danilov. E-mail: dan@pharm.mos.ru
>
>

anil k goel

2005-04-20, 9:23 am

Alexey,

Can you run the following statement on your database and post the result?

select db_property('IndexSt
atistics'), db_property('Cluster
edIndexes')

Also, are you willing to share, in strictest confidence, of course, your
database with us?

--
-anil
Research and Development, Query Processing
iAnywhere Solutions Engineering

-------------------------------------------------------------------------
** Whitepapers, TechDocs, bug fixes are all available through the **
** iAnywhere Developer Community at http://www.ianywhere.com/developer **
-------------------------------------------------------------------------

"Alexey Danilov" <dan@pharm.mos.ru> wrote in message
news:437895470@domai
n...
> Hello, anil!
> You wrote in conference sybase.public.sqlanywhere.general on 19 Apr 2005
> 06:43:39 -0700:
> ak> This sound like a bug. Are you able to generate a reproducible
> case?
>
>
> Having
> ak> a repro will greatly assist us in being able to find the
> problem.
>
>
> It's problem appear from time to time and I can't make a "case", but I can
> send stored procedures where this error has raised.
>
> ak> Which version of the server created the database?
>
>
> dbeng8 v 8.03.5212
> ak> Can you do 'select * from sysattributename' and post the result?
>
>
> 2,'Clustered index'
> 1,'Table Page Percent Free'
> 3,'Number of distinct key values'
>
> ak> --
> -anil
> Research and Development, Query Processing
> iAnywhere Solutions Engineering
> -----------------------------------------------------------------------
> ** Whitepapers, TechDocs, bug fixes are all available through the
> ** ** iAnywhere Developer Community at
> http://www.ianywhere.com/developer **
> -----------------------------------------------------------------------
>
>
> With best regards, Alexey Danilov. E-mail: dan@pharm.mos.ru
>
>

Alexey Danilov

2005-04-21, 3:23 am

Hello, anil!

I upgrade ASA to version 8.0.3 5223 and UPGRADE DataBase trought
SybaseCentral.
This problem stay present.

ak> Can you run the following statement on your database and post the
ak> result?

ak> select db_property('IndexSt
atistics'),
ak> db_property('Cluster
edIndexes')
select db_property('IndexSt
atistics'),
db_property('Cluster
edIndexes'),db_prope
rty('Capabilities')
db_property('IndexSt
atistics'),db_proper
ty('ClusteredIndexes
'),db_property('Capa
bilities')
'ON','ON','D3FFFDD'

ak> Also, are you willing to share, in strictest confidence, of course,
ak> your database with us?
It's no problem, but how you use it? (Russian charset cp1251, )
Send me your personal email, and I will send URL on it
(arjed db and log file on our ftp server).

With best regards
____________________
___________________
Alexey Danilov
Pharmaceutical information centre of
Moscow government health care committee
mailto:dan@pharm.mos.ru
tel:(095)924-1414

anil k goel

2005-04-21, 9:23 am

"Alexey Danilov" <dan@pharm.mos.ru> wrote in message
news:520836313@domai
n...
> Hello, anil!
>
> I upgrade ASA to version 8.0.3 5223 and UPGRADE DataBase trought
> SybaseCentral.
> This problem stay present.
>
> ak> Can you run the following statement on your database and post the
> ak> result?
>
> ak> select db_property('IndexSt
atistics'),
> ak> db_property('Cluster
edIndexes')
> select db_property('IndexSt
atistics'),
> db_property('Cluster
edIndexes'),db_prope
rty('Capabilities')
> db_property('IndexSt
atistics'),db_proper
ty('ClusteredIndexes
'),db_property('Capa
bilities')
> 'ON','ON','D3FFFDD'[
/color]

Right; so this looks like a problem introduced by the UPGRADE process. The
capability 'IndexStatistics' has been turned ON but the required new rows
have not been added to SYSATTRIBUTENAME. I'll investigate. In the meanwhile,
are you able to go on by unloadling and reloading your database?
[color=darkred]
> ak> Also, are you willing to share, in strictest confidence, of course,
> ak> your database with us?
> It's no problem, but how you use it? (Russian charset cp1251, )
> Send me your personal email, and I will send URL on it
> (arjed db and log file on our ftp server).

Please submit the database by using the "Report a Bug" mechanism. However,
you may have provided me with enough information above for me to be able to
diagnose the problem. Stay tuned...

--
-anil
Research and Development, Query Processing
iAnywhere Solutions Engineering

-------------------------------------------------------------------------
** Whitepapers, TechDocs, bug fixes are all available through the **
** iAnywhere Developer Community at http://www.ianywhere.com/developer **
-------------------------------------------------------------------------

anil k goel

2005-04-21, 9:23 am

I've been unable to get the database into the bad state. Please try and see
if you can repeat your steps and get another database into the bad state
that your database is in -- db_property('IndexSt
atistics') returns 'ON' and
SYSATTRIBUTENAME contains only 3 rows.

--
-anil
Research and Development, Query Processing
iAnywhere Solutions Engineering

-------------------------------------------------------------------------
** Whitepapers, TechDocs, bug fixes are all available through the **
** iAnywhere Developer Community at http://www.ianywhere.com/developer **
-------------------------------------------------------------------------

"Alexey Danilov" <dan@pharm.mos.ru> wrote in message
news:520836313@domai
n...
> Hello, anil!
>
> I upgrade ASA to version 8.0.3 5223 and UPGRADE DataBase trought
> SybaseCentral.
> This problem stay present.
>
> ak> Can you run the following statement on your database and post the
> ak> result?
>
> ak> select db_property('IndexSt
atistics'),
> ak> db_property('Cluster
edIndexes')
> select db_property('IndexSt
atistics'),
> db_property('Cluster
edIndexes'),db_prope
rty('Capabilities')
> db_property('IndexSt
atistics'),db_proper
ty('ClusteredIndexes
'),db_property('Capa
bilities')
> 'ON','ON','D3FFFDD'
>
> ak> Also, are you willing to share, in strictest confidence, of course,
> ak> your database with us?
> It's no problem, but how you use it? (Russian charset cp1251, )
> Send me your personal email, and I will send URL on it
> (arjed db and log file on our ftp server).
>
> With best regards
> ____________________
___________________
> Alexey Danilov
> Pharmaceutical information centre of
> Moscow government health care committee
> mailto:dan@pharm.mos.ru
> tel:(095)924-1414
>
>
>

anil k goel

2005-04-21, 11:23 am

Alexey,

Is it possible that when you upgraded ASA to v8.0.3, the script file called
"mksadb60.sql" was not upgraded to the 8.0.3 version.

The state of your database can be explained by the following:

1. The server you are running is 8.0.3.
2. Your database was actually created with the 8.0.3 server, not "upgraded"
as I thought before.
3. The mksadb60.sql file is the one from the 8.0.2GA release and not from
the 8.0.3 release.

To verify, find the file mksadb60.sql (also, look if you have multiple
versions of the file) and search for SYSATTRIBUTENAME in that file. The
8.0.3 version of the file must contain INSERT statements for 5 rows whereas
the 8.0.2 version inserts only 3 rows.

If your file contains 3 rows, that is your problem. You will need to update
that file and then recreate your database (a simple unload/reload) should do
the trick.

Please let us know what you find.

--
-anil
Research and Development, Query Processing
iAnywhere Solutions Engineering

-------------------------------------------------------------------------
** Whitepapers, TechDocs, bug fixes are all available through the **
** iAnywhere Developer Community at http://www.ianywhere.com/developer **
-------------------------------------------------------------------------

"Alexey Danilov" <dan@pharm.mos.ru> wrote in message
news:520836313@domai
n...
> Hello, anil!
>
> I upgrade ASA to version 8.0.3 5223 and UPGRADE DataBase trought
> SybaseCentral.
> This problem stay present.
>
> ak> Can you run the following statement on your database and post the
> ak> result?
>
> ak> select db_property('IndexSt
atistics'),
> ak> db_property('Cluster
edIndexes')
> select db_property('IndexSt
atistics'),
> db_property('Cluster
edIndexes'),db_prope
rty('Capabilities')
> db_property('IndexSt
atistics'),db_proper
ty('ClusteredIndexes
'),db_property('Capa
bilities')
> 'ON','ON','D3FFFDD'
>
> ak> Also, are you willing to share, in strictest confidence, of course,
> ak> your database with us?
> It's no problem, but how you use it? (Russian charset cp1251, )
> Send me your personal email, and I will send URL on it
> (arjed db and log file on our ftp server).
>
> With best regards
> ____________________
___________________
> Alexey Danilov
> Pharmaceutical information centre of
> Moscow government health care committee
> mailto:dan@pharm.mos.ru
> tel:(095)924-1414
>
>
>

Alexey Danilov

2005-04-21, 1:23 pm

Hello All!

Special thanks for "anil k goel" !

[skiped]
> To verify, find the file mksadb60.sql (also, look if you have multiple
> versions of the file) and search for SYSATTRIBUTENAME in that file. The
> 8.0.3 version of the file must contain INSERT statements for 5 rows
> whereas the 8.0.2 version inserts only 3 rows.
>
> If your file contains 3 rows, that is your problem. You will need to
> update that file and then recreate your database (a simple unload/reload)
> should do the trick.
I'm will try tomorrow this metod.

>
> Please let us know what you find.
Ok.

With best regards
____________________
___________________
Alexey Danilov
Pharmaceutical information centre of
Moscow government health care committee
mailto:dan@pharm.mos.ru
tel:(095)924-1414

Sponsored Links