« Table-Reference Restriction Lifted from Functions and Triggers in MySQL 5.0 | Main | Mastering Changes and Upgrades to Mission Critical Systems »

April 18, 2005

MySQL 5.0 In Depth - Stored Procedures, Views, Triggers and Cursors

The first session of the MySQL User's Conference (for me) is a tutorial on MySQL 5.0 features by Jan Kneschke, a MySQL trainer from Germany. Jan is going to be working on a new super-secret project called Merlin, being announced tomorrow. I've been writing a lot about the 5.0 features and am interested to see if there's anything I don't understand properly. Not attempting to get everything, just things I think might not be clear from the documentation on mysql.com.

Hmmm. Looks like 5.0.4 was released Saturday, will have to upgrade today.

A lot of MySQL users both program and manage the database. When moving into MySQL advanced features it becomes more important to have a person who just does the database stuff.

Stored Procedures

Stored Procedure Purpose

For handling business logic at the database level, reduce network traffic and have application code in the database. There are security benefits for putting data access behind a procedure.

Usage Scenario

A few things where stored procedures can be very helpful. Recursion, like in a tree, is a good use for keeping logic in the database.

Jan gets into a demonstration of recursion, and goes through the concept of a merge sort (break large set of data into smaller pieces, sort them, and then merge them together comparing the first items in each of the smaller pieces and stacking them up together).

Business logic abstration is also a good use, allows for strict control over access to the procedure as well as strict checking of input parameters.

Syntax

MySQL uses SQL:2003 standard for creating, managing and using stored procedures.

Stored Procedures and Stored Functions

External functions (UDFs) written in C have been available since MySQL 4.1. With C functions you can access everything in the system but not in the database. With internal functions, written in SQL, you cannot access the system but can interact with the data. Internal functions are available as of 5.0.

The main difference is that a procedure returns records, a function returns a single value.

The CREATE FUNCTION systax is used for both internal and external functions. If you're creating a UDF you include SONAME in the definition, if you're creating an internal procedure you leave out the SONAME syntax and put the statements in the body.

Neither stored procedures or functions are compiled in the database.

Cursors

Cursors are a pointer to a result-set. The FETCH gets data from the current row. In the case of recursion, the cursor can remain open while the procedure calls itself.

To get out of a cursor loop you need to DECLARE a HANDLER. You do not check to see if a FETCH fails, but let the HANDLER catch the NOT FOUND exception which sets a value that tells the loop it's done.

SP Example

Jan looks at a recursion procedure that does recursion to find a tree of data. The tutorial handout/booklet has printouts of the procedure, and all procedures from the presentation.

Issues with SPs

Procedures don't scale. If you have man complex operations in stored procedures they will add to MySQL becoming a bottleneck. Procedures are executed in the context of the database. Procedures should be focused on the data.

With replication, SP and trigger create statements aren't replicated. If the slave has the same SP and triggers the insert or update statements that trigger the trigger will run on the slave as they did on the master. The replication of the create statements will be fixed soon.

Errors in procedures are hard to find, there is no debugger. Procedures can cause dead-locks.

Stored procedures must be written internally in SQL, no support for external languages. There is hope that down the road there will be support.

Currently you cannot generate a SQL error from within your stored procedure. What I've done is use an INOUT parameter @error that the application must check before moving on.

As of 5.0.4, there is no limit on the number of cursors that can be opened. If you go out of control on a recursion, or have a lot of data to recurse over your MySQL server will happily open new cursors until the database server crashes because there's not enough resources.

Lock Problems with SP

Locks are taken when the function is called. So if you have a function that does a SELECT, a read lock is obtained. But if you call a function within that to do an INSERT, you have the wrong lock on the table.

The locking issues, found by Jan recently, will be fixed soon.

Triggers

Triggers, for Jan, are kind of derivative of a SP. They use the same syntax for the body, but are created with language to tell the database when they should be run automatically. Jan looks at an example where when an email address is inserted, a trigger reverses the address for indexing purposes.

Currently there is no way to see if a trigger applies to a statement sent to the database. One would think that there would be something like EXPLAIN that would show that a part of the statement includes running a trigger.

Don't use triggers just for fun, or for something complex. Jan suggests that a trigger should not be used for generating content in the database, for putting business logic into the database or for calling SQL statements that take a long time to complete. Triggers should be used for small data integrity checks or changes.

A rollback will work even if a trigger has been involved in the update.

Views

Are excellent for limiting visibility of columns in JOINs or limiting to certain rows. Also good for hiding new tables structures from legacy applications.

Views are created with a SELECT statement, which can get data from tables or views. Permissions can be granted separately for a view, which gives you ability to easily grant on columns across tables.

Using ORDER BY in a SELECT against a view will replace an ORDER BY that was.

Algorithm in Views

Merge allow is more efficient and updatability. It merges the CREATE VIEW statement with the SELECT view statement and executes (essentially creates a new columns and where clause based on the two statements). Merge doesn't work with functions and certain keywords (DISTINCT, GROUP BY, HAVING). In these cases a TEMPTABLE is used.

Views are Updatable

The algorithm must be MERGE, cannot have JOINs or subqueries and cannot have an updatable view in the FROM clause. In addition, an insert can happen only if there are no duplicate view column names and the column names are pure (no database prefix).

Actually, if the update only affects one of the tables in a join, it can be updated.

Posted by mike at April 18, 2005 11:55 AM