Stored Procedure Debugger

[Aris Bignay]

Hello all. I'm a new member of this forum and new to mySQL stored proc. I just want to know if stored procedure debugger will be supported by SQLYog. I expect everybody who develops database apps will agree that it will be a big help for database dev out there if this feature will be integrated. 😀

[peterlaursen]

Please post SQLyog questions in the SQLyog – and not MONyog – category!

This has been asked before.The problem is that MySQL provides no API functionalities (classes, functions etc.) for that! There simply is NO WAY to really debug a Stored Procedure/Function/Trigger/Event in MySQL like in SQL Server, Oracle etc. In that respect MySQL is still very primitive compared to other databases!

There are a few other 'debuggers' that we have studied and non of them really debug the Stored Procedures. They 'simulate' a Stored procedure – for instance by using @user_variables with plain SQL instead of declared variables inside Stored Procedures. This will work with simple demo examples – and not much more!

I think it is now about 9 months ago we discussed this, and we did not find a solution. We will not provide 'eyecatch' that does not do the right thing. But ideas are welcome of course!

[Aris Bignay]

I see it now. Anyway, thanks for your answer. I really appreciate it. I'm just surprised about your statement that MySQL is still very primitive compared to other databases like Oracle, SQL Server etc. I'm a new MySQL user and haven't had enough time to compare its features with other RDBMs. What I know is MySQL Server is already comparable to those database giants on the market. Anyway, I still lack strong knowledge on MySQL and it's new features that were made available on version 5.0 onwards… I still have to spend time studying those things… 😀

[peterlaursen]

let me explain further:

the API has no functions (or nothing else similar) like

*StoredProcedureSetBreakpoint(…)

*StoredProcedureExecuteWithBreakpoints(..)

*StoredProcedureListVariables(…)

etc..etc. – what would be required to really debug inside the stored procedure.

… so basically you will need to write the SP with debugging code inside it, and remove/comment it once it is not required anymore!

But still ideas are welcome!

[Aris Bignay]

I do this approach to some of programming language that I used before. Anyway, let's hope that debugging API will be included on the future releases of MySQL server. Perhaps, I have to create another table for the purpose of logging the debugging outputs. Thanks.

[Clyde Hart]

Well, it's not about SQLYog, but anyway, I've used Devart's dbForge Studio and it has a full-featured stored routine debugger, which allows debugging of stored procedures, functions, and triggers.

[peterlaursen]

@Clyde: I tried that a few years back (along with 1 or 2 more 'solutions') and it was not able to handle even trivial loops (not to mention handlers and cursors). I tried a few handfuls of pretty simple Routines and in 50% of cases it provided garbage and misleading information. They may of course have improved on that in between.  But basically it is not possible to debug a 'stored program' in MySQL (the MySQL API does not support it). You will have to 'emulate' it somehow – what means that what you are debugging is not exactly the same as the original 'stored program'. I do not believe in it when used with examples just a little more complicated than 'student exercises'.

[peterlaursen]

Let me add that if you want to see what such 'debugging tool' *really does* you should enable the general log on the server and try use the tool and next read the general log.  You will find that it does not CALL SOMEROUTINE() at all.  So this is not 'debugging' – it is 'emulation'.  

It is not possible to debug 'stored programs' on MySQL (unfortunately).  Actually I myself once (I think around 1½ years ago) participated in a vote about what should should be priority in MySQL future developments. I did cast all my points/votes into an ' API for stored programs' debugging'.

[Author]

Posts