Bulk Extraction and Insert Statement

[Marcwolf]

I have a quick question..

A long while ago I tried this and it failed.. but was wondering if the newer version of SQLyog has a solution.

When doing a extraction of a database/table and its contects we have the option to select the

Create Bulk Insert Statements flag.

Now – this can be a very useful feature if one has a LOT of data.. but a couple of time I have used it for very large tables I have got the error “SQL too long”

Does SQLYog now monitor the size of a Bulk Insert Statment and limit it so that it can perform say 5 or 6 bulk inserts that the system can handle rather than a loarge one that overflows a buffer.

Many thanks for your response

Dave

[CalEvans]

This is an issue with your server. (If I'm reading it right) There is a setting in your my.cnf on how big that statement can be. It's usually limited to 1MB but you can change it and then your statements will work. (I've had it up to 8MB before!) 🙂

=C=

[Marcwolf]

Ok.. This is useful information… However.

What happens when a query reaches this 8meg limit

Would it be possible for SQLYog to monitor a query size and ten put a break in it at a pre-determined size?

Many thanks for your reply thou.

Dave

[Ritesh]

As of SQLyog 3.71, we dont monitor size of bulk insert statements. You have to unselect BULK INSERT STATEMENT option, if the query is going beyond the limit.

This is a good feature request and I have added it up in our TO-DO list.

Thanks for your request.

[Marcwolf]

Thanks for that.

For example – last night I took a dump of our clients data so I could runs some tests on a new enhancement.

This is standard practice when checking developmetn issues and preparing to create an update script.

119000 (They there ARE that many 0's) queries later and about 3 hours of waiting.. the script completed its running

I have a pretty fast P4 development machine and a local copy of MySQL so its going as fast as it can.

The SQL script is generally in the 23 megs size too.

Take Care

Dave

[Shadow]

Increase bulk_insert_buffer_size (previously myisam_bulk_insert_tree_size) to a higher value to get better results.

[Author]

Posts