Mysql stored routines - common gotchas?

These are the ones I've come across:

• Inability to change the body of a stored procedure with ALTER PROCEDURE One should use DROP PROCEDURE and CREATE PROCEDURE.

• PREPARE doesn't accept local variables. This won't work:

  DECLARE sql VARCHAR(32) DEFAULT 'SELECT 1';

  PREPARE stmt FROM sql;

• FETCH [cursor_name] INTO .. doesn't accept global variables. Th开发者_运维问答is won't work:

  FETCH mycursor INTO @a;

Any more examples ?

---

First of all, sorry for the amount of shameless plugs in this post. I am doing it because I have struggled with many of these problems, and explaining them each and all in detail would take too much time. On the upside, all articles I link to should give you a clear idea if they are worth reading by checking out the first para or so.

FETCH [cursor_name] INTO .. doesn't accept global variables.

not true. local variables work just fine for that.

PREPARE

As for PREPARE, in the same vein as what you mentioned, specifying parameter values for placeholders needs to be done with user-defined variables too, not local variables:

PREPARE stmt FROM 'INSERT INTO tab VALUES (?,?)';
EXECUTE stmt USING @val1, @val2;
DEALLOCATE PREPARE stmt;

For more info in PREPARE, see my shameless plug: http://rpbouman.blogspot.com/2005/11/mysql-5-prepared-statement-syntax-and.html

Another limitation with PREPARE is that you cannot use it in stored functions or triggers

TRIGGERS

currently, MySQL only implements FOR EACH ROW triggers. This means that you can only act on a row-level INSERT, UPDATE or DELETE event. There is a trick you can use to emulate a BEFORE STATEMENT trigger, see: http://rpbouman.blogspot.com/2006/04/mysql-hack-emulates-before-statement_30.html. However, this is not really useful - more useful is an AFTER STATEMENT trigger, but I have not found any way to wing that one.

Another limitation with triggers is that you can only have one of each type per table. The "type" of the triggger is made up of four things: trigger time (before/after), trigger statement (INSERT,UPDATE,DELETE), trigger level (statement/row, of which only ROW is implemented). So for example in mysql you can only have one BEFORE INSERT FOR EACH ROW trigger on any given table.

Another very important limitation on triggers is that they do not fire for actions that are the result of a CASCADE-ing foreign key. So UPDATEs and DELETEs that are the result of the cascading action of a foreign key on an innodb table do not fire any UPDATE/DELETE triggers on the tables that are affected by the cascading action.

DELIMITER

I think the most common gotcha is that the statement delimiter inside stored routines is the same as the one used to delimit plain sql statements, the semi-colon. This means that to define a stored routine you must first set the delimiter to something else so the semi-colon can be used to separatee stored routine statements:

DELIMITER $$

CREATE PROCEDURE p(p_name)
BEGIN
    INSERT INTO tab VALUES (p_name);  -- these statements are terminated with semi-colon
    SELECT last_insert_id();
END; -- after this we do the custom delimiter to create the procedure
$$ 

-- now let's reset the delimiter again:
DELIMITER ; 

CALL p('Boe'); -- we can use the semi-colon again now.

More info on the MySQL delimiter, see: http://rpbouman.blogspot.com/2008/02/most-misunderstood-character-in-mysqls.html

RAISING AN ERROR

A very important gotcha is that you cannot raise a user defined error in MySQL 5.0...5.1. See http://rpbouman.blogspot.com/2006/02/dont-you-need-proper-error-handling.html for a description of the problem. This problem is fixed in MySQL 5.5, where you can use the standard SQL SIGNAL statement to do that. See http://rpbouman.blogspot.com/2009/12/validating-mysql-data-entry-with_15.html. For solutions to cope with this lacking feature in MySQL v < 5.5 look here: http://rpbouman.blogspot.com/2005/11/using-udf-to-raise-errors-from-inside.html . There are other ways too but they all rely on raising some other known but not user-defined error.

PERFORMANCE

I guess the other important gotcha is that MySQL stored procedures are not fast. Expression in plain SQL are executed much faster than when you put them inside a stored function or stored procedure. SQL inside stored routines is about as fast as SQL plain, but you do not benefit from precompilation, because MySQL does not implement that. A few simple benchmarks illustrate this:

mysql> SELECT BENCHMARK(10000000, 1+1);
+--------------------------+
| benchmark(10000000, 1+1) |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (0.30 sec)

Now the equivalent inside a function:

mysql> CREATE FUNCTION f_one_plus_one() RETURNS INT RETURN 1+1;
mysql> SELECT BENCHMARK(10000000, f_one_plus_one());
+---------------------------------------+
| benchmark(10000000, f_one_plus_one()) |
+---------------------------------------+
|                                     0 |
+---------------------------------------+
1 row in set (28.73 sec)

Now you may be quick to include that performance of the function is 28.73 / 0.30 which is about 100 times slower, because there are some other factors to consider. My advice is o benchmark your particular code.

CURSORS

Another gotcha is cursors. They are slow, because they are materialized inside a temporary table when you open them. To get a taste of cursor performance vs pure SQL see http://rpbouman.blogspot.com/2006/09/refactoring-mysql-cursors.html

When looping through a cursor, you cannot update the cursor inline - it is read only, and forward only. Another 'gotcha' is not really a gotcha, but it is worth mentioning: you can only use standard ansi sql to control cursor looping. That syntax tends to be much more verbose than the equivalent syntax in MS SQL and Oracle (and perhaps other products). For cursor looping in MySQL see http://rpbouman.blogspot.com/2005/09/want-to-write-cursor-loop-with-mysql.html

PARAMETERS

You cannot specify a default value for stored routine parameters.

NO MODULE/PACKAGE

Stored routines are just that - the container is the database. You cannot package multiple related routines in a package or module.

These are the most imporatant things that come to my mind at the moment. I am sure there are more things you can consider a gotcha though.

---

Stored Procedure Parameters

If you're an ASP.NET developer like me, you'll get some baffling and WTF errors if you aren't careful to specify your parameters in the exact same order they appear in your MySQL procedure. (I blogged about it here)

Also, if you're an ASP.NET developer, you'll be surprised to learn that input variables are not prefixed with @ anymore like in MSSQL - because @ in MySQL indicates a user/session variable.

Storing SELECT Results into a variable

It's different than MSSQL and the syntax is quite difficult to find on the internet. You have only one way of doing it, like so :

SELECT MyTextColumn INTO myVariable FROM myTable WHERE ... ;

PRINT

Doesn't automatically infer that it needs to convert it's arguement variables into a string type, instead, it just throws you an error.

CURRENT_TIMESTAMP

If you want a column where the Date/Time is updated automatically, you can only do it with the TIMESTAMP type with a default column value of CURRENT_TIMESTAMP. The gotcha here is that DATETIME has no default column value functions like this. AND, you can only have one CURRENT_TIMESTAMP default value'd column per table.

Datatypes

One could write a novel about the gotcha's in MySQL datatypes, but here are a few of the most common ones:

• VARCHAR only stores up to 255 characters. TEXT is used for anything more.
• Date information is stored like YYYY-MM-DD (as opposed to MS's MM-DD-YYYY)
• No BOOLEAN datatype; BIT is used for storing data like 0100101
• VARCHAR(8) doesn't indicate a field with a text value up to 8 characters, but up to 8 bytes.
• INTEGER(8) works the same way.