MySQL stored procedures debugging

development, mysql Add comments

It seems there are no built-in ways to debug user-defined functions or procedures in MySQL. So if the function behaves bad, it’s hard to find out why.

Here is what I do in order to trace a function. Yes, it’s a bit ugly, but better than nothing.

1. Run this in your MySQL command line (or PhpMySQL with // as delimiter, there is a field for it below the SQL window).

DELIMITER //

DROP PROCEDURE IF EXISTS Debug; //
CREATE PROCEDURE Debug(Message TEXT)
BEGIN
    CREATE TABLE IF NOT EXISTS _debug (
        `id` int(10) unsigned NOT NULL auto_increment,
        `msg` TEXT DEFAULT NULL,
        `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY  (`id`)
    );
    INSERT INTO _debug(`msg`)  VALUES(Message);
END; //

DROP PROCEDURE IF EXISTS ClearDebugMessages; //
CREATE PROCEDURE ClearDebugMessages()
BEGIN
    TRUNCATE TABLE _debug;
END; //

2. In your function, this can be made:

CALL Debug('Debug message goes here');

3. There gonna be _debug table in your database that contains debug messages with date of creation of each.

4. If you want to clear all the debug messages, make a call:

CALL ClearDebugMessages();

Alternative is to truncate the _debug table with PhpMyAdmin.

Comments are closed.

WP Theme & Icons by N.Design Studio
Entries RSS Comments RSS Log in