Unsupported Screen Size: The viewport size is too small for the theme to render properly.

Limit Size Of Bulk Inserts

forums forums SQLyog Using SQLyog Limit Size Of Bulk Inserts

  • This topic is empty.
Viewing 1 reply thread
  • Author
    Posts
    • #12352
      breckognize
      Member

      I have a large table with 100+ columns and 20k rows. When I do a dump from SqlYog and check the “Create bulk insert statement”, the dump creates a file with a single insert statement followed by all the values (as expected). However, when I go to execute this script I get the error “MySql has gone away”. When I don't use the bulk insert option this doesn't happen. The other tables in my database perform fine with bulk inserts, but those tables are much smaller. MySql is choking on the resulting insert because it is so large.

      I was wondering if the default behavior for “Create bulk insert statement” for a dump could be modified to insert values in blocks of 10 or 50, as the old MySql Administrator GUI Tool did. That is, instead of 1 insert statement followed by a giant block of all rows in the table, have 1 insert statement for every 10-50 rows.

    • #32337
      ashwin
      Member
      Quote:
      I was wondering if the default behavior for “Create bulk insert statement” for a dump could be modified to insert values in blocks of 10 or 50, as the old MySql Administrator GUI Tool did. That is, instead of 1 insert statement followed by a giant block of all rows in the table, have 1 insert statement for every 10-50 rows.

      There is an option in Tools -> Preferences -> General tab -> under Export option- “Max. size (in K:cool: for BULK INSERT statement(s)”. The DUMP (SQL-file) will contain INSERT statements with the (textual) length as you specify here. The option to 'get server default' will use the 'max_allowed-packet' setting for the actual server. Refer attached image.

      NOTE: The size should be specified not larger than the max_allowed_packet setting on the server where you want to restore the dump.

      Regards,

      Ashwin A

Viewing 1 reply thread
  • You must be logged in to reply to this topic.