Bug #46904 wrong or strange export of Routine
Submitted: 24 Aug 2009 22:22 Modified: 11 Nov 2009 12:58
Reporter: Andrea D'Orazio Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Workbench Severity:S3 (Non-critical)
Version:5.1.18 OS:Microsoft Windows (Vista64 SP2 )
Assigned to: CPU Architecture:Any
Tags: export, forward engineer, routine
Triage: Needs Triage: D2 (Serious)

[24 Aug 2009 22:22] Andrea D'Orazio 
Description:
I have some troubles exporting Routines:

this is a simple Routine in the editor:

-- --------------------------------------------------------------------------------
-- Routine DDL
-- --------------------------------------------------------------------------------
DELIMITER //

CREATE PROCEDURE `mydb`.`routine1` ()
BEGIN
    DECLARE var1 INT;
END;
//

first, when I put a semi colon after END and press return, I see the following

Le informazioni su come richiamare il debug JIT (Just-In-Time) anziché questa finestra
sono riportate in fondo al messaggio.

************** Testo dell'eccezione **************
System.NullReferenceException: Riferimento a un oggetto non impostato su un'istanza di oggetto.
   in ScintillaNet.ManagedRange.Dispose()
   in ScintillaNet.Scintilla.OnBeforeTextDelete(TextModifiedEventArgs e)
   in ScintillaNet.Scintilla.FireModified(NativeScintillaEventArgs ea)
   in ScintillaNet.Scintilla.WndProc(Message& m)
   in System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   in System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   in System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

************** Assembly caricati **************
mscorlib
    Versione assembly: 2.0.0.0
    Versione Win32: 2.0.50727.3074 (QFE.050727-3000)
    Base di codice: file:///C:/Windows/Microsoft.NET/Framework/v2.0.50727/mscorlib.dll
----------------------------------------
MySQLWorkbench
    Versione assembly: 5.1.17.4302
    Versione Win32: 5.1.17.4302
    Base di codice: file:///T:/EXP/database/MySQL%20Workbench%205.1.17%20OSS/MySQLWorkbench.exe
----------------------------------------
wbprivate.wr
    Versione assembly: 0.0.0.0
    Versione Win32: 
    Base di codice: file:///T:/EXP/database/MySQL%20Workbench%205.1.17%20OSS/wbprivate.wr.DLL
----------------------------------------
wbpublic.wr
    Versione assembly: 0.0.0.0
    Versione Win32: 
    Base di codice: file:///T:/EXP/database/MySQL%20Workbench%205.1.17%20OSS/wbpublic.wr.DLL
----------------------------------------
System.Windows.Forms
    Versione assembly: 2.0.0.0
    Versione Win32: 2.0.50727.3053 (netfxsp.050727-3000)
    Base di codice: file:///C:/Windows/assembly/GAC_MSIL/System.Windows.Forms/2.0.0.0__b77a5c561934e089/System.Windows.Forms.dll
----------------------------------------
System
    Versione assembly: 2.0.0.0
    Versione Win32: 2.0.50727.3053 (netfxsp.050727-3000)
    Base di codice: file:///C:/Windows/assembly/GAC_MSIL/System/2.0.0.0__b77a5c561934e089/System.dll
----------------------------------------
System.Drawing
    Versione assembly: 2.0.0.0
    Versione Win32: 2.0.50727.3053 (netfxsp.050727-3000)
    Base di codice: file:///C:/Windows/assembly/GAC_MSIL/System.Drawing/2.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll
----------------------------------------
MySQLCsUtilities
    Versione assembly: 1.0.0.0
    Versione Win32: 1.0.0.0
    Base di codice: file:///T:/EXP/database/MySQL%20Workbench%205.1.17%20OSS/MySQLCsUtilities.DLL
----------------------------------------
msvcm90
    Versione assembly: 9.0.21022.8
    Versione Win32: 9.00.21022.8
    Base di codice: file:///C:/Windows/WinSxS/x86_microsoft.vc90.crt_1fc8b3b9a1e18e3b_9.0.21022.8_none_bcb86ed6ac711f91/msvcm90.dll
----------------------------------------
MySQLGrtShell
    Versione assembly: 1.0.0.0
    Versione Win32: 1.0.0.0
    Base di codice: file:///T:/EXP/database/MySQL%20Workbench%205.1.17%20OSS/MySQLGrtShell.EXE
----------------------------------------
WeifenLuo.WinFormsUI.Docking
    Versione assembly: 2.2.3513.631
    Versione Win32: 2.2.0.0
    Base di codice: file:///T:/EXP/database/MySQL%20Workbench%205.1.17%20OSS/WeifenLuo.WinFormsUI.Docking.DLL
----------------------------------------
WbPlugins
    Versione assembly: 1.0.0.0
    Versione Win32: 1.0.0.0
    Base di codice: file:///T:/EXP/database/MySQL%20Workbench%205.1.17%20OSS/WbPlugins.DLL
----------------------------------------
Aga.Controls
    Versione assembly: 1.6.0.0
    Versione Win32: 1.6.0.0
    Base di codice: file:///T:/EXP/database/MySQL%20Workbench%205.1.17%20OSS/Aga.Controls.DLL
----------------------------------------
System.Windows.Forms.resources
    Versione assembly: 2.0.0.0
    Versione Win32: 2.0.50727.3053 (netfxsp.050727-3000)
    Base di codice: file:///C:/Windows/assembly/GAC_MSIL/System.Windows.Forms.resources/2.0.0.0_it_b77a5c561934e089/System.Windows.Forms.resources.dll
----------------------------------------
grt.db.fe
    Versione assembly: 1.0.0.0
    Versione Win32: 1.0.0.0
    Base di codice: file:///T:/EXP/database/MySQL%20Workbench%205.1.17%20OSS/grt.db.fe.DLL
----------------------------------------
mscorlib.resources
    Versione assembly: 2.0.0.0
    Versione Win32: 2.0.50727.3074 (QFE.050727-3000)
    Base di codice: file:///C:/Windows/Microsoft.NET/Framework/v2.0.50727/mscorlib.dll
----------------------------------------
grt.helpers.be
    Versione assembly: 1.0.0.0
    Versione Win32: 1.0.0.0
    Base di codice: file:///T:/EXP/database/MySQL%20Workbench%205.1.17%20OSS/grt.helpers.be.DLL
----------------------------------------
db.mysql.editors.wbp.fe
    Versione assembly: 1.0.0.0
    Versione Win32: 1.0.0.0
    Base di codice: file:///T:/EXP/database/MySQL%20Workbench%205.1.17%20OSS/db.mysql.editors.wbp.fe.DLL
----------------------------------------
db.mysql.editors.wbp.wr
    Versione assembly: 0.0.0.0
    Versione Win32: 
    Base di codice: file:///T:/EXP/database/MySQL%20Workbench%205.1.17%20OSS/db.mysql.editors.wbp.wr.DLL
----------------------------------------
sqlide.fe
    Versione assembly: 0.0.0.0
    Versione Win32: 0.0.0.0
    Base di codice: file:///T:/EXP/database/MySQL%20Workbench%205.1.17%20OSS/sqlide.fe.DLL
----------------------------------------
ScintillaNet
    Versione assembly: 2.0.3513.894
    Versione Win32: 2.0.0.0
    Base di codice: file:///T:/EXP/database/MySQL%20Workbench%205.1.17%20OSS/ScintillaNet.DLL
----------------------------------------
System.Xml
    Versione assembly: 2.0.0.0
    Versione Win32: 2.0.50727.3074 (QFE.050727-3000)
    Base di codice: file:///C:/Windows/assembly/GAC_MSIL/System.Xml/2.0.0.0__b77a5c561934e089/System.Xml.dll
----------------------------------------

************** Debug JIT **************
Per attivare il debug JIT, è necessario impostare il valore
jitDebugging nella sezione system.windows.forms del file di configurazione
dell'applicazione o del computer (machine.config).
L'applicazione inoltre deve essere compilata con il debug
attivato.

Ad esempio:

<configuration>
    <system.windows.forms jitDebugging="true" />
</configuration>

Quando il debug JIT è attivato, tutte le eccezioni non gestite
vengono inviate al debugger JIT registrato nel computer,
anziché essere gestite da questa finestra di dialogo.

if I export w Forward eng, I got this text

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `mydb`;

DELIMITER //
//
USE mydb//
CREATE PROCEDURE `mydb`.`routine1` ()
BEGIN
    DECLARE var1 INT;
END;
//

DELIMITER ;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

please note // after DELIMITER //
also, there is no way to delete blank line between DECLARE and USE

with more complex code, and exporting also triggers and tables, I got several random DELIMITER// and DELIMITER; without sense...

thx

How to repeat:
see above...
[25 Aug 2009 0:55] Miguel Solorzano 
Thank you for the bug report. Could you please provide the model project file which cause the reported behavior?. Thanks in advance.
[3 Sep 2009 14:35] Andrea D'Orazio 
produces wrong forward engin. db code

Attachment: bug.mwb (application/x-zip-compressed, text), 3.01 KiB.
[3 Sep 2009 14:39] Andrea D'Orazio 
produces wrong forward engin db code

Attachment: bug.mwb (application/x-zip-compressed, text), 3.01 KiB.
[23 Sep 2009 9:02] Andrea D'Orazio 
here you are another example of wrong sql code exportation when using routines:
you can find original code in attachment

*** for mark errors

DELIMITER //
//              *** 
USE doraznet_civ//
doraznet_civ//  ***
CREATE PROCEDURE R_ricchezza (naz CHAR(30))
BEGIN
    DECLARE ric INT;
    DECLARE ric1 INT;
    DECLARE ric2 INT;
    DECLARE ric3 INT;
    
    SET ric1 = (SELECT SUM(punti_investimento) FROM T_Nazione WHERE nazione = naz);
    SET ric1 = IFNULL(ric1, 0);
    SET ric2 = (SELECT SUM(val_investimento) FROM T_Citta WHERE nazione = naz);
    SET ric2 = IFNULL(ric2, 0);
    SET ric3 = (SELECT SUM(punti_investimento) FROM V_Esercito WHERE nazione = naz);
    SET ric3 = IFNULL(ric3, 0);

    SET ric = ric1+ric2+ric3;
    UPDATE T_Nazione SET ricchezza = ric WHERE nazione = naz;
END *** ; needed
//
// ***

USE doraznet_civ//

doraznet_civ// ***

CREATE FUNCTION R_nazione (cit CHAR(30)) RETURNS VARCHAR(30) DETERMINISTIC

BEGIN
    DECLARE naz CHAR(30);
    DECLARE cur CURSOR FOR SELECT DISTINCT nazione FROM T_Citta WHERE citta = cit;
    OPEN cur;
    FETCH cur INTO naz;
    CLOSE cur;
    RETURN (naz);
END;
//

DELIMITER ;
[23 Sep 2009 15:22] Miguel Solorzano 
Thank you for the feedback. You wrote:

"first, when I put a semi colon after END and press return, I see the following

Le informazioni su come richiamare il debug JIT (Just-In-Time) anziché questa finestra
sono riportate in fondo al messaggio."

Do you meant the application crashed?. If yes I couldn't repeat.
[24 Sep 2009 9:22] Andrea D'Orazio 
yes, the application crashes RANDOMLY, however after I can choose to continue.
instead, wrong code is ALWAYS generated.
[28 Sep 2009 15:53] Susanne Ebrecht 
Which version of Workbench are you using?

The crash information looks like you are using Workbench 5.2 and not Workbench 5.1.
[28 Sep 2009 16:04] Andrea D'Orazio 
5.1.18, same behaviour w/ 5.1.17

please note that the most frustrating thing is wrong code (I have to check it manually) not JIT errors...
[29 Sep 2009 10:43] Valeriy Kravchuk 
Forward engineering related problem outlined here is verified on 64-bit XP, using the .mwb file uploaded.
[11 Nov 2009 12:58] Susanne Ebrecht 
This is a duplicate of bug #46505