Exporting a database to another hangs after a while

Hi,

Using HeidiSQL_x64 v12.4.0.6659 on Windows 7 x64 on client side and Debian 10 buster, MySQL Ver 15.1 Distrib 10.4.28-MariaDB on server side.

I try to export a 8.4GiB local database to another local database, but hangs after a while. When setting inserting 5000 rows as option it seems always hangs. When setting inserting 1000 rows as option it seems sometimes the export finishes without error, sometimes hangs.

Earlier I use HeidiSQL 11.x and always worked without problems, never failed.

Sometimes after hours of hanging, it stops with errors like: / SQL Error (2013): Lost connection to server during query / or / SQL Error (1231): Variable 'sql_notes' can t be set to the value of '1' /

Is it possible, that it is a bug?

I found similar reports on this forum, some were libmysql.dll bug.

Any idea, what is wrong? Should I go back to HeidiSQL v11.x?

Thanks, Konrad Lorinczi

Do not worry too much if it just hangs, in which case HeidiSQL is busy with the export, without refreshing the GUI.

Errors like those you meantioned are a sign you probably should create the dump using other tools, like mysqldump. Creating such large exports always has its risk of losing a connection.

Hi Ansgar,

Thank you for your answer!

Yes, I was thinking about using mysqldump, but before I do, I was thinking about changing MySQL server settings.

Original settings:
# Default 2024-01-20: 
connect_timeout=10
net_read_timeout=30
net_write_timeout=60
max_allowed_packet=16777216

2024-01-21 Change1 (session & global):
connect_timeout=10
net_read_timeout=120
net_write_timeout=60
max_allowed_packet=16777216

I plan to increase these values one by one, hoping that I find the one which times out.

Which one do you recommend increasing and what are the suggested values?

Thanks,

Konrad Lorinczi