[peterlaursen]

I'll try to understand in detail later tonight!

But please try SQLyog 5.2 beta5. Before that there was a problem with /*! ..*/ style comments ('conditional comments') Good chances that this is the issue!

[peterlaursen]

Code:

Default language= English UTF-8

Understand that right! MySQL 4.0.x does not support UTF8. But the backup can be UTF8 encoded anyway.

Nevertheless phpMyAdmin will need to be configured correctly for 4.0! It should NOT 'SET NAMES' with MySQL 4.0!

If you do not use SQLyog instead!

[peterlaursen]

You arw exporting from a MySQL 3.23 or 4.0 server and importing to 4.1 or higher. That won't work! SQL is different.

You have two option:

1) edit the SQL-file to fit the syntax of of the server version you import to.

2) I think that phpMyAdmin has an option to generate 'compatible backups'. You can try. But be perfectly sure that the charset on boths servers are the same!

MySQL is not really designed to 'work across versions'. We have our problems ourselves to keep STRUCTURE SYNC 'compatible' too for instance!

[peterlaursen]

OK .. now I think I understand

the problem is that the concatted strings that SJA need to generate to build checksums with become to long compared to some buffer settings on the server (or the memory available) for those rows where FILE_CONTENT are largest. It is a LONGBLOB column and can be as big as 4 GB.

Use the option in the jobfile. Read about it here:

http://webyog.com/en/whitepapers/Using_SQL…L_Databases.pdf

as the column IDX1 is unique, you use that single columns with the option and concatted strings will be very short! And sync will be MUCH faster too.

[peterlaursen]

Quote:

missing content on the other web server.

can you identify those missing rows, copy them to a seperate table (CREATE TABLE AS SELECT … WHERE ….) and export it?

I think we will need to see what they look like in a HEX-editor!

Are you sure that all apps that you use don't have issues with the utf8 charset?

Quote:

Size of the db is more than 500MB.

what is that equivalent in rows?

try

Code:

…

localhost
root mintrold 3306 utf8
test

localhost
root mintrold 3306 ujis
tst

…..

[peterlaursen]

do you use the tag in your XML ?

Quote:

missing content on the other web server.

can you identify those missing rows, copy them to a seperate table (CREATE TABLE AS SELECT … WHERE ….) and export it?

I think we will need to see what they look like in a HEX-editor!

Are you sure that all apps that you use don't have issues with the utf8 charset?

[peterlaursen]

Quote:

please allow to send CSV and XML

hmmm … you can “SELCET …. INTO OUTFILE …”

Quote:

Actually the notification is an export

NO!!! it can be ANY valid SQL!

Quote:

some E-mail clients don't handle that yog html very well ..

We should have a look at that then.

Quote:

Actually the notification is an export

OK as far as the email notification is concerned, you can say so.

I'd rather say it is NOTIFICATION. It is meant for visual inspection primarily.

Opinions?

[peterlaursen]

Quote:

CHARSET=utf8;

is this the case on both servers?

how is the charset dropdown/ tag for BOTH connections?

Always inform about Program versions!!

MySQL versions?

Platform on which the servers run?

SJA version?

SJA for Linux or Windows?

[peterlaursen]

Sorry for SHOUTING! 😎

I do not like something that works as expected 99.9% of the times. For instance when a cell has been updated twice within a second, or some ISP admin forgot to configure 'light saving hours'.

I think my proposal will work 100% as expected! But both options could be there!

Keep on discussing!

[peterlaursen]

Quote:

about unprotected power-scripts like your tunnel script

it is not more powerfull than a lot of PHP-scripts. Is it?

If you use HTTP-tunnelling with both SQLyog and SJA I still do not understand …

[peterlaursen]

The SMTP auth. issue with 5.2 (not 5.19) is confirmed and fixed.

@LCS: we cannot do any progress with your problems without more info. Best of all server access.

You should understand that when you connect to the tunneller from SJA (unlike from a browser) connection will need to be established hundreds or thousands of times!

If you access from a browser and click 'refresh' several times does it then occur from time to time that you can't reach the tunneller? It can simply be because the webserver is configured too 'low' compared to what it has to do.

[peterlaursen]

Code:

It's our own responsibility to ensure our servers have properly synchronised time.

The reality is if people suffer dataloss, webyog will suffer financially.

We have to be realistic about the world we are living in that 75% of users do accept 'their own responsibility' here!

In LEGAL terms you are right of course. We need to think in MARKETING terms too!

But you don't have to, beause you do not risk anything!

WE CANNOT AFFORD TO BE THAT NAIVE ABOUT OUR MARKET POSITION!

[peterlaursen]

one option does not exclude the other!

But I would be very carefull with timestamps when a ordinary PC is involved.

If system clock gets 'out of tune' it can be disastrous.

That typically happens after 3-5 years when the motherboard battery get 'flat' – and we are talking about errors in the range of YEARS.

Also some software may reset the system clock to BIOS initial time by error (I have an example!)

BTW: you do not need a trigger to update a timestamp on update.

Just define as 'TIMESTAMP … ON UPDATE CURRENT TIMESTAMP'. that is possible from MySQL 4.1 and with versions before that the first timestamp in a table behaves this way. But triggers and a special table will be necessary to keep track of DELETES as a TIMESTAMP or a CHECKSUM saved in a row gets deleted with the row itself!

Keep discussing … we want to hear the opinion of everyone having one 😀

SQLyog sync as a tool to 'save' a broken MySQL replication is a nice idea indeed!

[peterlaursen]

1) we know we have an issue with mails with the SJA for Linux. Pretty silly that nobody – including ourselves – noticed this before. We will do pur best to release SJA version 5.2 with the fix.

2) no port needs to be opened if the router has NAT functionality. And it is the common HTP/HTTPS ports (and SMTP port for mail) that are used only. And the same for SQLyog and SJA. However a 'personal firewall' may need to be told that SJA.EXE shall have access to the internet. We have had quite a lot of issues with ZoneAlarm that it kept blocking connections even if it claimed to be configured to allow for them. A reinstall has often been the onluy solution.

I have no option to test HTTPS tonight, but just tested HTTP with 5.2 beta5 and a Notifications job.

The SQLyog executable (tries to) reconnect(s) if connection is lost. SJA does not. That could be the reason for the difference. However it then indicates a HTTP-connection that is not very stable.

You will need to wait for tomorrow for more action. If you can give temporary access you can create a ticket with the details.

EDIT: there seems to be a problem with SMTP authentication and 5.2 beta 5 .. but that is quite another issue! Connection and the query is OK!

[peterlaursen]

BTW: it could be a firewall issue too. Do you have a 'personal firewall', a router, or 'shared Internet Connection' ?

[Author]

Posts