In 5.5, a crashing, regression bug exists if you use SELECT COUNT(DISTINCT) *and* one of the WHERE operands is in the Primary Key (or just a unique index).
** Update: This bug may be fixed in 5.5.18. At least initial my initial test (the test case provided below) did not crash in 5.5.18. The MySQL Dev Team has not confirmed if this has been fixed or not, nor is there any mention of a fix in the 5.5.18 changelogs, so proceed with caution.
Note this bug does *not* affect any version of MariaDB – 5.1, 5.2, or 5.3. ** (12/5/2011)
This simple crash (if only one row is in the table) will crash mysqld.
Of course I’ve filed a bug report, but that has been nearly 3 months and no updates yet.
Here is the bug I filed (which you won’t be able to view):
Really, the only thing that happened to my bug report was that it was designated a duplicate of another bug (which we also cannot view):
Based on the id, and the submitted dates of bugs 61100 and 61102, this initial bug (61101) was filed on May 9, 2011. So, in fact, this bug has been present for over 5 months, and not one breath of an update to the public!
Therefore, I felt it necessary to warn others about this bug, (or possibly you’ll run across this if you’re searching on SELECT COUNT(DISTINCT) in the future).
All I can say is please watch out for it!
It is extremely easy to reproduce:
CREATE TABLE t (a int(1), b int(1), PRIMARY KEY (a,b)) ENGINE=InnoDB; INSERT INTO t VALUES (1, 1); SELECT COUNT(DISTINCT a) FROM t WHERE b = 1;
–> crash <--
For those interested, this was filed against 5.5.14. However, with each new release, I've continued testing, and this bug is present in 5.5.15, 5.5.16, and thus far in 5.5.17 (built from the latest bzr tree).
Hopefully we don't go too many more months before this is finally fixed.
And for reference (and those searching on the stack trace / error log messages), here is my full error log snippet from 5.5.16:
111017 10:54:47 [Note] C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld: ready for connections. Version: ‘5.5.16’ socket: ” port: 3308 MySQL Community Server (GPL) len 128; hex f8aec9037d803805f017fc03189ddc030000000… 111017 10:55:12 InnoDB: Assertion failure in thread 5000 in file btr0pcur.c line 236 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. 111017 10:55:12 – mysqld got exception 0xc0000005 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=26214400 read_buffer_size=65536 max_used_connections=1 max_threads=100 thread_count=1 connection_count=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 58325 K bytes of memory Hope that’s ok; if not, decrease some variables in the equation. Thread pointer: 0x3c98428 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong… 00CE92EC mysqld.exe!btr_pcur_restore_position_func()[btr0pcur.c:236] 00CA62FB mysqld.exe!sel_restore_position_for_mysql()[row0sel.c:3081] 00CA6CEA mysqld.exe!row_search_for_mysql()[row0sel.c:3820] 00C5FE20 mysqld.exe!ha_innobase::general_fetch()[ha_innodb.cc:5918] 00C5FEDD mysqld.exe!ha_innobase::index_next()[ha_innodb.cc:5956] 00C20DDA mysqld.exe!index_next_different()[opt_range.cc:11038] 00C249BC mysqld.exe!QUICK_GROUP_MIN_MAX_SELECT::next_prefix()[opt_range.cc:11099] 00C26BE7 mysqld.exe!QUICK_GROUP_MIN_MAX_SELECT::get_next()[opt_range.cc:10824] 00B68D01 mysqld.exe!rr_quick()[records.cc:344] 00BC1B9A mysqld.exe!sub_select()[sql_select.cc:11723] 00BD10A7 mysqld.exe!do_select()[sql_select.cc:11483] 00BD37BD mysqld.exe!JOIN::exec()[sql_select.cc:2370] 00BD3A29 mysqld.exe!mysql_select()[sql_select.cc:2581] 00BD3D4B mysqld.exe!handle_select()[sql_select.cc:297] 00ACD76E mysqld.exe!execute_sqlcom_select()[sql_parse.cc:4511] 00ACF816 mysqld.exe!mysql_execute_command()[sql_parse.cc:2118] 00AD2D1F mysqld.exe!mysql_parse()[sql_parse.cc:5548] 00AD3848 mysqld.exe!dispatch_command()[sql_parse.cc:1037] 00AD43BB mysqld.exe!do_command()[sql_parse.cc:771] 00AF2DB6 mysqld.exe!do_handle_one_connection()[sql_connect.cc:789] 00AF2F44 mysqld.exe!handle_one_connection()[sql_connect.cc:708] 00C33DE4 mysqld.exe!pthread_start()[my_winthread.c:61] 00D9C6F3 mysqld.exe!_callthreadstartex()[threadex.c:348] 00D9C79B mysqld.exe!_threadstartex()[threadex.c:326] 765F3823 kernel32.dll!BaseThreadInitThunk() 77CAA9BD ntdll.dll!LdrInitializeThunk() Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (03DC0F10): SELECT COUNT(DISTINCT a) FROM t WHERE b = 1 Connection ID (thread ID): 1 Status: NOT_KILLED The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. InnoDB: Thread 5980 stopped in file os0sync.c line 781 InnoDB: Thread 6820 stopped in file os0sync.c line 474 InnoDB: Thread 7532 stopped in file os0sync.c line 474