Use the strict sql mode when compiling a MySQL stored procedure to avoid unexpected errors.

Setting the mode to STRICT_ALL_TABLES when you compile your stored procedure or function can prevent a lot of subtle bugs in your MySQL application.
Consider this example:

DELIMITER //
CREATE FUNCTION test(p_first TINYINT, p_second TINYINT) RETURNS TINYINT
BEGIN
    DECLARE v_result TINYINT;
    SET v_result := p_first + p_second;
    RETURN v_result;
END//
DELIMITER ;

compile it and then test the funcion:

mysql> select test(126,1);
+-------------+
| test(126,1) |
+-------------+
|         127 |
+-------------+
1 row in set (0.00 sec)

mysql> select test(127,1);
+-------------+
| test(127,1) |
+-------------+
|         127 |
+-------------+
1 row in set, 1 warning (0.00 sec)

The first test produced the correct answer but the second test was incorrect. The reason is that the variable v_result is a TINYINT and this will not hold a value higher then 127.
The problem is that you will not get an error but an incorrect result!

It get’s even worse: you can assign a string to the TINYINT parameter:

mysql> select test('a',1);
+-------------+
| test('a',1) |
+-------------+
|           1 |
+-------------+
1 row in set, 1 warning (0.00 sec)

The invalid value for the parameter is ignored and the function produces an incorrect result.

In an interactive prompt you will see a warning next to the result but this will get unnoticed if this function was called within your application. You can show the warning like this:

mysql> show warnings;
+---------+------+---------------------------------------------------+
| Level   | Code | Message                                           |
+---------+------+---------------------------------------------------+
| Warning | 1264 | Out of range value for column 'v_result' at row 1 |
+---------+------+---------------------------------------------------+
1 row in set (0.00 sec)

You can prevent this problem by setting your sql mode to STRICT_ALL_TABLES when compiling the procedure or function. When executing the procedure in another sql mode you will still get the error because only the sql mode during compiling the procedure matters.

Let’s compile it again. We will save the current sql mode in a user variable, Set the sql mode to STRICT_ALL_TABLES, compile the function and restore the old sql mode.

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_TRANS_TABLES';
DELIMITER //
CREATE FUNCTION test(p_first TINYINT, p_second TINYINT) RETURNS TINYINT
BEGIN
    DECLARE v_result TINYINT;
    SET v_result := p_first + p_second;
    RETURN v_result;
END//
DELIMITER ;
SET sql_mode=@OLD_SQL_MODE;

Now test it again:

mysql> select test(127,1);
ERROR 1264 (22003): Out of range value for column 'v_result' at row 1

So update your template for a procedure or function and add the code for saving the sql mode like this:

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_TRANS_TABLES';

DELIMITER //
DROP FUNCTION IF EXISTS function_name;
//

CREATE FUNCTION function_name(
     p_param1 INT
    ,p_param2 VARCHAR(255)  
) RETURNS INT
/**
 * Description of function
 * <br>
 * %author Ronald Speelman
 * %version 1.0
 * %param p_param1                 Description of the parameter
 * %return result of calculation
*/

BEGIN
    /* declare variables */
    DECLARE v_result INT;

    /* do the real work here */
    SET v_result := NULL;

    /* this is your one and only exit point */
    RETURN v_result;
END//
DELIMITER ;

SET sql_mode=@OLD_SQL_MODE;

Related posts

Tags: , ,

One Response to “Use the strict sql mode when compiling a MySQL stored procedure to avoid unexpected errors.”

  1. bw says:

    This piece of information was very helpful in debugging and resolving a problem we ran into when upgrading our mysql database.

    “You can prevent this problem by setting your sql mode to STRICT_ALL_TABLES when compiling the procedure or function. When executing the procedure in another sql mode you will still get the error because only the sql mode during compiling the procedure matters.”

    Thank you for that!!!

Leave a Reply