Bug #102481 EXPLAIN FOR CONNECTION not good
Submitted: 4 Feb 15:37 Modified: 5 Feb 13:04
Reporter: HULONG CUI Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.23 OS:Any
Assigned to: CPU Architecture:Any

[4 Feb 15:37] HULONG CUI
Description:
#session1: create table and insert data
CREATE TABLE `t_temp` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  key (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#########################################################################################################
delimiter //
DROP PROCEDURE IF EXISTS proc_batch_insert;
CREATE PROCEDURE proc_batch_insert()
BEGIN
DECLARE pre_name BIGINT;
DECLARE ageVal INT;
DECLARE i INT;
SET pre_name=139;
SET ageVal=100;
SET i=1;
WHILE i <= 1000000 DO
        INSERT INTO t_temp(`name`,age,create_time,update_time) VALUES(CONCAT(pre_name,'@qq.com'),(ageVal+1)*rand()%30,NOW(),NOW());
SET pre_name=pre_name+100;
SET i=i+1;
END WHILE;
END //

delimiter ;
call proc_batch_insert();

##session2
mysql> show processlist;
+----+-----------------+-----------+-----------+---------+------+----------------------------+------------------------------------------------------------------------------------------------------+
| Id | User            | Host      | db        | Command | Time | State                      | Info                                                                                                 |
+----+-----------------+-----------+-----------+---------+------+----------------------------+------------------------------------------------------------------------------------------------------+
|  7 | event_scheduler | localhost | NULL      | Daemon  |  534 | Waiting on empty queue     | NULL                                                                                                 |
| 10 | root            | localhost | NULL      | Query   |    0 | init                       | show processlist                                                                                     |
| 15 | root            | localhost | employees | Query   |    0 | waiting for handler commit | INSERT INTO t_temp(`name`,age,create_time,update_time) VALUES(CONCAT(pre_name,'@qq.com'),(ageVal+1)* |
+----+-----------------+-----------+-----------+---------+------+----------------------------+------------------------------------------------------------------------------------------------------+
#connection id 15 is insert data ,but i explain for connection not have result
mysql> EXPLAIN FOR CONNECTION 15;
Query OK, 0 rows affected (0.00 sec)

I  think this feature not works well。

How to repeat:
Description  have repeat 。thanks
[5 Feb 13:04] MySQL Verification Team
HI Mr. jacky,

Thank you for your bug report.

I have run the EXPLAIN for the INSERT itself and got a great output. I have also run the EXPLAIN for the connection and got the same output as you, which is a result with not rows.

Hence, I have repeated the behaviour. Thank you for your contribution.

Verified as reported.
[5 Feb 14:39] MySQL Verification Team
Correct category is set.