MySQL Bugs: #28211: RENAME DATABASE and query cache don't play nicely together

Bug #28211	RENAME DATABASE and query cache don't play nicely together
Submitted:	3 May 2007 2:05	Modified:	23 Jun 2007 8:23
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S1 (Critical)
Version:	5.1.x, 5.1BK	OS:	Any
Assigned to:	Kristofer Pettersson	CPU Architecture:	Any
Tags:	DoS, hang, query cache

Description:
Under some circumstances RENAME SCHEMA x TO y will cause entire server to hang, with 100% cpu usage.

Tested on linux and windows with same results.

How to repeat:
==run the server==
mysqld_safe --query_cache_size=15M --skip-grant-tables &
or
mysqld-nt --console --query_cache_size=15M --skip-grant-tables

now, run this from shell/mysql command line client.
follow it exactly.

mysql -uroot
drop database if exists db1;
drop database if exists db2;
create database db1;
use db1;
create table t1(c1 int)engine=myisam;
insert into t1(c1) values (1);
use mysql;
select * from db1.t1 f;
rename schema db1 to db2; # hangs here!
exit

Suggested fix:
.

It's looping in the do/while loop of the query cache invalidate code:

mysqld.exe!Query_cache::invalidate
mysqld.exe!mysql_rm_db
mysqld.exe!mysql_rename_db
mysqld.exe!mysql_execute_command
mysqld.exe!mysql_parse
mysqld.exe!dispatch_command
mysqld.exe!do_command
mysqld.exe!handle_one_connection
mysqld.exe!pthread_start
mysqld.exe!_callthreadstart
mysqld.exe!_threadstart

do
{
  next= curr->next;
  if (strcmp(db, (char*)(curr->table()->db())) == 0)
   invalidate_table(curr);
 /*
   invalidate_table can freed block on which point 'next' (if
   table of this block used only in queries which was deleted
   by invalidate_table). As far as we do not allocate new blocks
   and mark all headers of freed blocks as 'FREE' (even if they are
   merged with other blocks) we can just test type of block
   to be sure that block is not deleted
 */
   if (next->type == Query_cache_block::FREE)
     goto restart_search;
   curr= next;
} while (curr != tables_blocks);

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/28500

ChangeSet@1.2529, 2007-06-11 17:16:15+02:00, thek@adventure.(none) +4 -0
  Bug#28211 RENAME DATABASE and query cache don't play nicely together
  When all table blocks were removed from the query cache the client session
  hung in a tight loop waiting on an impossible condition while consuming a lot
  of CPU.
  
  This patch also corrects an error which caused valid tables to sometimes be
  removed from the query cache.

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/29010

ChangeSet@1.2529, 2007-06-18 17:46:29+02:00, thek@adventure.(none) +3 -0
  Bug#28211 RENAME DATABASE and query cache don't play nicely together
  When all table blocks were removed from the query cache the client session
  hung in a tight loop waiting on an impossible condition while consuming a lot
  of CPU.
  
  This patch also corrects an error which caused valid tables to sometimes be
  removed from the query cache.

Pushed into 5.1.20-beta

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Bugfix documented in 5.1.20 changelog as follows:

          When the query cache was fully used, issuing <literal>RENAME
          DATABASE</literal> or <literal>RENAME SCHEMA</literal> could
          cause the server to hang, with 100% CPU usage.