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: | |
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
[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".