Bug #77144 prepared statement explain delete .. with strict mode violation flatlines cpu
Submitted: 24 May 2015 13:04 Modified: 14 Jul 2015 13:01
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.7.7 OS:Any
Assigned to: CPU Architecture:Any

[24 May 2015 13:04] Shane Bester 
Description:
Affects debug, release versions of 5.7, 5.8.  5.6 was not affected.

mysql> prepare s from "explain delete from `t1` where `a` || 'a' limit 1";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> execute s;
ERROR 1292 (22007): Truncated incorrect INTEGER value: 'a'
mysql> execute s; #hangs

connection2:

mysql> show processlist\G
*************************** 1. row ***************************
     Id: 2
   User: root
   Host: localhost
     db: test
Command: Query
   Time: 56
  State: System lock
   Info: explain delete from `t1` where `a` || 'a' limit 1
*************************** 2. row ***************************

How to repeat:
drop table if exists t1;
create table t1(a int)engine=innodb;
set sql_mode='strict_all_tables';
prepare s from "explain delete from `t1` where `a` || 'a' limit 1";
execute s;
execute s; #hangs
[24 May 2015 13:05] MySQL Verification Team 
the call stack while hanging (looping) is:
 mysqld!my_realloc+0x78 [.\mysys\my_malloc.c @ 101]
 mysqld!String::mem_realloc+0x72 [.\sql-common\sql_string.cc @ 114]
 mysqld!String::append+0xab [.\sql-common\sql_string.cc @ 483]
 mysqld!Explain_format_traditional::flush_entry+0x231 [.\sql\opt_explain_traditional.cc @ 201]
 mysqld!Explain::shallow_explain+0x1c [.\sql\opt_explain.cc @ 475]
 mysqld!Explain_table::shallow_explain+0x99 [.\sql\opt_explain.cc @ 1762]
 mysqld!Explain::send+0x34 [.\sql\opt_explain.cc @ 665]
 mysqld!explain_single_table_modification+0x1d5 [.\sql\opt_explain.cc @ 2004]
 mysqld!Sql_cmd_delete::mysql_delete+0x823 [.\sql\sql_delete.cc @ 349]
 mysqld!Sql_cmd_delete::execute+0xc9 [.\sql\sql_delete.cc @ 1338]
 mysqld!mysql_execute_command+0x1525 [.\sql\sql_parse.cc @ 3177]
 mysqld!Prepared_statement::execute+0x28c [.\sql\sql_prepare.cc @ 3957]
 mysqld!Prepared_statement::execute_loop+0x107 [.\sql\sql_prepare.cc @ 3567]
 mysqld!mysql_sql_stmt_execute+0xe9 [.\sql\sql_prepare.cc @ 2642]
 mysqld!mysql_execute_command+0x698 [.\sql\sql_parse.cc @ 2438]
 mysqld!mysql_parse+0x2b9 [.\sql\sql_parse.cc @ 5167]
 mysqld!dispatch_command+0x7a7 [.\sql\sql_parse.cc @ 1246]
 mysqld!do_command+0x1e7 [.\sql\sql_parse.cc @ 816]
 mysqld!handle_connection+0x128 [.\sql\conn_handler\connection_handler_per_thread.cc @ 300]
 mysqld!pfs_spawn_thread+0xf3 [.\storage\perfschema\pfs.cc @ 2150]
 mysqld!win_thread_start+0x1b [.\mysys\my_thread.c @ 38]
 mysqld!_callthreadstartex+0x17 [f:\dd\vctools\crt\crtw32\startup\threadex.c @ 376]
 mysqld!_threadstartex+0x102 [f:\dd\vctools\crt\crtw32\startup\threadex.c @ 354]
[14 Jul 2015 13:01] Paul DuBois 
Noted in 5.7.8, 5.8.0 changelogs.

Executing a prepared EXPLAIN statement could cause the server to
hang.