Bug #101001 mysqlsh does not correctly parse create procedure statement in sql context
Submitted: 29 Sep 19:56 Modified: 30 Sep 12:14
Reporter: Michael McLaughlin Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Document Store: MySQL Shell Severity:S3 (Non-critical)
Version:8.0.21 OS:Microsoft Windows (10)
Assigned to: CPU Architecture:Any
Tags: MySQL Server Shell, stored procedures

[29 Sep 19:56] Michael McLaughlin
Description:
The MySQL Server Shell in SQL Context fails to parse a CREATE PROCEDURE statement, while MySQL Workbench correctly parses the same file correctly. An attempt to create a procedure in mysqlsh \sql context fails with the following error message:

 MySQL  localhost:33060+ ssl  studentdb  SQL > source test.sql
Query OK, 0 rows affected (0.0003 sec)
ERROR: 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE PROCEDURE test
( pv_input1  CHAR(12)
, pv_input2  CHAR(19)) MODIFIES SQL ' at line 2
Query OK, 0 rows affected (0.0002 sec)
Query OK, 0 rows affected (0.0003 sec)
ERROR: 1305: PROCEDURE studentdb.test does not exist

The sample code provided in the "How to repeat" works fine when run as a script from the MySQL Workbench utility, returning:

Preparing...
Importing test.sql...
Finished executing script
Statement
CREATE PROCEDURE test
pv_input1
One
Operation completed successfully

How to repeat:
The following file fails in mysqlsh but succeed when run as a file from MySQL Workbench:

-- Reset the delimiter so that a semicolon can be used as a statement and block terminator.
DELIMITER $$

SELECT 'CREATE PROCEDURE test' AS "Statement";
CREATE PROCEDURE test
( pv_input1  CHAR(12)
, pv_input2  CHAR(19)) MODIFIES SQL DATA

BEGIN

  SELECT pv_input1;
  
END;
$$

-- Reset the standard delimiter to let the semicolon work as an execution command.
DELIMITER ;

-- Call the test procedure.
CALL test('One','Two');

Suggested fix:
It appears to be a parsing error generated by the mysqlsh code base.
[29 Sep 22:32] Alfredo Kojima
In your script, the SELECT and the CREATE PROCEDURE end up in the same SQL statement because the SELECT is terminated with ; when the delimiter was changed to $$. If you move the SELECT to before the DELIMITER directive or change the delimiter in the SELECT to $$ it should work fine:

DELIMITER $$

SELECT 'CREATE PROCEDURE test' AS "Statement"$$
CREATE PROCEDURE test
( pv_input1  CHAR(12)
, pv_input2  CHAR(19)) MODIFIES SQL DATA

BEGIN

  SELECT pv_input1;
  
END;
$$

The reason this works with the mysql client is that it enables CLIENT_MULTI_STATEMENTS while mysqlsh doesn't in order to minimize potential damage from SQL injection attempts in user scripts.
[29 Sep 23:05] Michael McLaughlin
THANKS Alfredo for the explaining the difference. I checked the MySQL Shell manual and didn't find a reference to that. Maybe there should be a note added to this document to explain that it doesn't support CLIENT_MULTI_STATEMENTS.

https://docs.oracle.com/cd/E17952_01/mysql-shell-8.0-en/mysql-shell-8.0-en.pdf

That fixed my problem.
[30 Sep 12:14] MySQL Verification Team
Thank you Mr. McLoughlin and Alfredo,

This bug report is closed now.

Not a bug.