MySQL Bugs: #54063: huge SQL fails to execute via proxy

Bug #54063	huge SQL fails to execute via proxy
Submitted:	28 May 2010 12:08	Modified:	31 May 2010 16:44
Reporter:	Andrii Nikitin	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Enterprise Monitor: Agent	Severity:	S1 (Critical)
Version:	2.2.1.1717	OS:	Any
Assigned to:	Jan Kneschke	CPU Architecture:	Any

Description:
I generate big SQL file using attached script and try to execute it trough proxy:

a@u:~$ ./test.sh
a@u:~$ ls -lha o*
-rw-r--r-- 1 a a 21M 2010-05-28 13:31 o1.sql
-rw-r--r-- 1 a a 21M 2010-05-28 13:31 o.sql
a@u:~$ mysql -uservice_manager -p1 -P6446 -h127.0.0.1 test < o.sql 
ERROR 2013 (HY000) at line 2: Lost connection to MySQL server during query

Errors in proxy log:

2010-05-28 13:41:01: (critical) job_collect_mysql.c:2146: fetching the QUAN config failed
2010-05-28 13:42:29: (critical) chassis-unix-daemon.c:186: [angel] PID=27783 died on signal=9 (it used 0 kBytes max) ... waiting 3min before restart
2010-05-28 13:42:31: (critical) MySQL Monitor Agent 2.2.1.1717 started.

related to bug #35202

How to repeat:
1. Save bash script and give it execution privilege:
http://bugs.mysql.com/file.php?id=14855

2. execute it and make sure that output file is generated o.sql

3. execute file using proxy, e.g.:

mysql -P6446 -h127.0.0.1 test < o.sql

4. observe proxy restart

Suggested fix:
should execute command properly

Jan Kneschke writes: 
Can't get it to crash on Mac OS X 10.6 x86_64.

Used the script to generate the 21MByte INSERT statement and raise the max_allowed_packet on the MySQL Server side with:

    SET GLOBAL max_allowed_packet = 32 * 1024 * 1024;

and used the same on the client side:

   /opt/local/bin/mysql5 test --host=127.0.0.1 --port=4040 --max_allowed_packet=`perl -e 'print 32 * 1024 * 1024'` < o.sql

I tried 2.2.1.1717 and was unable to reproduce the bug, could you try running the agent under gdb? Maybe that would give us more information and it would also explain why you see the crash but we do not.

See https://inside.mysql.com/wiki/ET:QAdebugCrash#Using_gdb_.28.2AUnix.29 for information about how to run the agent under gdb (Note the 2.2 agent section)

Thanks to your bug report I found http://bugs.mysql.com/bug.php?id=54157

Detailed analysis of testcase shows that crash happened because of lack of virtual memory.
Agent memory usage increases to 1.4G for processing 21M query. 
Leaving in "Can't repeat" state.

Diego Medina writes: 
Just as a reference:

The agent starts at 4-5MB of ram

if you send a 1MB  insert, the agent will go to 35MB
If you send a 21MB file, it will reach about 650MB

This is not using ssl and no other query was going through the proxy port.

submitted bug 54304 related to agent memory usage when QUAN is enabled