WHERE -clause with SJA

[peterlaursen]

How could I make SJA work with a WHERE – clause like

WHERE TimeModified > 2005051500000; // (records modified later than 15th may 2005)

or

WHERE TimeModified > CURRENT_TIMESTAMP – 31000000; // (records modified within the last 31 days)

or

WHERE TimeModified > CURRENT_TIMESTAMP – 100000000; // (records modified within the last month)

Both work as a WHERE-clause with a SELECT SQL-expression from the sql-pane in sqlyog, but with SJA it returns:

Code:

Table                      SrcRows  TgtRows  Inserted  Updated  Deleted
=========================  =======  =======  ========  =======  =======
`mp3_filer`                  37107    37107         0        0  

(no updates though there should be, and it seems like deletion is totally skipped!)

if I should specify on which of the hosts to test with the WHERE-clause I can't find the way to do it!

BTW:

The examples of how to use the WHERE-clause in the documentation and the SQLyog website are COMPLETELY MEANINGLESS! If something like the above can't be done it's worth nothing!

[peterlaursen]

and further …

1)

Wouldn't it be right/consequent to have one WHERE -clause used at source and another at target ? That could be either AND'ed or OR'ed (and maybe even EXOR'ed etc, though that is probably more “eye-catch” than a real useful feature)

2)

how to express “WHERE source.TimeModified <> target.TimeModified” ?

3)

actually using the symbolic adressing “source.column_name” and “target.column_name” could be an idea ??

The idea with my “project” was that I know for certain that less than 2% of the rows have been changed within the last month. Then I naively assumed that using the WHERE-clause would make sync at least 50 times faster 😀

Maybe I'm totally wrong in my expectations, but then please explain what the WHERE-option with SJA is for …

[peterlaursen]

OK then 😀

it seems that the most trouble was caused by writing “tablename.columnname” instead of just “columnname”. The first way seems to produce different errors. So it's not so bad though. Although very badly documented!

I now assume that the WHERE-clause is tested with the source!? Correct ?? Always?? For both 1-way and 2-way sync ??

I came across one thing more:

1) On the target run this: UPDATE SET mychar = 'newstring' WHERE mychar = 'oldstring';

2) Run SJA with the clause WHERE ” mychar = 'newstring' “

3) The row UPDATED first with 1) are now deleted on the target. I don't see the logic!!! Since no rows in the source fullfill the WHERE (in source value is still 'oldstring') nothing should happen I think!

I believe I'm able to run a test case involving about 500-700 rows out 35000. I'll return … hopefully not with fury in my mind!

[Ritesh]

First of all you dont need to specify the word WHERE in the where clause. SJA will automatically add WHERE.

Can you strip down the problem to less number of rows so that we can work on it and fix the bug if there are any?

[peterlaursen]

1)

I don't specify the word WHERE, so that's not the problem !! After all SJA won't start at all if I specify WHERE, so that could hardly be done wrong!

2)

I shall try to get closer to the problem!

3)

but the confusion came from this: writing “tablename.columnname” instead of just “columnname” generates errors with the SJA.

4)

And this one:

1) On the target run this: UPDATE SET mychar = 'newstring' WHERE mychar = 'oldstring';

2) Run SJA (1-way)with the clause WHERE ” mychar = 'newstring' “

3) The rows UPDATED first with 1) are now deleted on the target. I don't see the logic!!! Since no rows in the source fullfill the WHERE (in source value is still 'oldstring') nothing should happen I think!

.. I think still is true!

5)

and I'd like you shortly to answer this:

“I now assume that the WHERE-clause is tested with the source!? Correct ?? Always?? For both 1-way and 2-way sync ??”

That would make it a lot easier to dig into it if one knew how it is supposed to work!

BTW: I came across too that the ODBC-import wizard converts timestamps with NULL-values to “current_timestamp”-values even if no default is set with the wizard. If I export from MS-Access that does not occur. Is that an issue with SQLyog or due to the fact that two different ODBC-drivers are used ?

That means that I'll have to make new test data .. It might not be until next week that I get the time for it!

[peterlaursen]

sync finished …

using a WHERE clause where only 1057 rows (in source) “qualified”

resulted in

Table SrcRows TgtRows Inserted Updated Deleted

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

`mp3_filer` 37325 37090 280 770 10

it took 35-40 minutes where as a sync without a WHERE clause with the same data would be somewhat more than 10 hours

That was the main intention with the exercise to reduce sync … so far so good.

But there is some inconsistency:

1)

280+770+10 = 1060. How could more rows be affected than the number that “qualify” with the WHERE-clause ?

2)

There are now 35 rows “too many” in target. But that could simply be an error with the date used with where. I'll run it again with a “safe” date ..

[peterlaursen]

I am currently running this XML file with newly imported data from Access …

(you have data for the asterisks!)

localhost

********

******** 3306

*******

http://www.deepeter.dk/SQLyogTunnel.php

2112834

mydb1.surftown.dk

*********

******** 3306

***********

`mp3_files`

DateAdded > 20050500000000 or Modtime > 20050500000000

In the first run I used the where

“DateAdded > 20050515000000 or Modtime > 20050515000000”, but 15th May could be a little too “optimistic”. But 1st may is “safe”. Data are “younger” than that – I guarantee!

It is the column “DateLastPlayed” that is corrupted by ODBC-import (should be NULL if TrackPlayCount is NULL), but you won't have to use it for the test case.

there is plenty of room on the server. So just dublicate the table before working with it.

[peterlaursen]

forgot the CODE-tag 😡

Code:

localhost
******** ******** 3306 *******

http://www.deepeter.dk/SQLyogTunnel.php
2112834

mydb1.surftown.dk
********* ******** 3306 ***********

`mp3_files`
DateAdded > 20050500000000 or Modtime > 20050500000000

In the first run I used the where
“DateAdded > 20050515000000 or Modtime > 20050515000000”, but 15th May could be a little too “optimistic”.  But 1st may is “safe”.  Data are “younger” than that – I guarantee!

It is the column “DateLastPlayed” that is corrupted by ODBC-import (should be NULL if TrackPlayCount is NULL), but you won't have to use it for the test case.

there is plenty of room on the server.  So just dublicate the table before working with it.

[peterlaursen]

And

Modtime > 20050500000000 must be sufficient! Since ModTime is always equal to DateAdded or later.

[peterlaursen]

Quote:

BTW: I came across too that the ODBC-import wizard converts timestamps with NULL-values to “current_timestamp”-values even if no default is set with the wizard. If I export from MS-Access that does not occur. Is that an issue with SQLyog or due to the fact that two different ODBC-drivers are used ?

Here it comes! That of course is the reason why sync is so slow in my case! Because with each new ODBC-import the rows change if this particular TIMESTAMP-value is NULL (and it is in about 32000 out of 37325). And then of course SJA will UPDATE. It would not if NULL-value from the Access databae was correctly written to MySQL as NULL. But it writes a TIMESTAMP of import-time!

[peterlaursen]

By …

1) omitting the TIMESTAMP-field (DateLastPlayed) that ODBC handles incorrectly

2) using ONLY the PK and the “Modtime” field for compare .. (modtime will ALWAYS change if data are changed by the application using and generating the data . SJA can't know, but I know!)

3) and using WHERE (last update not older than a month) a sync operation that involves around 2000 rows is done in about 15 minutes.

That was what I was after !

If I had not had this ODBC-import problem and not adressing like “tablename.columnname” in the first run I might have avoided trouble at all. But wisdom grows from ignorance …

[peterlaursen]

see pic below 😀

this WHERE-clause modtime > (SELECT now() – INTERVAL 1 month) also works with SJA (of course – it's the MySQL server parsing in both cases).

You don't have to SELECT NOW() – you can SELECT FROM … And that means that you can write CURRENT_TIMESTAMP with a simple script or an application to a seperate TABLE and COLUMN (call the table lastsync and the column syncstart) just before starting SJA. And next time you sync you use the

WHERE modtime > (SELECT syncstart FROM lastsync).

For security you could use WHERE modtime > ((SELECT syncstart FROM lastsync) – INTERVAL 1 MINUTE).

Managing modtime is simple. It should be declared NOT NULL DEFAULT CURRENT_TIMESTAMP, and when the application is updating or inserting a row should explicitly be set to NULL.

See that's the sort of ideas I would expect from the SQLyog documentation .. and with documented and working examples …. 😀

[peterlaursen]

exactly like this (with this syntax it takes MySQL 4.1.1 or higher)

[peterlaursen]

I have one more question concerning this (now) rather confused matter 🙄

Does the SQL_WHERE option in the job-file have effect

1)for INSERTS+UPDATES

or

2)for UPDATES only

(think the the latter should be the case since the PK could perfectly control INSERTS and DELETES).

Besides the WHERE- will never find deleted data so I can't see how it will ever work with DELETES.

Of course I could test it with some sample data, but you would probably know !?

[peterlaursen]

Coming closer 🙂

In this case I have a TIMESTAMP field named 'modtime' and a 25 char-type name 'GUID'. GUID is the PK.

this XML

Code:

`mp3_filer`
ModTime modtime > current_timestamp – 5000000

Sync started at Thu Jun 02 03:24:45 2005

Table SrcRows TgtRows Inserted Updated Deleted

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

`mp3_filer` 37410 37325 146 501 61

Total time taken – 1240 sec(s)

(correct)

This XML

Code:

`mp3_filer`
´`ModTime“ modtime > current_timestamp – 5000000

procucs this erroneous result

Table SrcRows TgtRows Inserted Updated Deleted

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

`mp3_filer` 37410 37325 0 0

Total time taken – 7 sec(s)

and the error message:

Error No. 1064

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ModTime“`)),1,8),16,10)),

To point it out: NOT using backquotes works, using doble (or more) backquotes does not. Using exactly one backquote works too!

And it is actually very easy to make make the wizard place multiple backquotes in the XML. Just open the jobfile several times with the wizard.

The problem with table.column -adressing that I have postulated before might also have been a backquote problem. this XML

Code:

`mp3_filer`
`mp3_filer`.`ModTime` modtime > current_timestamp – 5000000

now works correct

And there is one problem more with the wizard:

and when you first added a column to the colums-list with the wizard it is almost impossible to get rid of it again, unless you open the XML-file in an editor and delete the lines.

This small parser/XML-writer-module or whatever you call it is very poor. You won't pass IT-exam in highschool with this one. 🙁

[Ritesh]

Quote:

I now assume that the WHERE-clause is tested with the source!? Correct ?? Always?? For both 1-way and 2-way sync ??

Nope. The WHERE clause is checked for both source and target. SJA always executes the same query to both source and target.

[Ritesh]

peterlaursen wrote on May 30 2005, 09:03 AM:

I came across one thing more:

1) On the target run this: UPDATE SET mychar = 'newstring' WHERE mychar = 'oldstring';

2) Run SJA with the clause WHERE ” mychar = 'newstring' “

3) The row UPDATED first with 1) are now deleted on the target. I don't see the logic!!! Since no rows in the source fullfill the WHERE (in source value is still 'oldstring') nothing should happen I think!

Its correct. The same where clause fulfills the criteria in the target but they are not existing in the source. Thus those rows are deleted in the target server as expected.

[Ritesh]

peterlaursen wrote on Jun 1 2005, 01:22 PM:

Does the SQL_WHERE option in the job-file have effect

1)for INSERTS+UPDATES

or

2)for UPDATES only

Both.

[Ritesh]

peterlaursen wrote on May 30 2005, 10:44 AM:

`mp3_filer` 37325 37090 280 770 10

But there is some inconsistency:

1)

280+770+10 = 1060. How could more rows be affected than the number that “qualify” with the WHERE-clause ?

Insert/Update/Deleted data consists number from both source and target. Thus it need not be necessarily same as numbers of Source server.

[Ritesh]

I guess its a bug with the GUI wizard provided with SQLyog. I have forwarded the issue to my development team.

[peterlaursen]

Quote:

Insert/Update/Deleted data consists number from both source and target. Thus it need not be necessarily same as numbers of Source server.

OK – that makes sence with a two-way sync. But this case was a one-way sync!

In case of two way-sync It would be nice it it displayed inserted, updated and deleted for each host independently.

[Ritesh]

Ritesh wrote on Jun 13 2005, 07:22 AM:

I guess its a bug with the GUI wizard provided with SQLyog. I have forwarded the issue to my development team.

Bug fixed in v4.07 development tree.

[peterlaursen]

Quote:

Q1: I now assume that the WHERE-clause is tested with the source!? Correct ?? Always?? For both 1-way and 2-way sync ??

A1: Nope. The WHERE clause is checked for both source and target. SJA always executes the same query to both source and target.

Q2: Does the SQL_WHERE option in the job-file have effect 1)for INSERTS+UPDATES or 2)for UPDATES only

A2: Both.

That raises a few more questions:

1) I guess that the WHERE's executed at source and target respectively are logically OR'ed to find records that qualify for the sync ?

2) Actually this morning I had some records at the target not being deleted when using a SQL_WHERE (“WHERE less than 10 days old”). Running the same job without a WHERE deleted the rows on the target. That must mean that the WHERE-clause also had effect here. The rows were about a year old, but where manually deleted at the source before the sync (and thus only existent at the target at sync-time). It was a one-way sync. This behaviour surprised me. With a one-way sync I think it's not logical that the WHERE should take effect at the target at the target – at least not as far as DELETion goes.

When running SJA interactively it's not a big deal. But in cas the use of SJA is “automated” (run at intervals and maybe even from inside an application) I think you should provided some more accurate documentation on how it works. Maybe even some user settings regarding the behaviour of the SQL_WHERE would be an idea. But at least a VERY PRECISE documentation should be provided – it is people's DATA at stake!

[Ritesh]

Quote:

I guess that the WHERE's executed at source and target respectively are logically OR'ed to find records that qualify for the sync ?

Nope. It simply executes the same query in both the server.

Quote:

think you should provided some more accurate documentation on how it works. Maybe even some user settings regarding the behaviour of the SQL_WHERE would be an idea. But at least a VERY PRECISE documentation should be provided – it is people's DATA at stake!

I guess we need to improve our documentation on this subject.

[peterlaursen]

Quote:

Nope. It simply executes the same query in both the server.

Well yes. But in case it returns TRUE on one server and FALSE on the other, what then ? In case of OR that would “qualify” the row for comparison on data – in case of AND it would not!

Quote:

I guess we need to improve our documentation on this subject.

Especially this example from the webyog webpage I believe is not good. http://www.webyog.com/sqlyog/sja-sample2.xml . First of all, I don't think you should use SJA to sync the mySQL system tables at all (at least you will have to be VERY sure about what you are doing!), second I believe a simple – almost primitive – example with data everyone understands would be much more informative. And the actual SQL_WHERE on this link goes host like '%%' … could it be more non-informative ?

[Ritesh]

Quote:

Well yes. But in case it returns TRUE on one server and FALSE on the other, what then ? In case of OR that would “qualify” the row for comparison on data – in case of AND it would not!

If the query with the WHERE clause returns data in the source but not in the TARGET then those rows will be added in the target server. If the query with the WHERE clause does not return data in the source but returns in the TARGET then those rows will be deleted from the target server.

Can you give me an example where you would like to have AND or OR functionality?

[peterlaursen]

Quote:

If the query with the WHERE clause returns data in the source but not in the TARGET then those rows will be added in the target server. If the query with the WHERE clause does not return data in the source but returns in the TARGET then those rows will be deleted from the target server.

And I could add then: if the WHERE returns the same data on both hosts, (all rows of) data will be compared, and in case they differ an UPDATION will take place. Right ?

OK – then I also understand the situation where my data at the target were not deleted. I would not have expected the WHERE to test on the target with a one-way sync.

I'm not requesting anything – just trying to understand in detail how it works! And I havn't not quite understood yet then how SJA works with both the ROW_SELECTION, the SQL_WHERE, and the PK at the same time. Or does it ?

[Ritesh]

Quote:

And I could add then: if the WHERE returns the same data on both hosts, (all rows of) data will be compared, and in case they differ an UPDATION will take place. Right ?

Yes 😀

Quote:

ROW_SELECTION, the SQL_WHERE, and the PK at the same time. Or does it ?

I am confused about what you mean by ROW_SELECTION. :huh:

[peterlaursen]

Sorry … I meant COLUMN_SELECTION

In case the jobfile contains

…

mycolumn

…

(and maybe even an SQL_WHERE)

… I can't figure out how it at the same time uses these options and its general “PK-rule”.

But maybe I just shouldn't care too much … 😉

[Author]

Posts