MySQL Bugs: #94577: Shell fails to output stored procedure results in loops

Bug #94577	Shell fails to output stored procedure results in loops
Submitted:	6 Mar 2019 14:23	Modified:	3 Jun 2019 10:13
Reporter:	Mario Beck	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Shell General / Core Client	Severity:	S3 (Non-critical)
Version:	8.0.15	OS:	Oracle Linux
Assigned to:		CPU Architecture:	Any
Tags:	Output, stored procedure

Description:
If I run a stored procedure that runs output in a loop shell displays only the first iteration, shows the first output again in second iteration and then returns. However the shell hangs because the procedure continues to run in the background.
So three issues:
#1 output is incomplete
#2 output is wrong (2nd iteration shows the first result again)
#3 shell is blocked until someone kills the procedure

How to repeat:
DELIMITER |
CREATE PROCEDURE shtest()
BEGIN
	WHILE 1=1 DO
		SELECT now();
		SELECT sleep(2);
	END WHILE;
END |
DELIMITER ;

CALL shtest();
+---------------------+
| now()               |
+---------------------+
| 2019-03-06 14:13:13 |
+---------------------+
1 row in set (0.0012 sec)
+---------------------+
| now()               |
+---------------------+
| 2019-03-06 14:13:13 |
+---------------------+
1 row in set (0.0012 sec)

 MySQL  127.0.0.1:3701 ssl  test  SQL > 
# See output. The time should have progressed. But it didn't. At this stage shell reads new statements from command line but does not execute. It hangs.

Suggested fix:
Behavior should be similar as in mysql CLI, which works as expected:
Output the current time every two seconds.

In addition it would be useful to have an option to kill the procedure by issuing ctrl-c. This does not work in mysql CLI today.

Hi,

Verified as described. Thanks for your report

All best
Bogdan

 MySQL  localhost  test  JS > \sql
Switching to SQL mode... Commands end with ;
Fetching table and column names from `test` for auto-completion... Press ^C to stop.

 MySQL  localhost  test  SQL > select * from t1;
+---+
| i |
+---+
| 1 |
| 1 |
| 1 |
+---+
3 rows in set (0.0004 sec)

 MySQL  localhost  test  SQL > DELIMITER |
 MySQL  localhost  test  SQL > CREATE PROCEDURE shtest()
                            -> BEGIN
                            -> WHILE 1=1 DO
                            -> SELECT now();
                            -> SELECT sleep(2);
                            -> END WHILE;
                            -> END |
Query OK, 0 rows affected (0.0042 sec)

 MySQL  localhost  test  SQL > DELIMITER ;
 MySQL  localhost  test  SQL > ;
ERROR: 1065 (42000): Query was empty

 MySQL  localhost  test  SQL > CALL shtest();
+---------------------+
| now()               |
+---------------------+
| 2019-03-07 04:30:12 |
+---------------------+
1 row in set (0.0005 sec)
+---------------------+
| now()               |
+---------------------+
| 2019-03-07 04:30:12 |
+---------------------+
1 row in set (0.0005 sec)

 MySQL  localhost  test  SQL > \q
Bye!
[arhimed@localhost bin]$ cd ~/sandboxes/msb_8_0_15/
[arhimed@localhost msb_8_0_15]$ ./use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 8.0.15 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql [localhost:8015] {msandbox} (test) > CALL shtest();
+---------------------+
| now()               |
+---------------------+
| 2019-03-07 04:30:38 |
+---------------------+
1 row in set (0.00 sec)

+----------+
| sleep(2) |
+----------+
|        0 |
+----------+
1 row in set (2.00 sec)

+---------------------+
| now()               |
+---------------------+
| 2019-03-07 04:30:40 |
+---------------------+
1 row in set (2.00 sec)

+----------+
| sleep(2) |
+----------+
|        0 |
+----------+
1 row in set (4.00 sec)

+---------------------+
| now()               |
+---------------------+
| 2019-03-07 04:30:42 |
+---------------------+
1 row in set (4.00 sec)

+----------+
| sleep(2) |
+----------+
|        0 |
+----------+
1 row in set (6.00 sec)

+---------------------+
| now()               |
+---------------------+
| 2019-03-07 04:30:44 |
+---------------------+
1 row in set (6.00 sec)

Posted by developer:
 
Changelog entry added for MySQL Shell 8.0.17:

MySQL Shell would hang when attempting to handle output from a stored procedure that produced results repeatedly from a single statement. The issues have now been corrected.