SQL anywhere 12-mobilink- START SYNCHRONIZATION SCHEMA CHANGE - Alter publication Add Table Fails...

[Former Member]

Hi,

I've been struggling with adding tables to an existing publication.

I'm constantly getting the following message... "This operation is only allowed within a synchronization schema change " http://dcx.sybase.com/index.html#1201/en/saerrors/errm1329.html

I' using SQL Anywhere 12... This must be some sort of security setting I'm sure... Just don't know what I should do in order to fix it...

for adding columns I'm using the following command: "START SYNCHRONIZATION SCHEMA CHANGE", But here I have to specify the tables so for adding columns this works perfectly... but adding new tables... wouldn't know how to handle it...

any tips are welcome !

EDIT: In previous version SQL Anywhere 11, this error would not occur (I can see this in legacy code). Also in order to add columns the "Drop publication" command would be used. I've tried this, but getting the same error.... But when looking at the following thread, the accepted answer says this should still be possible in sql anywhere 12....

http://sqlanywhere-forum.sap.com/questions/9479/fail-to-change-column-type-in-sybase-central-sqlanyw...

[VolkerBarth]

Just to understand:

• When trying to add a column to a table that is already part of an article of an existing publication, you do use the "START SYNCHRONIZATION SCHEMA CHANGE" statement.

• When trying to add a new table to a publication, you do not use the "START SYNCHRONIZATION SCHEMA CHANGE" statement, you simply use the ALTER PUBLICATION myPub ADD TABLE myTable statement.

(From reading the docs, I would expect that to be the correct way but the docs seem somewhat unclear here... - even for v16.)

[Former Member]

Exactly, and the strange thing is, that for one table it works but for another it does not work...

this is the current status... the tables with the RED arrows, do work when performing "ALTER PUBLICATION myPub ADD TABLE myTable"... the one with the blue Arrow does not work...

https://dl.dropboxusercontent.com/u/3246050/DBDiagram.jpg

may have something to do with relationships I'm guessing...

Will do some further tests...

[Former Member]

Okey, here are the results:

https://dl.dropboxusercontent.com/u/3246050/DBDiagramUpdate.jpg (The red arrows are working fine) (the blue ones fail)

Any table that has a foreign key makes this error occur.

I respect the order of creation so first I execute "Add Table" for the parent table, then the child table...

When I just remove the foreign key... the blue Arrow tables also work for "ALTER PUBLICATION myPub ADD TABLE myTable"...

not sure what I should do now...tips are welcome !

EDIT: updated picture

[Former Member]

Okey,

I think I know what's happening after a few tests....

For some reason SQL anywhere considers a foreign key as a modification and complains with the following error: "This operation is only allowed within a synchronization schema change "

So the only way to make this work is to first add the tables to the remote database without foreign keys, then add them to the publications and afterwards add the foreign keys to the tables.....

Unfortunately for me this is turning in to a near nightmare scenario since I'm using Microsoft Entity Framework to generate upgrade scripts....

[Breck_Carter]

Does the table exist on the remote database, and you are merely adding it to the publication?

Or are you both creating the table on the remote, AND adding it to the publication? The CREATE is a schema change, the other is an alteration to a publication, two different concepts.

As far as "generating upgrade scripts" via mechanized tools is concerned, that may not be a good idea... every upgrade will be different, and the amount of rote coding work is very small compared to the design and testing effort. In other words, write the upgrade code manually... mechanized tools are only good for rote coding, they cannot think for you.

[Former Member]

I'm doing both.

Create the table =>this works

alter publication (add the table to publication) => this only works if there is no foreign key to a newly created table...

---

in order to reproduce, we need to create 2 tables, with 1 table having a foreign key to the other new table.

Then (after creation) add both to the publication => FAIL

--

I cannot pinpoint the exact cause... But If I have to guess, I think that when these scripts execute inside 1 and the same open database connection, somehow SQL Anywhere gets a bit confused and thinks that the foreign key constraint should be inside a "Synchronization schema change"...

Entity framework just generates the "Create Table" + "Add Column" + "add foreign/primary keys..." etc...

updates to publications I add manually since "Entity framework" knows nothing of synchronization...

But I think Entity framework just runs all queries in 1 open connection... And now by creating a seperate Upgrade script I create a workaround for this problem....

[Former Member]

PS. I tried to add a "commit;" statement in between the creation of tables and adding tables to publication but no luck... will have to continue with my elaborate workaround

[VolkerBarth]

FWIW, even the builtin unload/reload tools are known to separate the creation of a table and its FKs, i.e. the FKs are typically created in a delayed fashion with separate ALTER TABLE statements. There are some performance advantages to do so, as Ivan has explained in detail here...

Note, that's not meant as an answer to your requirement (and it surely is none), it may just give a hint as why ML does treat FKs that (at least surprising) way...