| Bug #8532 | SELECT DISTINCT a, a causes server to crash | ||
|---|---|---|---|
| Submitted: | 16 Feb 2005 5:16 | Modified: | 8 Mar 2005 0:26 |
| Reporter: | [ name withheld ] | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | MySQL-server-5.0.3 built on Feb 14 | OS: | Linux (Linux) |
| Assigned to: | Timour Katchaounov | CPU Architecture: | Any |
[16 Feb 2005 8:16]
Heikki Tuuri
Hi!
I was able to crash mysqld-5.0.3 built on Linux on Monday. The crash happens both with a MyISAM type and InnoDB type table. A gdb stack trace below.
Looks like it believes there is a blob field in the key, though the table has no blobs.
Regards,
Heikki
heikki@hundin:~/mysql-5.0/client> ./mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.3-alpha-debug-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show create table t;
+-------+-----------------------------------------------------------------------
-------------------------------------------------------------------------+
| Table | Create Table
|
+-------+-----------------------------------------------------------------------
-------------------------------------------------------------------------+
| t | CREATE TABLE `t` (
`a` int(11) NOT NULL default '0',
`b` int(11) default NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------
-------------------------------------------------------------------------+
1 row in set (0.03 sec)
mysql> select * from t;
+---+------+
| a | b |
+---+------+
| 1 | 10 |
| 2 | 500 |
| 3 | 12 |
+---+------+
3 rows in set (0.00 sec)
mysql> select distinct a, a from t;
Aborted
heikki@hundin:~/mysql-5.0/client> ./mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.3-alpha-debug-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> alter table t type=myisam;
Query OK, 3 rows affected, 1 warning (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select distinct a, a from t;
(gdb) run
The program being debugged has been started already.
Start it from the beginning? (y or n) y
Starting program: /home/heikki/mysql-5.0/sql/mysqld
[New Thread 16384 (LWP 23269)]
[New Thread 32769 (LWP 23270)]
[New Thread 16386 (LWP 23271)]
[New Thread 32771 (LWP 23272)]
[New Thread 49156 (LWP 23273)]
[New Thread 65541 (LWP 23274)]
050216 9:30:25 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
050216 9:30:25 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 47925.
InnoDB: Doing recovery: scanned up to log sequence number 0 47925
InnoDB: Last MySQL binlog file position 0 95, file name ./binlog.000005
[New Thread 81926 (LWP 23275)]
[New Thread 98311 (LWP 23276)]
[New Thread 114696 (LWP 23277)]
[New Thread 131081 (LWP 23278)]
050216 9:30:25 InnoDB: Started; log sequence number 0 47925
[New Thread 147466 (LWP 23279)]
050216 9:30:25 [Warning] mysql.user table is not updated to new password format
; Disabling new password usage until mysql_fix_privilege_tables is run
/home/heikki/mysql-5.0/sql/mysqld: ready for connections.
Version: '5.0.3-alpha-debug-log' socket: '/home/heikki/bugsocket' port: 3307
Source distribution
[New Thread 163851 (LWP 23293)]
Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 163851 (LWP 23293)]
0x401941bc in memcpy () from /lib/i686/libc.so.6
(gdb) bt
#0 0x401941bc in memcpy () from /lib/i686/libc.so.6
#1 0x0817da1b in Field_blob::get_ptr(char**) (this=0x495250ff, str=0x43d502d8)
at field.h:1141
#2 0x0817ef1f in key_copy(char*, char*, st_key*, unsigned) (
to_key=0x8cb19fd '¥' <repeats 200 times>...,
from_record=0x8c864e8 "¥\003", key_info=0x8c863b8, key_length=1)
at key.cc:127
#3 0x0825097f in QUICK_GROUP_MIN_MAX_SELECT::reset() (this=0x8c8f7f8)
at opt_range.cc:7929
#4 0x081f6c4a in join_init_read_record (tab=0x8cacfa0) at sql_select.cc:9428
#5 0x081f5968 in sub_select (join=0x8cabe18, join_tab=0x8cacfa0,
end_of_records=false) at sql_select.cc:8873
#6 0x081f557a in do_select (join=0x8cabe18, fields=0x0, table=0x8c8f028,
procedure=0x0) at sql_select.cc:8655
#7 0x081e3b88 in JOIN::exec() (this=0x8cabe18) at sql_select.cc:1264
#8 0x081e6029 in mysql_select(THD*, Item***, st_table_list*, unsigned, List<Ite
m>&, Item*, unsigned, st_order*, st_order*, Item*, st_order*, unsigned long, sel
ect_result*, st_select_lex_unit*, st_select_lex*) (thd=0x8c8cf78,
rref_pointer_array=0x8c8d290, tables=0x8cabb48, wild_num=0,
fields=@0x8c8d1f8, conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0,
proc_param=0x0, select_options=8669697, result=0x8cabe08, unit=0x8c8cfc4,
select_lex=0x8c8d18c) at sql_select.cc:2049
#9 0x081e0550 in handle_select(THD*, st_lex*, select_result*, unsigned long) (
thd=0x8c8cf78, lex=0x8c8cfb8, result=0x8cabe08, setup_tables_done_option=0)
at sql_select.cc:227
#10 0x081afe95 in mysql_execute_command(THD*) (thd=0x8c8cf78)
at sql_parse.cc:2467
#11 0x081b6f2f in mysql_parse(THD*, char*, unsigned) (thd=0x8c8cf78,
inBuf=0x8cab9a8 "select distinct a, a from t", length=27)
at sql_parse.cc:4959
#12 0x081adca0 in dispatch_command(enum_server_command, THD*, char*, unsigned)
(command=COM_QUERY, thd=0x8c8cf78,
packet=0x8ca3949 "select distinct a, a from t", packet_length=28)
at sql_parse.cc:1675
#13 0x081ad50f in do_command(THD*) (thd=0x8c8cf78) at sql_parse.cc:1483
#14 0x081ac450 in handle_one_connection (arg=0x8c8cf78) at sql_parse.cc:1081
#15 0x40062f60 in pthread_start_thread () from /lib/i686/libpthread.so.0
#16 0x400630fe in pthread_start_thread_event () from /lib/i686/libpthread.so.0
#17 0x401f5327 in clone () from /lib/i686/libc.so.6
(gdb)
[28 Feb 2005 14:29]
Timour Katchaounov
The problem was in get_best_group_mini_max() which was incorrectly counting duplicate fields in DISTINCT queries as different fields. As a result the method QUICK_GROUP_MIN_MAX::reset() was accessing key parts for which the query has no fields that refer to them, consequently crashing mysqld.
[1 Mar 2005 5:55]
Timour Katchaounov
Fixed in 5.0.3.
[8 Mar 2005 0:26]
Paul DuBois
Noted in 5.0.3 changelog.
[26 Jul 2007 5:36]
Prashant Sonnaik
hi i'm working on windows platform and my PHP Version is 4.3.8 and mysql version is 3.23.49, i tried firing the query the way u have said but nothing, so called as crash takes place, instead is outputs same column again. i feel what u have said is not true
Description: I first noticed this using MyODBC with Microsoft Access, but it seems to be a problem with the mysql server. I have a table with the colums below and 703 rows in it. The wafer_ID column has 21 distinct values in it. +----------------+-------------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+-------------+------+-----+-------------------+-------+ | device_ID | varchar(10) | | PRI | | | | wafer_ID | int(20) | | MUL | 0 | | | section_ID | int(20) | YES | | 0 | | | design_ID | int(20) | YES | | 0 | | | Singulated | tinyint(1) | YES | | 0 | | | AR_Coated | tinyint(1) | YES | | 0 | | | NotInInventory | tinyint(1) | YES | | 0 | | | CapsShorted | tinyint(1) | YES | | NULL | | | Comments | text | YES | | NULL | | | stamp | timestamp | YES | | CURRENT_TIMESTAMP | | +----------------+-------------+------+-----+-------------------+-------+ When I issue the query: SELECT DISTINCT wafer_ID FROM chip_info; I get: +----------+ | wafer_ID | +----------+ | 0 | | 35 | | 49 | | 50 | | 83 | | 84 | | 85 | | 86 | | 88 | | 89 | | 90 | | 91 | | 92 | | 100 | | 104 | | 106 | | 107 | | 108 | | 109 | | 111 | | 121 | +----------+ But if I use the query: SELECT DISTINCT wafer_ID,wafer_ID FROM chip_info; The server goes to 99% CPU usage and stays there. To test this, I created a new table with only the wafer_ID column, and added all the data from this table. It still works for "SELECT DISTINCT wafer_ID FROM chip_info;", but now when I use "SELECT DISTINCT wafer_ID,wafer_ID FROM chip_info;" I get the following error: ERROR 2013 (HY000): Lost connection to MySQL server during query but the server stays running, and if I reissue the command, then my client instantly reconnects to the server, and then loses the connection again with the same error message. Here is my mysqld.log for these events, where you can see the server restarting after the query is issued: /usr/sbin/mysqld, Version: 5.0.2-alpha-standard-log. started with: Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock Time Id Command Argument 050215 20:16:43 1 Connect root@not.real.com on 050215 20:16:48 1 Query SELECT DATABASE() 1 Init DB test 050215 20:17:03 1 Query select distinct wafer_ID,wafer_ID from chip_info /usr/sbin/mysqld, Version: 5.0.2-alpha-standard-log. started with: Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock Time Id Command Argument 050215 20:17:57 1 Connect root@Sergei.versawave.com on test 1 Query select distinct wafer_ID,wafer_ID from chip_info /usr/sbin/mysqld, Version: 5.0.2-alpha-standard-log. started with: Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock Time Id Command Argument 050215 20:18:18 1 Connect root@Sergei.versawave.com on test 1 Query select 1 How to repeat: Create a table with a single column of type Int(20) with a key on it. Add 703 rows to the table, using 21 distinct values. Issue query "SELECT DISTINCT column FROM table;" to see that it works. Issue query "SELECT DISTINCT column,column FROM table;" which fails. Suggested fix: I could just change all my queries to use GROUP BY instead of DISTINCT. Or, find a way to stop Access/MyODBC from using the column name twice in SELECT DISTINCT queries.