Default options for SQLyog "export as script"

[patrickq]

Summary: when accepting the default choices for “Tools->Export Data as batch script” (same with DB->Export Database as batch scripts”), I believe the scripts created are both dangerous as well as non-functional (for large tables), hence I would like to suggest changing the default choices to make it both safer as well as functional always (rather than functional only for small tables).

Specifically, I think that:

– Include “DROP TABLE” statement should be OFF by default (not ON)

– Create Bulk Insert Statements should be OFF by default (not ON)

Details below.

By default, SQLyog creates scripts of the form:

drop table if exists 'myTable';

CREATE TABLE `myTable` …

INSERT INTO `mytable` VALUES

('2003-02-28 23:07:00',1,1,0,1),

('2003-02-27 21:03:00',1,0,1,1),

etc.

This is essentially one VERY long insert statement …

Unfortunately, MySQL 4.1 seems to have limits on the total length of a statement (which does seem to make sense), so such a script on a large table invariably ends-up with an error message “The MySQL server has gone away” and nothing has been done. As I have found out belatedly, this would have been solved by unselecting “Create Bulk Insert Statements”. I believe this setting should be unselected by default – why choose a default which works only for small tables?

In addition, the “DROP TABLE” statement in the script caused my table to be dropped, loosing valuable data I had created since the backup – I had meant only to recreate past data, not to erase new data! I believe the “Include DROP TABLE statement” setting should definitely be OFF by default as it is potentially very dangerous. It would be equivalent to a data restore program starting off by deleting everything on your disk before even being sure it will complete the restore successfully …

[peterlaursen]

I don't agree.

But of course it's very unfortunate that you lose data. But it's the very common way to do things. MySQLAdministrator does the samme as well as the backup utilities for popular forum's software such as phpBB2, and other clients that I know.

If you are running the scirpt the base is “reset” to the state it was, when the script was created. You could use the “syncronize” function instead – provided that you have another MySQL installation each table has a Primary Key.

[Ritesh]

Everybody has different requirements.

SQLyog keeps track of last selected options. So once you uncheck it, it will start off with those options unchecked everytime. 😀

[patrickq]

Let me try to make my point once again (and I promise I'll stop there :-)):

1. I don't think it can ever make sense to create a script which you know at the time it is created that it will not run successfully. This is doubly true when that same script starts its life by dropping the table which it will then fail to restore …

2. More to the point: note that MySQLDump is apparently aware of the limitation on MySQL statement length. As a result, if you run mysqldump with default options, it too will generate long INSERT statements, BUT (and here comes the major difference) only as long as the net_buffer_length parameter. This parameter defaults to 1047551 so the result for each table is a series of INSERT statements each of that length or less, and the script works.

If I may, I suggest that you indeed keep the default options as they are, if you think it's the best default for most users, but internally limit the length of each statement to what MySQL allows.

[Shadow]

It's the max_allowed_packet variable that controls the max allowed length of an insert statement. It could be a major enhancement, if SQLyog recognized this variable while loading data from script files.

[marcospc]

Unchecking the bulk option for batch scripts is actually a pain in the ass. Have you tried to insert 250K rows one by one? I think this needs serious modification. Uncheking the option might be worth it for a very few number of cases.

M.

[Author]

Posts