[peterlaursen]

If the read through the forms you'll find some more suggestions for enhancement/improvement of the BLOB-viever function. Some of them are on the TODO-list with sqlyog's development team.

[peterlaursen]

I'm not sure but but I think the term “CLOB” is used by diffent “Standard SQL” documents. The point is that CLOB/text will only accept character-data!

[peterlaursen]

just want to add something …

it takes a lot of discipline to run two versions of the same database … AND it takes a lot of code !!

YOU will have to write a lot of code that does what the MySQL server would normally do itself!

[peterlaursen]

There are workarounds, but it's hard for others to tell what will work best in your situation.

1)

You can use timestamps (alone or in conjunction field other fields) as the PK – but then MySQL server versions must be identical (timestamp formats have changed with different version). Note that MySQL automatically sets DEFALT as CURRENT_TIMESTAMP for the FIRST timestamp in the table definition if it is defined as “NOT NULL”. You can then get the “last updated time” by setting it explicitly to NULL (the server will then automatically overwrite NULL with CURRENT_TIMESTAMP since it's defined as “NOT NULL”).

2)

You can build a PK from more fields, where one is server specific. Example: Create a new column with each table. Could be a TINYINT for instance. Define it as “NOT NULL” and let DEFAULT be “0” with one server and “1” with the other. Set it explicitly “NULL” from your code when updating or inserting (the server will then automatically overwrite NULL with DEFAULT since it's defined as “NOT NULL”). Build a PK with that column and one or more others. In case they have been updated at both servers you will then have two records resulting from the sync: one with value “0” and another with value “1” in the PK. You could find those “double entries” with some query and decide what to do with them – either manually or automated. After doing that you run a maintainance script that set all rows -1 (at both servers). Just one example of a workaround – you must use your imagination!

In this case “0” means that data have been updated (or inserted) at one server “1” that they have been updated(or inserted) at the other server and “-1” that they have not been updated since last sync. How to handle deletion of data with this case … hhmmmmm … find out for yourself 😉 !

But no matter what you do, it takes a lot of discipline to run two versions of the same database, if data shall not be corrupted!

[peterlaursen]

Quote:

Is the answer for my problem -> do not config the same primary keys for source- and target tables?

No that won't work!! Field definitions including the PK must be TOTALLY identical for SJA to work.

You should:

1) select the field(s) that make up the PK with care.

2) select source and target with SJA appropriately

Or to put it in another way: if you have two versions of a DB running at different servers, must must execute some discipline when altering data. That's not a Sqlyog/SJA problem, I believe, but a general database issue

If you got 2 of them, the day comes where you gotta choose 😉

[peterlaursen]

AAAHH … there is a CODE-tag available .. then I think I'll succeed …

From the website:

Code:

If you are using SQLyog Enterprise 4.0, you can start SJA using the wizard included with SQLyog. If you want to run it from the command prompt type:

sja

sja supports the following options –
-l

File where sja will log all the errors it has encountered while syncing databases. If no log file is specified then sja will create a default log file sja.log in the current directory of the executable and will log all error messages in this file.

And cant possible make it work to have SJA accept another logfile no matter how I “tweak” the syntax !!

Does that only apply to the LINUX version ?

Please describe EXACTLY what to enter at command line

Quote:

sja.exe test.xml -l test.log

does not work with Windows command line here

Quote:

[peterlaursen]

That probably is because the PRIMARY KEY is identical for the record in source and target.

When that is the case data from the source will ALLWAYS overwrite the target. That's how SJA works. There is no other way for SJA to decide which data are the “right” ones.

Go throgh the forum and read dozens of post on the matter!

[peterlaursen]

one more discovery …

I can run more IDENTICAL SJA-sync jobs and improve transfer speed. By experimenting I found that with my server-configuration approx. 4 instances of SJA increases sync speed about 2½ times (more instances do not make any significant difference to the transfer speed ).

Probably that's WASTE of server resources (I believe that it often occurs that the same data are written several times – that probably also is the reason why more than 4 instances is no better than 4), but that's not my problem – that's the problem of my lousy webhosting! 😛 .

I think it's a particular problem with the network with my webhosting that there is a lot of time wasted each time a connection must be established or re-negotiated (DB access is low priority on the network). They probably also have some very small connection-specific buffersizes with the server (not configurable for me). PHP-configuration could be involved too …

Problems with conflicts with instances when adressing the log (which I still believe exist) can be “work-arounded” by setting “exit_on_error” = no.

[peterlaursen]

I Surrender … 😎

I was trying to copy from here:

http://www.webyog.com/sqlyog/sjaschema.html

[peterlaursen]

SHIT .. escape character problem again I guess

I'll try this one

Concerning the log file-problem I found this at the SQLyog website

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

If you want to run it from the command prompt type:

sja

sja supports the following options –

-l

File where sja will log all the errors it has encountered while syncing databases. If no log file is specified then sja will create a default log file sja.log in the current directory of the executable and will log all error messages in this file.

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

And cant possible make it work to have SJA accept another logfile no matter how I “tweak” the syntax !!

Does that only apply to the LINUX version ?

Please describe EXACTLY what to enter at command line

Quote:

sja.exe test.xml -l test.log

does not work with Windows command line here

[peterlaursen]

Concerning the log file-problem I found this at the SQLyog website

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

If you want to run it from the command prompt type:

sja

sja supports the following options –

-l

File where sja will log all the errors it has encountered while syncing databases. If no log file is specified then sja will create a default log file sja.log in the current directory of the executable and will log all error messages in this file.

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

And cant possible make it work to have SJA accept another logfile no matter how I “tweak” the syntax !!

Does that only apply to the LINUX version ?

Please describe EXACTLY what to enter at command line

Quote:

sja.exe test.xml -l test.log

does not work with Windows command line here

[peterlaursen]

You found the solution yourself! Thats what I do too! And I know no better fix at the moment.

The “fix” that I do to be able to use SJA is to have more versions of the MySQL server installed (actually I have three). I don't run them simutaneously but edit the server variables, stop and restart the server with “MySQL Administrator”. Actually I believe that 4.0.x and 4.1.x can use the same datadir (4.x and 5.x can't). Actually I use the same datadir with 4.0 and 4.1 and I believe it is in 4.1-format. But don't edit users with the “wrong” MySQL version and be careful with the use of timestamps!

I posted some screenshots of the procedure that I follow here

http://www.webyog.com/forums/index.php?act…=ST&f=10&t=1427

[peterlaursen]

@ Ritesh was here again .. it must be above bedtime for young people where he lives 😆

The propoasal of Ritesh works with the “RESULT”-pane but not the “TABLE DATA”-pane !

[peterlaursen]

No way as of now, I'm afraid

and besides I disagree with this

Quote:

because tinytext is technically a blob type

It is technically a CLOB-type (Character Long OBject)

… but that's an old discussion at this forum 🙂

Your request has been proposed before. I don't know it it came be implemented wit the SQLyog code. The problem is that BLOB- and -text-type variables in MySQL are not stored in the table itself (only a pointer is stored). So reading the table does not read the values of BLOB's and text “in the first run”.

Basically I think it's an unwise use of text-variables. They should be chars (for performance) or varchars (for effective use of discspace). An with MySQL 5 varchars can be about 55000 characters … But that of course is no solution where you are now.

Sorry!

[peterlaursen]

BTW – then SQLyog HTTP-tunnelling file is probably a “client” too then ??

The two servers involved are the one at my DNS-provider (that offers med 10 MB diskspce totally (for webdocuments, mail and DB's) and a supplementary hosting provider that ofers me 300 MB. With NEITHER of them I'm allowed to connect to the MySQl-port.

So I don't see how I can use your suggestions !? I have the choice of PHPmyAdmin or SQLyog (or some few other clients offering more primitive tunnelling possibilities then YOG does)

And especially when copying one table from one DB to another DB at the SAME server it seems somewhat ridiculous to generate a lot of traffic on the internet. But it works perfectly!

[Author]

Posts