[peterlaursen]

please detail this “btw: the “Topic Subscription Reply Notification” seems to be missing line breaks.”

[peterlaursen]

I think we should give a global option to 'prefix backup file name with database name' ?

That would also work when TIMESTAMPS are used and when backing up every table to a seperate files:

database_mydata.sql ('mydata' given as filename in dialogue, global option checked)

database_.sql (no name entered in dialogue, global option checked)

database_table.sql (file per table option, global option checked)

database_20071215152000 (timestamp, global option checked)

what say?

[peterlaursen]

“Error Code: 2006 – MySQL server has gone away”

Definitely it happens because you have a BULK INSERT larger than the max_allowed_packet setting for this server! (A BULK INSERT is an INSERT statement where more than one row gets inserted)

I think your options are

1)

In SQLYOG show ..variables will tell you the max_allowed_packet setting (or you may execute “SHOW VARIABLES LIKE 'max_allowed_packet';”). With recent server versions the setting is very low (1M) as default. If you are in control of the server configuration you could raise it. To do this you will need to insert for instance

max_allowed_packet = 100M

in the [mysqld] section of the mysql configuration file. You will need to restart the server for the change to take effect.

2)

If you still are able to create fresh backup from the same database try setting the BULK setting in SQLyog 'preferences' considerable lower than the MySQL max_allowed_packet setting. Howeveer if the setting is very low if may not help because SQLyog will need to finish the row it has started. And if a big BLOB follows after a certain %tage (I think we test for 80%) of the BULK setting in 'preferences' has been reached the actual INSERT statement may grow too big anyway.

3)

If the database got deleted or damaged and you absolutely need to restore this backup and you do not have access to the server configuration you will need to ask some support guy to temporarily raise the max_allowed_packet setting to import this dump. Editing the file is hopeless with such big file! if the setting is very low you should convince him to keep it higher! A compromise is 16M (the normal default setting for 'mysqldump').

4)

If you have a another server (like a local server) with a larger max_allowed_packet setting than this server you can import to this other server and use SQLyog 'copy to other' or 'data sync' functionalities to transfer data to the original server.

I think it is silly that MySQL started distributing server binaries with that low (1M) setting! I have explained this at least 20 times now in this Forums!

You also tell it is an InnoDB table. The table type/engine does not matter. But it does matter if you have Foreign Keys ('relationships') defined on the tables. Because if you have you should have the statement “SET FOREIGN_KEY_CHECKS = 0;” at the top (before INSERT statements)

[peterlaursen]

What is the error you are getting? There can be several reasons! But neither SQLyog nor MySQL has such size limit. But cheap hosting may have problems with the stability of the connection! After all connection will need to be kept alive while restore is running! But you were able to export, so you should also be able to import!

1)

do you use BULK INSERTs? If so the 'max_allowed_packet' setting in server configuration must be larger than largest INSERT statement.

Please read: http://webyog.com/faq/content/24/101/en/ab…-and-bulks.html

My judgement is that this is propabily the issue!

2)

do you try to restore on another server than where the backup was created. If so please read:

http://webyog.com/faq/content/24/158/en/ca…as-created.html

3)

do you use InnoDB tabels and Foreign Keys? If so you should check the option to “SET FOREIGN_KEYCHECKS = 0” when exporting. You can add the statement “SET FOREIGN_KEYCHECKS = 0;” on the top of an existing file (however you should make a backup of the file before editing – some editors may garble BLOB data).

Please try read and understand the points above. If you did not find a solution, please try to describe the error as detailed as possible!

Won't it connect or start at all? It is a specific INSERT that fails all the time? After the job has aborted check if did nothing got inserted at all?

As many details as you can tell!

[peterlaursen]

I know this setting in PSPAD. I was just not sure what file you would use as such 'plugin' for PSPAD!

I don't think our own CHM will be appropriate. This is instructive for the GUI – it is not an SQL guide, and is not intended to be either!

But there are other sources:

1 :In the 'edit' menu we have 'templates' allready (though rather elementary).

2: There also are help tables in the 'mysql' database (see `help_topics` table for instance). I think the help tables are only populated with recent server versions and I also think you can populate them further with some SQL script, but frankly do no remember details any more!

So basically what I could suggest was that we look up in those (1st lookup in help table, next our own templates) and display a tooltip when alt+f1 is pressed.

I think we can look up a selected/marked string. If no string is marked then the word where the cursor is positioned will be considered the selected/marked string.

Did I get you right this time?

[peterlaursen]

Please tell: Is it the “SQLformat.zip” file from here http://www.pspad.com/en/pspad-extensions.php?stranka=3 that you use with PSpad and provides functionality what you request similar in SQLyog editor?

[peterlaursen]

if it is not the same request then please explain!

Basically I think you all request *SOME WAY* to let SQLyog automatically open a set of connections when the program starts?

This is not possible at the moment, as there a no 'hooks' in the SQLyog code to where command line parameters (or parameters from another binary or script) can be passed to the SQLyog binary.

[peterlaursen]

@Nirloz .. you have a PM!

[peterlaursen]

@Julian .. you have a PM

[peterlaursen]

What is a 'superuser' in your understanding?

the 'root' user or a user with the SUPER privilege? MySQL does not use the term 'superuser'.

Is there such 'superuser'@'localhost' defined at all in MySQL?

Maybe this will help:

http://webyog.com/faq/content/23/18/en/err…on-denied-.html

[peterlaursen]

You will not need to forward ports for creating connection in outgoing directions.

Maybe you have some networking hardware that has problems with Vista, maybe the ISP has or maybe it is a telecom company/transmission issue.

Do you have an option to try from another location (family or friend) in another town/part of town.

I think I remember at least once before we had a report about very slow connection from one specific location that was not reproducable from other places.

I know it is all guesswork. I have no better idea than experimenting with all possible parameters. It would then be possible to exclude possibilities one by one. But it would be nice to know if an INSERT statement from command line client is equally slow!

[peterlaursen]

You cannot compare a client running on localhost (like phpMA) with a remote client (like SQLyog).

I think nobody really know the details about this but there are some issues with TCP implementation on Vista and there IS some issue with the MySQL client code on Vista as well (and that may be a 'incarnation' of the first issue!)

We also have posted this bug report to MySQL (now almost 3 months ago): http://bugs.mysql.com/bug.php?id=31109

I have heard that Vista ServicePack1 to be released in about 3 month should contain almost new code in this respect. anyway we have not urselves experience this .. but your ISP may use some networking equipment that is more 'vulnerable' to this. I also think that most ISPs will not test traffic to MySQL unless the have complaints (they have the idea that users need to use a mail program and a browser only!)

Quote:

“I also get the delay in the results tab of sqlyog on a certain table, and also tried using an insert statement in the query browser of sqlyog and still got the delay”.

No wonder – what SQLyog sends is the same. On the 'server side' is it impossible to decide if the query was entered in the editor or generated using the GUI

Quote:

“However, since changing to Vista the IP of the server at my webhost has changed, infact I even posted a thread on here about the problem i was having after changing the IP in sqlyog. I eventually solved this problem by creating a new sql account just for sqlyog, this account has full permissions, so surely this isnt an issue?”

I remember this post. I cannot tell if it is related.

But it seems like your webhost has some routing problems with requests/responses sent from/to a MySQL client running on Vista! And I think they should look into it. I suggest you install a MySQL server on your local. because that will also install the command line client. If you experience the same delay with a INSERT statement from the command line client (what I am 95% sure that you will) if definitely is a networking issue that should be solved by the ISP.

What you need to do from command line client is only something like this

Code:

C:Program FilesMySQLMySQL Server 5.0bin>mysql -u root -p -h 127.0.0.1
Enter password: ********
Welcome to the MySQL monitor. Commands end with; or g.
Your MySQL connection id is 5
Server version: 5.0.45-community-nt MySQL Community Edition (GPL)

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> Insert into test.dummy (id) values (99);
Query OK, 1 row affected (0.04 sec)

command line client tutorial:

http://www.informit.com/articles/article.a…65&seqNum=2

[peterlaursen]

Where did you hear that? What is the specific problem you are referring to?

Basically You should always check the option to “SET FOREIGN_KEY_CHECKS = 0” if you have Foreign keys.

Too often people do not understand to use the command SET FOREIGN_KEY_CHECKS and that causes that kind of misunderstandings!

However, there is a theoretical chance that a backup will fall to restore correctly due to a constraint violation if you use Foreign Keys.

That will happen only if a row is deleted from a 'parent' table of a Foreign Key that is referenced from a 'child' table between the start and end time of the backup operation.

Consider:

If you have to tables with data (a,b,c) and (x,y,z) where

a references x

b references y

c references z

Start backup of 1st table containing a, b and c values

a is backup up

While b and c is being backed up rows containing data a and x are deleted from 'child' and 'parent' table respectively by another sesson/client.

Now start backup of 2nd table. It now only contains y and z values. They are backed up.

1st table in the dump now contains data a, b, c – 2nd table y, z only.

During restore you have “SET FOREIGN_KEY_CHECKS = 0”, so it will restore actually but the FK where 'a' references 'x' is now broken and an inconsistence is introduced in the database.

For most databases this is not a problem really. You can manage that. But there are specialized InnoDB tools that create a so-callled 'consistent snapshot' before starting the real backup procedure. Especially for corporate administrative systems that would probably be best to use. Consider a product catalogue with millions of products where the 'items' available and their identifications (numbers, names) change every minute. With such example the scenario above is likely to happen sooner or later!

[peterlaursen]

SJA connects to MySQL with the C-API for MySQL. This is a library specific for MySQL (and not a 'database abstraction layer' like ODBC, JDBC, ADO etc.).

For that reason data sync cannot sync SQLite databases (to another SQLite database or to MySQL). It simply cannot connect. With SJA the database operations are performed with C routines (using the API extensions/classes/functions) and not OS calls.

Also Migration from SQLite to MySQL with SJA migration tool would require a decent ODBC driver for SQLite. The driver available may considered 'reasonable' with SQLite 2.x – but definitely not with SQLite 3.x.

But interesting how you would use AJAX calls to sync databases!

[peterlaursen]

first step: your request was added here:

http://code.google.com/p/sqlyog/issues/detail?id=467

[Author]

Posts