Bug #23094 MySQLNT Crashes when executing INNER JOIN query
Submitted: 8 Oct 2006 18:46 Modified: 9 Dec 2006 15:50
Reporter: Jonathan Yaniv Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:5.0 OS:Windows (WINXPSP2)
Assigned to: CPU Architecture:Any
Tags: INNER JOIN, MYSQL QUERY, MYSQL SERVER

[8 Oct 2006 18:46] Jonathan Yaniv
Description:
I executed this query using the query browser, and mysqlnt crashed.

select stdfirstname, stdlastname, stdcity, enrgrade from ((student inner join enrollment on student.stdssn = enrollment.stdssn ) inner join offering on offering.offerno = enrollment.offerno ), Faculty WHERE EnrGrade >= 3.5 and offterm = 'fall' and offyear = 2005 and facfirstname = 'leonard' and faclastname = 'vince' and faculty.facssn = offering.facssn;

How to repeat:
Execute this query

select stdfirstname, stdlastname, stdcity, enrgrade from ((student inner join enrollment on student.stdssn = enrollment.stdssn ) inner join offering on offering.offerno = enrollment.offerno ), Faculty WHERE EnrGrade >= 3.5 and offterm = 'fall' and offyear = 2005 and facfirstname = 'leonard' and faclastname = 'vince' and faculty.facssn = offering.facssn;

Suggested fix:
To fix it somehow so I can do my work and execute these queries.
[8 Oct 2006 18:47] Jonathan Yaniv
This is top priority. I cant execute any queries like this.
[9 Oct 2006 8:33] Valeriy Kravchuk
Thank you for a problem report. Please, send the last part of your error log that corresponds to this crash (or complete error log, if it is not large), send your my.ini file content, describe your hardware (how much RAM do you have), and send the results of SHOW CREATE TABLE and SHOW TABLE STATUS commands for all the tables involved in your SELECT (student, enrollment, offering, Faculty).

Send the results of

EXPLAIN select stdfirstname, stdlastname, stdcity, enrgrade from ((student inner join
enrollment on student.stdssn = enrollment.stdssn ) inner join offering on
offering.offerno = enrollment.offerno ), Faculty WHERE EnrGrade >= 3.5 and
offterm = 'fall' and offyear = 2005 and facfirstname = 'leonard' and faclastname
= 'vince' and faculty.facssn = offering.facssn\G

also.
[9 Oct 2006 17:13] Jonathan Yaniv
'course', 'CREATE TABLE `course` (
  `CourseNo` char(6) NOT NULL default '',
  `CrsDesc` varchar(250) default NULL,
  `CrsUnits` smallint(6) default NULL,
  PRIMARY KEY  (`CourseNo`),
  UNIQUE KEY `UniqueCrsDesc` (`CrsDesc`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1'

'student', 'CREATE TABLE `student` (
  `StdSSN` char(11) NOT NULL default '',
  `StdFirstName` varchar(50) default NULL,
  `StdLastName` varchar(50) default NULL,
  `StdCity` varchar(50) default NULL,
  `StdState` char(2) default NULL,
  `StdMajor` char(6) default NULL,
  `StdClass` char(2) default NULL,
  `StdGPA` decimal(3,2) default NULL,
  `StdZip` char(10) default NULL,
  PRIMARY KEY  (`StdSSN`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1'
[9 Oct 2006 17:13] Jonathan Yaniv
'enrollment', 'CREATE TABLE `enrollment` (
  `OfferNo` int(11) NOT NULL,
  `StdSSN` char(11) NOT NULL,
  `EnrGrade` decimal(3,2) default NULL,
  PRIMARY KEY  (`OfferNo`,`StdSSN`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1'

'faculty', 'CREATE TABLE `faculty` (
  `FacSSN` char(11) NOT NULL default '',
  `FacFirstName` varchar(50) NOT NULL,
  `FacLastName` varchar(50) NOT NULL,
  `FacCity` varchar(50) NOT NULL,
  `FacState` char(2) NOT NULL,
  `FacDept` char(6) default NULL,
  `FacRank` char(4) default NULL,
  `FacSalary` decimal(10,2) default NULL,
  `FacSupervisor` char(11) default NULL,
  `FacHireDate` date default NULL,
  `FacZipCode` char(10) NOT NULL,
  PRIMARY KEY  (`FacSSN`),
  KEY `FKFacSupervisor` (`FacZipCode`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1'

'offering', 'CREATE TABLE `offering` (
  `OfferNo` int(10) unsigned NOT NULL auto_increment,
  `CourseNo` char(6) NOT NULL,
  `OffLocation` varchar(50) default NULL,
  `OffDays` char(6) default NULL,
  `OffTerm` char(6) default NULL,
  `OffYear` int(11) default NULL,
  `FacSSN` char(11) default NULL,
  `OffTime` time default NULL,
  PRIMARY KEY  (`OfferNo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1'
[9 Oct 2006 17:13] Jonathan Yaniv
"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
1,"SIMPLE","offering","ALL","PRIMARY","","","",13,"Using where"
1,"SIMPLE","enrollment","ref","PRIMARY","PRIMARY","4","student.offering.OfferNo",4,"Using where"
1,"SIMPLE","Faculty","eq_ref","PRIMARY","PRIMARY","11","student.offering.FacSSN",1,"Using where"
1,"SIMPLE","student","eq_ref","PRIMARY","PRIMARY","11","student.enrollment.StdSSN",1,""
[9 Oct 2006 17:14] Jonathan Yaniv
Event Type:	Error
Event Source:	Application Error
Event Category:	(100)
Event ID:	1000
Date:		10/8/2006
Time:		2:32:06 PM
User:		N/A
Computer:	LAPTOP
Description:
Faulting application mysqld-nt.exe, version 0.0.0.0, faulting module mysqld-nt.exe, version 0.0.0.0, fault address 0x001d4015.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 41 70 70 6c 69 63 61 74   Applicat
0008: 69 6f 6e 20 46 61 69 6c   ion Fail
0010: 75 72 65 20 20 6d 79 73   ure  mys
0018: 71 6c 64 2d 6e 74 2e 65   qld-nt.e
0020: 78 65 20 30 2e 30 2e 30   xe 0.0.0
0028: 2e 30 20 69 6e 20 6d 79   .0 in my
0030: 73 71 6c 64 2d 6e 74 2e   sqld-nt.
0038: 65 78 65 20 30 2e 30 2e   exe 0.0.
0040: 30 2e 30 20 61 74 20 6f   0.0 at o
0048: 66 66 73 65 74 20 30 30   ffset 00
0050: 31 64 34 30 31 35         1d4015
[9 Oct 2006 17:14] Jonathan Yaniv
Event Type:	Error
Event Source:	Application Error
Event Category:	(100)
Event ID:	1000
Date:		10/8/2006
Time:		2:42:25 PM
User:		N/A
Computer:	LAPTOP
Description:
Faulting application mysqld-nt.exe, version 0.0.0.0, faulting module mysqld-nt.exe, version 0.0.0.0, fault address 0x001d4015.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 41 70 70 6c 69 63 61 74   Applicat
0008: 69 6f 6e 20 46 61 69 6c   ion Fail
0010: 75 72 65 20 20 6d 79 73   ure  mys
0018: 71 6c 64 2d 6e 74 2e 65   qld-nt.e
0020: 78 65 20 30 2e 30 2e 30   xe 0.0.0
0028: 2e 30 20 69 6e 20 6d 79   .0 in my
0030: 73 71 6c 64 2d 6e 74 2e   sqld-nt.
0038: 65 78 65 20 30 2e 30 2e   exe 0.0.
0040: 30 2e 30 20 61 74 20 6f   0.0 at o
0048: 66 66 73 65 74 20 30 30   ffset 00
0050: 31 64 34 30 31 35         1d4015
[9 Oct 2006 17:15] Jonathan Yaniv
Do I get a free iPod for finding this bug?
[9 Oct 2006 17:50] MySQL Verification Team
Thank you for the feedback. Could you please provide a dump of the table
(with data insert commands) just with the table definition isn't enough
to repeat. Also please provide the exactly server version i.e: 5.0.24a.
Thanks in advance.
[9 Oct 2006 18:34] Jonathan Yaniv
Version 5.0.24a

Also, when using MySQL Administrator to do a database backup on this database, MySQL Server crashes, I will be posting a new bug with this as well.
[9 Oct 2006 19:47] MySQL Verification Team
from checking .map files, this crash appears in innodb code possibly. please check the error log and windows event viewer in case there are any other errors. also, you should run check table on all the tables.
[9 Oct 2006 19:53] Jonathan Yaniv
Innodb could not find key n:o 0 with name PRIMARY from dict cache for table student/enrollment

For more information, see Help and Support Center at http://www.mysql.com.
[9 Oct 2006 20:05] MySQL Verification Team
Here's by guess (without having a core/memory dump)
of where a crash occurred:

ibool
dict_index_contains_col_or_prefix(
/*==============================*/
				/* out: TRUE if contains the column or its
				prefix */
	dict_index_t*	index,	/* in: index */
	ulint		n)	/* in: column number */
{
	dict_field_t*	field;
	dict_col_t*	col;
	ulint		pos;
	ulint		n_fields;
	
	ut_ad(index);
	ut_ad(index->magic_n == DICT_INDEX_MAGIC_N);

	if (index->type & DICT_CLUSTERED) {

		return(TRUE);
	}

	col = dict_table_get_nth_col(index->table, n); <--------- CRASHED HERE

	n_fields = dict_index_get_n_fields(index);
[9 Oct 2006 20:13] MySQL Verification Team
oops, wrong line :)  i meant "if (index->type & DICT_CLUSTERED)" crashed due to invalid index. 

So, it means mysqld-debug.exe might catch this error and print more information?
[9 Oct 2006 20:17] Jonathan Yaniv
How do u execute mysql debug?
[11 Oct 2006 14:09] Valeriy Kravchuk
Just run it from the command line, as described at http://dev.mysql.com/doc/refman/5.0/en/windows-start-command-line.html (but use mysqld-debug.exe instead of mysqld). Please, send the error log of MySQL server also (file <hostname>.err in the data directory).
[11 Oct 2006 20:13] Jonathan Yaniv
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Owner.YOUR-8C087A2E17>mysqld-debug --console
061011 13:11:57 [Warning] You have forced lower_case_table_names to 0 through a
command-line option, even though your file system 'C:\mysql50\data\' is case ins
ensitive.  This means that you can corrupt a MyISAM table by accessing it with d
ifferent cases. You should consider changing lower_case_table_names to 1 or 2
061011 13:12:06  InnoDB: Started; log sequence number 0 208558
Can't start server: Bind on TCP/IP port: No such file or directory
061011 13:12:06 [ERROR] Do you already have another mysqld server running on por
t: 3306 ?
061011 13:12:06 [ERROR] Aborting

061011 13:12:06  InnoDB: Starting shutdown...
061011 13:12:08  InnoDB: Shutdown completed; log sequence number 0 208558
061011 13:12:08 [Note] mysqld-debug: Shutdown complete

C:\Documents and Settings\Owner.YOUR-8C087A2E17>

Thing is, there is not another mysql server running...
[11 Oct 2006 20:15] Jonathan Yaniv
Error log

Attachment: laptop.rar (application/octet-stream, text), 9.28 KiB.

[11 Oct 2006 20:16] Jonathan Yaniv
Will I be getting a free iPod for all this work?
[22 Oct 2006 11:58] Valeriy Kravchuk
Please, send the results of:

netstat -a

command from cmd.exe. I want to double-check if you really has no other program listening to port 3306.
[22 Oct 2006 17:06] Jonathan Yaniv
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Owner.YOUR-8C087A2E17>netstat -a

Active Connections

  Proto  Local Address          Foreign Address        State
  TCP    laptop:epmap           laptop:0               LISTENING
  TCP    laptop:microsoft-ds    laptop:0               LISTENING
  TCP    laptop:902             laptop:0               LISTENING
  TCP    laptop:912             laptop:0               LISTENING
  TCP    laptop:990             laptop:0               LISTENING
  TCP    laptop:1027            laptop:0               LISTENING
  TCP    laptop:3306            laptop:0               LISTENING
  TCP    laptop:3389            laptop:0               LISTENING
  TCP    laptop:4446            laptop:0               LISTENING
  TCP    laptop:5561            laptop:0               LISTENING
  TCP    laptop:5581            laptop:0               LISTENING
  TCP    laptop:1025            localhost:32000        ESTABLISHED
  TCP    laptop:1037            laptop:0               LISTENING
  TCP    laptop:3135            localhost:3136         ESTABLISHED
  TCP    laptop:3136            localhost:3135         ESTABLISHED
  TCP    laptop:5679            laptop:0               LISTENING
  TCP    laptop:7438            laptop:0               LISTENING
  TCP    laptop:32000           laptop:0               LISTENING
  TCP    laptop:32000           localhost:1025         ESTABLISHED
  TCP    laptop:netbios-ssn     laptop:0               LISTENING
  TCP    laptop:netbios-ssn     laptop:0               LISTENING
  TCP    laptop:netbios-ssn     laptop:0               LISTENING
  TCP    laptop:epmap           S010600301b099e7c.vw.shawcable.net:1975  ESTABLI
SHED
  TCP    laptop:epmap           S010600301b099e7c.vw.shawcable.net:2832  ESTABLI
SHED
  TCP    laptop:netbios-ssn     laptop:0               LISTENING
  TCP    laptop:1521            laptop:0               LISTENING
  TCP    laptop:3137            static-fxfeeds.nslb.sj.mozilla.com:http  ESTABLI
SHED
  TCP    laptop:3138            72.14.253.147:http     ESTABLISHED
  TCP    laptop:3139            static-fxfeeds.nslb.sj.mozilla.com:http  ESTABLI
SHED
  TCP    laptop:3142            a72-246-53-41.deploy.akamaitechnologies.com:http
  ESTABLISHED
  TCP    laptop:3143            a72-246-53-50.deploy.akamaitechnologies.com:http
  ESTABLISHED
  TCP    laptop:3144            a72-246-53-50.deploy.akamaitechnologies.com:http
  ESTABLISHED
  TCP    laptop:3149            promo.intl.ebay.com:http  ESTABLISHED
  TCP    laptop:3152            thumbs.ebay.com:http   ESTABLISHED
  TCP    laptop:3153            thumbs.ebay.com:http   ESTABLISHED
  TCP    laptop:3155            a72-246-53-65.deploy.akamaitechnologies.com:http
  ESTABLISHED
  TCP    laptop:3156            a72-246-53-65.deploy.akamaitechnologies.com:http
  ESTABLISHED
  TCP    laptop:3157            comm.ebay.ca:http      CLOSE_WAIT
  TCP    laptop:3167            mail2.wallop.com:http  TIME_WAIT
  TCP    laptop:3170            proxy.wallop.com:http  ESTABLISHED
  TCP    laptop:3172            alpha.wallop.com:https  ESTABLISHED
  TCP    laptop:3174            alpha.wallop.com:http  ESTABLISHED
  TCP    laptop:3175            alpha.wallop.com:http  ESTABLISHED
  TCP    laptop:3176            a72-246-53-11.deploy.akamaitechnologies.com:http
  ESTABLISHED
  TCP    laptop:3177            64.233.167.111:995     TIME_WAIT
  TCP    laptop:3178            a72-246-53-11.deploy.akamaitechnologies.com:http
  ESTABLISHED
  TCP    laptop:3179            64.59.144.31:8080      CLOSE_WAIT
  TCP    laptop:3180            64.59.144.31:8080      CLOSE_WAIT
  TCP    laptop:3181            64.59.144.30:8080      CLOSE_WAIT
  UDP    laptop:microsoft-ds    *:*
  UDP    laptop:isakmp          *:*
  UDP    laptop:1034            *:*
  UDP    laptop:1494            *:*
  UDP    laptop:1505            *:*
  UDP    laptop:2072            *:*
  UDP    laptop:2707            *:*
  UDP    laptop:2708            *:*
  UDP    laptop:2709            *:*
  UDP    laptop:3611            *:*
  UDP    laptop:3776            *:*
  UDP    laptop:4500            *:*
  UDP    laptop:ntp             *:*
  UDP    laptop:1680            *:*
  UDP    laptop:1900            *:*
  UDP    laptop:3107            *:*
  UDP    laptop:4783            *:*
  UDP    laptop:ntp             *:*
  UDP    laptop:netbios-ns      *:*
  UDP    laptop:netbios-dgm     *:*
  UDP    laptop:ntp             *:*
  UDP    laptop:netbios-ns      *:*
  UDP    laptop:netbios-dgm     *:*
  UDP    laptop:1900            *:*
  UDP    laptop:ntp             *:*
  UDP    laptop:netbios-ns      *:*
  UDP    laptop:netbios-dgm     *:*
  UDP    laptop:1900            *:*
  UDP    laptop:ntp             *:*
  UDP    laptop:netbios-ns      *:*
  UDP    laptop:netbios-dgm     *:*
  UDP    laptop:1900            *:*

C:\Documents and Settings\Owner.YOUR-8C087A2E17>
[5 Nov 2006 20:29] Jonathan Yaniv
Once again, will I be getting a free iPod for this?
[5 Nov 2006 21:00] Jim Winstead
The current "Find a bug and win an iPod!" promotion is not just a blanket gift of an iPod to all bug reporters. You can see the rules for the promotion at http://dev.mysql.com/find_bug-win_ipod.html
[8 Nov 2006 15:31] Heikki Tuuri
Jonathan,

please post the complete .err log as ASCII text. I cannot read laptop.rar.

Can you test this on Linux, so that we could get a stack trace?

Regards,

Heikki
[10 Dec 2006 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".