MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Debugging MySQL Functions

with 4 comments

Somebody, who read this post on Debugging MySQL Procedures, asked why the strategy of selecting a string literal didn’t work in a MySQL function. That’s easy, they’re not designed to support a SELECT statement, only a SELECT-INTO statement.

Why? That’s the purpose of a function to perform something and return a single reply.

That’s also why a MySQL functions only support the IN mode of operation for formal and call parameters. When formal parameters are restricted to in-mode-only operations, they implement a pass-by-value function model. This can also be expressed from the other side of the looking glass. In that case, MySQL functions don’t support pass-by-reference functions that use the INOUT or OUT mode operations.

If you put a SELECT statement in a function to print internal values or comments, it raises an error. Take for example the following attempt to create the debugging function with an echo of output (that works in stored procedures).

CREATE FUNCTION debugger() RETURNS INT
BEGIN
  SELECT '[Debug #1]';
  RETURN 1;
END;
$$

It fails to create the function because you’ve violated a key integrity rule. It also raises the following error:

ERROR 1415 (0A000): Not allowed to return a result set from a function

You have two potential solutions to this problem. The first is limited and inflexible. The second isn’t as limited or inflexible and is the recommended way to debug your functions without a tool. That’s to use a temporary table to record run-time debugging events.

Session Variable Debugging ↓

Expand this section to see the steps for debugging functions with session variables.

  1. Create two session level variables, like these:
SET @counter := 0;
SET @msg := '';
  1. Create a function that uses the SELECT-INTO statement to collect and store debugging information during function execution.
CREATE FUNCTION debugger() RETURNS INT
BEGIN
  SELECT @counter + 1 INTO @counter;
  SELECT CONCAT('[Debug #',@counter,']') INTO @msg;
  RETURN 1;
END;
$$
  1. Run the function and then query the session variable for results
SELECT debugger();
SELECT @msg;

You’ll see the following text:

+------------+
| @msg       |
+------------+
| [Debug #1] |
+------------+

Temporary Table Debugging ↓

Expand this section to see the steps for debugging functions with session variables.

  1. Only when you want a counter, create one session level variables.
SET @counter := 0;
  1. Create an in-memory table to store debugging information from function execution.
CREATE TABLE debugger
( debug_comment CHAR(80)) ENGINE=MEMORY;
  1. Create a function that supports inserts into the in-memory table. Naturally, you may need to make the columns larger when your debugging results are large. I’ve found that 80 characters is generally adequate for most debugging exercises.
1
2
3
4
5
6
7

CREATE FUNCTION debugger() RETURNS INT
BEGIN
  SELECT @counter + 1 INTO @counter;
  INSERT INTO debugger VALUES (CONCAT('[Debug #',@counter,']'));
  RETURN 1;
END;
$$
  1. Call the function and query the debugging results.
SELECT debugger();
SELECT debugger();
SELECT debugger();
SELECT debug_comment FROM debugger;

You’ll see the following text:

+---------------+
| debug_comment |
+---------------+
| [Debug #1]    |
| [Debug #2]    |
| [Debug #3]    |
+---------------+

Complete Code Sample ↓

Expand this section to see the sample working code for all examples.

This script creates, runs, and tests the code from the above discussions.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76

-- Conditionally drop the function when it exists.
DROP FUNCTION IF EXISTS debugger;
 
-- Set delimiter to create a function with semicolon statment terminators.
DELIMITER $$
 
-- Create a function that returns 1.
CREATE FUNCTION debugger() RETURNS INT
BEGIN
  SELECT '[Debug #1]';
  RETURN 1;
END;
$$
 
-- Reset the delimiter to enable normal execution.
DELIMITER ;
 
-- Declare session level variables.
SET @counter := 0;
SET @msg := '';
 
-- Conditionally drop the function when it exists.
DROP FUNCTION IF EXISTS debugger;
 
-- Set delimiter to create a function with semicolon statment terminators.
DELIMITER $$
 
-- Create a function that writes to local session variables.
CREATE FUNCTION debugger() RETURNS INT
BEGIN
  SELECT @counter + 1 INTO @counter;
  SELECT CONCAT('[Debug #',@counter,']') INTO @msg;
  RETURN 1;
END;
$$
 
-- Reset the delimiter to enable normal execution.
DELIMITER ;
 
-- Test the function code and read the session-level variable contents.
SELECT debugger();
SELECT @msg;
 
-- Declare a session level variable.
SET @counter := 0;
 
-- Conditionally drop the function when it exists.
DROP TABLE IF EXISTS debugger;
 
-- Create a temporary (in-memory) table to record debugging information.
CREATE TABLE debugger
( debug_comment CHAR(80)) ENGINE=MEMORY;
 
-- Conditionally drop the function when it exists.
DROP FUNCTION IF EXISTS debugger;
 
-- Set delimiter to create a function with semicolon statment terminators.
DELIMITER $$
 
-- Create a function that writes to a debugging table.
CREATE FUNCTION debugger() RETURNS INT
BEGIN
  SELECT @counter + 1 INTO @counter;
  INSERT INTO debugger VALUES (CONCAT('[Debug #',@counter,']'));
  RETURN 1;
END;
$$
 
-- Reset the delimiter to enable normal execution.
DELIMITER ;
 
-- Test the function code and read the session-level variable contents.
SELECT debugger();
SELECT debugger();
SELECT debugger();
SELECT debug_comment FROM debugger;

Written by maclochlainn

June 26th, 2010 at 6:51 pm

Posted in MySQL,Persistent Stored Modules,pl/sql,PSM,sql,Stored Procedures

Tagged with ,

«
»