|
Home > Archive > SQL Anywhere Mobile > October 2005 > Cannot Update Primary Key Value
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Cannot Update Primary Key Value
|
|
|
| Hi,
We are using ASA 9.0.1.1899 version for the sync. process
with Oracle. this process doing well. suddenly we got the
error Like
SQL statement failed: (-780) Cannot update primary key for
table
'EFB_EfbWpi_waypoint
_info' involved in a publication
this table data is upto 600000 records. I applied the option
below
SET OPTION PUBLIC. PREVENT_ARTICLE_PKEY
_UPDATE='OFF';
then also sync. process failed coz of the same problem. what
might be the reason. In the testing environment i am not
able to simulate this problem. it is coming only in the
production server where it was working for the last 3 months
with out any problem.
Kindly reply me the propable solution for this.
Regards,
Baskar.S
| |
| David Fishburn 2005-10-27, 8:21 am |
| Joe wrote in news:434decd2.576f.1681692777@sybase.com
of sybase.public.sqlanywhere.mobilink:
J> SQL statement failed: (-780) Cannot update primary key for
J> table
J> 'EFB_EfbWpi_waypoint
_info' involved in a publication
J>
J> this table data is upto 600000 records. I applied the option
J> below
Can you confirm that a primary key is attempting to be updated?
--
David Fishburn
Certified ASA Developer Version 8
iAnywhere Solutions - Sybase
Professional Services
Please only post to the newsgroup
Please ALWAYS include version and MORE importantly BUILD number with
EACH post (dbeng9 -v).
EBFs and Maintenance Releases
http://downloads.sybase.com/swx/sdmain.stm
Developer Community / Whitepapers
http://www.ianywhere.com/developer
CaseXpress - to report bugs
http://casexpress.sybase.com
CodeXchange - Free samples
[url]http://ianywhere.codexchange.sybase.com/servlets/ ProjectDocumentList[
/url]
| |
|
| Hi David,
Thanks for the response.
Yes it is Primary Key column. I read the document it says
that we can use -b option in dbmlsrv9 for this kind of
error. is it ok. or i need to do the more setup. Please tell
me exactly what might be the error cause.
Regards,
Joe
> Joe wrote in news:434decd2.576f.1681692777@sybase.com
> of sybase.public.sqlanywhere.mobilink:
>
> J> SQL statement failed: (-780) Cannot update primary key
> for J> table
> J> 'EFB_EfbWpi_waypoint
_info' involved in a publication
> J>
> J> this table data is upto 600000 records. I applied the
> option J> below
>
> Can you confirm that a primary key is attempting to be
> updated?
>
> --
> David Fishburn
> Certified ASA Developer Version 8
> iAnywhere Solutions - Sybase
> Professional Services
> Please only post to the newsgroup
> Please ALWAYS include version and MORE importantly BUILD
> number with EACH post (dbeng9 -v).
>
> EBFs and Maintenance Releases
> http://downloads.sybase.com/swx/sdmain.stm
>
> Developer Community / Whitepapers
> http://www.ianywhere.com/developer
>
> CaseXpress - to report bugs
> http://casexpress.sybase.com
>
> CodeXchange - Free samples
>
[url]http://ianywhere.codexchange.sybase.com/servlets/ ProjectDocumentList[
/url]
>
| |
|
| hi david,
I hope it is trying to update the Primary key value. How
will i check that this is trying to update the primary key
and which value is getting updated.
Regards,
Joe
> Hi David,
> Thanks for the response.
> Yes it is Primary Key column. I read the document it says
> that we can use -b option in dbmlsrv9 for this kind of
> error. is it ok. or i need to do the more setup. Please
> tell me exactly what might be the error cause.
> Regards,
> Joe
>
[url]http://ianywhere.codexchange.sybase.com/servlets/ ProjectDocumentList[
/url][color=darkred]
| |
| David Fishburn 2005-10-27, 8:21 am |
| Joe wrote in news:434e677e.5da7.1681692777@sybase.com
of sybase.public.sqlanywhere.mobilink:
J> Yes it is Primary Key column. I read the document it says
J> that we can use -b option in dbmlsrv9 for this kind of
J> error. is it ok. or i need to do the more setup. Please tell
J> me exactly what might be the error cause.
Updating a primary key is a _very_ bad idea.
That is the reason this option was added, for very good reasons.
Updating primary keys in a distributed database environment introduces a
lot of unnecessary complexities.
If you find you are updating PK columns, this means you have made a poor
choice for the columns of the table. They should be there to uniquely
identify the row, they should not be used for business logic (or
synchronization logic).
People often use them for territory assignments, so if a sales rep
changes territories, they have to update the PKs. This is a bad design.
How and why are you updating PKs?
--
David Fishburn
Certified ASA Developer Version 8
iAnywhere Solutions - Sybase
Professional Services
Please only post to the newsgroup
Please ALWAYS include version and MORE importantly BUILD number with
EACH post (dbeng9 -v).
EBFs and Maintenance Releases
http://downloads.sybase.com/swx/sdmain.stm
Developer Community / Whitepapers
http://www.ianywhere.com/developer
CaseXpress - to report bugs
http://casexpress.sybase.com
CodeXchange - Free samples
[url]http://ianywhere.codexchange.sybase.com/servlets/ ProjectDocumentList[
/url]
| |
|
| Hi,
I am running only this query on download_cursor :
CALL ml_add_table_script(
'default', 'ClientTable_Name,
'download_cursor', 'SELECT col1,col2 FROM SerTable WHERE
modified_datetime >= ?');
what hap. if we do sync. on this query. I don't know when it
is trying to update the primary key and why it is trying to
update it.
Pls explain me how to prevent it and why it is behaving like
this?
Regards,
Joe
> Joe wrote in news:434e677e.5da7.1681692777@sybase.com
> of sybase.public.sqlanywhere.mobilink:
>
> J> Yes it is Primary Key column. I read the document it
> says J> that we can use -b option in dbmlsrv9 for this
> kind of J> error. is it ok. or i need to do the more
> setup. Please tell J> me exactly what might be the error
> cause.
>
> Updating a primary key is a _very_ bad idea.
> That is the reason this option was added, for very good
> reasons. Updating primary keys in a distributed database
> environment introduces a lot of unnecessary complexities.
>
> If you find you are updating PK columns, this means you
> have made a poor choice for the columns of the table.
> They should be there to uniquely identify the row, they
> should not be used for business logic (or synchronization
> logic).
>
> People often use them for territory assignments, so if a
> sales rep changes territories, they have to update the
> PKs. This is a bad design.
>
> How and why are you updating PKs?
>
> --
> David Fishburn
> Certified ASA Developer Version 8
> iAnywhere Solutions - Sybase
> Professional Services
> Please only post to the newsgroup
> Please ALWAYS include version and MORE importantly BUILD
> number with EACH post (dbeng9 -v).
>
> EBFs and Maintenance Releases
> http://downloads.sybase.com/swx/sdmain.stm
>
> Developer Community / Whitepapers
> http://www.ianywhere.com/developer
>
> CaseXpress - to report bugs
> http://casexpress.sybase.com
>
> CodeXchange - Free samples
>
[url]http://ianywhere.codexchange.sybase.com/servlets/ ProjectDocumentList[
/url]
>
| |
| David Fishburn 2005-10-27, 8:21 am |
| Joe wrote in news:434fc66c.1dd5.1681692777@sybase.com
of sybase.public.sqlanywhere.mobilink:
J> I am running only this query on download_cursor :
J>
J> CALL ml_add_table_script(
'default', 'ClientTable_Name,
J> 'download_cursor', 'SELECT col1,col2 FROM SerTable WHERE
J> modified_datetime >= ?');
J>
J> what hap. if we do sync. on this query. I don't know when it
J> is trying to update the primary key and why it is trying to
J> update it.
No, the application is trying to run an update statement against the
"remote" database. The download_cursor should have nothing to do with
it.
--
David Fishburn
Certified ASA Developer Version 8
iAnywhere Solutions - Sybase
Professional Services
Please only post to the newsgroup
Please ALWAYS include version and MORE importantly BUILD number with
EACH post (dbeng9 -v).
EBFs and Maintenance Releases
http://downloads.sybase.com/swx/sdmain.stm
Developer Community / Whitepapers
http://www.ianywhere.com/developer
CaseXpress - to report bugs
http://casexpress.sybase.com
CodeXchange - Free samples
[url]http://ianywhere.codexchange.sybase.com/servlets/ ProjectDocumentList[
/url]
| |
|
| Hi,
Thanks for Response.
We are not doing any updation on sync. I am not able to
understand what it is doing on sync. process to update.
could you explain me please on which condition it is
happening. Pls tell me i am confused with this issue, coz we
are not doing any explicit update or insert on sync. upart
from this download_cursor query.
Regards,
Joe
> Joe wrote in news:434fc66c.1dd5.1681692777@sybase.com
> of sybase.public.sqlanywhere.mobilink:
>
> J> I am running only this query on download_cursor :
> J>
> J> CALL ml_add_table_script(
'default', 'ClientTable_Name,
> J> 'download_cursor', 'SELECT col1,col2 FROM SerTable
> WHERE J> modified_datetime >= ?');
> J>
> J> what hap. if we do sync. on this query. I don't know
> when it J> is trying to update the primary key and why it
> is trying to J> update it.
>
> No, the application is trying to run an update statement
> against the "remote" database. The download_cursor should
> have nothing to do with it.
>
>
> --
> David Fishburn
> Certified ASA Developer Version 8
> iAnywhere Solutions - Sybase
> Professional Services
> Please only post to the newsgroup
> Please ALWAYS include version and MORE importantly BUILD
> number with EACH post (dbeng9 -v).
>
> EBFs and Maintenance Releases
> http://downloads.sybase.com/swx/sdmain.stm
>
> Developer Community / Whitepapers
> http://www.ianywhere.com/developer
>
> CaseXpress - to report bugs
> http://casexpress.sybase.com
>
> CodeXchange - Free samples
>
[url]http://ianywhere.codexchange.sybase.com/servlets/ ProjectDocumentList[
/url]
>
| |
|
| Hi,
Thanks for Response.
We are not doing any updation on sync. I am not able to
understand what it is doing on sync. process to update.
could you explain me please on which condition it is
happening. Pls tell me i am confused with this issue, coz
we are not doing any explicit update or insert on sync.
upart from this download_cursor query.
Here is the result log from the DBMLSync process.
I. 10/13 10:20:10. Synchronization Started
I. 10/13 10:20:10. 1: -c
I. 10/13 10:20:10. 2: ********************
****
I. 10/13 10:20:10. 3: -v+
I. 10/13 10:20:10. 4: -u
I. 10/13 10:20:10. 5:
EFBf191009e83324b9c9
a2f909e606c2976#N142
PH
I. 10/13 10:20:10. 6: -x
I. 10/13 10:20:10. 7: 3m
I. 10/13 10:20:10. 8: -e
I. 10/13 10:20:10. 9: verbose=3don
I. 10/13 10:20:10. 10: -n
I. 10/13 10:20:10. 11:
EFBDATAf191009e83324
b9c9a2f909e606c2976
I. 10/13 10:20:10. 12: -n
I. 10/13 10:20:10. 13:
EFBDOCf191009e83324b
9c9a2f909e606c2976
I. 10/13 10:20:10. Adaptive Server Anywhere MobiLink
Synchronization Version 9.0.1.1899
I. 10/13 10:20:10.
I. 10/13 10:20:10. Copyright =a9 1989-2004 Sybase, Inc.
Portions Copyright =a9 2002-2004,
iAnywhere Solutions, Inc.
I. 10/13 10:20:10. All rights reserved. All unpublished
rights reserved.
I. 10/13 10:20:10.
I. 10/13 10:20:10. This software contains confidential and
trade secret information of
I. 10/13 10:20:10. iAnywhere Solutions, Inc.
Use, duplication or disclosure of the
software and documentation
I. 10/13 10:20:10. by the U.S. Government is subject to
restrictions set forth in a license
I. 10/13 10:20:10. agreement between the Government and
iAnywhere Solutions, Inc. or
I. 10/13 10:20:10. other written agreement specifying the
Government's rights to use the
I. 10/13 10:20:10. software and any applicable FAR
provisions, for example, FAR 52.227-19.
I. 10/13 10:20:10.
I. 10/13 10:20:10. iAnywhere Solutions, Inc., One Sybase
Drive, Dublin, CA 94568, USA
I. 10/13 10:20:10.
I. 10/13 10:20:10. Connecting to remote database
I. 10/13 10:20:10. Loading synchronization information
I. 10/13 10:20:10. Begin synchronizing
'EFBDATAf191009e8332
4b9c9a2f909e606c2976
' for MobiLink user
'EFBf191009e83324b9c
9a2f909e606c2976#N14
2PH'
I. 10/13 10:20:10. Options for this synchronization:
I. 10/13 10:20:10.
V=3d'YES',CTP=3d'TCP
IP',ADR=3d'host=3dSe
rvername'
I. 10/13 10:20:10. Log scan starting at offset 02394174948
I. 10/13 10:20:10. Processing transaction logs from
directory "C:\PROGRAM FILES\ELECTRONIC FLIGHT BAG\Database\"
I. 10/13 10:20:10. Transaction log "C:\PROGRAM
FILES\ELECTRONIC FLIGHT BAG\Database\051013A
A.LOG" starts at
offset 02394141561
I. 10/13 10:20:10. Processing transactions from transaction
log "C:\PROGRAM FILES\ELECTRONIC FLIGHT
BAG\Database\051013A
A.LOG"
I. 10/13 10:20:18. Transaction log ends at offset
02555176407
I. 10/13 10:20:18. Processing transactions from active
transaction log
I. 10/13 10:20:34. Transaction log renamed to: C:\PROGRAM
FILES\ELECTRONIC FLIGHT BAG\Database\051013A
B.LOG
I. 10/13 10:20:34. Processing transaction logs from
directory "C:\PROGRAM FILES\ELECTRONIC FLIGHT BAG\Database\"
I. 10/13 10:20:34. Transaction log "C:\PROGRAM
FILES\ELECTRONIC FLIGHT BAG\Database\051013A
B.LOG" starts at
offset 02555176407
I. 10/13 10:20:34. Processing transactions from transaction
log "C:\PROGRAM FILES\ELECTRONIC FLIGHT
BAG\Database\051013A
B.LOG"
I. 10/13 10:20:34. Transaction log ends at offset
02716182123
I. 10/13 10:20:34. Processing transactions from active
transaction log
I. 10/13 10:20:34. Hovering at end of active log
I. 10/13 10:20:34. Connecting to MobiLink server at
'host=3dLFTEFB1' using 'dbmlsock9.dll'
I. 10/13 10:20:34. Uploading header
I. 10/13 10:20:34. Stream version: 529
I. 10/13 10:20:34. Processor is little-endian
I. 10/13 10:20:34. Character set: cp1252
I. 10/13 10:20:34. MobiLink user name:
EFBf191009e83324b9c9
a2f909e606c2976#N142
PH
I. 10/13 10:20:34. Script version: default
I. 10/13 10:20:34. Synchronization ID: 682375400
I. 10/13 10:20:34. Publication
'EFBDATAf191009e8332
4b9c9a2f909e606c2976
' - Synchronizing -
Log offset 02555176314 - Last download time 1900-01-01
00:00:00.0.
I. 10/13 10:20:34. Publication
'EFBDOCf191009e83324
b9c9a2f909e606c2976'
- Not Synchronizing
- Log offset 02394174948 - Last download time 1900-01-01
00:00:00.0.
I. 10/13 10:20:34. Begin upload
I. 10/13 10:20:34. Uploading publication definition(s)
.....
..
..
I. 10/13 10:24:00. Insert/Update row:
I. 10/13 10:24:00. BALDER
I. 10/13 10:24:00. BALDER
I. 10/13 10:24:00. OFSH
I. 10/13 10:24:00. NULL
I. 10/13 10:24:00. 2717.63
I. 10/13 10:24:00. N
I. 10/13 10:24:00. 9058.05
I. 10/13 10:24:00. W
I. 10/13 10:24:00. 2
I. 10/13 10:24:00. N
I. 10/13 10:24:00. NULL
I. 10/13 10:24:00. NULL
I. 10/13 10:24:00. 0
I. 10/13 10:24:00. NULL
I. 10/13 10:24:00. NULL
I. 10/13 10:24:00. NULL
I. 10/13 10:24:00. 0
I. 10/13 10:24:00. NULL
I. 10/13 10:24:00. NULL
I. 10/13 10:24:00. NULL
I. 10/13 10:24:00. NULL
I. 10/13 10:24:00. NULL
I. 10/13 10:24:00. NULL
I. 10/13 10:24:00. 2005-10-08 04:56:41.0
I. 10/13 10:24:00. 2005-10-08 04:56:41.0
I. 10/13 10:24:00. PILOT
I. 10/13 10:24:00. Insert/Update row:
I. 10/13 10:24:00. MARAN
I. 10/13 10:24:00. MARRANAS
I. 10/13 10:24:00. OFSH
I. 10/13 10:24:00. OFSH
I. 10/13 10:24:00. 2837.52
I. 10/13 10:24:00. N
I. 10/13 10:24:00. 09200.47
I. 10/13 10:24:00. W
I. 10/13 10:24:00. 0
I. 10/13 10:24:00. N
I. 10/13 10:24:00. NULL
I. 10/13 10:24:00. NULL
I. 10/13 10:24:00. 0
I. 10/13 10:24:00. NULL
I. 10/13 10:24:00. NULL
I. 10/13 10:24:00. NULL
I. 10/13 10:24:00. 0
I. 10/13 10:24:00. NULL
I. 10/13 10:24:00. NULL
I. 10/13 10:24:00. NULL
I. 10/13 10:24:00. NULL
I. 10/13 10:24:00. NULL
I. 10/13 10:24:00. NULL
I. 10/13 10:24:00. 2005-10-08 15:42:36.0
I. 10/13 10:24:00. 2005-10-08 15:42:36.0
I. 10/13 10:24:00. PILOT
I. 10/13 10:24:00. Insert/Update row:
I. 10/13 10:24:00. SM60
I. 10/13 10:24:00. MARRANAS
I. 10/13 10:24:00. OFSH
I. 10/13 10:24:00. OFSH
I. 10/13 10:24:00. 2837.52
I. 10/13 10:24:00. N
I. 10/13 10:24:00. 09200.47
I. 10/13 10:24:00. W
I. 10/13 10:24:00. 0
I. 10/13 10:24:00. N
I. 10/13 10:24:00. NULL
I. 10/13 10:24:00. NULL
I. 10/13 10:24:00. 0
I. 10/13 10:24:00. NULL
I. 10/13 10:24:00. NULL
I. 10/13 10:24:00. NULL
I. 10/13 10:24:00. 0
I. 10/13 10:24:00. NULL
I. 10/13 10:24:00. NULL
I. 10/13 10:24:00. NULL
I. 10/13 10:24:00. NULL
I. 10/13 10:24:00. NULL
I. 10/13 10:24:00. NULL
I. 10/13 10:24:00. 2005-10-08 15:42:36.0
I. 10/13 10:24:00. 2005-10-08 15:42:36.0
I. 10/13 10:24:00. PILOT
E. 10/13 10:24:00. SQL statement failed: (-780) Cannot
update primary key for table 'EFB_EfbWpi_waypoint
_info'
involved in a publication
I. 10/13 10:24:00. ROLLBACK
I. 10/13 10:24:04. Download stream processing failed
I. 10/13 10:24:04. End synchronizing
'EFBDOCf191009e83324
b9c9a2f909e606c2976'
for MobiLink user
'EFBf191009e83324b9c
9a2f909e606c2976#N14
2PH'
I. 10/13 10:24:04. Disconnecting from MobiLink server
I. 10/13 10:24:04. Discarded memory to be recovered at next
complete rescan: 0 bytes.
I. 10/13 10:24:04. Complete log scan required.
I. 10/13 10:24:04. Synchronization completed
I. 10/13 10:24:04. Disconnecting from remote database
Regards,
> Joe
>
[url]http://ianywhere.codexchange.sybase.com/servlets/ ProjectDocumentList[
/url][color=darkred]
| |
| Greg Fenton 2005-10-27, 8:21 am |
| Joe wrote:
> We are not doing any updation on sync. I am not able to
> understand what it is doing on sync. process to update.
Do you have any triggers on your tables in the remote?
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/
| |
|
| Hi,
Thanks for response.
We don't have any triggers in our client.
Regards,
Joe
> Joe wrote:
>
> Do you have any triggers on your tables in the remote?
>
> greg.fenton
> --
> Greg Fenton
> Consultant, Solution Services, iAnywhere Solutions
> --------
> Visit the iAnywhere Solutions Developer Community
> Whitepapers, TechDocs, Downloads
> http://www.ianywhere.com/developer/
| |
| David Fishburn 2005-10-27, 8:21 am |
| Joe wrote in news:434decd2.576f.1681692777@sybase.com
of sybase.public.sqlanywhere.mobilink:
J> We are using ASA 9.0.1.1899 version for the sync. process
J> with Oracle. this process doing well. suddenly we got the
J> error Like
J>
J> SQL statement failed: (-780) Cannot update primary key for
J> table
J> 'EFB_EfbWpi_waypoint
_info' involved in a publication
J>
J> this table data is upto 600000 records. I applied the option
J> below
Since we aren't getting anywhere on this one, lets start over.
Start ML using -v+ -ot ml.txt
Start dbmlsync using -v+ -e verbose=ON -o rem.txt
Synchronize.
Make sure the error is reported.
Show the create table statement (from the remote database) for the table
in question. This can be easily found using Sybase Central (by right
clicking on the table in the right side panel and choosing copy, then
paste it into this reply) or via dbunload using the -n (no data, just
schema) switch. Just cut and paste the create table statement from the
reload.sql file into your response.
--
David Fishburn
Certified ASA Developer Version 8
iAnywhere Solutions - Sybase
Professional Services
Please only post to the newsgroup
Please ALWAYS include version and MORE importantly BUILD number with
EACH post (dbeng9 -v).
EBFs and Maintenance Releases
http://downloads.sybase.com/swx/sdmain.stm
Developer Community / Whitepapers
http://www.ianywhere.com/developer
CaseXpress - to report bugs
http://casexpress.sybase.com
CodeXchange - Free samples
[url]http://ianywhere.codexchange.sybase.com/servlets/ ProjectDocumentList[
/url]
|
|
|
|
|