Description: A particular select distinct with three columns
consistently crashes mysqld. This happens with both 4.0.2 and 4.0.3.
How-To-Repeat: This may be somewhat challenging, because the table in
question contains 12 million records, comprising nearly 1GB of data.
Here is the output of show create table:
CREATE TABLE `naked_ipcs` (
`rec_type` enum('A','B','B9','S','T') NOT NULL default 'A',
`decl_year` year(4) NOT NULL default '0000',
`decl_num` mediumint(8) unsigned NOT NULL default '0',
`ix` tinyint(3) unsigned NOT NULL default '0',
`patent_id` char(16) default NULL,
`naked_ipc` char(32) default NULL,
`file` char(16) default NULL,
PRIMARY KEY (`rec_type`,`decl_year`,`decl_num`,`ix`),
KEY `file` (`file`),
KEY `naked_ipc` (`naked_ipc`)
) TYPE=MyISAM
Here is the output of myisamchk -dvv:
MyISAM file: naked_ipcs.MYI
Record format: Fixed length
Character set: latin1 (8)
File-version: 1
Creation time: 2002-09-21 6:04:49
Recover time: 2002-09-21 6:19:09
Status: checked,analyzed,sorted index pages
Data records: 12388956 Deleted blocks: 0
Datafile parts: 12388956 Deleted data: 0
Datafile pointer (bytes): 4 Keyfile pointer (bytes): 3
Datafile length: 879615876 Keyfile length: 274899968
Max datafile length: 304942678014 Max keyfile length: 17179868159
Recordlength: 71
table description:
Key Start Len Index Type Rec/key Root Blocksize
1 2 1 unique binary 0 1024 1024
3 1 binary 0
4 3 uint24 0
7 1 binary 1
2 56 16 multip. char packed stripped NULL 41159 147346432 1024
3 24 32 multip. char packed stripped NULL 85 210661376 1024
Field Start Length Nullpos Nullbit Type
1 1 1
2 2 1
3 3 1
4 4 3
5 7 1
6 8 16 1 2
7 24 32 1 4
8 56 16 1 8
This is the query that causes the crash:
mysql> select count( distinct rec_type, decl_year, decl_num ) from naked_ipcs;
ERROR 2013: Lost connection to MySQL server during query
Here is the result of following the instructions for reporting stack
traces:
0x80e13e9 handle_segfault__Fi + 449
0x40034f54 _end + 935256504
0x4016f1a3 _end + 936543239
0x8349a53 tree_insert + 483
0x80ae94e add__23Item_sum_count_distinct + 226
0x8113a9f update_sum_func__FPP8Item_sum + 31
0x810e243 end_send_group__FP4JOINP13st_join_tableb + 607
0x810d596 sub_select__FP4JOINP13st_join_tableb + 262
0x810d2a6 do_select__FP4JOINPt4List1Z4ItemP8st_tableP9Procedure + 418
0x81064e9
mysql_select__FP3THDP13st_table_listRt4List1Z4ItemP4ItemP8st_orderT4T3T4UlP13select_result
+ 6393
0x81128e0 handle_select__FP3THDP6st_lexP13select_result + 100
0x80ec758 mysql_execute_command__Fv + 940
0x80f01e7 mysql_parse__FP3THDPcUi + 551
0x80eb898 dispatch_command__F19enum_server_commandP3THDPcUi + 1468
0x80f1539 do_command__FP3THD + 93
0x80eab1e handle_one_connection__FPv + 670
0x400320ba _end + 935244574
0x401c9d4a _end + 936914862
Similar queries for only count( distinct rec_type ) and for count (
distinct rec_type, decl_year ) complete without error.
Fix: unknown
Submitter-Id:
Originator: Robert Coie
Organization: Apropos K.K.
MySQL support: none (ancient probably out-of-date licence)
Synopsis: 4.0.x server crash on select count distinct multiple fields
Severity: serious (I think server crashes are serious)
Priority: low (I couldn't find other reports of it, so may be localized)
Category: mysql
Class: sw-bug
Release: mysql-4.0.3-beta (Source distribution)
Server: /usr/bin/mysqladmin Ver 8.37 Distrib 4.0.3-beta, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 4.0.3-beta
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 8 min 48 sec
Threads: 1 Questions: 1 Slow queries: 0 Opens: 6 Flush tables: 1 Open tables: 0
Queries per second avg: 0.002
Environment:
<machine, os, target, libraries (multiple lines)>
System: Linux kawazu2 2.4.19 #1 Sat Sep 14 17:09:38 JST 2002 i686 unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/2.95.4/specs
gcc version 2.95.4 20011002 (Debian prerelease)
Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Sep 14 15:04 /lib/libc.so.6 ->
libc-2.2.5.so
-rwxr-xr-x 1 root root 1153784 Aug 2 12:36 /lib/libc-2.2.5.so
-rw-r--r-- 1 root root 2390922 Aug 2 12:37 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Aug 2 12:37 /usr/lib/libc.so
Configure command: ./configure --prefix=/usr --exec-prefix=/usr --libexecdir=/usr/sbin
--datadir=/usr/share --sysconfdir=/etc/mysql --localstatedir=/var/lib/mysql
--includedir=/u\
sr/include --infodir=/usr/share/info --mandir=/usr/share/man --enable-shared
--without-libwrap --enable-assembler --with-berkeley-db --with-innodb --enable-static
--enable-shared\
--enable-local-infile --with-raid --enable-thread-safe-client --without-readline
--with-unix-socket-path=/var/run/mysqld/mysqld.sock --with-mysqld-user=mysql
--without-bench --w\
ith-client-ldflags=-lstdc++ --with-extra-charsets=all
--
Robert Coie <[EMAIL PROTECTED]>
Implementor, Apropos Ltd.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
