MySQL Stored Procedure Custom Error Handling
When using MySQL Stored Procedures in your application, it is important to be able to return multiple messages to the caller. MySQL will take care of all the directly database related errors, such as table not found, but for user errors, there is not much built in for that. An example of this would be on user login, user name does not exist and/or password is incorrect. Returning one of these could be done with a select statement, but when you want to return both errors, it is not as simple. The following is the system I came up with for our applications.
Within the Stored Procedure, here are the steps from a high level with a short explanation.
1. Create a temporary table to hold errors. Temporary tables have session scope, and if you design the stored procedures correctly, there will be one call per session. This essentially makes this table “local” to the stored procedure. The column(s) can be whatever you want, but currently, I store errors as INTs which can then be interpreted by the caller. This makes multilingual platform errors easier to output and takes message maintenance away from the database. With most databases not on the same server as the caller, this minimizes the amount of data transferred across the bottleneck of the internet.
2. Process the passed parameters and run all checks needed. Anything that is incorrect, insert the corresponding error into the table.
3. At this point, if there was any errors with the parameters passed or the call in general, they should all be in the table. See #01 below to see how I handle the exception. Get a count from the errors table. If 0, then continue with processing, otherwise, select all from errors table and LEAVE the SP or have this as the last executable statement.
Here is an example for the user login functionality:
CREATE PROCEDURE userLogin( _email VARCHAR(50), _password VARCHAR(30) ) MODIFIES SQL DATA SQL SECURITY DEFINER BEGIN BEGIN DROP TABLE IF EXISTS errors;CREATE TEMPORARY TABLE errors(error INT)ENGINE=MEMORY; END; BEGIN DECLARE hasErrors BOOLEAN DEFAULT FALSE; ...declarations... SELECT TRUE, (password = hashThis(_password)) INTO emailExists, usingCorrectPassword FROM users WHERE email = _email; IF _email IS NULL || CHAR_LENGTH(_email) = 0 THEN INSERT INTO errors VALUES(1); ELSEIF !emailExists THEN INSERT INTO errors VALUES(2); END IF; IF _password IS NULL || CHAR_LENGTH(_password) = 0 THEN INSERT INTO errors VALUES(3); ELSEIF !usingCorrectPassword THEN INSERT INTO errors VALUES(4); END IF; SELECT COUNT(*) > 0 INTO hasErrors FROM errors; IF hasErrors THEN SELECT * FROM errors; ELSE ...Log user in... END IF; END; END~
The errors table can be changed if your application would do better returning a string, or to multiple columns if needed. I think this is a very flexible solution. This will prevent your application from making many calls per error. One call can return many errors, which will give a better user experience in the end. Next week I will get into some scripts I wrote to make connecting, creating, and modifying our databases simpler.
#01 :: There are some Stored Procedures which do much more processing than a login, and will not know if they will complete as early as this one. For this, you may need to get errors after checking everything, after processing partially. To accomplish this, you must understand the functionality of the COMMIT, ROLLBACK, and LABEL in MySQL. Once you understand how this works, you can put something like the following in the processing section of your code. You will need a label at the outermost BEGIN (another thing to look up) and you will need to turn AUTOCOMMIT OFF.
ROLLBACK; INSERT INTO errors VALUES(82, 5); SELECT * FROM errors; LEAVE sp;
This will undo all the processing you have done, report the error, and exit the Stored Procedure (MySQL please make EXIT functionality).