Error: Primary key in table article_cols not unique during dbxtract

[Former Member]

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.

Thanks

[Breck_Carter]

A table can only have one primary key. In your case, it is likely that the primary key for "article_cols" consists of Key1 and Key2 because the error messages says "Primary key value ('720, 1' )".

So, each of your publications only specifies ONE column of the primary key, leaving the other column unspecified... it is not surprising that an extract of both subscriptions into the same table will contain overlapping rows. This would be a problem in Version 8, but perhaps there were no overlaps before... it depends on the data.

[regdomaratzki]

Overlapping rows with respect to the data in the two publications is not an issue, the problem here is in the unload code determining the columns that need to be extracted.

Let's assume the sample schema I posted in my answer, and the following four rows of data in table 't1'.

(1,1,'one','one')

(1,2,'one','two')

(2,1,'two','one')

(2,2,'two','two')

If I extract user rem1, I would expect the first three rows to be extracted into the new database. The fac that the first row meets two requirements during the extract should not be an issue.

[regdomaratzki]

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.

Thanks, Reg