MySQL Bugs: #63173: MySql hangs in state statistics - only with specific amount of data-rows

Bug #63173	MySql hangs in state statistics - only with specific amount of data-rows
Submitted:	9 Nov 2011 21:15	Modified:	13 Dec 2011 19:43
Reporter:	t p	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	5.1, 5.5	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	hangs, statistics state select

Description:
We discovered a really strange bug where a select-statement ends in an infinite loop.
The statement hangs depending on the amount of rows in a table - not depending on the result itself. 
I'm not sure if it's the same bug as #20932.

We use multiple joins in the select query (auto-generated by system), which looks like this:

SELECT task.id FROM `time` time,`task` task,`task` column_32_task,`task` column_33_task,`task` column_34_task,`task` column_36_task,`user` column_43_user,`task` column_44_task,`user` column_45_user,`task` column_46_task,`user` column_62_user,`user` column_63_user,`user` column_64_user,`user` column_65_user,`user` column_66_user,`user` column_67_user,`task` column_68_task,`task` column_69_task,`task` column_70_task,`task` column_71_task,`task` column_72_task,`task` column_73_task WHERE `column_32_task`.`id` = `time`.`task_id` AND `column_33_task`.`id` = `time`.`task_id` AND `column_34_task`.`id` = `time`.`task_id` AND `column_36_task`.`id` = `time`.`task_id` AND `column_43_user`.`administrators_id` = `time`.`user_id` AND `column_44_task`.`id` = `time`.`task_id` AND `column_45_user`.`administrators_id` = `time`.`user_id` AND `column_46_task`.`id` = `time`.`task_id` AND `column_62_user`.`administrators_id` = `time`.`user_id` AND `column_63_user`.`administrators_id` = `time`.`user_id` AND `column_64_user`.`administrators_id` = `time`.`user_id` AND `column_65_user`.`administrators_id` = `time`.`user_id` AND `column_66_user`.`administrators_id` = `time`.`user_id` AND `column_67_user`.`administrators_id` = `time`.`user_id` AND `column_68_task`.`id` = `time`.`task_id` AND `column_69_task`.`id` = `time`.`task_id` AND `column_70_task`.`id` = `time`.`task_id` AND `column_71_task`.`id` = `time`.`task_id` AND `column_72_task`.`id` = `time`.`task_id` AND `column_73_task`.`id` = `time`.`task_id` AND  `task`.`id` = `time`.`task_id` AND `time`.`status` != 1     AND `time`.`start_time` BETWEEN '2011-01-01 00:00:00' AND '2011-10-31 23:59:59'    ORDER BY `time`.`start_time` DESC LIMIT 0, 1;

The 3 create-table-statements (inkl. data) can be found here:
http://dl.dropbox.com/u/236273/bug_mysql.sql

If you start it right away it will hand - if you insert 3 (!) new lines in the table "user" it will finish immediately.
I tried with mysql-server 5.1.x as well as 5.5.x

Thanks for any advice!

How to repeat:
Just run the queries and change number of rows in table "user" to see the problem.

Side note: I do not see "user" table accessed in your query.

Please, send the output of:

show variables like 'optimizer%';

from the environments affected.

hi!

the select-statement joins the user-table multiple times:

`user` column_62_user,`user`
column_63_user,`user` column_64_user,`user` column_65_user,`user` column_66_user,`user`
column_67_user,

here is the output for show variables like 'optimizer%':
optimizer_prune_level	1
optimizer_search_depth	62
optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on

thanks for any help!

Please, specify exact version, 5.1.x and 5.5.y, affected. Then, please, try to execute these queries after:

set session optimizer_search_depth=0;

See bug #41740 for reference.

hi again!

server versions:
System 1:
version	5.0.67
version_comment	Source distribution
version_compile_machine	i686
version_compile_os	apple-darwin8.11.1

System 2:
version	5.1.49-3-log
version_comment	(Debian)
version_compile_machine	x86_64
version_compile_os	debian-linux-gnu

System 3:
version                 | 5.5.17-log                  
version_comment         | MySQL Community Server (GPL)
version_compile_machine | x86_64                      
version_compile_os      | linux2.6   

after setting the optimizer_search_depth to 0 the query executes (after some time).
is it recommended to keep it set to 0 (for "auto" as i understand)?

So, we have a workaround... 

Yes, value 0 is much better than 62 in general. Or set it to N where N is equal to maximum number of tables joined plus 1.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".