---------- Forwarded message ----------
Date: Mon, 7 Mar 2005 15:54:11 -0800 (PST)
From: Ronald Braunstein <[EMAIL PROTECTED]>
To: mysql@lists.mysql.com
Subject: mysql server crash on nested selects
>Description:
Server crashes. This crashes the following servers:
4.1.10
4.1.10 debug release
4.1.11 nightly snapshot with safe memory checking
Basically, my query died when I added a select ot.* from ( ...)
around an already existing query.
It dies on empty tables (and on tables with data).
I'll include the schema
========================================================================
Query:
==============
set group_concat_max_len = 128000;
select ot.* from (
select a.assigned_to
, c.ticket_id
, type
, active_time as active_since
, max(corr.received_time) last_message
, c2.sender_email as last_replyer
, count(corr.correspondence_id) as num_mails
, (select received_time from correspondence
where correspondence_id = (select min(correspondence_id)
from correspondence co, resolvers re
where co.ticket_id = c.ticket_id
and co.sender_email = re.resolver)) as first_response
, if(r1.resolver is null, 'OUR COURT', 'THEIR COURT') ball
, now()
, c.status
, timediff(now(), active_time) as diff
from contacts c left join assignment a on c.ticket_id = a.ticket_id
, correspondence corr
, correspondence c2 left join resolvers r1 on c2.sender_email =
r1.resolver
where status != 'resolved'
and corr.ticket_id = c.ticket_id
and c2.correspondence_id = (select max(correspondence_id) from
correspondence where ticket_id = c.ticket_id)
and active_time > '2005-02-25'
group by a.assigned_to, c.ticket_id, subject, type, active_time
order by active_since
) ot
==========================================================================
BackTrace:
==========
(gdb) backtrace full
#0 0x40104ae1 in __kill () from /lib/i686/libc.so.6
No locals.
#1 0x4003776b in raise (sig=6) at signals.c:65
retcode = 32766
#2 0x40106062 in abort () at ../sysdeps/generic/abort.c:88
act = {__sigaction_handler = {sa_handler = 0x4020a860
<_IO_2_1_stderr_>, sa_sigaction = 0x4020a860 <_IO_2_1_stderr_>}, sa_mask =
{__val = {0, 1703853748, 1073974542, 0, 146102944, 0, 1073962380, 1075882080,
1074014168, 1703853780, 1075883936,
146102936, 1073833280, 1074381984, 1074384032, 1703853836, 1073795894,
1074382396, 1074384064, 1, 0, 1075882080, 1073962380, 146103072, 1075889844,
1075890448, 1074678075, 1074643060, 1075889844, 146102944, 8, 1703853908}},
sa_flags = 1073796304,
sa_restorer = 0x1 <__strtol_internal+1>}
sigs = {__val = {32, 0 <repeats 31 times>}}
#3 0x400fe200 in __assert_fail () at assert.c:74
function = 0x400427d8 "\fJ\001"
buf = 0x8b55aa0 "\210« @\210« @d: item.cc:1739: void
Item_field::make_field(Send_field *): Assertion `tmp_field->table_name'
failed.\n"
#4 0x80d9844 in Item_field::make_field (this=0x8b90788, tmp_field=0x658ebfe4)
at item.cc:1739
this = (Item_field *) 0x8b90788
#5 0x8121f15 in Protocol::send_fields (this=0x8b4b4a4, list=0x8b4adf8, flag=1)
at protocol.cc:536
pos = 0x8b79091 "esponse\f?"
cs = (CHARSET_INFO *) 0x85f9dc0
field = {db_name = 0x8436c3b "", table_name = 0x0, org_table_name =
0x0, col_name = 0x8b78e08 "now()", org_col_name = 0x8b78e08 "now()", length =
19, charsetnr = 63, flags = 129, decimals = 0, type = MYSQL_TYPE_DATETIME}
this = (Protocol *) 0x8b4b4a4
it = {<base_list_iterator> = {list = 0x8b4adf8, el = 0x8b907ec, prev =
0x0, current = 0x0}, <No data fields>}
item = (Item *) 0x8b90788
buff =
"\f\017?\b\000\000\000\000?\017?\b\004Á\216eø´\bø\a¹\b\034Ð\025\b¸ù¸\bÐ\b¹\b\024Á\216e_Ñ\025\bl¬´\b\b\000\000\000$Á\216exÆ\026\b\000\000\000\000àË\216e$Á\216eÖÐ\026\b¸ù¸\b"
tmp = {Ptr = 0x658ec060 "\f\017?\b", str_length = 80, Alloced_length =
80, alloced = false, str_charset = 0x8615360}
prot = {<Protocol> = {thd = 0x8b4ac60, packet = 0x8b4b518, convert =
0x8b4b52c, field_pos = 6, field_types = 0x0, field_count = 1703854228, _vptr. =
0x862e9e0}, <No data fields>}
local_packet = (String *) 0x8b4b518
thd_charset = (CHARSET_INFO *) 0x8616360
_db_func_ = 0x658ec070
"ø´\bø\a¹\b\034Ð\025\b¸ù¸\bÐ\b¹\b\024Á\216e_Ñ\025\bl¬´\b\b"
_db_file_ = 0x2 <Address 0x2 out of bounds>
_db_level_ = 1703854200
_db_framep_ = (char **) 0x815e1a4
count = 9
#6 0x811c900 in select_send::send_fields (this=0x8b8f9a8, [EMAIL PROTECTED],
flag=1) at sql_class.cc:790
this = (select_send *) 0x8b8f9a8
#7 0x8172ae2 in do_select (join=0x8b8f9b8, fields=0x8b4adf8, table=0x0,
procedure=0x0) at sql_select.cc:5638
error = 0
join_tab = (JOIN_TAB *) 0x8490941
end_select = (int (*)()) 0x81653e6 <JOIN::exec(void)+50>
_db_func_ = 0x658ec1a4 "äÁ\216e1o\026\b¸ù¸\b"
_db_file_ = 0x658ec1a0 ""
_db_level_ = 958
_db_framep_ = (char **) 0x8165026
#8 0x8166a2a in JOIN::exec (this=0x8b8f9b8) at sql_select.cc:1482
this = (JOIN *) 0x8b8f9b8
tmp_error = 0
_db_func_ = 0x0
_db_file_ = 0x1 <Address 0x1 out of bounds>
_db_level_ = 146229328
_db_framep_ = (char **) 0x0
curr_join = (JOIN *) 0x8b8f9b8
curr_all_fields = (List<Item> *) 0x8b902f0
curr_fields_list = (List<Item> *) 0x8b4adf8
curr_tmp_table = (TABLE *) 0x0
#9 0x8166f31 in mysql_select (thd=0x8b4ac60, rref_pointer_array=0x8b4ae94,
tables=0x8b74850, wild_num=1, [EMAIL PROTECTED], conds=0x0, og_num=0,
order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2156153344,
result=0x8b8f9a8, unit=0x8b4aca8,
select_lex=0x8b4ad90) at sql_select.cc:1602
err = 0
free_join = true
_db_func_ = 0x81e676e "\203Ä\020ë\004ë\002ë"
_db_file_ = 0x658ec254 "äÆ\216edÑ\023\b`¬´\b\234¬´\b¨ù¸\b"
_db_level_ = 146058336
_db_framep_ = (char **) 0x8632a60
join = (JOIN *) 0x8b8f9b8
#10 0x8162f5c in handle_select (thd=0x8b4ac60, lex=0x8b4ac9c, result=0x8b8f9a8)
at sql_select.cc:193
res = 0
select_lex = (SELECT_LEX *) 0x8b4ad90
_db_func_ = 0x813d113 "\203Ä\020\211ÀPèNÊ,"
_db_file_ = 0x658ec6e4 "$Ç\216e®'\024\b`¬´\b`¬´\b8p·\b/\004"
_db_level_ = 1703855844
_db_framep_ = (char **) 0x9
#11 0x813d164 in mysql_execute_command (thd=0x8b4ac60) at sql_parse.cc:2082
result = (select_result *) 0x8b8f9a8
res = 0
lex = (LEX *) 0x8b4ac9c
slave_fake_lock = false
fake_prev_lock = (MYSQL_LOCK *) 0x0
select_lex = (SELECT_LEX *) 0x8b4ad90
tables = (TABLE_LIST *) 0x8b74c10
unit = (SELECT_LEX_UNIT *) 0x8b4aca8
_db_func_ = 0x0
_db_file_ = 0x0
_db_level_ = 140794780
_db_framep_ = (char **) 0xffffffff
....
(gdb)
======================================================================
>How-To-Repeat:
============================
mysql < -
===
-- MySQL dump 10.9
--
-- Host: localhost Database: devmail
-- ------------------------------------------------------
-- Server version 4.1.11-nightly-20050307-debug-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0
*/;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
--
-- Table structure for table `aggregates`
--
DROP TABLE IF EXISTS `aggregates`;
CREATE TABLE `aggregates` (
`date` date NOT NULL default '0000-00-00',
`average_open_time` int(10) unsigned default NULL,
`average_response_time` int(10) unsigned default NULL,
`average_total_response_time` int(10) unsigned default NULL,
PRIMARY KEY (`date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Table structure for table `assignment`
--
DROP TABLE IF EXISTS `assignment`;
CREATE TABLE `assignment` (
`ticket_id` int(11) unsigned NOT NULL default '0',
`assigned_to` varchar(128) NOT NULL default '',
PRIMARY KEY (`ticket_id`),
KEY `assigned_to` (`assigned_to`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Table structure for table `cc`
--
DROP TABLE IF EXISTS `cc`;
CREATE TABLE `cc` (
`ticket_id` int(11) NOT NULL default '0',
`email_address` varchar(127) NOT NULL default '',
PRIMARY KEY (`ticket_id`,`email_address`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Table structure for table `contacts`
--
DROP TABLE IF EXISTS `contacts`;
CREATE TABLE `contacts` (
`ticket_id` int(11) NOT NULL auto_increment,
`subject` text NOT NULL,
`status` varchar(127) NOT NULL default '',
`type` varchar(255) NOT NULL default 'other',
`urgency` enum('5','4','3') NOT NULL default '5',
`requester_email` varchar(127) NOT NULL default '',
`perforce_client` varchar(127) NOT NULL default '',
`active_time` datetime NOT NULL default '0000-00-00 00:00:00',
`resolved_time` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`ticket_id`),
UNIQUE KEY `ticket_id` (`ticket_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Table structure for table `correspondence`
--
DROP TABLE IF EXISTS `correspondence`;
CREATE TABLE `correspondence` (
`correspondence_id` int(11) NOT NULL auto_increment,
`reply_to_id` int(11) NOT NULL default '0',
`ticket_id` int(11) NOT NULL default '0',
`received_time` datetime NOT NULL default '0000-00-00 00:00:00',
`sender_email` varchar(127) NOT NULL default '',
`correspondence_text` text NOT NULL,
`keep_ball` tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY (`correspondence_id`),
KEY `ticket_id` (`ticket_id`),
KEY `keep_ball` (`keep_ball`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Table structure for table `faq`
--
DROP TABLE IF EXISTS `faq`;
CREATE TABLE `faq` (
`faq_id` int(11) NOT NULL default '0',
`ticket_id` int(11) unsigned NOT NULL default '0',
`author_email` varchar(127) NOT NULL default '',
`time_added` datetime NOT NULL default '0000-00-00 00:00:00',
`type` int(11) NOT NULL default '0',
`description` varchar(80) NOT NULL default '',
`details` text,
`deleted` tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY (`faq_id`),
KEY `type` (`type`),
KEY `ticket_id` (`ticket_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Table structure for table `faq_type`
--
DROP TABLE IF EXISTS `faq_type`;
CREATE TABLE `faq_type` (
`type_id` int(11) NOT NULL auto_increment,
`type_name` varchar(80) NOT NULL default '',
PRIMARY KEY (`type_id`),
UNIQUE KEY `type_name` (`type_name`),
KEY `type_id` (`type_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Table structure for table `group_membership`
--
DROP TABLE IF EXISTS `group_membership`;
CREATE TABLE `group_membership` (
`id` int(11) NOT NULL auto_increment,
`group_name` varchar(80) NOT NULL default '',
`ticket_id` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `group_name` (`group_name`),
KEY `ticket_id` (`ticket_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Table structure for table `quarantined_issue`
--
DROP TABLE IF EXISTS `quarantined_issue`;
CREATE TABLE `quarantined_issue` (
`id` int(11) NOT NULL auto_increment,
`ticket_id` int(11) NOT NULL default '0',
`reminder_time` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
UNIQUE KEY `ticket_id` (`ticket_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Table structure for table `resolvers`
--
DROP TABLE IF EXISTS `resolvers`;
CREATE TABLE `resolvers` (
`resolver` tinytext
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Table structure for table `status_changes`
--
DROP TABLE IF EXISTS `status_changes`;
CREATE TABLE `status_changes` (
`change_id` int(11) NOT NULL auto_increment,
`ticket_id` int(11) NOT NULL default '0',
`change_time` datetime NOT NULL default '0000-00-00 00:00:00',
`requester_email` varchar(127) NOT NULL default '',
`old_status` varchar(127) NOT NULL default '',
`new_status` varchar(127) NOT NULL default '',
`reason` varchar(255) default NULL,
PRIMARY KEY (`change_id`),
KEY `ticket_id` (`ticket_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Table structure for table `urgency_changes`
--
DROP TABLE IF EXISTS `urgency_changes`;
CREATE TABLE `urgency_changes` (
`change_id` int(11) NOT NULL auto_increment,
`ticket_id` int(11) NOT NULL default '0',
`change_time` datetime NOT NULL default '0000-00-00 00:00:00',
`requester_email` varchar(127) NOT NULL default '',
`old_urgency` int(11) NOT NULL default '0',
`new_urgency` int(11) NOT NULL default '0',
`reason` varchar(255) default NULL,
PRIMARY KEY (`change_id`),
KEY `ticket_id` (`ticket_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Table structure for table `watch_list`
--
DROP TABLE IF EXISTS `watch_list`;
CREATE TABLE `watch_list` (
`id` int(10) unsigned NOT NULL auto_increment,
`ticket_id` int(10) unsigned NOT NULL default '0',
`user` varchar(180) NOT NULL default '',
PRIMARY KEY (`id`),
KEY `user` (`user`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40014 SET [EMAIL PROTECTED] */;
/*!40014 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
===========
Run query mentioned above.
>Fix:
unknown
>Submitter-Id: [EMAIL PROTECTED]
>Originator:
>Organization:
>MySQL support: [none ]
>Synopsis: server crash on nested selectes
>Severity: critical
>Priority: high
>Category: mysql
>Class: sw-bug
>Release: mysql-4.1.10-debug (MySQL Community Edition - Debug (GPL))
>C compiler: 2.95.3
>C++ compiler: 2.95.3
>Environment:
<machine, os, target, libraries (multiple lines)>
System: Linux ron-2.desktop.amazon.com 2.4.25-1.5a #1 Mon Nov 1 12:43:48 PST
2004 i686 unknown
Architecture: i686
Some paths: /opt/third-party/bin/perl /opt/third-party/bin/make /usr/bin/gmake
/opt/third-party/bin/gcc
GCC: Reading specs from
/opt/third-party/depot/Linux-2.4c2.2-i686/gcc-2.95.3/lib/gcc-lib/i686-pc-linux-gnu/2.95.3/specs
gcc version 2.95.3 20010315 (release)
Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=pentiumpro' CXX='gcc'
CXXFLAGS='-O2 -mcpu=pentiumpro -felide-constructors' LDFLAGS='' ASFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Sep 26 2003 /lib/libc.so.6 ->
libc-2.2.4.so
-rwxr-xr-x 1 root root 1283964 Dec 8 2001 /lib/libc-2.2.4.so
-rw-r--r-- 1 root root 27314296 Dec 8 2001 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Dec 8 2001 /usr/lib/libc.so
Configure command: ./configure '--prefix=/usr/local/mysql'
'--localstatedir=/usr/local/mysql/data' '--libexecdir=/usr/local/mysql/bin'
'--with-comment=MySQL Community Edition - Debug (GPL)'
'--with-extra-charsets=complex' '--with-server-suffix=-debug'
'--enable-thread-safe-client' '--enable-local-infile' '--enable-assembler'
'--disable-shared' '--with-client-ldflags=-all-static' '--with-debug'
'--with-mysqld-ldflags=-all-static' '--with-readline' '--with-embedded-server'
'--with-archive-storage-engine' '--with-innodb' 'CFLAGS=-O2 -mcpu=pentiumpro'
'CPPFLAGS=-DDEFAULT_THREAD_STACK=126976' 'CXXFLAGS=-O2 -mcpu=pentiumpro
-felide-constructors' 'CXX=gcc'set group_concat_max_len = 128000;
select ot.* from (
select a.assigned_to
, c.ticket_id
, type
, active_time as active_since
, max(corr.received_time) last_message
, c2.sender_email as last_replyer
, count(corr.correspondence_id) as num_mails
/* , (select sender_email from correspondence
where correspondence_id = (select min(correspondence_id)
from correspondence co, resolvers re
where co.ticket_id = c.ticket_id
and co.sender_email = re.resolver)) as first_responder
*/
, (select received_time from correspondence
where correspondence_id = (select min(correspondence_id)
from correspondence co, resolvers re
where co.ticket_id = c.ticket_id
and co.sender_email = re.resolver)) as first_response
, if(r1.resolver is null, 'OUR COURT', 'THEIR COURT') ball
, now()
, c.status
, timediff(now(), active_time) as diff
/* Minutes till first response --real */
/* Minutes till first response --business */
/* Minutes till resolve --real */
/* Minutes till resolve --business */
from contacts c left join assignment a on c.ticket_id = a.ticket_id
, correspondence corr
/* join on correspondence and resolver to get last replyer */
, correspondence c2 left join resolvers r1 on c2.sender_email =
r1.resolver
where status != 'resolved'
and corr.ticket_id = c.ticket_id
and c2.correspondence_id = (select max(correspondence_id) from
correspondence where ticket_id = c.ticket_id)
and active_time > '2005-02-25'
group by a.assigned_to, c.ticket_id, subject, type, active_time
order by active_since
) ot
;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
