[jira] [Updated] (NETBEANS-2273) Default mysql delimiter when editing stored procedures breaks procedures

["Brian Dayhoff (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/NETBEANS-2273?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Brian Dayhoff updated NETBEANS-2273:
------------------------------------
    Description: 
Summary:

The default delimiter used when selecting `edit` on a stored procedure is `@@`. This is unsafe, because this string is used natively in mysql to select session variables (and occasionally global variables). Any stored procedure that works with session variables using the typical shorthand `select @@session.varname` breaks when re-inserting the stored procedure as is, because netbeans inserts `DELIMITER @@` at the beginning automatically, and further instances of `@@` as delimiters to close the stored procedure. This should be changed to `$$`, which has no native sql functionality.

 

Steps to reproduce:

 

Create a simple stored procedure that references a session value:

```

DELIMITER $$

DROP PROCEDURE IF EXISTS test $$

CREATE PROCEDURE test ()

proc:begin

SELECT @@session.group_concat_max_len;

END $$

DELIMITER ;

```

 

Run it:

```

CALL test(); – Runs clean

```

 

Select `edit` from the dropdown menu in services referencing the mysql database:

services > Databases > test_database > Procedures > test > edit

 

Execute the generated stored procedure text as is, replacing the original (which should retain exact functionality)

 

Run the provided stored procedure without editing anything.

 

Run the procedure again:

```

CALL test(); – broken.

```

  was:
Summary:

The default delimiter used when selecting `edit` on a stored procedure is `@@`. This is unsafe, because this string is used natively in mysql to select session variables (and occasionally global variables). Any stored procedure that works with session variables using the typical shorthand `select @@session.varname` breaks when re-inserting the stored procedure as is, because netbeans inserts `DELIMITER @@` at the beginning automatically, and further instances of `@@` as delimiters to close the stored procedure. This should be changed to `$$`, which has no native sql functionality.

 

Steps to reproduce:

 

Create a simple stored procedure that references a session value:

```

DELIMITER $$

DROP PROCEDURE IF EXISTS test $$

CREATE PROCEDURE test ()

proc:begin

SELECT @@session.group_concat_max_len;

END $$

DELIMITER ;

```

 

Run it:

```

CALL test(); – Runs clean

```

 

Select `edit` from the dropdown menu in services referencing the mysql database:

services > Databases > test_database > Procedures > test > edit

 

Run the provided stored procedure without editing anything.

 

Run the procedure again:

```

CALL test(); – broken.

```


> Default mysql delimiter when editing stored procedures breaks procedures
> ------------------------------------------------------------------------
>
>                 Key: NETBEANS-2273
>                 URL: https://issues.apache.org/jira/browse/NETBEANS-2273
>             Project: NetBeans
>          Issue Type: Bug
>          Components: db - MySQL
>    Affects Versions: 8.2, 9.0, Next
>         Environment: Any version of netbeans with any version of mysql
>            Reporter: Brian Dayhoff
>            Priority: Minor
>
> Summary:
> The default delimiter used when selecting `edit` on a stored procedure is `@@`. This is unsafe, because this string is used natively in mysql to select session variables (and occasionally global variables). Any stored procedure that works with session variables using the typical shorthand `select @@session.varname` breaks when re-inserting the stored procedure as is, because netbeans inserts `DELIMITER @@` at the beginning automatically, and further instances of `@@` as delimiters to close the stored procedure. This should be changed to `$$`, which has no native sql functionality.
>  
> Steps to reproduce:
>  
> Create a simple stored procedure that references a session value:
> ```
> DELIMITER $$
> DROP PROCEDURE IF EXISTS test $$
> CREATE PROCEDURE test ()
> proc:begin
> SELECT @@session.group_concat_max_len;
> END $$
> DELIMITER ;
> ```
>  
> Run it:
> ```
> CALL test(); – Runs clean
> ```
>  
> Select `edit` from the dropdown menu in services referencing the mysql database:
> services > Databases > test_database > Procedures > test > edit
>  
> Execute the generated stored procedure text as is, replacing the original (which should retain exact functionality)
>  
> Run the provided stored procedure without editing anything.
>  
> Run the procedure again:
> ```
> CALL test(); – broken.
> ```



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@netbeans.apache.org
For additional commands, e-mail: commits-help@netbeans.apache.org

For further information about the NetBeans mailing lists, visit:
https://cwiki.apache.org/confluence/display/NETBEANS/Mailing+lists