Bug #90257 Alter procedure with cte
Submitted: 29 Mar 2018 20:00 Modified: 3 Apr 2018 23:52
Reporter: Tammy McClellan Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Workbench Severity:S3 (Non-critical)
Version:6.3.0, 6.3.10 OS:Microsoft Windows
Assigned to: CPU Architecture:Any

[29 Mar 2018 20:00] Tammy McClellan
Description:
When parsing a stored procedure that uses a cte, I receive the error "Syntax error: missing 'end'. When right clicking on the stored proc and choose Alter procedure, the message, "There was an error parsing the DDL retrieved from the server."

I am able to initially create the stored proc, but not alter it.

How to repeat:
Create a stored proc with a cte and try and either parse it, or alter it.
[29 Mar 2018 20:48] Miguel Solorzano
Thank you for the bug report. Are you using 5.3.10? try it otherwise. If the issue continues provide a procedure test case script. Thanks.
[2 Apr 2018 15:16] Tammy McClellan
I'm using 8.0.3-rc. I've attached a copy of the script for your review. I can create it no problem, but cannot use call or edit procedure immediately after creating. Other stored procs without a CTE works as expected. 

Thank you!
[3 Apr 2018 8:35] Mike Lischke
This should be handled properly by the upcoming Workench 8 release.
[3 Apr 2018 10:17] Umesh Shastry
Thank you for the report.
As Mike mentioned this would be handled properly by the upcoming Workench 8 release.  Verifying for now.

######## create routine from CLI

[umshastr@hod03]/export/umesh/server/binaries/Trunk/mysql-commercial-8.0.12-tr-linux-x86_64: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.12-tr-commercial MySQL Enterprise Server - Commercial

Copyright (c) 2000, 2018, 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> CREATE USER 'ushastry'@'%' IDENTIFIED WITH mysql_native_password BY '123';
Query OK, 0 rows affected (0.08 sec)

mysql> GRANT ALL ON *.* TO 'ushastry'@'%';
Query OK, 0 rows affected (0.10 sec)

mysql> create database test;
Query OK, 1 row affected (0.03 sec)

mysql> use test
Database changed
mysql> delimiter $$
mysql>
mysql> CREATE DEFINER=`ushastry`@`%` PROCEDURE `new_procedure`()
    -> BEGIN
    ->
    -> WITH RECURSIVE my_cte AS
    -> (
    ->   SELECT 1 as f, 1 as next_f
    ->   UNION ALL
    ->   SELECT next_f, f+next_f FROM my_cte WHERE f < 500
    -> )
    -> SELECT * FROM my_cte;
    -> END$$
Query OK, 0 rows affected (0.07 sec)

From WB, try to alter

There was an error while parsing the DDL retrieved from the server.
Do you want to view the DDL or cancel processing it?

Thanks,
Umesh
[3 Apr 2018 23:52] Tammy McClellan
Thank you - Is there a timeline for releasing the new version of Workbench?