0

If you look around the internet (e.g. What is maximum query size for mysql?), you will find claims, that the maximum query size for MariaDB/MySQL equals the value of the max_allowed_packet variable. But when you actually try that, you will find that this is not exactly true.

Here is a small Lua script that generates a test query of a desired size:

prefix = "SELECT IF(0,'"
postfix = "',NULL);"
max_allowed_packet = 16777216 -- The value for my setup

function gen_query(size)
    local n = size - string.len(prefix) - string.len(postfix)
    local query = prefix .. string.rep('X', n) .. postfix
    io.open("/tmp/testquery.sql", "w"):write(query):close()
end

gen_query(max_allowed_packet)

/tmp/testquery.sql now contains a query like SELECT IF(0,'<a lot of Xs here>',NULL);.

If we now try to execute this, we will get an error:

$ mysql -A -B < /tmp/testquery.sql
ERROR 1153 (08S01) at line 1: Got a packet bigger than 'max_allowed_packet' bytes

If we do the same thing, but with gen_query(max_allowed_packet - 1) (generating a query one byte smaller), the server will happily execute the query:

$ mysql -uroot -pmysql --protocol=tcp -A -B < /tmp/testquery.sql 
IF(0,'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
NULL

If we dig around the documentation of MySQL (couldn't find the equivalent for MariaDB), this begins to make sense, as the packet includes an extra byte (0x03) that announces the packet as a COM_QUERY packet: https://dev.mysql.com/doc/internals/en/com-query.html

So far this all makes sense to me, but if we go ahead and modify our lua program to not include a semicolon at the end (by setting postfix = "',NULL)"), this is suddenly the result again:

$ mysql -uroot -pmysql --protocol=tcp -A -B < /tmp/testquery.sql
ERROR 1153 (08S01) at line 1: Got a packet bigger than 'max_allowed_packet' bytes

The test query is still max_allowed_packet - 1 bytes long:

$ wc -c /tmp/testquery.sql
16777215 /tmp/testquery.sql

A max_allowed_packet - 2 bytes long query without the semicolon works again as expected.

What is going on? Does the mysql command line client / the mysql library automatically insert a ; at the end? As far as I can tell, it doesn't, here is an excerpt of an strace output:

$ strace -v -e write=3 mysql -A -B < /tmp/testquery.sql
<<< irrelevant syscalls omitted >>>
sendto(3, "\377\377\377\0\3SELECT IF(0,'XXXXXXXXXXXXXX"..., 16384, 0, NULL, 0) = 16384
 | 00000  ff ff ff 00 03 53 45 4c  45 43 54 20 49 46 28 30  .....SELECT IF(0 |
 | 00010  2c 27 58 58 58 58 58 58  58 58 58 58 58 58 58 58  ,'XXXXXXXXXXXXXX |
<<< lots of hexdump data omitted >>>
 | ffbff0  58 58 58 58 58 58 58 58  58 58 58 58 27 2c 20 4e  XXXXXXXXXXXX', N |
 | ffc000  55 4c 4c                                          ULL              |
sendto(3, "\1\0\0\1)", 5, 0, NULL, 0)   = 5
 | 00000  01 00 00 01 29                                    ....)            |
<<< more irrelevant syscalls omitted >>>

Can I send a query that is max_allowed_packet - 2 bytes long and always expect it to work (given there are no errors in the sql query, of course :) )? And is this behavior documented somewhere?

Laria Chabowski
  • 93
  • 1
  • 6
  • 1
    semi colon are not permit in COM_QUERY (if MULTI_STATEMENTS capability if not set, and i would imagine it's default for your lua driver). I don't know the driver you use, but the semicolon is probably removed from query. That would explain your result – Diego Dupin Aug 28 '18 at 22:30
  • @DiegoDupin: I only used Lua to generate the query itself, the query was executed with the mysql command line tool. Nontheless, this explains the different behavior, since the semicolon is not transmitted to the server in the first place (verified it with strace). Thank you, this really helped me understand what's going on. I still don't fully understand, why the max query size is max_allowed_packet - 2 and not max_allowed_packet - 1, though. – Laria Chabowski Aug 29 '18 at 09:03

0 Answers0