MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Debugging MySQL Functions

with 3 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.

Written by maclochlainn

June 26th, 2010 at 6:51 pm

3 Responses to 'Debugging MySQL Functions'

Subscribe to comments with RSS or TrackBack to 'Debugging MySQL Functions'.

  1. […] how they could debug runtime variable values in a MySQL Stored Procedure […]

  2. In the Session variable debugging part it should say:
    SELECT debugger();
    not SELECT debugging();

    michael stokes

    28 Jun 10 at 9:43 am

  3. Great catch! I’ve updated the post with the change.

    maclochlainn

    28 Jun 10 at 1:20 pm

Leave a Reply