Bug #67448 max_allowed_packet problem on mysql client but not mysqldump
Submitted: 1 Nov 2012 13:13 Modified: 29 Jan 2013 17:01
Reporter: Sheeri Cabral (Candidate Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S4 (Feature request)
Version:5.1.63 OS:Linux
Assigned to: CPU Architecture:Any

[1 Nov 2012 13:13] Sheeri Cabral
Description:
We are having a problem with max_allowed_packet:

mysql>  SELECT arg FROM ts_job WHERE jobid=83415224;
ERROR 2020 (HY000): Got packet bigger than 'max_allowed_packet' bytes

OK, let's see what max_allowed_packet is:

mysql>  SELECT @@max_allowed_packet, length(arg), @@max_allowed_packet-length(arg) as difference FROM ts_job WHERE jobid=83415224;
+----------------------+-------------+------------+
| @@max_allowed_packet | length(arg) | difference |
+----------------------+-------------+------------+
|            134217728 |    28253199 |  105964529 |
+----------------------+-------------+------------+
1 row in set (0.01 sec)

So max_allowed_packet is 128M, we're trying to get a field with a length of <30M. Maybe it's a charset issue?

mysql> \P grep arg
PAGER set to 'grep arg'
mysql> show create table ts_job;
  `arg` longblob,

Not a charset issue, because it's a LONGBLOB, and those have no charset.

Interestingly, mysqldump works just fine, with a 32M max_allowed_packet size set:

# mysqldump bugs ts_job --where " jobid=83415224" > bigarg.sql
# ls -lh bigarg.sql 
-rw-r--r-- 1 root root 29M Nov  1 06:05 bigarg.sql

[root@tp-bugs01-slave01 ~]# mysqldump --help | grep packet
  --max_allowed_packet=# 
                      The maximum packet length to send to or receive from
max_allowed_packet                33554432

and this is defined in /etc/my.cnf:
[mysqldump]
quick
max_allowed_packet=32M

(and there's no .my.cnf, etc)

And obviously the data got IN somehow, via a web application, but the same web application can't get the data OUT (which is how we found the issue, because the web application was giving errors).

How to repeat:
Don't know if it's repeatable but you can try making a table, inserting data, and trying to get it out.

Suggested fix:
Make it so that we can retrieve the data.
[1 Nov 2012 13:59] Jesper Hansen
Thought I would add my findings here as well:
* mysqldump uses SELECT INTO OUTFILE meaning it completely ignores 'max_allowed_packet' hence mysqldump cannot be used as a reference for "something that works"
* mysql client only checks if len == packet_error or len == 0.
./sql-common/client.c - #696-708
  if (len == packet_error || len == 0)
  {
    DBUG_PRINT("error",("Wrong connection or packet. fd: %s  len: %lu",
			vio_description(net->vio),len));
#ifdef MYSQL_SERVER
    if (net->vio && vio_was_interrupted(net->vio))
      return (packet_error);
#endif /*MYSQL_SERVER*/
    end_server(mysql);
    set_mysql_error(mysql, net->last_errno == ER_NET_PACKET_TOO_LARGE ?
                    CR_NET_PACKET_TOO_LARGE: CR_SERVER_LOST, unknown_sqlstate);
    return (packet_error);
  }
[1 Nov 2012 18:37] Jesper Hansen
I compiled a windows build of 5.1.66 and these are the results:
* The connection is dropped when:
mysql> SELECT args FROM sheeri;
ERROR 2020 (HY000): Got packet bigger than 'max_allowed_packet' bytes
mysql>
mysql> SELECT @@global.max_allowed_packet, @@session.max_allowed_packet;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    23
Current database: test

+-----------------------------+------------------------------+
| @@global.max_allowed_packet | @@session.max_allowed_packet |
+-----------------------------+------------------------------+
|                   335544320 |                    335544320 |
+-----------------------------+------------------------------+
1 row in set (0.01 sec)

setup:
* test table two columns: id (ai), args (longblob)
* 30MB single row filled into table
* 320MB max_allowed_packet

Compared against: 5.5.28-MariaDB-mariadb1~precise-log
* Connection not dropped
* 32MB max_allowed_packet
[1 Nov 2012 19:53] Jesper Hansen
I grabbed a look at loopback device in wireshark and compared it against my 30MB file.
I found mysql got around to ~16,787,270 (might be few packages off) before the client asked the server to RST the connection.
Pretty close to 16*1024*1024 = 16,777,216.
http://i.imgur.com/jYjFL.png

Is there an internal limit besides max_allowed_packet that we don't know about?
[1 Nov 2012 20:02] Jesper Hansen
Final findings and cause and workaround:
* SELECT @@global.max_allowed_packet, @@session.max_allowed_packet; does not mean the mysql command line follows these limits in 5.1
* Found mysql --help showed max_allowed_packet is 16M
* Workaround: mysql --max_allowed_packet=60M
[1 Nov 2012 20:04] Brandon Johnson
It would appear the client max_allowed_packet is being exceeded.

This defaults to 16MB if not set in the [mysql] section of the my.cnf, and for some odd reason doesn't follow the server's max_allowed_packet.

Try setting it exclusively in the my.cnf under [mysql]
[2 Nov 2012 13:29] Sheeri Cabral
The work-around does, indeed work.

I admit this may not be a bug, but a feature, and I'm assuming it's a bug because I'm assuming the client takes the max_allowed_packet value as default from the server.

HOWEVER, I don't see any way to actually *view* the max_allowed_packet variable for the client - @@session.max_allowed_packet shows the same as @@global.max_allowed_packet. Maybe there needs to be something like max_allowed_packet_client, just like there's client variables for charsets?
[3 Nov 2012 14:24] Shane Bester
The C API has a 1G limit.  So, this is a feature request to remove the ancient 16M default value of the client utilities (mysql, mysqldump).

I assume in the 90's when normal computers were Pentium 100mhz with 64M ram, a 16M limit made sense.  Now, it makes no sense.
[13 Nov 2012 17:44] Sveta Smirnova
Thank you for the report.

What is the output of  SELECT count(arg) FROM ts_job WHERE jobid=83415224;?
[13 Nov 2012 18:00] Sheeri Cabral
The result is 1.
[22 Dec 2012 12:30] Shane Bester
Unless specified in [client] or [mysql] section of my.cnf, mysql client sets it's own packet limit to 16M. 

---
{"max_allowed_packet", OPT_MAX_ALLOWED_PACKET,
   "The maximum packet length to send to or receive from server.",
   &opt_max_allowed_packet, &opt_max_allowed_packet, 0,
   GET_ULONG, REQUIRED_ARG, 16 *1024L*1024L, 4096,
   (longlong) 2*1024L*1024L*1024L, MALLOC_OVERHEAD, 1024, 0},
---

A client also cannot discover his own max_allowed_packet setting by querying @@max_allowed_packet.

So, this is a feature request to either remove the mysql client 16M builtin default (and use protocol limit of 1G) or to increase it.  Furthermore, you cannot change a session's max_allowed_packet after connecting.
[28 Jan 2013 16:33] Georgi Kodinov
Sheeri,

If you're selecting megabytes of binary data using an interactive client chances are you're doing it wrong.
So the 16M limit is actually pretty generous already IMHO.
The problem is that mysql is much more than an interactive client. It's a scripting tool too. And there it makes a lot of sense to do as you're suggesting. 

How about a compromise ? 
Let's keep the current limit for mysql interactive sessions and increase it drastically as you're suggesting for non-interactive clients.
[29 Jan 2013 6:00] Sheeri Cabral
Is it actually that difficult to allow the interactive client limit to be raised? 

Are there 2 different limits, one for interactive clients and one for non-interactive? If so, there should be visible variables into both of them. 

I'd rather be able to increase both, because we're working with bigger and bigger data these days, and querying megabytes of data isn't "wrong".

Whatever is done, we need *visibility* into what's going on. I tried to query the max_allowed_packet variable and ended up getting the server variable, not the client variable. I should be able to see the client variable when I query, even if it's unchangeable in interactive sessions.