Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

Crash at end of export

jmlsteele's profile image jmlsteele posted 3 years ago in Import/Export Permalink

Using latest nightly (Revision 5116 (yours: 5116))

Export from one server (remote) to another (localhost)

Remote Server: 5.6.26-enterprise-commercial-advanced-log

Localhost Server: 10.1.13-MariaDB

Log shows the following:

[localhost] /*!40000 ALTER TABLE `workbench_moderation_transitions` ENABLE KEYS */;
[localhost] 
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
[localhost] /*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/* [localhost] SQL Error (1231): Variable 'foreign_key_checks' can't be set to the value of '1' */

Bug report attached.

If I click on continue HeidiSQL appears to continue to work fine. I'm unable to click on the cancel button to close the export window, and escape does nothing, but closing the window with the X works fine.

I did notice one of the tables early in the export process had an error and in the log the following appears:

/* [localhost] SQL Error (1153): Got a packet bigger than 'max_allowed_packet' bytes */
1 attachment(s):
jmlsteele's profile image jmlsteele posted 3 years ago Permalink

Changing the Max INSERT size to a larger value (5120) causes different tables to throw the "got a bigger packet" error, and the original table that throws said error to throw a disconnect error.

Changing it to 0 causes the same table to throw an error.

Remote server has a max packet of 16M, localhost has a max packet of 1M.

The table in question has a longblob field. The longest length is ~800k so I could see an instance where a single row is indeed over the 1mb packet length.

Increasing localhost's max packet value (while leaving it set to 1024 KB within HeidiSQL) lets the export/import work properly, and there's no crash when it completes.

ansgar's profile image ansgar posted 3 years ago Permalink

Yes, that is exactly the problem - 1M allowed packet size on the target server, and 1 row having more than 1M of data. In such cases the only way to prevent such errors is to increase the allowed packet size on the target server.

Setting the "Max INSERT size" in HeidiSQL's export dialog to 0K lets you effectively export one by one row, which can be very slow when having many small rows. You should be safe to leave it at 1024K, at least if the target server has 1M or more of one allowed packet size. Having a larger row to export is the only situation where you then run into issues.

Please login to leave a reply, or register at first.