MySQL Bugs: #16496: segfault when combining DISTINCT and GROUP BY in one query

Bug #16496	segfault when combining DISTINCT and GROUP BY in one query
Submitted:	13 Jan 2006 21:04	Modified:	1 Feb 2006 16:28
Reporter:	Olaf Stehr	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.18-log	OS:	Linux (SUSE LINUX Enterprise Server 9)
Assigned to:		CPU Architecture:	Any

Description:
It appears that a SELECT statement that uses DISTINCT _and_ GROUP BY consistently crashes the server.

The bug occurs since we switched from 4.1.15 to 5.0.15 in November and persists till now, when we are using 5.0.18 an SUSE Enterprise 9. Not sure about any other operating systems.

How to repeat:
the following query works fine:
SELECT country , country_area , UCASE( value_type ) , MIN( `date` ) FROM display2 . feed_nuclear GROUP BY country , country_area , value_type

this query always crashes the server:
SELECT DISTINCT country , country_area , UCASE( value_type ) , MIN( `date` ) FROM display2 . feed_nuclear GROUP BY country , country_area , value_type

I know, that the DISTINCT is needless in the query, but it shouldn't crash the server!

some additonal information on the host:

sibdn815:~ # uname -a
Linux sibdn815 2.6.5-7.201-smp #1 SMP Thu Aug 25 06:20:45 UTC 2005 i686 i686 i386 GNU/Linux

sibdn815:~ # cat /etc/SuSE-release
SUSE LINUX Enterprise Server 9 (i586)
VERSION = 9
PATCHLEVEL = 2

-------------
stack trace:

sibdn815:~ #  cat mysqld.stacktrace
0x817b676 handle_segfault + 566
0xffffe420 _end + -139405848
0x8badea8 _end + 7065712
0x81d7f25 _ZN4JOIN7destroyEv + 677
0x828d596 _ZN13st_select_lex7cleanupEv + 310
0x81e3a7a _Z12mysql_selectP3THDPPP4ItemP13st_table_listjR4ListIS1_ES2_jP8st_orderSB_S2_SB_mP13select_resultP18st_select_lex_unitP13st_sel + 266
0x81e7fb4 _Z13handle_selectP3THDP6st_lexP13select_resultm + 212
0x8196057 _Z21mysql_execute_commandP3THD + 13879
0x819c25a _Z11mysql_parseP3THDPcj + 522
0x819e720 _Z16dispatch_command19enum_server_commandP3THDPcj + 3344
0x819fb07 handle_one_connection + 2215
0x40167a13 _end + 935816155
0x403579da _end + 937847714

-------------
error-log:

sibdn815:~ # sibdn815.err
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=402653184
read_buffer_size=2093056
max_used_connections=5
max_connections=100
threads_connected=5
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 802415 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x8badea8
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0x5c2c22d4, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x817b676
0xffffe420
0x8badea8
0x81d7f25
0x828d596
0x81e3a7a
0x81e7fb4
0x8196057
0x819c25a
0x819e720
0x819fb07
0x40167a13
0x403579da
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do 
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x8bb97b0 = SELECT DISTINCT country , country_area , UCASE( value_type ) , MIN( `date` ) FROM display2 . feed_nuclear GROUP BY country , country_area , value_type  LIMIT 0, 30
thd->thread_id=7
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

Number of processes running now: 0
060113 20:41:49  mysqld restarted
060113 20:41:49  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
060113 20:41:49  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 3 41108691.
InnoDB: Doing recovery: scanned up to log sequence number 3 41108691
InnoDB: In a MySQL replication slave the last master binlog file
InnoDB: position 0 1432706, file name enterprise-bin.000211
InnoDB: Last MySQL binlog file position 0 520, file name ./mysql-bin.000203
060113 20:41:49  InnoDB: Started; log sequence number 3 41108691
060113 20:41:49 [Note] Recovering after a crash using mysql-bin
060113 20:41:49 [Note] Starting crash recovery...
060113 20:41:49 [Note] Crash recovery finished.
060113 20:41:49 [Note] /usr/local/mysql/libexec/mysqld: ready for connections.
Version: '5.0.18-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution

Thank you for a problem report. Please, send the SHOW CREATE TABLE results for your `display2 . feed_nuclear` table. It's data dump may be also useful, if it is not large.

CREATE TABLE `feed_nuclear` (
  `feed_id` int(10) unsigned NOT NULL default '0',
  `country` varchar(32) collate latin1_general_cs NOT NULL default '',
  `country_area` varchar(32) collate latin1_general_cs NOT NULL default '',
  `date` date NOT NULL default '0000-00-00',
  `mw_online` int(10) unsigned NOT NULL default '0',
  `value_type` varchar(8) collate latin1_general_cs NOT NULL default '',
  `datasource` varchar(16) collate latin1_general_cs NOT NULL default '',
  PRIMARY KEY  (`country`,`country_area`,`date`,`datasource`),
  KEY `feed_id` (`feed_id`),
  KEY `allowed_datasources` (`country`,`country_area`,`value_type`,`datasource`),
  CONSTRAINT `feed_nuclear_datasources` FOREIGN KEY (`country`, `country_area`, `value_type`, `datasource`) REFERENCES `def_datasource_nuclear` (`country`, `country_area`, `type`, `company`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `feed_nuclear_id` FOREIGN KEY (`feed_id`) REFERENCES `feed_logs` (`feed_id`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs

---
Sorry, I can't provide a data dump because of a nondisclosure agreement.
The table has some ten thousand rows with data about the load of nuclear plants in Europe.

OK, your data are not really needed, I believe. As you have foreign keys, please, send the SHOW CREATE TABLE results for the table referenced, `def_datasource_nuclear` and `feed_logs`.

CREATE TABLE `def_datasource_nuclear` (
  `company` varchar(16) collate latin1_general_cs NOT NULL default '',
  `type` varchar(8) collate latin1_general_cs NOT NULL default 'Measure',
  `country` varchar(32) collate latin1_general_cs NOT NULL default '',
  `country_area` varchar(32) collate latin1_general_cs NOT NULL default '',
  PRIMARY KEY  (`country`,`country_area`,`type`),
  KEY `type` (`type`),
  KEY `company` (`company`),
  KEY `area` (`country`,`country_area`),
  KEY `datasource` (`country`,`country_area`,`type`,`company`),
  CONSTRAINT `def_datasource_nuclear_country_areas` FOREIGN KEY (`country`, `country_area`) REFERENCES `def_country_areas` (`country`, `tag`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `def_datasource_nuclear_company` FOREIGN KEY (`company`) REFERENCES `data_companies` (`tag`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `def_datasource_nuclear_type` FOREIGN KEY (`type`) REFERENCES `def_value_types` (`tag`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs

CREATE TABLE `feed_logs` (
  `feed_id` int(10) unsigned NOT NULL auto_increment,
  `timestamp` datetime NOT NULL default '0000-00-00 00:00:00',
  `feedmethod` varchar(16) collate latin1_general_cs NOT NULL default '',
  `method_details` varchar(64) collate latin1_general_cs NOT NULL default '',
  `original_source` varchar(255) collate latin1_general_cs NOT NULL default '',
  `display_admin` varchar(16) collate latin1_general_cs NOT NULL default '',
  PRIMARY KEY  (`feed_id`),
  KEY `display_admin` (`display_admin`),
  KEY `feedmethod` (`feedmethod`),
  CONSTRAINT `feed_logs_method` FOREIGN KEY (`feedmethod`) REFERENCES `def_feedmethods` (`tag`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `feed_logs_admin` FOREIGN KEY (`display_admin`) REFERENCES `data_persons` (`handle`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs PACK_KEYS=1

OK, these tables refer to some other ones... So, I decided to try to reproduce on simpler single table, without foreign keys:

CREATE TABLE `feed_nuclear` (
  `feed_id` int(10) unsigned NOT NULL default '0',
  `country` varchar(32) collate latin1_general_cs NOT NULL default '',
  `country_area` varchar(32) collate latin1_general_cs NOT NULL default '',
  `date` date NOT NULL default '0000-00-00',
  `mw_online` int(10) unsigned NOT NULL default '0',
  `value_type` varchar(8) collate latin1_general_cs NOT NULL default '',
  `datasource` varchar(16) collate latin1_general_cs NOT NULL default '',
  PRIMARY KEY  (`country`,`country_area`,`date`,`datasource`),
  KEY `feed_id` (`feed_id`),
  KEY `allowed_datasources`
(`country`,`country_area`,`value_type`,`datasource`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs;

Can you, please, send the results of

EXPLAIN SELECT country , country_area , UCASE( value_type ) , MIN( `date` ) FROM
display2 . feed_nuclear GROUP BY country , country_area , value_type;

and 

EXPLAIN SELECT DISTINCT country , country_area , UCASE( value_type ) , MIN( `date` ) FROM display2 . feed_nuclear GROUP BY country , country_area , value_type;

on your data? The second one may crash your server, so take care. In the meantime I am looking for similar bugs... By the way, both EXPLAINs work OK for me on 5.0.19-BK with empty table.

mysql> EXPLAIN SELECT country , country_area , UCASE( value_type ) , MIN( `date` ) FROM
    -> display2 . feed_nuclear GROUP BY country , country_area , value_type;
+----+-------------+--------------+-------+---------------+---------------------+---------+------+-------+-------------+
| id | select_type | table        | type  | possible_keys | key                 | key_len | ref  | rows  | Extra       |
+----+-------------+--------------+-------+---------------+---------------------+---------+------+-------+-------------+
|  1 | SIMPLE      | feed_nuclear | index | NULL          | allowed_datasources | 88      | NULL | 63751 | Using index |
+----+-------------+--------------+-------+---------------+---------------------+---------+------+-------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT DISTINCT country , country_area , UCASE( value_type ) , MIN( `date` ) FROM 
    -> display2 . feed_nuclear GROUP BY country , country_area , value_type;
+----+-------------+--------------+-------+---------------+---------------------+---------+------+-------+----------------------------------------------+
| id | select_type | table        | type  | possible_keys | key                 | key_len | ref  | rows  | Extra                                        |
+----+-------------+--------------+-------+---------------+---------------------+---------+------+-------+----------------------------------------------+
|  1 | SIMPLE      | feed_nuclear | index | NULL          | allowed_datasources | 88      | NULL | 63751 | Using index; Using temporary; Using filesort |
+----+-------------+--------------+-------+---------------+---------------------+---------+------+-------+----------------------------------------------+
1 row in set (0.00 sec)

------
remark: the server didn't crash when executing this queries.

Did it crash when you omit explain ???

Also, please , if it crash, run CHECK TABLE EXTENDED on each table, REPAIR if necessary.

Yes: without the EXPLAIN, the server crashes again, just like before.

As you suggested, I ran CHECK TABLE EXTENDED on all three tables - everything looks fine:

mysql> check table feed_nuclear extended;
+-----------------------+-------+----------+----------+
| Table                 | Op    | Msg_type | Msg_text |
+-----------------------+-------+----------+----------+
| display2.feed_nuclear | check | status   | OK       |
+-----------------------+-------+----------+----------+
1 row in set (0.08 sec)

mysql> check table def_datasource_nuclear extended;
+---------------------------------+-------+----------+----------+
| Table                           | Op    | Msg_type | Msg_text |
+---------------------------------+-------+----------+----------+
| display2.def_datasource_nuclear | check | status   | OK       |
+---------------------------------+-------+----------+----------+
1 row in set (0.00 sec)

mysql> check table feed_logs extended;
+--------------------+-------+----------+----------+
| Table              | Op    | Msg_type | Msg_text |
+--------------------+-------+----------+----------+
| display2.feed_logs | check | status   | OK       |
+--------------------+-------+----------+----------+
1 row in set (0.34 sec)

...I ran the check an ALL tables in the database - everything status OK.

After that, another test with our lovely select statement: mysql crashes again! :-(

I tried to repeat with 5.0.19-BK and similar amount of sample generated data, but both queries work OK for me:

mysql> select count(*) from feed_nuclear;
+----------+
| count(*) |
+----------+
|    49152 |
+----------+
1 row in set (1.95 sec)

mysql> EXPLAIN SELECT country , country_area , UCASE( value_type ) , MIN( `date` ) FROM feed_nuclear GROUP BY country , country_area , value_type;
+----+-------------+--------------+-------+---------------+---------------------+---------+------+-------+-------------+
| id | select_type | table        | type  | possible_keys | key| key_len | ref  | rows  | Extra       |
+----+-------------+--------------+-------+---------------+---------------------+---------+------+-------+-------------+
|  1 | SIMPLE      | feed_nuclear | index | NULL          | allowed_datasources | 96      | NULL | 44423 | Using index |
+----+-------------+--------------+-------+---------------+---------------------+---------+------+-------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT DISTINCT country , country_area , UCASE(value_type) , MIN( `date` ) FROM feed_nuclear GROUP BY country , country_area , value_type;
+----+-------------+--------------+-------+---------------+---------------------+---------+------+-------+----------------------------------------------+
| id | select_type | table        | type  | possible_keys | key | key_len | ref  | rows  | Extra                                        |
+----+-------------+--------------+-------+---------------+---------------------+---------+------+-------+----------------------------------------------+
|  1 | SIMPLE      | feed_nuclear | index | NULL          | allowed_datasources | 96      | NULL | 44423 | Using index; Using temporary; Using filesort |
+----+-------------+--------------+-------+---------------+---------------------+---------+------+-------+----------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT country , country_area , UCASE( value_type ) , MIN( `date` ) FROM
 feed_nuclear GROUP BY country , country_area , value_type;
+---------+--------------+---------------------+---------------+
| country | country_area | UCASE( value_type ) | MIN( `date` ) |
+---------+--------------+---------------------+---------------+
| France  | Europe       |                     | 2006-01-14    |
| France  | Europe       | HEIGHT              | 2006-01-14    |
| Ukraine | Europe       |                     | 2006-01-14    |
| Ukraine | Europe       | SOME VAL            | 2006-01-14    |
+---------+--------------+---------------------+---------------+
4 rows in set (0.54 sec)

mysql> SELECT DISTINCT country , country_area , UCASE( value_type ) , MIN( `date` ) FROM feed_nuclear GROUP BY country , country_area , value_type;
+---------+--------------+---------------------+---------------+
| country | country_area | UCASE( value_type ) | MIN( `date` ) |
+---------+--------------+---------------------+---------------+
| France  | Europe       |                     | 2006-01-14    |
| France  | Europe       | HEIGHT              | 2006-01-14    |
| Ukraine | Europe       |                     | 2006-01-14    |
| Ukraine | Europe       | SOME VAL            | 2006-01-14    |
+---------+--------------+---------------------+---------------+
4 rows in set (1.04 sec)

The only difference I see is key_len in explain results (96 in my case versus 88 in yours). Ypu may simply wait for 5.0.19 to be officially released.

Any further ideas on how to repeat the problem on 5.0.18 are also welcomed.

Valeriy,

first of all, thank you very much for sparing no effort in building up tables with extensive sample data to reproduce our system. 

Unfortunately you didn't experience the problems as we do with 5.0.18. So we cannot learn more about what triggers the crash; hoping the problem will vanish when we are switching to 5.0.19 as soon as it is released. 

I will report on any new developments in this matter right here.

Have a nice weekend!
OLAF

I've tried to repeat with random data on SuSE Linux 9.3 and 5.0.18 - still no crashes. So, please, wait for 5.0.19 and check with it or provide any additional information on how to repeat that crash.