Friday, May 29, 2009

Stored Procedures in MySQL 5.x: log / trace messages

There is no standard way to log / trace message in MySQL.
But there is a trick - execute SQL queries like next:
SELECT 'log message' AS 'title';

So to have Log Message feature we need next stored procedures:
  • procedure to execute dynamic SQL statements
  • procedure to create SELECT SQL query and simulate log output

See the code below:

DELIMITER $$


-- PROCEDURE pLog
-- outputs log message
-- Params:
-- sTitle - title
-- sMsg - log message
DROP PROCEDURE IF EXISTS `pLog` $$
CREATE PROCEDURE `pLog`(IN sTitle VARCHAR(255), IN sMsg VARCHAR(255))
BEGIN
DECLARE strSQL VARCHAR(512);

SET strSQL = CONCAT('SELECT ''', sMsg, ''' AS ''', sTitle, '''');
CALL pExecuteImmediate(strSQL);
END $$


-- PROCEDURE pExecuteImmediate
-- executes dynamic SQL statement
-- Params:
-- tSQLStmt - SQL statement to be executed
DROP PROCEDURE IF EXISTS `pExecuteImmediate` $$
CREATE PROCEDURE `pExecuteImmediate`(IN tSQLStmt TEXT)
BEGIN
SET @executeImmediateSQL = tSQLStmt;
PREPARE executeImmediateSTML FROM @executeImmediateSQL;
EXECUTE executeImmediateSTML;
DEALLOCATE PREPARE executeImmediateSTML;
END $$


DELIMITER ;

Function pCheckCurrentUserRoot contains examples of using pLog

DELIMITER $$


-- FUNCTION pCheckCurrentUserRoot
-- checks if we are connected as root user
DROP PROCEDURE IF EXISTS `pCheckCurrentUserRoot` $$
CREATE PROCEDURE `pCheckCurrentUserRoot`()
BEGIN
DECLARE strUser VARCHAR(50);

CALL pLog('INFO', 'pCheckCurrentUserRoot...');

SELECT USER() INTO strUser;
CALL pLog('INFO', CONCAT('user: ', strUser));

IF (strUser LIKE 'root%') THEN
CALL pLog('INFO', 'current user is root');
ELSE
CALL pLog('INFO', 'current user is not root');
END IF;
END $$


DELIMITER ;

Additional information about Stored Procedures in MySQL 5.x could be found here: