2

I'm working on a tool that generates and runs SQL against a MySQL database. I'm trying to add the ability to limit the size of a insert statement based on a user-specified setting. I found some info about the max_allowed_packet setting, but I haven't found anything that explains how to calculate a packet's size. The MySQL docs say that "A communication packet is a single SQL statement sent to the MySQL server," but I don't understand how the size of the SQL statement is determined.

I'm passing a SQL statement and some parameters to my driver:

cmd.CommandText= "INSERT INTO myTable VALUES(@int, @date, @text)";
cmd.Parameters.AddWithValue("@int", 1);
cmd.Parameters.AddWithValue("@date", DateTime.Now);
cmd.Parameters.AddWithValue("@text", "how big is this???");
cmd.ExecuteNonQuery();

Is the size of the packet just the sum of the bytes used in the string representation of all the variables and the SQL statement? Or should I figure out the size of each parameter based on how much space they would take up in the database and sum that with the bytes used in the SQL string? Or something else? I'm not really sure how database drivers work, so maybe some context around that would be helpful, if you know anything about them.

Microsoft Excel
  • 159
  • 2
  • 11
  • Out of shear curiosity, why do you want to limit the size of an insert statement? – Sam M Dec 15 '17 at 23:57
  • 2
    I've run into the max_packet_allowed limit a couple times, and I'd rather not change that setting just for some edge cases in the app. – Microsoft Excel Dec 17 '17 at 02:10

2 Answers2

3

Turns out that the max_allowed_packet is compared to the size of the resulting query that was sent across the wire in ASCII encoding. I just subbed the parameter values into the SQL statement instead of the parameter names and took the size of the resulting string. The number of bytes was a spot-on prediction of whether or not the MySQL would reject the statement, so I could figure out when to start a new statement based on that number.

Microsoft Excel
  • 159
  • 2
  • 11
0

My understanding of packet size, as defined in MySql max_packet_allowed, comes from the documentation. I spent some time looking for a definitive answer, without any success. So I'm assuming my answer is correct but YMMV and you may still run into issues.

You must increase [max_packet_allowed] value if you are using large BLOB columns or long strings. It should be as big as the largest BLOB you want to use. The protocol limit for max_allowed_packet is 1GB. The value should be a multiple of 1024; nonmultiples are rounded down to the nearest multiple.

The above quote is from the v5.5 documentation at https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_max_allowed_packet.

From the statement, "it should be as big as the largest BLOB you want to use", I infer that the max_packet_size is based on column size, not the sql statement size. Column data size is going to be dependent on content and encoding. Check your character set and collation settings to find out if you are using 2-byte or 4-byte per character.

An easy way to verify whether it is statement size or column size is to have a simple table with two text columns. Suppose your max_packet_allowed is 16M on both the client and server. Run an insert statement where one of the columns is 8M and the other column is 10M (total of 18M). If the statement fails to run, it's the statement size. If it doesn't fail, you can assume it is based on column size.

As you implement a packet size check against max_packet_allowed, it is important to remember that BOTH the server and the client have this value set. And whichever is lower is the real maximum. Because the client can set it, you cannot assume they are always the same.

I'm more than happy to edit this answer if additional references are provided to correct information. And if I'm way off base, I will gladly delete the answer.

Sam M
  • 4,136
  • 4
  • 29
  • 42