Using Sybase Anywhere 12, I'm trying to extract a database for a remote user, and I'm recieving an error concerning the system table article_cols; error stating that primary key is not unique. I'm able to reproduce the error with the following simplified example.
A single table with two interger fields Key1 and Key2, that are both primary keys The table also includes two text fields Name, and SomeValue... but probably not important to this example.
Two publications are created, each including an article for this table. Both publications include all the four columns of the table. The only difference between the two publications is PubA is SUBSCRIBE BY column Key1.. while PubB is SUBSCRIBE BY column Key2.
A remote user is created, and includes SQL Remote Subscriptions to both of these publications.. providing appropriate Subscription Values for each.
When I attempt to extract the database for this user... error occurs: SQL error: Primary key for table 'article_cols' is not unique: Primary key value ('720, 1').
This functionality was possible in Sybase 8.. and I want to confirm that the scenario is not supported in Sybase 12.
asked 25 Feb '13, 09:22
Interesting. This is a side effect of the re-write we did of the unload code in v10, so you are the first person to run into this problem since we released v10 back in September of 2006. The article_cols table is a temporary table we create during the unload process.
The problem has nothing to do with data in the table. I can reproduce the problem with SQL below, and then attempting to run dbxtract against the consolidated database for rem1.
GRANT CONNECT TO cons; GRANT PUBLISH TO cons; CREATE REMOTE MESSAGE TYPE FILE ADDRESS 'cons'; CREATE TABLE t1 ( pk1 bigint NOT NULL, pk2 bigint NOT NULL, c1 char(64) NULL, c2 char(64) NULL, PRIMARY KEY (pk1,pk2) ); CREATE PUBLICATION p1 ( TABLE t1(pk1,pk2,c1,c2) SUBSCRIBE BY pk1 ); CREATE PUBLICATION p2 ( TABLE t1(pk1,pk2,c1,c2) SUBSCRIBE BY pk2 ); GRANT CONNECT TO rem1; GRANT REMOTE TO rem1 TYPE FILE ADDRESS 'rem1'; CREATE SUBSCRIPTION TO p1('1') FOR rem1; CREATE SUBSCRIPTION TO p2('1') FOR rem1;
The issue revolves around how we determine which columns in table t1 need to be extracted for remote rem1. We only need to do this if if you specified a column subset in your create publication statements. If the schema above mirrors your schema, you can work around the issue by changing your publication definitions so that the unload process does need to figure out which columns are needed in the remote database.
CREATE PUBLICATION p1 ( TABLE t1 SUBSCRIBE BY pk1 ); CREATE PUBLICATION p2 ( TABLE t1 SUBSCRIBE BY pk2 );
If this resolves your issue, I'd say we're done. If it doesn't, can you please post the exact schema of the table in question, as well as the publication defintions that reference the table.
answered 25 Feb '13, 16:53