detect changes only for specific columns

[Mackenzie]

When I sync tables selecting particular columns only (with identical data) still the record is updated if there's a difference in data in one of the other columns (the not-selected ones). Am I misinterpreting the use of this feature?

[Shadow]

Yes, you are misinterpreting this feature. SQLyog uses checksums to detect chages done to a record. If you select specific columns, then SQLyog will use only those columns to generate checksums, therefore if any other column gets changed, the change won't be detected by SQLyog.

[Mackenzie]

I think we both are saying the same thing, but that feature didn't work for me then.

To get it straight : w,x,y,z is data from a record:

column | source db | target db

1 | x | x

2 | y | y

3 | z | w

I do a checksum on column 1 & 2 only (and the 'ALL' option is not selected). The record shouldn't be updated in the target db right? It did in my case when I tested it! I only downloaded a trial here yesterday, but I assume that the trial isn't any different from the registered version when it comes to this feature.

Can you doublecheck this isn't a bug, but just me doing something wrong or still not understanding the purpose of this feature?

[Ritesh]

SJA is behaving correctly. It ignores the selected columns while generating checksums. If the other columns result in different checksums then the whole row is modified in the target db and not the columns that were selected.

This feature is useful when you dont want to check for changes in BLOB data to reduce the computation time.

[Shadow]

Wait a second, Ritesh! A quote from the help file:

Quote:

Additionally, you can configure SJA to detect changes only for specific columns. This makes it an ideal tool to sync data even if there is limited bandwidth. Since SQLyog uses checksums to detect changes unselecting BLOB data may significantly speed up the process as these columns will not be used to generate checksums.

In my interpretation sja uses the values of the selected columns to generate checksums not vica versa! Actually, it works for me the above descibed way…

Mackenzie: there may be other options you didn't set in the right way. Could you post sample data in this forum that don't work for you?

[Mackenzie]

Thanks for your help guys. I don't know what sample data I can give from which to conclude the option settings from, but try this:

Load the .sql script. There are 2 example db's in it : 'source' and 'target', with one identical table 'tablename1' (infact, 'target' was a copy of 'source'). The table holds one record with a primary key and 3 columns : column1 (int), column2 en column3 (both varchar). The last 2 columns have different input.

Now this is what I did (this is SQLYog v3.71 on a win2000), I selected ONLY column1 from tablename1 (the value in both dbs for the record is 1, so the checksums should also be identical (if I understand this feature), therefore the record, the entire row should not be updated. Yet…it did…no idea why 🙁

Table SrcRows TgtRows Inserted Updated Deleted

========================= ======= ======= ======== ======= =======

`tablename1` 1 1 0 1 0

I also tried it the other way by deselecting. Each time the record got updated in the target db. What could alter the checksums other than the numerical value of column1 ?

[Mackenzie]

an sql script would be nice 🙂

[Shadow]

Interesting. If id is used (it has the same value in both tables), then sync works as expected. If column1 is used, however, the row gets updated despite the fact that the values are the same.

Is it a bug, Ritesh?

[Ritesh]

Shadow wrote on Jun 30 2004, 02:05 PM:

Wait a second, Ritesh! A quote from the help file:

Quote:

Additionally, you can configure SJA to detect changes only for specific columns. This makes it an ideal tool to sync data even if there is limited bandwidth. Since SQLyog uses checksums to detect changes unselecting BLOB data may significantly speed up the process as these columns will not be used to generate checksums.

In my interpretation sja uses the values of the selected columns to generate checksums not vica versa! Actually, it works for me the above descibed way…

Mackenzie: there may be other options you didn't set in the right way. Could you post sample data in this forum that don't work for you?

Ooopps. I was wrong with that statement. SJA will use the selected columns to generate checksums and not the other way round. Sorry 🙄

[Shadow]

And what about the possible bug?

[Ritesh]

My developers are working on it 😀

[Mackenzie]

Thanks guys. Great follow-up!

[Ritesh]

Interesting. It works great for me. I recreated the source and target db on my 4.1.1-alpha-max-nt server. I used the following two schemas:

Code:

localhost
root 3306 source

localhost
root 3306 target

`tablename1`
`column1`

and

Code:

localhost
root 3306 source

localhost
root 3306 target

`tablename1`
`id`

both the cases I got the same result:

Code:

SQLyog Job Agent Version 2.0
Copyright (c) Webyog. All Rights Reserved.

Sync started at Fri Jul 02 19:38:18 2004

Table                      SrcRows  TgtRows  Inserted  Updated  Deleted
=========================  =======  =======  ========  =======  =======
`tablename1`                     1        1         0        0        0  

Total time taken – 0 sec(s)

[Mackenzie]

found the problem thanks to your xml scheme, Ritesh.

I saved my job to an xml too to see the difference. The first time mine said :

Want to know why? I clicked the 'next' button without 'verifying' my column-selection. I assumed my selecting would automatically be saved when i click 'next'. By clicking outside the dropdownbox (with the columns) the selection is being saved. I skipped that part and I took the saving for granted after clicking the 'next' button (windows-habit I'm afraid).

So no need to consider this as a bug, but maybe confusing to other users as well. Anyway, I take full responsibility for my acts! Thanks again guys! 😀

[Mackenzie]

Missing a piece in my prev mail : first time my xml-sheme said : columns all=”yes”

[Shadow]

That's right, I thought the job file got modified. It didn't…

[Author]

Posts