MacLochlainns Weblog

I’ve been experimenting with the mysqlsh since installing it last week. It’s been interesting. Overall, I’m totally impressed but I did find a problem with how it parses stored procedures.

First thought is always, is it my code? I checked the file by running it as a script file through MySQL Workbench. It ran perfectly in MySQL Workbench but failed repeatedly when run from the mysqlsh utility. Next step, reduce the code to a small test case, retest it, and log a bug if it is replicated. My test case in a test.sql file generates the following errors when run from the mysqlsh utility:

 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 same file generates this series of successful messages when run as a script file from MySQL Workbench:

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

For those who are curious enough to review the test case, here it is:

-- 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 CONCAT(pv_input1,', ',pv_input2) AS message;
END;
$$
 
-- Reset the standard delimiter to let the semicolon work as an execution command.
DELIMITER ;
 
-- Call the test procedure.
CALL test('One','Two');

The reply in the bug explained the behavior difference between MySQL Workbench and the MySQL Shell (mysqlsh) environments. MySQL Workbench uses the MySQL client, which supports multiple client statements with the CLIENT_MULTI_STATEMENTS option. Recognizing that, the logging entry SELECT statement should move to a position before setting the DELIMITER, like:

-- Set a label for the log file.
SELECT 'CREATE PROCEDURE test' AS "Statement";
 
-- Reset the delimiter so that a semicolon can be used as a statement and block terminator.
DELIMITER $$
 
CREATE PROCEDURE test
( pv_input1  CHAR(12)
, pv_input2  CHAR(19)) MODIFIES SQL DATA
BEGIN
  SELECT CONCAT(pv_input1,', ',pv_input2) AS message;
END;
$$
 
-- Reset the standard delimiter to let the semicolon work as an execution command.
DELIMITER ;
 
-- Call the test procedure.
CALL test('One','Two');

The new test case only submits one statement at a time. The logging query is submitted by the semicolon, and the test procedure by the double dollar ($$) symbol set.

So, I was correct identifying a parsing behavior difference between MySQL Workbench and MySQL Shell. It appears to be a difference by design but the MySQL Shell documentation fails to explain it can’t manage multiple statements. I hope identifying this saves others time.

It’s also true that the MySQL client software supports TEE and NOTEE to write log files. Unfortunately, MySQL Shell (mysqlsh) doesn’t support the TEE and NOTEE syntax. You can only do minimal logging with the control of standard error (stderr) by using the application and AdminAPI log utilities, which are covered in Chapter 8 of the MySQL Shell 8.0 documentation.