Forked from biniama/MySql-StoredProcedure-Logger.sql
Created
December 22, 2016 22:07
-
-
Save jacquelineIO/b14da322b4059eb6add9f0cbfb8c3da1 to your computer and use it in GitHub Desktop.
MySql StoredProcedure Logger
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DELIMITER $$ | |
DROP PROCEDURE IF EXISTS setupLogging $$ | |
CREATE PROCEDURE setupLogging() | |
BEGIN | |
CREATE TABLE IF NOT EXISTS sp_logger(ts timestamp DEFAULT current_timestamp, thingID bigint, msg varchar(512)) ENGINE = MyISAM; | |
END $$ | |
CALL setupLogging() $$ | |
DROP PROCEDURE IF EXISTS setupTmpLog $$ | |
CREATE PROCEDURE setupTmpLog() | |
BEGIN | |
CREATE TEMPORARY TABLE IF NOT EXISTS tmplog (msg varchar(512)) ENGINE = MEMORY; | |
END $$ | |
DROP PROCEDURE IF EXISTS doLog $$ | |
CREATE PROCEDURE doLog(in logMsg varchar(512)) | |
BEGIN | |
DECLARE CONTINUE HANDLER FOR 1146 -- Table not found | |
BEGIN | |
CALL setupTmpLog(); | |
INSERT INTO tmplog VALUES('resetup tmp table'); | |
INSERT INTO tmplog VALUES(logMsg); | |
END; | |
INSERT INTO tmplog VALUES(logMsg); | |
END $$ | |
DROP PROCEDURE IF EXISTS saveAndLog $$ | |
CREATE PROCEDURE saveAndLog(IN thingId INT, IN lastMsg varchar(512)) | |
BEGIN | |
CALL doLog(lastMsg); | |
INSERT INTO sp_logger(thingId, msg) (SELECT thingId, msg FROM tmplog); | |
TRUNCATE TABLE tmplog; | |
END $$ | |
-- Example of using the Logger | |
DROP PROCEDURE IF EXISTS parseAndStoreList $$ | |
CREATE PROCEDURE parseAndStoreList( | |
in thingId int, | |
in i_list varchar (128), | |
out returnCode INT) | |
BEGIN | |
DECLARE v_loopIndex INT DEFAULT 0; | |
DECLARE Exit Handler FOR SQLEXCEPTION | |
BEGIN | |
call saveAndLog(thingId, 'got exception parsing list'); -- save the logs if things go badly | |
set returnCode = -1; | |
END; | |
call doLog(concat_ws('got list:', i_list)); -- say we got to the start | |
parse_loop: LOOP | |
set v_loopIndex = v_loopIndex + 1; | |
call doLog(concat_wc(',', 'at loop iteration ', v_loopIndex)); -- say we got to nth iteration | |
-- actually do the parsing, or whatever | |
END LOOP parse_loop; | |
set returnCode = 0; | |
END $$ | |
DELIMITER ; | |
CALL parseAndStoreList(12, 786, @returnCode); | |
select @returnCode; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Debugging Stored Procedures: http://www.drdobbs.com/database/debugging-mysql-stored-procedures/218100564
Other options:
http://gilfster.blogspot.co.at/2006/03/debugging-stored-procedures-in-mysql.html
http://www.bluegecko.com/debugging-stored-procedures/ -- forked to my own repo expands on Dr Dobbs article