MySQL Bugs: #30201: crash with query cache when killing a select

Bug #30201	crash with query cache when killing a select
Submitted:	2 Aug 2007 10:40	Modified:	25 Aug 2007 15:50
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S1 (Critical)
Version:	5.0.48BK,5.1.21BK	OS:	Linux (suse9.3 32bit)
Assigned to:	Gleb Shchepa	CPU Architecture:	Any
Tags:	crash, KILL

Description:
While performing routine QA on my application, I got a server crash.
Only new feature I added was ability to KILL QUERY <id> and KILL <id>
it's own queries, when requested.

Version: '5.0.48-debug' socket: '/tmp/mysql.sock' port: 3306 yes
070802 12:16:36 [ERROR] /home/sbester/server/5.0/mysql-5.0.48-linux-i686/bin/mysqld: Sort aborted
070802 12:20:57 [ERROR] /home/sbester/server/5.0/mysql-5.0.48-linux-i686/bin/mysqld: Sort aborted
070802 12:21:58 - mysqld got signal 11;
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=8388572
read_buffer_size=131072
max_used_connections=18
max_connections=100
threads_connected=14
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225788 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x8d26d30
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...
Cannot determine thread, fp=0xbd5fef58, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x80e27a3 handle_segfault + 529
0x846588d __pthread_sighandler + 173
0x81f7418 _Z25query_cache_end_of_resultP3THD + 638
0x8102695 _Z11mysql_parseP3THDPKcjPS2_ + 371
0x80f8651 _Z16dispatch_command19enum_server_commandP3THDPcj + 2007
0x80f7e6f _Z10do_commandP3THD + 529
0x80f6f5e handle_one_connection + 874
0x8460761 pthread_start_thread + 225
0x84c375a __clone + 106
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/using-stack-trace.html and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x8d5b158 = select * from `qa06` limit 10
thd->thread_id=20371
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

You are running a statically-linked LinuxThreads binary on an NPTL system.
This can result in crashes on some distributions due to LT/NPTL conflicts.
You should either build a dynamically-linked binary, or force LinuxThreads
to be used with the LD_ASSUME_KERNEL environment variable. Please consult
the documentation for your distribution on how to do that.
mysqld: my_new.cc:50: int __cxa_pure_virtual(): Assertion `"Pure virtual method called." == "Aborted"' failed.
Fatal signal 6 while backtracing
mysqld: my_new.cc:50: int __cxa_pure_virtual(): Assertion `"Pure virtual method called." == "Aborted"' failed.

Number of processes running now: 11
070802 12:21:58 mysqld restarted

How to repeat:
will upload testcase later.

Suggested fix:
.

oops, query cache is *enabled* !

I rebuilt with dynamically linking (due to the warning) and it still crashed.

sbester@www:~/server/5.0/mysql-5.0.48-linux-i686> file ./bin/mysqld
./bin/mysqld: ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), for GNU/Linux 2.2.5, dynamically linked (uses shared libs), stripped

Strangely, I haven't been able to repeat this crash on windows yet.

testcase

Attachment: bug30201.c (text/plain), 7.83 KiB.

repeated with statically and dynamically linked 5.0.48BK on suse 9.3 x86.
repeated with statically linked 5.0.46BK on suse 9.3 x86.
couldn't repeat with 5.0.44-enterprise-glib23 on suse 9.3 x86
couldn't repeat with any windows version.

if you cannot repeat the crash within 20 minutes, it might be something specific to my build/system.

I started mysqld_safe --skip-grant-tables --skip-name-resolve --back_log=100

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/32027

ChangeSet@1.2497, 2007-08-03 01:15:22+05:00, gshchepa@gleb.loc +1 -0
  Fixed bug #30201.
  Killing a SELECT query with KILL QUERY or KILL CONNECTION
  caused a server crash if the query cache is enabled.
  
  Normal evaluation of a query may be interrupted by the
  KILL QUERY/CONNECTION statement, in this case the mysql_execute_command
  function returns TRUE. However, result of this function was ignored,
  and the query_cache_end_of_result function was called with
  the incompletely initialized lex object value. That caused a crash.

It looks like the query is registered in the query cache, because the first IF in the query_cache_end_of_result() if following:

  if (thd->net.query_cache_query == 0)
    DBUG_VOID_RETURN;

so if you do nothing (no ending, no aborting query) it will mean memory leak.

I propose fallowing change:

         if (mysql_execute_command(thd))
           query_cache_abort(&thd->net);
         else
           query_cache_end_of_result(thd);

P.S.: sorry for unswering here, but I have not found the commit mail in my mailbox.

Gleb, please make sure this gets reviewed by Kristofer, he's been responsible for the QC bugs recently.

(Alerting Kristofer as well).

Please ignore last commit. Sorry.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/32739

ChangeSet@1.2498, 2007-08-20 17:02:24+05:00, gshchepa@gleb.loc +1 -0
  Fixed bug #30201.
  Killing a SELECT query with KILL QUERY or KILL CONNECTION
  caused a server crash if the query cache is enabled.
  
  Normal evaluation of a query may be interrupted by the
  KILL QUERY/CONNECTION statement, in this case the mysql_execute_command
  function returns TRUE. In this case the result of the query may
  be cached incompletely (omitting call to query_cache_insert inside
  the net_real_write function), and next call to query_cache_end_of_result
  may lead to server crash.
  Thus, the mysql_parse function has been modified to abort query
  cache in the case of query execution failure instead of
  the query_cache_end_of_result function call.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/32854

ChangeSet@1.2498, 2007-08-22 11:34:29+05:00, gshchepa@gleb.loc +1 -0
  Fixed bug #30201.
  Killing a SELECT query with KILL QUERY or KILL CONNECTION
  causes a server crash if the query cache is enabled.
  
  Normal evaluation of a query may be interrupted by the
  KILL QUERY/CONNECTION statement, in this case the mysql_execute_command
  function returns TRUE, and the thd->killed flag has true value.
  In this case the result of the query may
  be cached incompletely (omitting call to query_cache_insert inside
  the net_real_write function), and next call to query_cache_end_of_result
  may lead to server crash.
  Thus, the query_cache_end_of_result function has been modified to abort
  query cache in the case of killed thread.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/32858

ChangeSet@1.2498, 2007-08-22 14:17:35+05:00, gshchepa@gleb.loc +1 -0
  Fixed bug #30201.
  Killing a SELECT query with KILL QUERY or KILL CONNECTION
  causes a server crash if the query cache is enabled.
  
  Normal evaluation of a query may be interrupted by the
  KILL QUERY/CONNECTION statement, in this case the mysql_execute_command
  function returns TRUE, and the thd->killed flag has true value.
  In this case the result of the query may
  be cached incompletely (omitting call to query_cache_insert inside
  the net_real_write function), and next call to query_cache_end_of_result
  may lead to server crash.
  Thus, the query_cache_end_of_result function has been modified to abort
  query cache in the case of killed thread.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/32865

ChangeSet@1.2498, 2007-08-22 15:30:43+05:00, gshchepa@gleb.loc +1 -0
  Fixed bug #30201.
  Killing a SELECT query with KILL QUERY or KILL CONNECTION
  causes a server crash if the query cache is enabled.
  
  Normal evaluation of a query may be interrupted by the
  KILL QUERY/CONNECTION statement, in this case the mysql_execute_command
  function returns TRUE, and the thd->killed flag has true value.
  In this case the result of the query may
  be cached incompletely (omitting call to query_cache_insert inside
  the net_real_write function), and next call to query_cache_end_of_result
  may lead to server crash.
  Thus, the query_cache_end_of_result function has been modified to abort
  query cache in the case of killed thread.

Patch looks OK except following 2 places:

1) if (thd->killed) IMHO is less probabile then if (thd->net.query_cache_query == 0) => better change them (first thd->net.query_cache_query test then thd->killed).

2) Actually I can't imagine situation where it is possible to have query without any data returning (there should be at least OK sent to client) so I'd prefer to see comment when it is possible.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/32870

ChangeSet@1.2498, 2007-08-22 18:15:54+05:00, gshchepa@gleb.loc +1 -0
  Fixed bug #30201.
  Killing a SELECT query with KILL QUERY or KILL CONNECTION
  causes a server crash if the query cache is enabled.
  
  Normal evaluation of a query may be interrupted by the
  KILL QUERY/CONNECTION statement, in this case the mysql_execute_command
  function returns TRUE, and the thd->killed flag has true value.
  In this case the result of the query may
  be cached incompletely (omitting call to query_cache_insert inside
  the net_real_write function), and next call to query_cache_end_of_result
  may lead to server crash.
  Thus, the query_cache_end_of_result function has been modified to abort
  query cache in the case of killed thread.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/32991

ChangeSet@1.2573, 2007-08-24 00:12:58+05:00, gshchepa@gleb.loc +1 -0
  Fixed bug #30201.
  Recommit to 5.1.22.
  Killing a SELECT query with KILL QUERY or KILL CONNECTION
  causes a server crash if the query cache is enabled.
  
  Normal evaluation of a query may be interrupted by the
  KILL QUERY/CONNECTION statement, in this case the mysql_execute_command
  function returns TRUE, and the thd->killed flag has true value.
  In this case the result of the query may
  be cached incompletely (omitting call to query_cache_insert inside
  the net_real_write function), and next call to query_cache_end_of_result
  may lead to server crash.
  Thus, the query_cache_end_of_result function has been modified to abort
  query cache in the case of killed thread.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/32994

ChangeSet@1.2570, 2007-08-24 01:59:48+05:00, gshchepa@gleb.loc +1 -0
  Fixed bug #30201.
  Recommit to 5.1.22.
  Killing a SELECT query with KILL QUERY or KILL CONNECTION
  causes a server crash if the query cache is enabled.
  
  Normal evaluation of a query may be interrupted by the
  KILL QUERY/CONNECTION statement, in this case the mysql_execute_command
  function returns TRUE, and the thd->killed flag has true value.
  In this case the result of the query may
  be cached incompletely (omitting call to query_cache_insert inside
  the net_real_write function), and next call to query_cache_end_of_result
  may lead to server crash.
  Thus, the query_cache_end_of_result function has been modified to abort
  query cache in the case of killed thread.

Pushed into 5.1.22-beta

Pushed into 5.0.48

Noted in 5.0.48, 5.1.22 changelogs.

Using KILL QUERY or KILL CONNECTION to kill a SELECT statement caused
a server crash if the query cache was enabled.

Pushed into 5.1.23-beta