MySQL Bugs: #21511: crash on SELECT

Bug #21511	crash on SELECT
Submitted:	8 Aug 2006 16:25	Modified:	14 Aug 2006 16:25
Reporter:	Nicholas Gresham	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.22	OS:	Linux (gentoo linux 64-bits)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
SELECT DISTINCT statement with ORDER BY clause causes mysqld to crash with  SIGSEGV

platform: 

System uname: 2.6.17-gentoo-r4-ng x86_64 AMD Opteron(tm) Processor 246
gcc version 4.1.1 (Gentoo 4.1.1)
glibc-2.4
binutils-2.17

CBUILD="x86_64-pc-linux-gnu"
CFLAGS="-march=k8 -O1 -pipe -ggdb"
CHOST="x86_64-pc-linux-gnu"

files attached: pride.sql (part of a proteomics database)
                mysqld.trace
                mysql.gdb (backtrace)

How to repeat:
# mysqladmin -p create pride_test
# mysql -p pride_test <pride.sql
# mysql -p 

mysql> use pride-test;
Database changed
mysql> SELECT DISTINCT A0.cv_label,A0.accession,A0.name FROM mzdata_param A0 WHERE ( ( A0.param_type = 5) AND  (A0.classname = 'uk.ac.ebi.pride.rdbms.ojb.model.mzData.CvParamBean')) AND  (A0.classname = 'uk.ac.ebi.pride.rdbms.ojb.model.mzData.CvParamBean') order by 1,1;
+----------+-----------+-----------------+
| cv_label | accession | name            |
+----------+-----------+-----------------+
| mesh     | D001792   | Blood Platelets |
+----------+-----------+-----------------+
1 row in set (0.01 sec)

mysql> use pride-test; 

No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)
ERROR:
Can't connect to the server

mysqld trace

Attachment: mysqld.trace.bz2 (application/octet-stream, text), 45.38 KiB.

gdb backtrace

Attachment: mysql.gdb (application/octet-stream, text), 4.73 KiB.

database fragment

Attachment: pride.sql.bz2 (application/octet-stream, text), 11.68 KiB.

Thank you for the bug report. I was unable to repeat with current source
server on Linux and also the crash not happens with 5.0.24 on Windows,
could you please try to test with the latest release version? Thanks in
advance.

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.25-debug | 
+--------------+
1 row in set (0.00 sec)

mysql> SELECT DISTINCT A0.cv_label,A0.accession,A0.name FROM mzdata_param A0
    -> WHERE ( ( A0.param_type = 5) AND  (A0.classname =
    -> 'uk.ac.ebi.pride.rdbms.ojb.model.mzData.CvParamBean')) AND  (A0.classname =
    -> 'uk.ac.ebi.pride.rdbms.ojb.model.mzData.CvParamBean') order by 1,1;
+----------+-----------+-----------------+
| cv_label | accession | name            |
+----------+-----------+-----------------+
| mesh     | D001792   | Blood Platelets | 
+----------+-----------+-----------------+
1 row in set (0.07 sec)

mysql> use pride-test; 
Database changed
mysql> show tables;
+------------------------------+
| Tables_in_pride-test         |
+------------------------------+
| mzdata_acq_specification     | 
| mzdata_acquisition           | 
<cut>

5.0.24 exhibits the same behavior for me:

# mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.24-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use pride-test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT DISTINCT A0.cv_label,A0.accession,A0.name FROM mzdata_param A0 WHERE ( ( A0.param_type = 5) AND  (A0.classname = 'uk.ac.ebi.pride.rdbms.ojb.model.mzData.CvParamBean')) AND  (A0.classname = 'uk.ac.ebi.pride.rdbms.ojb.model.mzData.CvParamBean') order by 1,1;
+----------+-----------+-----------------+
| cv_label | accession | name            |
+----------+-----------+-----------------+
| mesh     | D001792   | Blood Platelets |
+----------+-----------+-----------------+
1 row in set (0.02 sec)

mysql> use pride-test; No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)
ERROR:
Can't connect to the server

same problem with 5.1.7_beta on the same machine AND with 5.0.22 running on an AMD64 fedora machine

Thank you for the feedback. I tested on Suse Linux 32-bit. I will ask to
my coworker for to test on his 64-bit box.

Verified as described by reporter on FC5 x64 AMD with MySQL server 5.0.23 official, 5.0.25 BK and 5.1.12. After result reaches client, server crashes.

mysql> use pride-test
Database changed
mysql> SELECT DISTINCT A0.cv_label,A0.accession,A0.name FROM mzdata_param A0 WHE
RE ( ( A0.param_type = 5) AND  (A0.classname = 'uk.ac.ebi.pride.rdbms.ojb.model.
mzData.CvParamBean')) AND  (A0.classname = 'uk.ac.ebi.pride.rdbms.ojb.model.mzDa
ta.CvParamBean') order by 1,1;
+----------+-----------+-----------------+
| cv_label | accession | name            |
+----------+-----------+-----------------+
| mesh     | D001792   | Blood Platelets |
+----------+-----------+-----------------+
1 row in set (0.03 sec)

mysql> select version();
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: pride-test

--<cut>--
+-------------------+
| version()         |
+-------------------+
| 5.1.12-beta-debug |
+-------------------+
1 row in set (0.20 sec)

Modified script and trace file for 5.1.12

Attachment: bug21511data.zip (application/x-zip-compressed, text), 57.44 KiB.

Stealing this one as it looks like a dup of bug#20503.

This bug is a duplicate of bug#20503.