Search code examples
mysqlsqlyog

MySQL - 'a bulk size specified must be increased' during copy database attempt

I am trying to copy over a database, and have been able to do so for months without issue. Today however, I ran into an error that says 'A BULK size specified must be increased'. I am using SQLYog.

I didn't find much on google about this but it seems as tough it should be fixed by increasing the bulk_insert_buffer_size through something like 'SET SESSION bulk_insert_buffer_size = 1024 * 1024 * 256'. (Tried with GLOBAL instead of SESSION too)

This has not worked and I am still getting the error unfortunately. The only other bit of information I found was the source code where the message is generated as seen here: screenshot of source code from this page: https://github.com/Fale/sqlyog/blob/master/src/CopyDatabase.cpp. Unfortunately I really don't know what to do with that information. I tried looking through the code to find out what mysql variables (like bulk_insert_buffer_size) were tied to the variables used in the source code but wasn't able to follow it effectively.

Any help would be appreciated.

Solution

  • http://faq.webyog.com/content/24/101/en/about-chunks-and-bulks.html says you can specify BULK size:

    The settings for the 'export' tool are available from 'preferences' and for the 'backup' 'powertool' the option is provided by the backup wizard.

    You should make sure the BULK size is no larger than your MySQL Server's max_allowed_packet config option. This has a default value of 1MB, 4MB, or 64MB depending on your MySQL version.

    I'm not a user of SQLYog, but I know mysqldump has a similar concept. Mysqldump auto-calculates the max bulk size by reading the max_allowed_packet.

    For what it's worth, bulk_insert_buffer_size is not relevant for you unless you're copying into MyISAM tables. But in general, you shouldn't use MyISAM tables.