MySQL Bugs: #18728: server crash

Bug #18728	server crash
Submitted:	3 Apr 2006 7:01	Modified:	28 Apr 2006 9:05
Reporter:	Aleksander Machniak	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.19	OS:	Linux (Slackware Linux 10.2)
Assigned to:		CPU Architecture:	Any

Description:
Query:

SELECT documents.id AS id, number, cdate, type, customerid, name, address, 
	zip, city, template, closed, 
	CASE reference WHEN 0 
		THEN SUM(a.value*a.count) 
		ELSE SUM((a.value+b.value)*(a.count+b.count)) - SUM(b.value*b.count) 
	END AS value, 
	COUNT(a.docid) AS count 
FROM documents 
LEFT JOIN invoicecontents a ON (a.docid = documents.id) 
LEFT JOIN invoicecontents b ON (reference = b.docid AND a.itemid = b.itemid) 
LEFT JOIN numberplans ON (numberplanid = numberplans.id) 
WHERE (type = 3 OR type = 1) 
GROUP BY documents.id, number, cdate, customerid, name, address, zip, city, 
	template, closed, type, reference 
ORDER BY documents.id asc

Resolve_stack_dump result:

0x8165aac handle_segfault + 636
0x401cf4b1 _end + 935639649
0x83ac2b6 get_ptr_compare + 294
0x83ac931 queue_insert + 81
0x820aad4 _Z13merge_buffersP13st_sort_paramP11st_io_cacheS2_PhP10st_buffpekS5_S5_i + 340
0x820c05a _Z8filesortP3THDP8st_tableP13st_sort_fieldjP10SQL_SELECTmPm + 3754
0x81bbf05 _Z21make_unireg_sortorderP8st_orderPj + 597
0x81c3a07 _ZN4JOIN4execEv + 2135
0x81c5678 _Z12mysql_selectP3THDPPP4ItemP13st_table_listjR4ListIS1_ES2_jP8st_orderSB_S2_SB_mP13select_resultP18st_select_lex_unitP13st_sel + 168
0x81c5cd0 _Z13handle_selectP3THDP6st_lexP13select_resultm + 272
0x817c100 _Z21mysql_execute_commandP3THD + 4240
0x8182770 _Z11mysql_parseP3THDPcj + 320
0x8182ff4 _Z16dispatch_command19enum_server_commandP3THDPcj + 1924
0x8183d98 _Z10do_commandP3THD + 136
0x818477c handle_one_connection + 2252
0x401c7f5b _end + 935609611
0x40345bea _end + 937173402

How to repeat:
Problem apear when table documents grow up to 183 records (invoicecontents has ca. 200 rec.)

Suggested fix:
I've been using my-small.cnf, but when I've changed config to my-medium.cnf problem has disapeared.

Thank you for a problem report. Please, send the SHOW CREATE TABLE and SHOW TABLE STATUS results for all the tables in your SELECT:

- documents 
- invoicecontents
- numberplans

Can you try to repeat the same SELECT on the same data and configuration with a newer version of MySQL server, 5.0.19?

CREATE TABLE `documents` (
  `id` int(11) NOT NULL auto_increment,
  `type` tinyint(4) NOT NULL default '0',
  `number` int(11) NOT NULL default '0',
  `cdate` int(11) NOT NULL default '0',
  `customerid` int(11) NOT NULL default '0',
  `userid` int(11) NOT NULL default '0',
  `name` varchar(255) collate utf8_polish_ci NOT NULL default '',
  `address` varchar(255) collate utf8_polish_ci NOT NULL default '',
  `zip` varchar(10) collate utf8_polish_ci NOT NULL default '',
  `city` varchar(32) collate utf8_polish_ci NOT NULL default '',
  `ten` varchar(16) collate utf8_polish_ci NOT NULL default '',
  `ssn` varchar(16) collate utf8_polish_ci NOT NULL default '',
  `paytime` tinyint(4) NOT NULL default '0',
  `paytype` varchar(255) collate utf8_polish_ci NOT NULL default '',
  `numberplanid` int(11) NOT NULL default '0',
  `closed` tinyint(1) NOT NULL default '0',
  `reference` int(11) NOT NULL default '0',
  `extnumber` varchar(255) collate utf8_polish_ci NOT NULL default '',
  PRIMARY KEY  (`id`),
  KEY `cdate` (`cdate`),
  KEY `numberplanid` (`numberplanid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;

CREATE TABLE `invoicecontents` (
  `docid` int(11) NOT NULL default '0',
  `value` decimal(9,2) NOT NULL default '0.00',
  `prodid` varchar(255) NOT NULL default '',
  `content` varchar(16) NOT NULL default '',
  `count` decimal(9,2) NOT NULL default '0.00',
  `description` varchar(255) NOT NULL default '',
  `tariffid` int(11) NOT NULL default '0',
  `itemid` smallint(6) NOT NULL default '0',
  `taxid` int(11) NOT NULL default '0',
  `discount` decimal(4,2) NOT NULL default '0.00',
  KEY `docid` (`docid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `numberplans` (
  `id` int(11) NOT NULL auto_increment,
  `template` varchar(255) collate utf8_polish_ci NOT NULL default '',
  `period` smallint(6) NOT NULL default '0',
  `doctype` int(11) NOT NULL default '0',
  `isdefault` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;

+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+----------------+----------+----------------+---------+
| 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 |
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+----------------+----------+----------------+---------+
| documents | MyISAM |      10 | Dynamic    |  182 |            121 |       22288 | 281474976710655 |        11264 |       120 |            496 | 2006-03-21 23:11:50 | 2006-04-03 09:02:27 | 2006-03-21 23:11:50 | utf8_polish_ci |     NULL |                |         |
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+----------------+----------+----------------+---------+

| 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 |
+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| invoicecontents | MyISAM |      10 | Dynamic    |  205 |             68 |       14004 | 281474976710655 |         5120 |        56 |           NULL | 2006-03-21 23:11:50 | 2006-04-03 09:06:00 | 2006-03-21 23:11:50 | latin1_swedish_ci |     NULL |                |         |

| 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 |
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+----------+----------------+---------+
| numberplans | MyISAM |      10 | Dynamic    |    2 |             24 |          48 | 281474976710655 |         2048 |         0 |              3 | 2006-03-21 23:11:50 | 2006-04-03 09:17:03 | NULL       | utf8_polish_ci |     NULL |                |         |

Problem is when I've got in documents and invoicecontents tables one row more than in stats above. I've checked 5.0.19 and problem stil exists. My friend has the same problem on different distribution (the same program/query and 5.0.18, but bigger database and different my.cnf settings)

Please, try to repeat with a newer version, 5.0.20a, and inform about the results. 

Have you created these tables in 5.0.x or they were created in 4.x.y and then you just updated binaries?

5.0.20a works fine. Problem solved, thanks!