How Long Before Sync W/o Primary Key

[reyn]

I have the connections sorted between my local site and remote site using a trial version, what a disapointment it was to find out that without a Primary key the db will not sync.

When do you envisage that this feature will be removed? 😀

I really only require it for this feature and I cannot alter the db tables.

Thank You

Frank

[peterlaursen]

Did you read this paper of mine?

http://www.webyog.com/forums/index.php?act=ST&f=2&t=1507

Quote:

I really only require it for this feature and I cannot alter the db tables.

Well, however ANY sync mechanism will need to be able to UNIQUELY identify each row of data. A Primary Key is the most simple way. In my paper I proposed an option to use any unique index as well. That should work too. But if you really can't alter the tables at all, that would not help you either. And besides I don't think that feature is “just around the corner”.

I think you are doing a mistake. Maybe YOU are able to identify which row of data on each host that correspond to one another. That is then because it is your data and you know them. However a computer will need a PK or a unique index to find out which row of data on each host that correspond.

What your are requesting is a magician, not a program 😛

[Ritesh]

Quote:

When do you envisage that this feature will be removed?

We plan to add a feature in v4.4 or v4.5 wherein you can sync two tables without PRIMARY KEY index.

Quote:

Well, however ANY sync mechanism will need to be able to UNIQUELY identify each row of data

SJA will then use UNIQUE INDEX if PRIMARY KEY index is not found. If the user does not have any UNIQUE or PRIMARY index on the table, SJA will simply drop the target table and insert all rows from source to target.

Any ideas or suggestion is highly appreciated.

[reyn]

Thank you both for your answers, I have now read the whitepaper, which I must say is very clearly written, and I follow the problem.

I am not requiring anything, just trying to find a way to synchronise two database's that I cannot modify, at the moment I use the phpMyAdmin Export and Import SQL method which is very clunky. I use the free version of SQLyog and was trying out the Enterprise version to see if synchonising was possible. When the Unique Index version is available I will try that, all the tables have either a Primary or Unique key.

Thanks again

Frank

[Ritesh]

We will be supporting UNIQUE INDEX in v4.2. SJA will then follow the given sequence:

1.) If PK found, do a sync using PK.

2.) If PK is not available and UNIQUE INDEX is present, SJA will use the UNIQUE INDEX.

3.) If there are multiple UNIQUE INDEXES on a table, SJA will use the first UNIQUE INDEX.

[peterlaursen]

1.) If PK found, do a sync using PK.

2.) If PK is not available and UNIQUE INDEX is present, SJA will use the UNIQUE INDEX.

3.) If there are multiple UNIQUE INDEXES on a table, SJA will use the first UNIQUE INDEX.

change to

1.) If identical PK's found, do a sync using PK.

2.) If PK's are not available or not identical and identical UNIQUE INDEXes are present, SJA will use the UNIQUE INDEX.

3.) If there are multiple UNIQUE INDEXES on a table on both hosts, SJA will use the first UNIQUE INDEX identical on both hosts.

… what I also believe was what you meant!

[gberz3]

Would it not also be possible, in the event of no PK or UNIQUE ID, to at least compare existing rows for presence then add complete rows based on that? I know that using PYTHON there are a few lambda functions where you can compare lists for individual rows and basically say “complete rows in (or not in) another list” then add the rows you need. Granted, this might result in “duplicate” entries with differing data, but I'd rather have that than to completely DROP another table. Just my 2¢.

[peterlaursen]

Isn't that almost the same thing that we do now?

http://webyog.com/faq/11_62_en.html

But I strongly recommend that if you use DATA SYNC without a PK that you understand how it works.

In this article

http://webyog.com/articles/Using_SQLyog_En…L_Databases.pdf

there is a paragrahp 'sync'ing without a Primary Key' at the end.

Quoting:

Quote:

Let us now describe more precisely how sync without a PK works:

1) First a number is retrieved by executing this SQL on the target with data being taken from the

source:

select count(*) from targetdb.table where col1=data, col2=data…..

If the count(*)-number is not zero in the target then the row exists in the target (as well as the

source). Then there is not anything more to do with that row (row exists in both source and

target) and the same operation is repeated for the next row of the source.

2) If the count(*)-number is zero then the row does not exist in target and a:

insert into targetdb.table values ( data1, data2,…. )

is generated and executed it in the target. This is the explanation that the two rows (that both

have been changed) are now considered to be two different rows. Actually there is no question

of UPDATE as it is not possible know which columns(s) to update. The select count(*)

statement only retruns a number (typically “0” or “1”). Or to put it another way: it returns

information of the existence of the data – not about the data itself! And querying detailed

information of each row of data is out of question for performance reason.

3) Finally SJA check for the need to perform DELETES: if the count(*) with data from the

target returns a non-zero number when executed on the target and a zero-number on the source

the row in target is deleted. Otherwise the process moves on to the next row.

Also note that the sync without a PK as it is described here is not able to distinguish between

situations where there is one row with a certain dataset and more identical rows with the same

dataset on the same server. More identical rows in source will result in sync to one row in target

only and one row in source identical to more identical rows in target will not trigger any action.

One row and more identical rows on the same host are simply considered identical situations.

[Author]

Posts