Bug #68752 Long query in "statistics" phase cannot be killed, prevents db shutdown too

• Submitted: 22 Mar 2013 21:28
• Modified: 21 Jul 2014 20:00
• Reporter: Franjo Markovic
• Status: Can't repeat
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.6.10
• OS: Any
• CPU Architecture: Any

[22 Mar 2013 21:28] Franjo Markovic

Description:
I had a case where a large query was sitting in statistics mode for 15+ hours! And could do nothing but force-kill database server.

How to repeat:
Here is very simplified sample that stays for 10+ minutes in statistics phase. Adjust the numbers and you can get it stay as long as you want.

delimiter $$

CREATE TABLE `a2` (
  `k1` int(11) NOT NULL,
  `k2` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`k1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
$$
                    
CREATE PROCEDURE test_a2 ()
BEGIN
    declare i int;            
    
    set i = 1000;      
    while (i < 200000) do
        set i = i + 1;             
		insert into a2 (k1) values (i);
    end while;
END $$

CREATE PROCEDURE test_a3 ()
BEGIN
    declare i int;   

    set @sql1 = "select k1,k2 from a2 where k1 in (0";
         
    set i = 500;      
    while (i < 190000) do
        set i = i + 1;             
        set @sql1 = concat(@sql1, ",", i);
    end while;
	
    set @sql1 = concat(@sql1, ")");

    PREPARE stmt FROM @sql1;
    EXECUTE stmt;
    
    DEALLOCATE PREPARE stmt;
END $$

call test_a2() $$

call test_a3() $$

      

Suggested fix:
Abort the statistics on kill.

[1 Apr 2013 16:47] MySQL Verification Team

The fact this query takes so long on 5.6.10 is due to:
http://bugs.mysql.com/bug.php?id=68046

Tested 5.6.12:
189000 rows in set (1 min 6.78 sec)
Query OK, 0 rows affected (1 min 50.34 sec)

Tested 5.6.10:
189000 rows in set (13 min 28.79 sec)
Query OK, 0 rows affected (14 min 6.58 sec)

[1 Apr 2013 16:50] MySQL Verification Team

I should note that the 1 min+ is spent concatting the string, not for the actual query itself!  So, I'm setting this as duplicate.

[3 Apr 2013 0:15] Franjo Markovic

This is not about "in" behavior and optimization. There are other cases where "statistics" phase takes long in very complex queries.
This bug is about non-responsiveness of "statistics" phase. Why should it not be stoppable?

[23 Apr 2013 20:41] MySQL Verification Team

Please try 5.6.11. Thanks.

[2 May 2013 18:17] Franjo Markovic

5.6.11 has fixed "in" behavior, making my test case not appropriate any more.

[21 Jul 2014 20:00] Sveta Smirnova

Thank you for the feedback.

Closed as "Can't repeat" since the issue is not repeatable anymore.