heidisql / Tickets / #1240 cannot copy a remote table with "export database as sql"

Originally posted by: rosenfie...@gmail.com

I see two issues here.

a)
The variable 'max_allowed_packet' is, as far as the vague manual will inform you,
simultaneously used for deciding the length of an internal per-connection buffer in
the server, and for limiting the byte length of accepted SQL queries.

Using the same variable for two completely different things is a classical trademark
in piss-poor design.

Semi-luckily, MySQL protocol messages are hardcoded to at most 16 MiB per message
(because the message length field is an unsigned 24-bit integer), at which point
incoming SQL strings are fragmented into multiple messages. What this means is that
if you set max_allowed_packet to anything larger than 16 MiB, the server just might
"only" use a 16 MiB buffer per connection, which is affordable in RAM, at least on a
64-bit server.

My recommendation would be:
- set max_allowed_packet to the maximum allowed on a test server (manual says 1
GiB),
- start the test server,
- note the memory usage
- connect with 1 client over TCP/IP
- note the memory usage again

Repeat the test with max_allowed_packet set to 16 MiB. If you see the same memory
usage in both scenarios, just leave it at the maximum setting.

Does this fix the problem?

b)
The server fails to give you an error message when it cuts the connection due to an
SQL string that is longer than 'max_allowed_packet'.

This probably happens because of a bug in the server or client networking code.

On the server side, the server may never send an error message, instead just cutting
the connection abruptly.

In the theoretical situation that the server behaved correctly and sent an error
message, the client would have to behave correctly too. In the event that it
discovers that the TCP connection is no longer alive, it would still have to read
the remaining messages off of the network stream before it aborts, and abort with
the error message sent by the server rather than what you see above.

If you want to report item b) as a bug to MySQL, you can find out which is broken,
client or server, by using Wireshark to capture packets while this happens. You
want to look for MySQL protocol message with a type of ERROR right before the
connection dies. If the error message is there, the server works but the client
that HeidiSQL uses (libmysql.dll, provided by MySQL) is broken. If the error
message is not there, the server is broken.

And a final note:
In theory, HeidiSQL could work around this issue by reading the SQL, recognizing the
extended insert syntax, and splitting it into multiple queries depending on what the
max_allowed_packet is for the destination server.

Similarly, HeidiSQL could recognize non-extended insert syntax and convert to the
(due to another protocol design flaw) much faster extended insert syntax on-the-fly.

Is it worth it to start working around considerable MySQL problems in a client like
HeidiSQL? In theory no, a fix by MySQL would fix things for many more clients. In
practice, political issues may make a workaround in HeidiSQL easier to accomplish in
a given timeframe. For now, I'm marking this issue as Upstream; but if you want I
can change it to a feature request.

Status: Upstream

*Originally posted by:* [rosenfie...@gmail.com](http://code.google.com/u/117240754853497489692/)

I see two issues here.

a\)
The variable 'max\_allowed\_packet' is, as far as the vague manual will inform you,
simultaneously used for deciding the length of an internal per-connection buffer in
the server, and for limiting the byte length of accepted SQL queries.

Using the same variable for two completely different things is a classical trademark
in piss-poor design.

Semi-luckily, MySQL protocol messages are hardcoded to at most 16 MiB per message
\(because the message length field is an unsigned 24-bit integer\), at which point
incoming SQL strings are fragmented into multiple messages.&nbsp; What this means is that
if you set max\_allowed\_packet to anything larger than 16 MiB, the server just might
&quot;only&quot; use a 16 MiB buffer per connection, which is affordable in RAM, at least on a
64-bit server.

My recommendation would be:
 \- set max\_allowed\_packet to the maximum allowed on a test server \(manual says 1
GiB\),
 \- start the test server,
 \- note the memory usage
 \- connect with 1 client over TCP/IP
 \- note the memory usage again

Repeat the test with max\_allowed\_packet set to 16 MiB.&nbsp; If you see the same memory
usage in both scenarios, just leave it at the maximum setting.

Does this fix the problem?

b\)
The server fails to give you an error message when it cuts the connection due to an
SQL string that is longer than 'max\_allowed\_packet'.

This probably happens because of a bug in the server or client networking code.

On the server side, the server may never send an error message, instead just cutting
the connection abruptly.

In the theoretical situation that the server behaved correctly and sent an error
message, the client would have to behave correctly too.&nbsp; In the event that it
discovers that the TCP connection is no longer alive, it would still have to read
the remaining messages off of the network stream before it aborts, and abort with
the error message sent by the server rather than what you see above.

If you want to report item b\) as a bug to MySQL, you can find out which is broken,
client or server, by using Wireshark to capture packets while this happens.&nbsp; You
want to look for MySQL protocol message with a type of ERROR right before the
connection dies.&nbsp; If the error message is there, the server works but the client
that HeidiSQL uses \(libmysql.dll, provided by MySQL\) is broken.&nbsp; If the error
message is not there, the server is broken.

Similarly, HeidiSQL could recognize non-extended insert syntax and convert to the
\(due to another protocol design flaw\) much faster extended insert syntax on-the-fly.

Is it worth it to start working around considerable MySQL problems in a client like
HeidiSQL?&nbsp; In theory no, a fix by MySQL would fix things for many more clients.&nbsp; In
practice, political issues may make a workaround in HeidiSQL easier to accomplish in
a given timeframe.&nbsp; For now, I'm marking this issue as Upstream; but if you want I
can change it to a feature request.

**Status:** Upstream

Add attachments
Cancel

You seem to have CSS turned off. Please don't fill out this field.

cannot copy a remote table with "export database as sql"

Searches

Help

#1240 cannot copy a remote table with "export database as sql"

Discussion