How do you debug MySQL stored procedures?

by

The following debug_msg procedure can be called to simply output a debug message to the console:

DELIMITER $$

DROP PROCEDURE IF EXISTS `debug_msg`$$
DROP PROCEDURE IF EXISTS `test_procedure`$$

CREATE PROCEDURE debug_msg(enabled INTEGER, msg VARCHAR(255))
BEGIN
  IF enabled THEN
    select concat('** ', msg) AS '** DEBUG:';
  END IF;
END $$

CREATE PROCEDURE test_procedure(arg1 INTEGER, arg2 INTEGER)
BEGIN
  SET @enabled = TRUE;

  call debug_msg(@enabled, 'my first debug message');
  call debug_msg(@enabled, (select concat_ws('','arg1:', arg1)));
  call debug_msg(TRUE, 'This message always shows up');
  call debug_msg(FALSE, 'This message will never show up');
END $$

DELIMITER ;

Then run the test like this:

CALL test_procedure(1,2)

It will result in the following output:

** DEBUG:
** my first debug message
** DEBUG:
** arg1:1
** DEBUG:
** This message always shows up

More Related Contents:

  1. MySQL stored procedures use them or not to use them
  2. MySQL triggers cannot update rows in same table the trigger is assigned to. Suggested workaround?
  3. How to schedule a stored procedure in MySQL
  4. Which DB technology is sued to solve the following search issue? [closed]
  5. MyISAM versus InnoDB [closed]
  6. Compare two MySQL databases [closed]
  7. How should I resolve –secure-file-priv in MySQL?
  8. What does character set and collation mean exactly?
  9. What’s faster, SELECT DISTINCT or GROUP BY in MySQL?
  10. Storing image in database directly or as base64 data?
  11. Implementation of Levenshtein distance for mysql/fuzzy search?
  12. Does MySQL ignore null values on unique constraints?
  13. Pass array to MySQL stored routine
  14. What’s the difference between MyISAM and InnoDB? [duplicate]
  15. Is there an ANSI SQL alternative to the MYSQL LIMIT keyword?
  16. In SQL, is it OK for two tables to refer to each other?
  17. MySQL create stored procedure syntax with delimiter
  18. Error renaming a column in MySQL
  19. Mysql database sync between two databases
  20. Writing optional parameters within stored procedures in MySQL? [duplicate]
  21. How to increase MySQL connections(max_connections)?
  22. Does mysql have the equivalent of Oracle’s “analytic functions”?
  23. mysql stored procedure that calls itself recursively
  24. Remove special characters from a database field
  25. Using reserved words in column names
  26. How big can a MySQL database get before performance starts to degrade
  27. What is the difference between SQL and MySQL? [closed]
  28. mysql Failed to read auto-increment value from storage engine
  29. How to use Table output from stored MYSQL Procedure
  30. How to store data with dynamic number of attributes in a database

Leave a Comment