Bug #11088 Crash on query
Submitted: 3 Jun 2005 22:34 Modified: 21 Jun 2005 12:18
Reporter: Bara Mustafa Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:4.1.12 & 5.0.6 OS:Linux (Linux & Windows)
Assigned to: Igor Babaev CPU Architecture:Any

[3 Jun 2005 22:34] Bara Mustafa
Description:
I have a table with 1709 rows in it.  When I tried to do a query on it, the database died and had to be restarted.  I have tested this with MySQl v4.1.12 on Linux & v5.0.6-beta on Windows.  I will try to include all the data possible.

How to repeat:
CREATE TABLE `log_logins` (
  `idd` int(11) NOT NULL auto_increment,
  `user_id` int(11) NOT NULL default '0',
  `datetime` int(11) NOT NULL default '0',
  `ipaddress` varchar(16) NOT NULL default '',
  `browser` varchar(250) NOT NULL default '',
  `country` varchar(20) NOT NULL default '',
  `hostname` longtext NOT NULL,
  PRIMARY KEY  (`idd`),
  KEY `ipaddress` (`ipaddress`),
  KEY `browser` (`browser`),
  KEY `country` (`country`),
  KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1710;

INSERT INTO `log_logins` VALUES (15, 7, 1117282312, '205.188.117.69', 'Mozilla/4.0 (compatible; MSIE 6.0; AOL 9.0; Windows NT 5.1; SV1; iebar; .NET CLR 1.1.4322)', 'US', 'cache-dtc-af05.proxy.aol.com');
INSERT INTO `log_logins` VALUES (16, 3, 1117282438, '192.168.1.1', 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; Hotbar4.5.3.0)', 'AU', 'what.ever.com');
INSERT INTO `log_logins` VALUES (17, 7, 1117282522, '205.188.117.69', 'Mozilla/4.0 (compatible; MSIE 6.0; AOL 9.0; Windows NT 5.1; SV1; iebar; .NET CLR 1.1.4322)', 'US', 'cache-dtc-af05.proxy.aol.com');
INSERT INTO `log_logins` VALUES (18, 7, 1117282542, '205.188.117.69', 'Mozilla/4.0 (compatible; MSIE 6.0; AOL 9.0; Windows NT 5.1; SV1; iebar; .NET CLR 1.1.4322)', 'US', 'cache-dtc-af05.proxy.aol.com');
        

SELECT hostname AS name, COUNT(DISTINCT user_id) AS no
FROM log_logins
WHERE hostname LIKE '%aol%'
GROUP BY hostname
[3 Jun 2005 22:37] Bara Mustafa
I forgot to mention the returned message I get when using PHP is:
#2006 - MySQL server has gone away
[3 Jun 2005 23:37] MySQL Verification Team
Thank you for the bug report I was able to repeat with current BK source
5.0 server.

050604  2:29:38 [Note] /home/miguel/dbs/5.0/libexec/mysqld: ready for connections.
Version: '5.0.7-beta-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread 180236 (LWP 11539)]

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 180236 (LWP 11539)]
0x081ca927 in String::real_alloc (this=0x8e98d9c, arg_length=0) at sql_string.cc:57
warning: Source file is more recent than executable.

57        }
(gdb) backtrace full
#0  0x081ca927 in String::real_alloc (this=0x8e98d9c, arg_length=0) at sql_string.cc:57
No locals.
#1  0x0815fb01 in String (this=0x8e98d9c, length_arg=4294967295) at sql_string.h:48
No locals.
#2  0x0815f825 in Item_str_buff (this=0x8e98d90, arg=0x8e971e8) at item.h:1502
No locals.
#3  0x0815eef9 in new_Item_buff (item=0x8e971e8) at item_buff.cc:33
No locals.
#4  0x0823cfd2 in alloc_group_fields (join=0x8e97920, group=0x8e978d8) at sql_select.cc:12247
        tmp = (class Item_buff *) 0xbe3fefcc
#5  0x0823cf73 in make_group_fields (main_join=0x8e97920, curr_join=0x8e97920) at sql_select.cc:12227
No locals.
#6  0x082235d1 in JOIN::exec (this=0x8e97920) at sql_select.cc:1481
        tmp_error = -2138813952
        _db_func_ = 0x8e7b634 " ré\b@té\b\002"
        _db_file_ = 0x8e7b390 "H×a\b(ï\177\b,ï\177\bX×a\b(xé\b qé\b"
        _db_level_ = 149518624
---Type <return> to continue, or q <return> to quit---
        _db_framep_ = (char **) 0x8e7b5c4
        curr_join = (JOIN *) 0x8e97920
        curr_all_fields = (List<Item> *) 0x8e9864c
        curr_fields_list = (List<Item> *) 0x8e7b634
        curr_tmp_table = (TABLE *) 0x0
#7  0x08224e31 in mysql_select (thd=0x8e7b390, rref_pointer_array=0x8e7b6d4, tables=0x8e97488, wild_num=0,
    fields=@0x8e7b634, conds=0x8e97718, og_num=1, order=0x0, group=0x8e978d8, having=0x0, proc_param=0x0,
    select_options=2156153344, result=0x8e97910, unit=0x8e7b3e0, select_lex=0x8e7b5c4) at sql_select.cc:2042
        err = false
        free_join = true
        _db_func_ = 0x8e7b5c4 "\2104_\b"
        _db_file_ = 0xbe3ff10c "ĵç\bĵç\bà³ç\b\020yé\b,ñ?¾I9\037\b\020yé\b\001"
        _db_level_ = 4294967295
        _db_framep_ = (char **) 0xffffffff
        join = (JOIN *) 0x8e97920
#8  0x0821f325 in handle_select (thd=0x8e7b390, lex=0x8e7b3d0, result=0x8e97910, setup_tables_done_option=0)
    at sql_select.cc:242
---Type <return> to continue, or q <return> to quit---
        unit = (SELECT_LEX_UNIT *) 0x8e7b3e0
        res = false
        select_lex = (SELECT_LEX *) 0x8e7b5c4
        _db_func_ = 0x8e97910 "ÈÖa\b\220³ç\bà³ç\b\217\217\217\217\030\212é\bÄí?¾è\210é\b"
        _db_file_ = 0x81f3949 "\213E\bÇ"
        _db_level_ = 3191861548
        _db_framep_ = (char **) 0x8e97910
#9  0x081e6e56 in mysql_execute_command (thd=0x8e7b390) at sql_parse.cc:2400
        result = (class select_result *) 0x8e97910
        res = false
        result = 0
        lex = (LEX *) 0x8e7b3d0
        select_lex = (SELECT_LEX *) 0x8e7b5c4
        slave_fake_lock = false
        fake_prev_lock = (MYSQL_LOCK *) 0x0
        first_table = (TABLE_LIST *) 0x8e97488
        all_tables = (TABLE_LIST *) 0x8e97488
---Type <return> to continue, or q <return> to quit---
        unit = (SELECT_LEX_UNIT *) 0x8e7b3e0
        _db_func_ = 0x0
        _db_file_ = 0x0
        _db_level_ = 0
        _db_framep_ = (char **) 0x8e7b3d0
#10 0x081ef6b1 in mysql_parse (thd=0x8e7b390,
    inBuf=0x8e97130 "SELECT hostname AS name, COUNT(DISTINCT user_id) AS no FROM log_logins WHERE hostname LIKE '%aol%' GROUP BY hostname", length=116) at sql_parse.cc:5303
        lex = (LEX *) 0x8e7b3d0
        _db_func_ = 0x8e8f145 ""
        _db_file_ = 0x0
        _db_level_ = 12
        _db_framep_ = (char **) 0x40051530
#11 0x081e4f72 in dispatch_command (command=COM_QUERY, thd=0x8e7b390,
    packet=0x8e8f0d1 "SELECT hostname AS name, COUNT(DISTINCT user_id) AS no FROM log_logins WHERE hostname LIKE '%aol%' GROUP BY hostname", packet_length=117) at sql_parse.cc:1653
        packet_end = 0x8e971a4 ""
---Type <return> to continue, or q <return> to quit---
        net = (NET *) 0x8e7bb04
        error = false
        _db_func_ = 0x0
        _db_file_ = 0x0
        _db_level_ = 0
        _db_framep_ = (char **) 0x0
#12 0x081e4782 in do_command (thd=0x8e7b390) at sql_parse.cc:1456
        packet = 0x8e8f0d0 "\003SELECT hostname AS name, COUNT(DISTINCT user_id) AS no FROM log_logins WHERE hostname LIKE '%aol%' GROUP BY hostname"
        old_timeout = 30
        packet_length = 117
        net = (NET *) 0x8e7bb04
        command = COM_QUERY
        _db_func_ = 0x8e7cb50 "ÿÿÿÿ"
        _db_file_ = 0x81bbd44 "ÉÃU\211å\203ì(\213E\b\211\004$è¡"
        _db_level_ = 3191863788
        _db_framep_ = (char **) 0x1010
---Type <return> to continue, or q <return> to quit---
#13 0x081e38d7 in handle_one_connection (arg=0x8e7b390) at sql_parse.cc:1114
        error = 0
        net = (NET *) 0x8e7bb04
        thd = (class THD *) 0x8e7b390
        launch_time = 0
        set = {__val = {0 <repeats 32 times>}}
#14 0x40187e51 in pthread_start_thread () from /lib/libpthread.so.0
No symbol table info available.
#15 0x40187ecf in pthread_start_thread_event () from /lib/libpthread.so.0
No symbol table info available.
#16 0x4030f92a in clone () from /lib/libc.so.6
No symbol table info available.
(gdb)
[4 Jun 2005 0:18] MySQL Verification Team
Below call stack for server 4.1.13 BK on Windows:

/sql/sql_string.cpp
--53--
    if (!(Ptr=(char*) my_malloc(arg_length,MYF(MY_WME))))
      return TRUE;
    Alloced_length=arg_length;
    alloced=1;
  }
  Ptr[0]=0;
^^^^^^^^^^^^^^^^^^^^^crashes here
  return FALSE;
}

>	mysqld-debug.exe!String::real_alloc(unsigned long arg_length=0)  Line 58 + 0x5	C++
 	mysqld-debug.exe!String::String(unsigned long length_arg=4294967295)  Line 49	C++
 	mysqld-debug.exe!Item_str_buff::Item_str_buff(Item * arg=0x02fb8b08)  Line 1121 + 0x2a	C++
 	mysqld-debug.exe!new_Item_buff(Item * item=0x02fb8b08)  Line 32 + 0x1f	C++
 	mysqld-debug.exe!alloc_group_fields(JOIN * join=0x02fb9130, st_order * group=0x02fb90e8)  Line 8637 + 0xe	C++
 	mysqld-debug.exe!make_group_fields(JOIN * main_join=0x02fb9130, JOIN * curr_join=0x02fb9130)  Line 8617 + 0x13	C++
 	mysqld-debug.exe!JOIN::exec()  Line 1352 + 0xd	C++
 	mysqld-debug.exe!mysql_select(THD * thd=0x00efced0, Item * * * rref_pointer_array=0x00efd108, st_table_list * tables=0x02fb8e38, unsigned int wild_num=0, List<Item> & fields={...}, Item * conds=0x02fb8fa8, unsigned int og_num=1, st_order * order=0x00000000, st_order * group=0x02fb90e8, Item * having=0x00000000, st_order * proc_param=0x00000000, unsigned long select_options=2189707776, select_result * result=0x02fb9120, st_select_lex_unit * unit=0x00efcf1c, st_select_lex * select_lex=0x00efd01c)  Line 1607	C++
 	mysqld-debug.exe!handle_select(THD * thd=0x00efced0, st_lex * lex=0x00efcf10, select_result * result=0x02fb9120)  Line 195 + 0x87	C++
 	mysqld-debug.exe!mysql_execute_command(THD * thd=0x00efced0)  Line 2087 + 0x11	C++
 	mysqld-debug.exe!mysql_parse(THD * thd=0x00efced0, char * inBuf=0x02fb8a58, unsigned int length=116)  Line 4222 + 0x9	C++
 	mysqld-debug.exe!dispatch_command(enum_server_command command=COM_QUERY, THD * thd=0x00efced0, char * packet=0x02fb0899, unsigned int packet_length=117)  Line 1503 + 0x1d	C++
 	mysqld-debug.exe!do_command(THD * thd=0x00efced0)  Line 1316 + 0x31	C++
 	mysqld-debug.exe!handle_one_connection(void * arg=0x00efced0)  Line 1048 + 0x9	C++
 	mysqld-debug.exe!pthread_start(void * param=0x00f0f520)  Line 63 + 0x7	C
 	mysqld-debug.exe!_threadstart(void * ptd=0x00f0fde0)  Line 173 + 0xd	C
 	kernel32.dll!7c80b50b() 	
 	kernel32.dll!7c8399f3()
[7 Jun 2005 10:15] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/25685
[7 Jun 2005 10:56] Igor Babaev
The crash can be demonstrated with a simpler script:

CREATE TABLE t1 (id int PRIMARY KEY, user_id int, hostname longtext);

INSERT INTO t1 VALUES
  (1, 7, 'cache-dtc-af05.proxy.aol.com'),
  (2, 3, 'what.ever.com'),
  (3, 7, 'cache-dtc-af05.proxy.aol.com'),
  (4, 7, 'cache-dtc-af05.proxy.aol.com');

SELECT hostname, COUNT(DISTINCT user_id) as no FROM t1
  WHERE hostname LIKE '%aol%'
    GROUP BY hostname;

The crash happens due to an attempt to allocate a buffer of size 4294967295 bytes for the group value. Now the size of this buffer is limited by the value of  max_sort_length.
[7 Jun 2005 14:23] Igor Babaev
ChangeSet
  1.2317 05/06/07 03:05:57 igor@rurik.mysql.com +5 -0
  sql_select.cc, item_buff.cc, item.h:
    Fixed bug #11088: a crash for queries with GROUP BY a BLOB column
    + COUNT(DISTINCT...) due to an attempt to allocate a too large
    buffer for the BLOB field.
    Now the size of the buffer is limited by max_sort_length.
  group_by.test, group_by.result:
    Added a test case for bug #11088.

The fix will appear in 4.1.13 and 5.0.7.
[10 Jun 2005 18:08] Paul DuBois
Noted in 4.1.13, 5.0.7 changelogs.
[21 Jun 2005 11:24] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26234