MySQL Bugs: #101001: mysqlsh does not correctly parse create procedure statement in sql context

Bug #101001	mysqlsh does not correctly parse create procedure statement in sql context
Submitted:	29 Sep 2020 19:56	Modified:	30 Sep 2020 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:	Windows (10)
Assigned to:		CPU Architecture:	Any
Tags:	MySQL Server Shell, stored procedures

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.

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.

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.

Thank you Mr. McLoughlin and Alfredo,

This bug report is closed now.

Not a bug.