| 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: | |
| 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: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>
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