Bug #10619 GROUP_CONCAT() crashes mysqld
Submitted: 13 May 2005 11:18 Modified: 2 Jun 2005 19:17
Reporter: Torsten Kasch Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:4.1.11/4.1.12 OS:Solaris (Solaris 9 (SPARC)/Windows)
Assigned to: Igor Babaev CPU Architecture:Any

[13 May 2005 11:18] Torsten Kasch
Description:
Some days ago, we had to experience several crashes of our production MySQL server (self-compiled 64bit binaries from 4.1.9 sources) on Solaris 9 (SPARC). I tracked this issue down and found that a quite complex query of one of our users involving a GROUP_CONCAT() statement was the cause.

I've set up two test systems (both Solaris 9 SPARC), used the official mysql-debug-4.1.11-sun-solaris2.9-sparc-64bit binaries and was able to reproduce the crashes on these systems. Below is a (much simpliefied) test case that results in mysqld crashing on our systems.

The client gets the following error message:

ERROR 2013 (HY000): Lost connection to MySQL server during query

The error log shows:
--- snip ---
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=268435456
read_buffer_size=1044480
max_used_connections=1
max_connections=100
threads_connected=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 466543 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
--- snip ---

How to repeat:
Create a database with the follwing tables and data:

DROP TABLE IF EXISTS clone;
CREATE TABLE clone (
        uid MEDIUMINT(9) NOT NULL auto_increment,
        id VARCHAR(30) NOT NULL default '',
        alter_name VARCHAR(30) NOT NULL default '',
        alternative_id VARCHAR(30) NOT NULL default '',
        seq_type ENUM('EST','cDNA','GENOMIC','RFLP') NOT NULL default 'cDNA',
        PRIMARY KEY  (uid),
        KEY name_idx (id, alter_name, alternative_id)
);

INSERT INTO clone VALUES (1, 'A-1', 'a-1', 'KI-1', 'ABC' );
INSERT INTO clone VALUES (2, 'A-2', 'a-2', 'KI-2', 'DEF' );

DROP TABLE IF EXISTS sts;
CREATE TABLE sts (
        uid MEDIUMINT(9) NOT NULL default '0',
        workgroup_id SET('KO','KI','GA') default NULL,
        PRIMARY KEY  (uid)
);

INSERT INTO sts VALUES (1, 'KO');
INSERT INTO sts VALUES (2, 'KO');

SELECT GROUP_CONCAT(DISTINCT sts.workgroup_id SEPARATOR ',')
        FROM sts, clone
        WHERE sts.uid = 2;

#
#  The following Query crashes mysqld:
#
SELECT GROUP_CONCAT(DISTINCT sts.workgroup_id SEPARATOR ',')
        FROM sts, clone
        WHERE sts.uid = 2;

Suggested fix:
I had some problems with the "--debug" option to "mysqld" which would result in crashes for almost every SQL command I typed ("SHOW DATABASES", "SHOW TABLES"), but if I start the server without that option, the above SELECT query did NOT crash the server if I:

* omit the "WHERE sts.uid = 2" clause
* omit the table "clone" from the "FROM" statement
* replace the plain "sts.workgroup_id" with "CONCAT(sts.workgroup_id)" in
  the GROUP_CONCAT() argument
[13 May 2005 11:33] Hartmut Holzgraefe
Doesn't crash 4.1.11 on Linux/x86
[13 May 2005 11:52] MySQL Verification Team
The Linux BK source not crashes but the Windows server 4.1.11 I got a
crash will test Windows server latest BK.
[13 May 2005 13:24] MySQL Verification Team
Testing a Windows server 4.1.12 (BK source) I noticed that only the optimized server crashes, the debug server not crashed.
Below a call stack from optimized server:

>	mysqld-nt.exe!Field_enum::val_int()  + 0x13	C++
 	mysqld-nt.exe!Field_enum::cmp()  + 0x15	C++
 	mysqld-nt.exe!group_concat_key_cmp_with_distinct()  + 0x52	C++
 	mysqld-nt.exe!_tree_insert()  + 0x42	C
 	mysqld-nt.exe!Item_func_group_concat::add()  + 0xb3	C++
 	mysqld-nt.exe!JOIN::make_sum_func_list()  + 0x478	C++
 	mysqld-nt.exe!safe_index_read()  + 0xbe5	C++
 	mysqld-nt.exe!create_myisam_from_heap()  + 0x719	C++
 	mysqld-nt.exe!create_myisam_from_heap()  + 0x466	C++
 	mysqld-nt.exe!JOIN::exec()  + 0xe18	C++
 	mysqld-nt.exe!mysql_select()  + 0x205	C++
 	mysqld-nt.exe!handle_select()  + 0x81	C++
 	mysqld-nt.exe!mysql_execute_command()  + 0x42a	C++
 	mysqld-nt.exe!mysql_parse()  + 0xe6	C++
 	mysqld-nt.exe!dispatch_command()  + 0x46d	C++
 	mysqld-nt.exe!do_command()  + 0xa7	C++
 	mysqld-nt.exe!_handle_one_connection()  + 0x1ed	C++
 	mysqld-nt.exe!_pthread_create()  + 0xcb	C
 	mysqld-nt.exe!__beginthread()  + 0xce	
 	ntdll.dll!7c9106eb() 	
 	kernel32.dll!7c80b50b() 	
 	ntdll.dll!7c9106eb() 	
 	kernel32.dll!7c8399f3()
[2 Jun 2005 19:01] Igor Babaev
I failed to repeat the problem with mysqld-nt from 4.1.12a release.
[2 Jun 2005 19:17] MySQL Verification Team
Yes I can confirm that a server built from today source
not crashes any more:

mysql> #
mysql> #  The following Query crashes mysqld:
mysql> #
mysql> SELECT GROUP_CONCAT(DISTINCT sts.workgroup_id SEPARATOR ',')
    ->         FROM sts, clone
    ->         WHERE sts.uid = 2;
+-------------------------------------------------------+
| GROUP_CONCAT(DISTINCT sts.workgroup_id SEPARATOR ',') |
+-------------------------------------------------------+
| KO,KO                                                 |
+-------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.13-nt |
+-----------+
1 row in set (0.00 sec)

mysql>