[peterlaursen]

Actually we changed that about 1 year ago to create FULL insert statements.

That was a repeated user request actually .. and I think you can't make everybody happy!

Two comments:

1) If you use BULK inserts like 

Code:

insert into `mytbl`(`columna`,`columnb`,`columnc`,`columnd`) values ('a1', 'b1', 'c1', 'd1'),('a2', 'b2', 'c2', 'd2')…('an', 'bn', 'cn', 'dn'); 

.. the 'overhead' won't be much

Read about BULKs and CHUNKs here: http://www.webyog.com/faq/24_101_en.html

2) The (scheduled) backup powertool of the ENTERPRISE version has the option to use FULL inserts or not.  See image.

[peterlaursen]

Quote:

I am not using password yes, and I am not connecting to localhost.localdomain but rather just localhost

basically this a a MySQL authentication issue – connection is OK!

first:

“using password = YES ”  just tells that you are using password.  MySQL will not send print the password in readable form on the screen, but use this 'metafor”

next:

“I am not connecting to localhost.localdomain but rather just localhost” .  Yes that is a problem with the configuration on certain variants of *nix!

“database user name and password using the command line login in a shell”  Problem is that a shell logon is not tunnelling and the two may be treated differently by the OS.

This FAQ has some similar discussions..

http://www.webyog.com/faq/23_36_en.html

Is it possible to create a 'user'@'localdomain' by using some alias or the ip?  What about 127.0.0.1 as host ??

This 'localdomain' occurring form time to time can be tricky.  And I am naot the expert to answer it.

Please post any solution that you may find – by studying configuration, experimenting or by the help of others.

[peterlaursen]

This is a very reasonable request, I think.  We shall try to 'put that in' as soon as possible!

[peterlaursen]

Please first read: http://webyog.com/faq/23_18_en.html

Basically the error message “access denied ..”  tells that connection details are OK, but authentication details are not!

Probably the user is not configured to allow connections from remoste hosts. Only 'this_user'@'localhost' is defined – not 'this_user'@'some_remote_host(s)'!

But maybe your ISP will not allow for that and maybe you have no option to define such remote user (check your COntrol Panel!).  Maybe you will need to use tunnelling!  With tunnelling you connect from 'localhost'.

[peterlaursen]

I cannot tell in details what happened on the server side.

But before 5.22 we used a query like “SELECT …FROM INFORMATION_SCHEMA ….”, now we “SHOW … STATUS ..”.

Basically it is a configuration error on the server!  You should be able to SELECT details from INFORMATION_SCHEMA the details of those objects that are accessible from the user account that you are using!

[peterlaursen]

Code:

I've now tried changing the max_allowed_packet to 90MB and using BULK (10000 rows) as well as CHUNKS (90000K:cool: – but still get the same error.

hmmm …  

1) CHUNKS only have effect with HTTP-tunneled connections

2) BULKs are specified in KB's … not rows!

If you raise max_allowed_packet to 90MB you should specify a LOWER valaue for the BULK.

Now .. you are perfectly aware that the BULK setting only works when exporting, so you will need another dump from the other server to change BU

Ks ??

I think that there is still a chance that max_allowed_packet is the issue.  Also because command line client has the same problem, and the error occurs with the first row of data.  I think all statements (CREATE etc.) b

fore the first INSERT statement are executed correctly?

Please try to:

1) keep the max_allowed_packet as 90MB

2) export with a BULK of much lower .. 1 MB for instance.  That means you must enter 1000 in the dialogue box.

Actually I can reproduce the “Error Code: 2006 – MySQL server has gone away” by setting BULK = 100K, export, change server config to “Max-allowed-packet = 10K”, drop the database and try to import.  I think something messed up for you!  Did you restart MySQL after changing the configuration?

[peterlaursen]

This issue is now fixed.  It will me merged into the 5.23 tree and released with beta1.

Also another issue was fixed in 'the same shot':  When a ENUM or a SET was defined with “NOT NULL” the dropdown (in both DATA tab and RESULT tab) still displayed NULL as an option.

[peterlaursen]

Quote:

the problem is that SQLyog can't handle this amount of data at once..

It does not!  Let me explain how it works.

1) using the concat_ws() and md5() functions it generates a checksum of 1024 rows (ans this is done by the SERVER – not SQLyog!) and compares for the two databases.  

2) If there is no difference we proceed to the next 1024 rows

3) if there is a difference 1) is executed recursively untill the rows that are different are identified.

4) Those rows are now INSERTED, UPDATED of deleted.

So it simply does not matter if there are 1024 rows or 1024*1.000.000 rows.  What is inside SJA at a particular time is the same.

If it is not DATA-specific, then it must be a server issue, that it only allows for a connection for 200 minutes (for instance – 3-4 hours ~ 200 minutes).

We cannot do any progress without a test case!  So if you want us to keep on working on it you will need to provide one!  We can then easily test with a broad set of servers!

BTW:

Quote:

to fix the problem, i did select 3 or 4 or 5 rows at a time, it took me like 4 or 5 days but fixed the problem.

How do you select rows with SJA DATA SYNC.  Or did I misunderstand something basic?

[peterlaursen]

We will definitely fix this, and likely in next release (5.23).

Data tab is not affected and SET types are not either.

So I do not think it is a big issue.

Probably we will understand the issue in detail later today or tomorrow.

A workaround is to do things from DATA tab once.

Now you copy the SQL from HISTORY, paste into editor, edit and execute.

[peterlaursen]

Most simple solution is to add an autoincrement PK to every table that does not have a PK, if that will not break your applications.

[peterlaursen]

bug confirmed!

Code:

CREATE TABLE `asqw` ( `id` bigint(20) NOT NULL auto_increment, `c` enum('','a','b') NOT NULL,
PRIMARY KEY (`id`) )
ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into `asqw`(`id`,`c`) values ( NULL,'a');
insert into `asqw`(`id`,`c`) values ( NULL,'b');

select * from asqw where id = 1 or `c` IS NULL;

[peterlaursen]

@Monte

Please explain the problem more in detail, and please inform about the program version and connection method!

But most likely this is a server/remote network configuration/resource issue.  The remote host simply only 'absorbs' the data in that rate.  Wioth my personal (cheap) ISP I sometimes have to accept transfer reate of around 10 kbit/sec.  There are 200/ (or 2000?) more users on the same server …  

[peterlaursen]

Program version, please ?!

If not 5.22 then first try an upgrade and repeat!

If problem persists and if there is a dump file in the installation directory, please attach it here.

We will need to know the EXACT program version if we shall be able to use these dump file(s)!

[peterlaursen]

“After too much time waiting ..”.  If the mail does not arrive for some reason, you can always go to http://www.webyog.com/CustomerArea .  If you are not registered there an error has occured with our purchase processor.  Please create a ticket then and we will sort things out.

“Then i got errors while i want to transfer tables without a primary key. I can understand that things are somewhat harder to handle without a unique key”  A primary key and a unique key is two different things.  Please read http://webyog.com/faq/28_70_en.html

“For this i really should possible to get the data from a table though it has no primary key.”  You can always export and iimport, for instance.  For syncronisation every row of data will have to be uniquely identified.  If you like you can read the dicsussion here: http://webyog.com/en/whitepapers/Using_SQL…L_Databases.pdf

“at the moment i wait till 10 minutes … “.  If you run SJA from inside the GUI you will get lot of messages about what is happening.  Don't you?  Or do you still use the old version?

“Dont u use a compression if available like others do?”  We do not use compression.  Could you explain what sort of compression you would expect with MySQL?  To my knowledge there is no one supported by MySQL.  With HTTP-tunnelling there is the gzip option, but our experience that it des not make any

difference whether it is used or not if only you have a 128 kbs connection or faster.  It is not transmission of data that is the bottleneck, but the server(s

' response time.

Encrytion (SSL) howvever will be included in 5.23 beta 1 to be released in less than a week.  This is supported natively with MySQL.  And encrypted HTTP (HTTPS) is there allready.

“Another thing i really dont like:

1. Choose a table

2. Set: Show all

3. Choose another table with about 100.000 data-sets

4. … wait, wait, wait … “

What is the program version you are using?  With recent versions selecting the DATA tab wil only load the number of rows specified in the LIMIT setting.  Please study the programs' 'preferences'!  What is your LIMIT setting (not your PERSONAL, but that of your program!)?

No, we only tell GOOD jokes here …. 🙄

[peterlaursen]

Also SQLyog tries to reconnect, but it just does not advertise it!

MySQL server should not 'go away'.  And it does in line 26.

It is not easy to help without some kind of test case!

But …

this could be an issue with the max_allowed_packet setting in MySQL.  If you did not change configuration it is 1 MB only.  And if you did not use 'BULK INSERTS' every table is inserted with a single INSERT statement!

Please first read:

1) http://www.webyog.com/faq/24_101_en.html?highlight=chunks

and try to:

2) raise the max_allowed_packet drastically (64 MB for instance) and try again.

(you can do from MySQL Administrator .. edit configuration .. 'Advanceb Networking' tab.  Restart MySQL for the change to take effect!

[Author]

Posts