Bug #30663 MySQL with stored function allocates a lot of memory with possible DoS attack
Submitted: 28 Aug 2007 7:15 Modified: 29 Aug 2007 7:21
Reporter: Vadim TKACHENKO Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.0.45 OS:Any
Assigned to: CPU Architecture:Any
Tags: qc

[28 Aug 2007 7:15] Vadim TKACHENKO
Description:
When I use query with stored function against the table with million of rows, MySQL allocates GBs of memory, that freezes the server.
More details available here:
http://www.mysqlperformanceblog.com/2007/08/26/memory-allocation-in-stored-function/

How to repeat:
the function 
CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50) RETURN CONCAT('Hello, ',s,'!'); 

Table
CREATE TABLE `testf` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=20971521 DEFAULT CHARSET=latin1
 
mysql> SELECT count(*) FROM testf;
+----------+
| count(*) |
+----------+
| 20971520 | 
+----------+
1 row IN SET (0.00 sec) 

name - is random strings

Query:
SELECT avg(length(hello(name))) FROM testf; 

In 85 sec MySQL allocates 5Gb of memory.
[28 Aug 2007 9:23] Sveta Smirnova
Thank you for the report.

I can not repeat described behaviour in my environment.

Please provide your configuration file, output of `uname -a` and SHOW TABLE STATUS LIKE 'testf';
[28 Aug 2007 9:29] Vadim TKACHENKO
Hello,
uname -a
Linux hostname 2.6.9-55.0.2.ELsmp #1 SMP Tue Jun 26 14:14:47 EDT 2007 x86_64 x86_64 x86_64 GNU/Linux

mysql> SHOW TABLE STATUS LIKE 'testf';
+-------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| Name  | Engine | Version | Row_format | Rows     | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options | Comment |
+-------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| testf | MyISAM |      10 | Dynamic    | 20971520 |            266 |  5595298560 | 281474976710655 |    215552000 |         0 |       20971521 | 2007-08-26 15:14:51 | 2007-08-26 15:30:36 | NULL       | latin1_swedish_ci |     NULL |                |         | 
+-------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)

my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
log-slow-queries
long-query-time=2

[mysql.server]
user=mysql
basedir=/usr

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[28 Aug 2007 17:09] Sveta Smirnova
Thank you for the feedback.

We can not repeat described behaviour in our environment.
[28 Aug 2007 21:20] Vadim TKACHENKO
Sveta,

Could you post output of vmstat 5 during execution of query on your system ?
It would be allow me to figure out what is going wrong on my box.
[29 Aug 2007 7:09] Sveta Smirnova
vmstat output

Attachment: bug30663-vmstat.txt (text/plain), 23.29 KiB.

[29 Aug 2007 7:09] Sveta Smirnova
Vadim,

output of vmstat is attached. I run it many times with short intervals.
[29 Aug 2007 7:21] Vadim TKACHENKO
Sveta,

Just vmstat shows average values since the start of system and so does not show the current load on the box.
To estimate current load vmstat should show several lines, you can run, for example, vmstat 1 and grab 5 lines.

Thanks,
Vadim.
[29 Aug 2007 8:23] Sveta Smirnova
vmstat output

Attachment: bug30663-vmstat2.txt (text/plain), 238.90 KiB.

[29 Aug 2007 8:24] Sveta Smirnova
Vadim,

requested output attached.