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:

• Stored Procedures in MySQL 5.x
• Raise Exception / Error in MySQL 5.0