[peterlaursen]

and …

Does the SQLyog BLOB-viewer display the graphics correctly ?

[peterlaursen]

Not really any other suggestion than getting access to another DB or even another server.

SJA won't sync two tables on the same DB with different names, as you have noticed yourself.

You don't have a MySQL server running on your local machine? That would be the one solution to have it.

With this solution you could sync staging_%tbl_name% from your webhosting to localhost, rename staging_%tbl_name% to prod_%tbl_name% on localhost and sync prod_%tbl_name% from localhost to your webhost – with the result that staging_%tbl_name% will be sync'ed with prod_%tbl_name%

Personally I would prefer running the development or “staging” -database on localhost at least in early stages of development. But of course that would probably involve a complete setup of a webserver, a preprocessor (PHP or ASP most likely) and and MySQL server on your local. However it is not very complicated to set up (at least not Apache + PHP + Mysql).

If you only wnat to install MySQL on your local it will install and cofigure itself in a few minutes … Of course there are a lot of optimizations that could be done afterwards, but with this simple need you won't have to do any of that stuff…

[peterlaursen]

datatype mediumBLOB should be OK.

Here http://dev.mysql.com/doc/mysql/en/storage-…quirements.html it says that that the mediumblob could store data sixes of 2 ^ 24 bytes. That is about 16 MB.

Do you use PHP or ASP or whatever ?? Could you provide some code example, that show which method you use to fetch the data ?

What is the purpose of your attachment ? We can't use it for two reasons:

1) you can't copy a mySQL Datafile (a.MYD-file) like was it for instance an Access database. The MYD-file will only have meaning with the server environment where it was created.

2) actually BLOBs are not stored within a table. Only a pointer is stored in the table. The binary data elsewhere …

[peterlaursen]

I can connect too with “MySQL Administrator” and “MySQL Query Browser” and office programs like MS-Access and OpenOffice Base using the “MyODBC connector”.

Are you sure it's not only a problem with the letter-case in some connection variable ?

[peterlaursen]

I connect too without any problems. And I took some liberty too 😀

[peterlaursen]

I found what I was looking for, it says

Quote:

For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column)+1 WHERE prefix=given-prefix. This is useful …

from here: http://dev.mysql.com/doc/mysql/en/example-…-increment.html . But since you are using INNODB, it's not relevant …

[peterlaursen]

BTW: I don't think reversing the PK definition would make any difference. It works as well here …

[peterlaursen]

It works here! I tried both with and without HTTP-tunnelling on MySQL 5.07. I used your table definition and a few rows of data entered from the keyboard. SJA will both INSERT, UPDATE and DELETE as it should. I tried one-way and two-way sync as well.

two questions:

1) You are sure that there are no “”-characters in the “serveur_id” field anywhere ? Or any other character having a special meaning with MySQL such as ” ' ” .

2) What is the

Code:

(yes or no?) in the job-file and do you use any SQL_WHERE ?

If not believe there might be some problems with your data!

further I note that:

This PK definition

“PRIMARY KEY (`serveur_id`,`num`),” is unusual – most often you would have the number field first.

You could try reverting with an ALTER TABLE-statement like PRIMARY KEY (`num`,`serveur_id`)

(There is something special with the MySQL server when using an autoincrement integer in conjunction with another field as a PK. I just can't rmember details and also just could not find it in the MySQL-documentation (have to go for a while now!)).

It would be useful to if you told us if the problem is recent (whether it has worked before or not).

Is there any chance that you will be able to spot the data/rows that should sync, but don't ?

********************************************************************************

*

BTW: there is another problem (bug in SQLyog I think!) with your table definition, namely this

Quote:

`ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

Sqlyog does not respect the “on update CURRENT_TIMESTAMP” condition (does not write CURRENT_TIMESTAMP, but just keeps the old value. Be aware of that if you use SQLyog to update the table … but that does not relate to the current problem!

[peterlaursen]

probably it is a problem with the Primary Key that you are using.

There is a bug with version 4.06 (should be fixed in 4.07 development tree) that generates exactly this error if the PK is a text-of-some-type-field containing the “”character. thus, For instance you cant use a field containg a windows' filename as a PK.

What is the PK of the tables in tour case ??

[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 … 😉

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

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

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

[peterlaursen]

my problem: all my editors are so “intelligent” that they show a line-break instead! No matter which standard for line-break the file uses (DOS, ANSI, OS/2, Linux or whatever!).

[peterlaursen]

OK … it clarified one point: that there is no automatic MySQL escape-character handling with the CSV-import.

I believe there is one problem more: If you enter more than one (two) characters as escape charater (i.e “your own” escape-character for your own dataset, not the MySQL standard “” escape-character), the .csv written only contains one (the first) of those two characters! Try yourself to enter “!!” or “!?” as escape-character and use a field delimiter that is represented in some char-field in your data..

There are some more very confusing things about it. If you specify “!!” or “!?” the dialogue will remember both. But in the file it is only “!”. So if you don't check the file but just trust the dialogue, you'll get an error! You must delete the last one from the dialogue to make it work. However – if you specify “\” the dialogue will remember only “” (what is actually what is written too) and then you will have to add one more manually to adress the mySQL escaping correctly. Here you also can't trust the dialogue!!

As far as I remember “\” is the program's default (with a fresh installation). Right ? Bad idea, I think! In case you will need BOTH the escape-character with the MySQL server AND an escape-character your own dataset they should be VERY DIFFERENT! Use for instance “^” as the escape-character for the dataset instead.

Well .. if you know and understand all this maybe it's no problem. But don't you see how it can confusing ? It took me 1½ year to get it right (hope I did!)

One last Q: how did you see the Linux-style line-endings – I have a lot of editors, but none of them show it ??

[Author]

Posts