MySQL Bugs: #55006: Routines Editor Adds Extra Line Breaks

Bug #55006	Routines Editor Adds Extra Line Breaks
Submitted:	5 Jul 2010 17:04	Modified:	6 Feb 2012 19:41
Reporter:	d m	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	5.2.25 CE rev6303, 5.2.29	OS:	Windows (XP Pro SP3)
Assigned to:		CPU Architecture:	Any
Tags:	Routines Editor

Description:
Hi,

When viewing a stored routine / function Workbench adds extra linebreaks not visible in original code.

This makes readability worse.

This is not the case when viewing the same routines in MySQL Query Browser (v1.2.13), nor HeidiSQL (v5.2.0.3441).

Original routines written in MySQL Query Browser and/or HeidiSQL.

This has been the case in the last few iterations of Workbench.
 
I attach screenshots to illustrate.

Thank you for your attention,

David Marten

How to repeat:
1. In Workbench, open a connection to server
2. In SQL Editor tab select db from 'Default' combobox
3. Expand  'Routines' folder from server in Treeview
4. Right-click desired routine and choose 'Alter routine ...'
5. Routine editor opens and displays routine with additional line breaks between each line.
6. Open alternative client.
7. Open same SP/SF for editing
8. Routine appears as expected

Reproducible: always

Suggested fix:
Amend code that is used to display SP/SF's! Or tell me if I have a kooky setting that is causing this!

Routine Editor output in MySQL Workbench 5.2.25 RC

Attachment: 20100705_MySQL_Workbench_5.2.24_CE_RC_SP.png (image/png, text), 24.93 KiB.

Routine Editor output in MySQL Query Browser 1.2.13

Attachment: 20100705_MySQL_Query_Browser_SP.png (image/png, text), 79.61 KiB.

Routine Editor output in HeidiSQL 5.1.0.3441

Attachment: 20100705_HeidiSQL_5.1.0.3441_SP.png (image/png, text), 54.68 KiB.

just making sure I wrote correct version of Workbench

First attached image of WB is version 5.2.24 but it is the same in 5.2.25

I do not see this on Mac, so it can be Windows-specific problem related to end of line characters used.

All is fine on Linux too. This seems to be a Windows only problem.

Maybe XP only problem.

Could you please provide a model file project which presents the issue reported?. Thanks in advance.

Bug #55006 - Demo Workbench Model

Attachment: Bug_55006_Demo.mwb (application/octet-stream, text), 5.32 KiB.

I have attached a WB model to demonstrate.

Model contains 1 table and 3 routines.

I created one routine in WB, one in Query Browser and one in HeidiSQL.

When you view the routine created in WB in the Routines Editor it shows as it was originally typed.

When viewing the routines created in QB/Heidi the extra lines are added.

To create the routines I just copied and pasted the same DDL into each client.

Interestingly, when creating a table using the table editor (right-click on Tables folder in Schema treeview and choose 'Create table ...') and I clicked to apply the changes, I copied the DDL from the Review window that pops up before final Apply. I pasted it into the SQL Query editor and it inserted extra line breaks there too.

Hope this all helps, keep up the good work!

Cheers,

d

Thank you for the feedback.

Please connect to MySQL server via MySQL command line client and provide us output of query

select body from mysql.proc where db='YOUR_DATABASE_NAME' and name='YOUR_ROUTINE_NAME';

Command line output follows:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5722
Server version: 5.1.41-community-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select body from mysql.proc where db = 'test' and name in ('test_routine_
HeidiSQL', 'test_routine_QUERY_BROWSER', 'test_routine_WORKBENCH')\G
*************************** 1. row ***************************
body: BEGIN

    SELECT t.TestID, t.TestNumber, t.TestString
    FROM tbl_test t
    WHERE t.TestNumber = Param1
      AND t.TestString = Param2;

END
*************************** 2. row ***************************
body: BEGIN

    SELECT t.TestID, t.TestNumber, t.TestString
    FROM tbl_test t
    WHERE t.TestNumber = Param1
      AND t.TestString = Param2;

END
*************************** 3. row ***************************
body: BEGIN

    SELECT t.TestID, t.TestNumber, t.TestString
    FROM tbl_test t
    WHERE t.TestNumber = Param1
      AND t.TestString = Param2;

END
3 rows in set (0.02 sec)

mysql>

Please provide hex code of the strings here.

Apologies for delay in responding to your request - I've been on holiday.

OK, output via command line client on server:

Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 371
Server version: 5.1.41-community-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select name, body, hex(body) from mysql.proc where db = 'test' and name i
n ('test_routine_HeidiSQL', 'test_routine_QUERY_BROWSER', 'test_routine_WORKBENC
H')\G
*************************** 1. row ***************************
     name: test_routine_HeidiSQL
     body: BEGIN

    SELECT t.TestID, t.TestNumber, t.TestString
    FROM tbl_test t
    WHERE t.TestNumber = Param1
      AND t.TestString = Param2;

END
hex(body): 424547494E0D0A0D0A2020202053454C45435420742E5465737449442C20742E54657
3744E756D6265722C20742E54657374537472696E670D0A2020202046524F4D2074626C5F7465737
420740D0A20202020574845524520742E546573744E756D626572203D20506172616D310D0A20202
0202020414E4420742E54657374537472696E67203D20506172616D323B0D0A0D0A454E44
*************************** 2. row ***************************
     name: test_routine_QUERY_BROWSER
     body: BEGIN

    SELECT t.TestID, t.TestNumber, t.TestString
    FROM tbl_test t
    WHERE t.TestNumber = Param1
      AND t.TestString = Param2;

END
hex(body): 424547494E0D0A0D0A2020202053454C45435420742E5465737449442C20742E54657
3744E756D6265722C20742E54657374537472696E670D0A2020202046524F4D2074626C5F7465737
420740D0A20202020574845524520742E546573744E756D626572203D20506172616D310D0A20202
0202020414E4420742E54657374537472696E67203D20506172616D323B0D0A0D0A454E44
*************************** 3. row ***************************
     name: test_routine_WORKBENCH
     body: BEGIN

    SELECT t.TestID, t.TestNumber, t.TestString
    FROM tbl_test t
    WHERE t.TestNumber = Param1
      AND t.TestString = Param2;

END
hex(body): 424547494E0A0A2020202053454C45435420742E5465737449442C20742E546573744
E756D6265722C20742E54657374537472696E670A2020202046524F4D2074626C5F7465737420740
A20202020574845524520742E546573744E756D626572203D20506172616D310A202020202020414
E4420742E54657374537472696E67203D20506172616D323B0A2020202020200A454E44
3 rows in set (0.00 sec)

mysql>

Output from workbench using:
select name, body, hex(body) 
from mysql.proc 
where db = 'test' 
  and name in ('test_routine_HeidiSQL', 
               'test_routine_QUERY_BROWSER', 
               'test_routine_WORKBENCH');

test_routine_HeidiSQL
424547494E0D0A0D0A2020202053454C45435420742E5465737449442C20742E546573744E756D6265722C20742E54657374537472696E670D0A2020202046524F4D2074626C5F7465737420740D0A20202020574845524520742E546573744E756D626572203D20506172616D310D0A202020202020414E4420742E54657374537472696E67203D20506172616D323B0D0A0D0A454E44

test_routine_QUERY_BROWSER
424547494E0D0A0D0A2020202053454C45435420742E5465737449442C20742E546573744E756D6265722C20742E54657374537472696E670D0A2020202046524F4D2074626C5F7465737420740D0A20202020574845524520742E546573744E756D626572203D20506172616D310D0A202020202020414E4420742E54657374537472696E67203D20506172616D323B0D0A0D0A454E44

test_routine_WORKBENCH
424547494E0A0A2020202053454C45435420742E5465737449442C20742E546573744E756D6265722C20742E54657374537472696E670A2020202046524F4D2074626C5F7465737420740A20202020574845524520742E546573744E756D626572203D20506172616D310A202020202020414E4420742E54657374537472696E67203D20506172616D323B0A2020202020200A454E44

Regards,

David Marten

Hi,
can you please check with most recent version (5.2.29) if the problem still exists?

Hi Johannes,

Yes, I can confirm this behaviour is still present in WB 5.2.29.

Using the following query (again) in workbench:

select name, body, hex(body) 
from mysql.proc 
where db = 'test' 
  and name in ('test_routine_HeidiSQL', 
               'test_routine_QUERY_BROWSER', 
               'test_routine_WORKBENCH');

gives results:

test_routine_HeidiSQL
424547494E0D0A0D0A2020202053454C45435420742E5465737449442C20742E546573744E756D6265722C20742E54657374537472696E670D0A2020202046524F4D2074626C5F7465737420740D0A20202020574845524520742E546573744E756D626572203D20506172616D310D0A202020202020414E4420742E54657374537472696E67203D20506172616D323B0D0A0D0A454E44

test_routine_QUERY_BROWSER
424547494E0D0A0D0A2020202053454C45435420742E5465737449442C20742E546573744E756D6265722C20742E54657374537472696E670D0A2020202046524F4D2074626C5F7465737420740D0A20202020574845524520742E546573744E756D626572203D20506172616D310D0A202020202020414E4420742E54657374537472696E67203D20506172616D323B0D0A0D0A454E44

test_routine_WORKBENCH
424547494E0A0A2020202053454C45435420742E5465737449442C20742E546573744E756D6265722C20742E54657374537472696E670A2020202046524F4D2074626C5F7465737420740A20202020574845524520742E546573744E756D626572203D20506172616D310A202020202020414E4420742E54657374537472696E67203D20506172616D323B0A2020202020200A454E44

Please let me know if there is anything further I can do to help achieve a solution.

Regards,

David Marten

I haven't verified this, but by looking at the uploaded data I think this is caused by incorrect line ending conversion in Windows when fetching the CREATE PROCEDURE data from the server. 

The SQL Editor code should be changed so that when the routine body is fetched from the server,
line ending normalization is performed before giving the code to the parser. 

d m: If you Reverse Engineer the 3 routines into a new model, does the same problem happen when viewing the procedure code?

Marked bug #56885 as a duplicate

Following on from bug #56885, I can confirm that I'm seeing the same problem when editing from the routine editor in the model viewer. 

Client: WB 5.2.29, Win7 64bit
Server: MySQL 5.1.50, Win2008 64bit

> The SQL Editor code should be changed so that [...]

The same CRLF-duplicating behavior described occurs whenever I open any CRLF-newlined text file in the SQL Editor. It will not happen if the same text enters the editor via pasting from the clipboard.

Example:

File contents:
0d61 620a 0a0d 0d63 000a
(dumped using "od -x" under cygwin)

Loads in SQL Editor as:
0d61 0d0a 620a 0a0d 0a0d 0d63 0d0a 000a
(copy-pasted into wscite (which auto-detected and preserved the use of CRLF), saved to file, dumped using "od -x")

I'm using Workbench 5.2.31 on Win XP SP3.

Fixed as of 5.2.38:

On Microsoft Windows, the Routines editor would insert extra
line endings.