[peterlaursen]

Exactly as you describe it here I cannot reproduce a crash.

But I've had crashes with 5.1 BETA when clicking the DATA tab or when clicking a table in Object Browser while DATA tab was active.

I think there is a buffer issue of some kind. But it might depend on hardware configuration and system settings how this materializes.

[peterlaursen]

@ritesh

regarding the crash: compare ticket #98

[peterlaursen]

@ritesh: you are supposed to answer – not to ask questions 😀

I see two possibilities:

1)

Recent MySQL versions store comments in UTF8 no matter the server/db/table charset. If so SQLyog must send UTF8 or let the server convert.

2)

It is a bug with MySQL tools. I believe I read that Mike Zinner wrote somewhere that they use utf8 internally. Maybe they simply forgot to convert?

[peterlaursen]

It would still be a help if you could tell how this was reproducable.

If I kill the thread I don't get this error!

[peterlaursen]

Hello to Germany 🙂

but it should definitly be the same. No. It is only the same for the ASCII-subrange (nonaccented characters). A German letter like ä or a Danish like ø is encoded with two bytes of data with utf8 – only one byte with Latin1.

What is the MySQL version and SQLyog version that you use?

If recent MySQL versions always use utf8 for comments, I think we have overlooked this. Will be fixed then! We will have to change charset with the 'set_characters_result = utf8' command when writing and reading comments and change back when working with data themselves.

SQLyog uses the server default charset. In your situation that is probably LATIN1 since you are living in Germany – but it maybe LATIN2, ..5, ..7 or a single byte Cyrillic or Arabic character set as well.

With SQLyog 5.1 (in Beta) you can specify another charset to use than the server default. Single byte charsets are supported. Multibyte charsets (utf8, ucs2) will be fully supported with SQLyog 5.2 (a couple of months away).

[peterlaursen]

Hmm …

I have no comments at the moment.

I can get NULLs if I mismatch utf8 and latin charsets in server and client configuration and proces srings outside the ASCII subrange (ssuch as accented characters).

[peterlaursen]

You can see that for yourself in the HISTORY tab if you enable 'logging to HISTORY with batch jobs' in 'preferences'.

but basically it is as simple as (if we omit the charset and sql_mode related commands):

Code:

show create table `test1`.`mydb`;
use test2;
CREATE TABLE `mydb` // here goes what 'show crate table' returned
select * from `test1`.`p1a`;
insert into .. //repeated for each row with the data returned by the SELECT.

There are variations depending on whether the MySQL version has INFORMATION_SCHEMA (5.0 ++) or not.

However this shows only the SQL processed.

What is processed inside the SQLyog binary is not shown here.

You cannot do this with pure SQL only!

[peterlaursen]

1) The DATA sync tool can be run from a batch file like

sja syncjob1.xml

sja syncjob2.xml

…

sja syncjob40.xml

You will need 40 different job(XML-files) then, but if it is only the name of the database that changes that can quickly be done from an editor. You may also write a simple application with a loop ( for job = 1 to 40 …) calling the SJA and replacing inside the jobfile.

2)

Regarding the crash. We had a similar reports a few days ago. Looks like it happens at the very moment when SQLyogEnt.exe handles control over to SJA.exe. Working on it.

[peterlaursen]

Quote:

Then after I tried to return to the table view, it quit/crashed with no error.

There are some issue with the data buffer of the DATA pane with this BETA

What do you mean by 'NULLED OUT' ?

[peterlaursen]

I don't think that there are 'MySQL replicators'

there is MySQL replication. It is built-in the MySQL server. Just must be enabled in configration. However it lets you replicate SERVERS, not DATABASES or TABLES. So you must have two servers that you don't share with others. And the concept of replication implies that the servers should be able to communicate (though of course if the slave crashes it will 'pick up' what it missed when it gets connected again)

And I think that even two-way replication between two servers is not secure (two servers being master and slave for one and another at the same time has some issues). You can create a 'polygon' (three or more) of replicating servers a secure way – but not only two.

If the only thing you need is

1) compare two TIMESTAMPs and copy row from the server with the most recent to the other

2) check for a to-delete 'mark-up' and delete on both servers if this 'mark'up' is positive on either

.. then a program for it is pretty simply. At least if there are not huge amounts of data to be written and 'peak' performance is not an issue.

But also notice that a TIMESTAMP has an 'accuracy' of one second. So if same row is updated one the two servers within the same second you won't be able to track it and the sync will fail (and if system clocks get 'out of tune' you are pretty much lost!). MySQL replication uses the Binary Logs of the servers. SQLyog synchronization uses DATA THEMSELVES. If you respect their limitations none of them will ever fail.

[peterlaursen]

First you should understand the difference between replication and synchronization:

http://www.webyog.com/faq/11_60_en.html

Next we have an extensive article on the SQLyog DATA synchronization here:

http://www.webyog.com/articles/Using_SQLyo…L_Databases.pdf

And last let me try to answer your question:

What you are trying to do is not really possible with the SQLyog DATA SYNC tool. You write this:

Quote:

but when i'm editing the target table – my changes are discard during sinchronisation process

… and if you read the article you will understand that this is exactly how the tool works. This simply is what 'source' and 'target' means here. Same with rows deleted in target.

You write: i have last_edit field.. Is it a TIMESTAMP (or a DATETIME)? Sounds so. The data Sync tool does not use such TIME information. You can define a PK that uses this field but it won't solve the problem.

You cannot UPDATE on both servers between syncs and sync changes with the SQLyog DATA sync tool. You can INSERT on both in you choose an approiate PK. And two-way sync wil always copy rows that exist on one server only. So rows deleted on one server only will be restored with a two-way sync.

The solution in your situation would be to write a program (a PHP script, Visual Basic – anything that can connect to MySQL) that connects to both databases, compares this TIME for each row of data and writes data from newer >> older when this TIME information differs.

But there are some issues, that you must solve:

* If a row exists on one server only, how to tell if it was inserted on one server (and should be copied to the other) or it was deleted on one server (and should be deleted on the other as well). No program can tell. You must define the 'rule' to apply!

* Now if the same row has been updated on both servers what to copy then? Always the LAST one. So simple? or another 'rule' to apply?

* How to avoid a PK-conflict when rows are inserted on both servers? (tip: don't use auto-increments!)

Added:

In the article I wrote this:

I have an idea that it would be useful to have an ability to use a symbolic addressing using “source” and “target” with the SJA as some sort of “reserved words” like “source.tablename.columnname” and “target.tablename.columnname” with the SQL_WHERE . That would allow for SQL_WHERE expressions like

source.tablename.columnname >= target.tablename.columnname

That actually is what you request I think. But it is not available. And will only solve situations where identical rows exist on both servers!

And it won't solve the issue with DELETEd rows. But there will be solutions to this as well. One idea: Don't physically delete, but mark-up for delete using a special column for that (an ENUM yes/no for instance). An option to DELETE instead of INSERT when this is 'yes' could be added to the tool. And BTW: you can use this trick with your own script!

[peterlaursen]

The error message is a MySQL server message.

http://dev.mysql.com/doc/refman/5.0/en/err…ges-server.html

[peterlaursen]

And of course I also tried just stopping the query too!

No problems either!

[peterlaursen]

I wonder ..

Do you use LOCKs, TRANSACTIONs .. anything special?

Storage Engine? MySQL version ?

I am not sure if the Server should allow for closing client connection (to avoid data corruption).

[peterlaursen]

Exactly how did you trigger this ?

I tried:

1) Running a query (a 'select * from …' running for a few minutes) and killing MySQL Server while query is running. Error message 'Lost connection to server during query' occurs. I can close this popup and close the program easily. This applies to direct connection and SSH-tunnelling as well.

2) Connecting with HTTP-tunnelling and killing the webserver while query is running. HTTP-error '… wrong handle …' occurs. I also can close this popup and close the program.

3) Opening two SQLyog program instances to the same server, running a query from one. While query isrunning I kill user thread used by first program instance from the other instance. No error message occurs, but I can stop the query from red 'X' icon without problems.

4) Tried the same three things with an 'export as SQL'. No problems here either.

All tested with 5.1b4

However I think there is another issue: if the main program thread locks for some reason the red 'X' icon is not responsive. But that is quite another thing.

[Author]

Posts