Lets paint the picture:

Scenario part 1 : Migrating a couple thousand stored procedures from database technology X to mysql
Scenario part 2 : Legacy system where the people who wrote it left a long time ago
Scenario part 3 : Developers sure can get real creative and invent all kinds of ways to get data (eg: having a stored proc which formulates a big query using concat after going through a bunch of conditions (fair enough), BUT the parts making up the different queries are stored in a table on a database rather than within the stored proc itself) … talk about KIS – Keep it simple!!
Scenario part 4 : This stored proc references 18 tables, 4 views, and another two stored procedures on 5 databases

Now close your eyes and try to imagine that for a few seconds, nah kidding don’t want you to hurt yourself.

I wonder, who’s gonna cover my health insurance if i go crazy? :)

mysql 02:55:47 DEV > call storedprocblahbla(‘I’,’am’,’going’,’crazy’);
ERROR 1052 (23000): Column ‘state_of_mind’ in field list is ambiguous

Sure thats REALLY REALLY helpful thanks :), you know what, lets just mysqldump -d -R -B db1 db2 db3 db4 db5 > /wherever/you/like and grep for column ‘state_of_mind’.

I love a challenge but facing migration of so many stored procs I’d really love to have something “GOOD” to debug them with.

The solutions I found were:
1. Illatis eclipse plugin for $40
works on linux, mac and windows but does show me a lot of:
“An error has occurred when activating this view
2. mydebugger for $50
works on linux and mac via wine
3. dbForge for MySQL for $50
works only on windows due to .net framework (kinda crap because I `hate` using the former)

any other ideas are very welcome, especially opensource ones but I can’t find anything.

I can always try setting up debugging using GDB but would rather try to avoid that.

VN:F [1.9.22_1171]
Rating: 10.0/10 (1 vote cast)
VN:F [1.9.22_1171]
Rating: +1 (from 1 vote)

MySQL stored procedure debugging, can I sue for going insane?, 10.0 out of 10 based on 1 rating

  1. jswanhart says:

    You could try Toad for MySQL.

  2. Toad® for MySQL is a freeware development tool that improves the productivity of developers and administrators running MySQL on Windows.

    Remember I’m running Linux and am not sure what is worse, using windows to migrate the stored procs or not having a debugger (possibly the former). But I appreciate your suggestion.

    Keep them coming

  3. mcstafford says:

    In my not-so-humble opinion MySQL’s PL/SQL error handling is pretty weak.

    I have kept some of my hair in a manner like this:

    declare exit handler for sqlexception
    select concat(‘sqlexception encountered: ‘, l_action) message;

    set l_action = ‘insert into some.table’;
    insert into some.table (
    f1, f2, f3
    ) values (
    l_f1, l_f2, l_f3

  4. JosefMiran says:

    Two approaches may be used to debug code in MySQL:

    1. Control execution of stored code on the server (used in dbForge Studio for MySQL )
    2. Interpretation of stored code in an external program with execution of stored code expressions on the server (used in Illatis StepIn and in Debugger for MySQL from MyDebugger.com)

    The first approach is the same as use of debugging package DBMS_DEBUG in Oracle; it is used in dbForge Studio for MySQL. This approach guarantees validity of executed code, as an executed stored procedure/function/trigger does the same operations and gives the same result as during usual execution. See more information about the approach here: http://www.devart.com/dbforge/mysql/studio/articles/debugging.html

    The second approach allows escaping code locating on the server. This does not guarantee debugging validity. The code executed during debugging and in a usual mode differs. The causes may be either an error in an external interpreter (errors during processing conditionals, errors during retrieving data during reading a cursor, work with variables) or an error on the server.
    From release to release, sometimes errors appear in MySQL interpreter, or they are fixed. And in an external interpreter, it is impossible to take into account all the peculiarities of executing stored code with each released server version.

    The first approach is more beneficial during debugging stored procedures, functions, and triggers, and other important code.

  5. Fernando Gonzalez says:

    Two years later….

    No open source debuggers have been suggested so far, but there’s Connector/NET 6.6, this has stored routines debugging integrated into Visual Studio (2008/2010), so yes you need a .NET client, but is dual license MySql/GPL.

  6. Belatedly: ocelotgui (the Ocelot GUI) does include a debugger and does work with Ubuntu and is free (GPL License). Late alpha. Download via https://github.com/ocelot-inc/ocelotgui