[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]

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]

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]

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]

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!

[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]

I get this error message too from time to time, where it does not make sence. Clicking OK and everything works…

Below an example from the “manage privileges” pane. Error message appears with just one DB (no matter who is is the user and no mattter if the user has any privileges to the DB or not) and not other DB. The DB in question is imported with the SQLyog “ODBC import wizard”. The only thing special about it is that is uses VIEWS (MySQL 5.0.4) and is rater big (approx 37500 rows)

[peterlaursen]

You write “what if the server bugs down”.

Well, with MySQL 4.1 and above there is the possiblity of clustering. Considered that ?

You could get into terrible problems if two terminals update (or delete) the same row(s) between sync's. And just thinking of the chances of conflicting PK's with 1 “master database” and 25 “terminal databases”.

Besides I don't think MySQL 5.0.2 is fit for a production environment. But probably 5.0.7 is coming soon and that could be …

[peterlaursen]

Two comments:

1)

You write:

“can sja manage to handle around 25 terminals”

would you let each terminal use it's own copy of the database ?? Why not connect to ONE database ?

2)

How often skipping rows will happen ? Depends! In my case I think that typically about 1/1000 of the rows are skipped due to server not responding when SJA attempts to read (that's why I rerun SJA using a loop from a .bat-file until it's done!). But it also could be due to “read-locks” applied by other applications.

[peterlaursen]

One proposal for a small change

“ALL” privilges should be shown at left and be applicable for as well “global privileges”. “Schema privileges”, “table privileges” and “column privileges”. To make it ease to GRANT all available privileges of a table to a certain user.

And privileges that are not applicable with a certain server version should either “grey out” or be hidden.

But I I'm not sure I quite understand what you mean. Should all tables available with the connection be shown or only tables of a DB that is highlighted ? In the first case it could come out to be an ENORMOUS view, in the second case the idea of everviewing everything in one pane is lost, I think.

I'd consider an ability to “unfold” a DB to show tables below it and “unfold” a table to show columns accordingly. In case a (for instance) DB in “infolded” the privileges to the user that apply only to certain tables and columns could have it's own symbol or color. A triangle symbol at the left of the text is widely used as an “unfold/infold” symbol.

There is one thing more to consider. I believe a user can only GRANT a priviliege to otheres if he has the privilege himself. Right ? Privileges that are not GRANTable for current user could also be shown in another symbol or color (example: red cross over box).

And one last thing: It should be able to set miscelaneous information (Full name, Description, Email, Contact Information)about the user (Table “user_info” i the “mysql” D:cool:

[peterlaursen]

Just one comment … no two ..

1)

there are quite a lot of new privileges wiht the MySQL-server ver. 5. They should be included too!

2)

It should also show “resources” available to user (# of simultaneous connections etc. )

[peterlaursen]

that depends I think …

If it's a full replication running always it's best the way as CalEvans describes it. But if it is only some tables and some colums that need to be sync'ed and only in certain periods I belive the .bat-file solution is acceptable. And you can only use CalEvans's proposal if your are in control of both server configurations. If it's webhosting <> local server I don't see how it can be done. At least you must be a very important customer to persuade the hosting company … and pay for it!

The main drawback with the .bat-file-solution is that someone might accidently close it and you'll get no warning.

Actually I very often use the bat-file method when sync'ing because some rows may be skipped for some reason (with me it's mostly due to a not to stable network connection to the server at my webhost, but it could also simply because of locks if the server is accessed by other users). But what does not come in the first run comes in the next. I let it run until it says “Inserted 0 , updated 0 , Deleted 0”

[peterlaursen]

very simple 😀

create a windows/DOS “myjob.bat”-file like

(if it's the Linux version of SJA, you must help yourselv to find something equal)

content of myjob.bat:

Quote:

:label1

sja myjob.xml

goto label1

then run the .bat file. It wil start all over again when it has finished! Note that in this case you should not use the windows scheduler at all!

[peterlaursen]

Of couser it should have read:

Slowing down could be because of the DB fragmenting. :wub:

[peterlaursen]

Slowing down could be because of the DB defragmenting.

With InnoDB fragmentation can occurs “within” the InnoDB data-file (normally ONE file unless you specify the “InnoDB_file_pr_table” -option). It is described as far as InnoDB tables goes.

http://dev.mysql.com/doc/mysql/en/innodb-f…ragmenting.html.

But fragmentation can allways occur with the server file system. With MyIsam there can be a lot of seperate files (data files and index files) . They can be spread over the entire disk volumes available to the server, and each file can be in many fragments. Especially if you use webhosting you probably share the disk volumes with thousands of other users. Dumping and reloading data at intervals could be a solution here.

You also could discuss the matter with the servers' maintanaince personnel. There might be some tools available with your webhosting. It depends on the server OS (and version) and what maintanaince software they use.

[Author]

Posts