Mysql procedure return error - Исправление ошибок и поиск оптимальных решений проблем

Summary: in this tutorial, you will learn how to use MySQL handler to handle errors encountered in stored procedures.

When an error occurs inside a stored procedure, it is important to handle it appropriately, such as continuing or exiting the current code block’s execution, and issuing a meaningful error message.

MySQL provides an easy way to define handlers that handle from general conditions such as warnings or exceptions to specific conditions e.g., specific error codes.

Declaring a handler

To declare a handler, you use the DECLARE HANDLER statement as follows:

DECLARE action HANDLER FOR condition_value statement;
Code language: SQL (Structured Query Language) (sql)

If a condition whose value matches the condition_value , MySQL will execute the statement and continue or exit the current code block based on the action .

The action accepts one of the following values:

CONTINUE : the execution of the enclosing code block ( BEGIN … END ) continues.
EXIT : the execution of the enclosing code block, where the handler is declared, terminates.

The condition_value specifies a particular condition or a class of conditions that activate the handler. The condition_value accepts one of the following values:

A MySQL error code.
A standard SQLSTATE value. Or it can be an SQLWARNING , NOTFOUND or SQLEXCEPTION condition, which is shorthand for the class of SQLSTATE values. The NOTFOUND condition is used for a cursor or SELECT INTO variable_list statement.
A named condition associated with either a MySQL error code or SQLSTATE value.

The statement could be a simple statement or a compound statement enclosing by the BEGIN and END keywords.

MySQL error handling examples

Let’s take some examples of declaring handlers.

The following handler set the value of the hasError variable to 1 and continue the execution if an SQLEXCEPTION occurs

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 
SET hasError = 1;
Code language: SQL (Structured Query Language) (sql)

The following handler rolls back the previous operations, issues an error message, and exit the current code block in case an error occurs. If you declare it inside the BEGIN END block of a stored procedure, it will terminate the stored procedure immediately.

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    ROLLBACK;
    SELECT 'An error has occurred, operation rollbacked and the stored procedure was terminated';
END;



  


                       
  



Code language: SQL (Structured Query Language) (sql)

The following handler sets the value of the RowNotFound variable to 1 and continues execution if there is no more row to fetch in case of a cursor or SELECT INTO statement:

DECLARE CONTINUE HANDLER FOR NOT FOUND 
SET RowNotFound = 1;
Code language: SQL (Structured Query Language) (sql)

If a duplicate key error occurs, the following handler issues an error message and continues execution.

DECLARE CONTINUE HANDLER FOR 1062
SELECT 'Error, duplicate key occurred';
Code language: SQL (Structured Query Language) (sql)

MySQL handler example in stored procedures

First, create a new table named SupplierProductsfor the demonstration:

CREATE TABLE SupplierProducts (
    supplierId INT,
    productId INT,
    PRIMARY KEY (supplierId , productId)
);
Code language: SQL (Structured Query Language) (sql)

The table SupplierProducts stores the relationships between the table suppliers and products. Each supplier may provide many products and each product can be provided by many suppliers. For the sake of simplicity, we don’t create Products and Suppliers tables, as well as the foreign keys in the SupplierProducts table.

Second, create a stored procedure that inserts product id and supplier id into the SupplierProducts table:

CREATE PROCEDURE InsertSupplierProduct(
    IN inSupplierId INT, 
    IN inProductId INT
)
BEGIN
    -- exit if the duplicate key occurs
    DECLARE EXIT HANDLER FOR 1062
    BEGIN
 	SELECT CONCAT('Duplicate key (',inSupplierId,',',inProductId,') occurred') AS message;
    END;
    
    -- insert a new row into the SupplierProducts
    INSERT INTO SupplierProducts(supplierId,productId)
    VALUES(inSupplierId,inProductId);
    
    -- return the products supplied by the supplier id
    SELECT COUNT(*) 
    FROM SupplierProducts
    WHERE supplierId = inSupplierId;
    
END$$

DELIMITER ;
Code language: SQL (Structured Query Language) (sql)

How it works.

The following exit handler terminates the stored procedure whenever a duplicate key occurs (with code 1062). In addition, it returns an error message.

DECLARE EXIT HANDLER FOR 1062
BEGIN
    SELECT CONCAT('Duplicate key (',supplierId,',',productId,') occurred') AS message;
END;
Code language: SQL (Structured Query Language) (sql)

This statement inserts a row into the SupplierProducts table. If a duplicate key occurs, the code in the handler section will execute.

INSERT INTO SupplierProducts(supplierId,productId) 
VALUES(supplierId,productId);
Code language: SQL (Structured Query Language) (sql)

Third, call the InsertSupplierProduct() to insert some rows into the SupplierProducts table:

CALL InsertSupplierProduct(1,1);
CALL InsertSupplierProduct(1,2);
CALL InsertSupplierProduct(1,3);
Code language: SQL (Structured Query Language) (sql)

Fourth, attempt to insert a row whose values already exist in the SupplierProducts table:

CALL InsertSupplierProduct(1,3);
Code language: SQL (Structured Query Language) (sql)

Here is the error message:

+------------------------------+
| message                      |
+------------------------------+
| Duplicate key (1,3) occurred |
+------------------------------+
1 row in set (0.01 sec)
Code language: JavaScript (javascript)

Because the handler is an EXIT handler, the last statement does not execute:

SELECT COUNT(*) 
FROM SupplierProducts
WHERE supplierId = inSupplierId;
Code language: SQL (Structured Query Language) (sql)

If you change the EXIT in the handler declaration to CONTINUE , you will also get the number of products provided by the supplier:

DROP PROCEDURE IF EXISTS InsertSupplierProduct;

DELIMITER $$

CREATE PROCEDURE InsertSupplierProduct(
    IN inSupplierId INT, 
    IN inProductId INT
)
BEGIN
    -- exit if the duplicate key occurs
    DECLARE CONTINUE HANDLER FOR 1062
    BEGIN
	SELECT CONCAT('Duplicate key (',inSupplierId,',',inProductId,') occurred') AS message;
    END;
    
    -- insert a new row into the SupplierProducts
    INSERT INTO SupplierProducts(supplierId,productId)
    VALUES(inSupplierId,inProductId);
    
    -- return the products supplied by the supplier id
    SELECT COUNT(*) 
    FROM SupplierProducts
    WHERE supplierId = inSupplierId;
    
END$$

DELIMITER ;
Code language: SQL (Structured Query Language) (sql)

Finally, call the stored procedure again to see the effect of the CONTINUE handler:

CALL InsertSupplierProduct(1,3);
Code language: SQL (Structured Query Language) (sql)

Here is the output:

+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.02 sec)
Code language: SQL (Structured Query Language) (sql)

MySQL handler precedence

In case you have multiple handlers that handle the same error, MySQL will call the most specific handler to handle the error first based on the following rules:

An error always maps to a MySQL error code because in MySQL it is the most specific.
An SQLSTATE may map to many MySQL error codes, therefore, it is less specific.
An SQLEXCPETION or an SQLWARNING is the shorthand for a class of SQLSTATES values so it is the most generic.

Based on the handler precedence rules, MySQL error code handler, SQLSTATE handler and SQLEXCEPTION takes the first, second and third precedence.

Suppose that we have three handlers in the handlers in the stored procedure insert_article_tags_3 :

DROP PROCEDURE IF EXISTS InsertSupplierProduct;

DELIMITER $$

CREATE PROCEDURE InsertSupplierProduct(
    IN inSupplierId INT, 
    IN inProductId INT
)
BEGIN
    -- exit if the duplicate key occurs
    DECLARE EXIT HANDLER FOR 1062 SELECT 'Duplicate keys error encountered' Message; 
    DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT 'SQLException encountered' Message; 
    DECLARE EXIT HANDLER FOR SQLSTATE '23000' SELECT 'SQLSTATE 23000' ErrorCode;
    
    -- insert a new row into the SupplierProducts
    INSERT INTO SupplierProducts(supplierId,productId)
    VALUES(inSupplierId,inProductId);
    
    -- return the products supplied by the supplier id
    SELECT COUNT(*) 
    FROM SupplierProducts
    WHERE supplierId = inSupplierId;
    
END$$

DELIMITER ;
Code language: SQL (Structured Query Language) (sql)

Call the stored procedure to insert a duplicate key:

CALL InsertSupplierProduct(1,3);
Code language: SQL (Structured Query Language) (sql)

Here is the output:

+----------------------------------+
| Message                          |
+----------------------------------+
| Duplicate keys error encountered |
+----------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)
Code language: JavaScript (javascript)

As you see the MySQL error code handler is called.

Using a named error condition

Let’s start with an error handler declaration.

DELIMITER $$

CREATE PROCEDURE TestProc()
BEGIN

    DECLARE EXIT HANDLER FOR 1146 
    SELECT 'Please create table abc first' Message; 
        
    SELECT * FROM abc;
END$$

DELIMITER ;
Code language: SQL (Structured Query Language) (sql)

What does the number 1146 really mean? Imagine you have stored procedures polluted with these numbers all over places; it will be difficult to understand and maintain the code.

Fortunately, MySQL provides you with the DECLARE CONDITION statement that declares a named error condition, which associates with a condition.

Here is the syntax of the DECLARE CONDITION statement:

DECLARE condition_name CONDITION FOR condition_value;
Code language: SQL (Structured Query Language) (sql)

The condition_value can be a MySQL error code such as 1146 or a SQLSTATE value. The condition_value is represented by the condition_name .

After the declaration, you can refer to condition_name instead of condition_value .

So you can rewrite the code above as follows:

DROP PROCEDURE IF EXISTS TestProc;

DELIMITER $$

CREATE PROCEDURE TestProc()
BEGIN
    DECLARE TableNotFound CONDITION for 1146 ; 

    DECLARE EXIT HANDLER FOR TableNotFound 
	SELECT 'Please create table abc first' Message; 
    SELECT * FROM abc;
END$$

DELIMITER ;
Code language: SQL (Structured Query Language) (sql)

As you can see, the code is more obviously and readable than the previous one. Notice that the condition declaration must appear before handler or cursor declarations.

In this tutorial, you have learned how to use MySQL handlers to handle exception or errors occurred in stored procedures.

Was this tutorial helpful?

Источник

Содержание

Обработка ошибок MySQL в хранимых процедурах
Объявление обработчика
Примеры обработки ошибок MySQL
Пример обработчика MySQL в хранимых процедурах
Приоритет обработчиков MySQL
Использование проименованных условий ошибки
mysqli::$error
Description
Parameters
Return Values
Examples
See Also
User Contributed Notes 7 notes
MySQL Error Handling
Please visit other related articles.

Обработка ошибок MySQL в хранимых процедурах

В этой статье мы расскажем вам, как использовать обработчик MySQL для обработки исключений или ошибок, возникающих в хранимых процедурах.

При возникновении ошибки внутри хранимой процедуры, важно исправить ее соответствующим образом, например, продолжая или останавливая исполнение операции и выдавая сообщение об ошибке.

MySQL предоставляет простой способ определить обработчики, которые обрабатывают ошибки, исходя из общих условий, таких как предупреждения или исключения из условий, например, конкретные коды ошибок.

Объявление обработчика

Чтобы объявить обработчик мы используем оператор DECLARE HANDLER :

Если значение условия совпадает со значением condition_value , MySQL выполнит оператор statement и продолжит или завершит текущий блок кода, исходя из значения action .

action может принимать следующие значения:

CONTINUE : исполнение блокированного кода (BEGIN … END) продолжается;
EXIT : выполнение блокированного кода, в котором был объявлен обработчик, завершается.

condition_value задает конкретное условие или класс условия, которые активируют обработчик.

condition_value может принимать одно из следующих значений:

код ошибки MySQL ;
стандартное значение SQLSTATE . Или это может быть условие SQLWARNING , NOTFOUND или SQLEXCEPTION , которое является сокращением для класса значений SQLSTATE . Условие NOTFOUND используется для курсора или оператора SELECT INTO variable_list ;
название условия, связанного либо с кодом ошибки MySQL , либо со значением SQLSTATE .

В качестве statement может использоваться простой оператор или составной оператор, вшитый с помощью ключевых слов BEGIN и END .

Примеры обработки ошибок MySQL

Давайте рассмотрим несколько примеров объявления обработчиков.

Обработчик, приведенный ниже, означает: когда происходит ошибка, устанавливается значение переменной has_error 1 и выполнение продолжается:

Ниже приводится другой обработчик, который означает, что в случае возникновении любой ошибки, производится откат предыдущей операции, выдается сообщение об ошибке и осуществляется выход из текущего блока кода.

Если вы объявляете его внутри блока BEGIN END хранимой процедуры, он немедленно завершает хранимую процедуру:

Если строк для вывода больше нет, для вариантов cursor или оператора SELECT INTO , значение переменной no_row_found устанавливается равным 1 и продолжается исполнение:

При возникновении ошибки дублирования ключа, выдается ошибка MySQL 1062 . Следующий обработчик выдает сообщение об ошибке и продолжает выполнение:

Пример обработчика MySQL в хранимых процедурах

Во-первых , для демонстрации мы создаем новую таблицу с именем article_tags :

В таблице article_tags хранятся связи между статьями и тегами. К каждой статье может относиться несколько тегов и наоборот.

Для простоты, мы не будем создавать таблицы articles и tags , а также внешние ключи в таблице article_tags .

Во-вторых , мы создаем хранимую процедуру, которая вставляет пару идентификаторов статьи и тега в таблицу article_tags :

В-третьих , для статьи 1 мы добавляем идентификаторы тега 1, 2 и 3, с помощью вызова хранимой процедуры insert_article_tags :

Четвертое . Давайте попробуем вставить дубликат ключа, чтобы увидеть, действительно ли вызывается обработчик:

Мы получили сообщение об ошибке. Однако, поскольку мы объявили тип обработчика CONTINUE , хранимая процедура продолжает исполняться.

В результате, мы все равно получили список тегов для статьи:

Если мы в объявлении обработчика изменим команду CONTINUE на EXIT , мы получим только сообщение об ошибке:

Теперь, мы можем попробовать добавить дубликат ключа, чтобы увидеть результат:

Приоритет обработчиков MySQL

В случае если у вас есть несколько обработчиков, которые имеют право обрабатывать ошибку, MySQL для обработки ошибки будет вызывать наиболее подходящий обработчик.

Ошибка всегда обозначается одним из кодов ошибки MySQL , так что MySQL в этом плане имеет возможность четко их идентифицировать.

Обозначения SQLSTATE для многих кодов ошибок MySQL менее специфичны. SQLEXCPETION или SQLWARNING представляют собой сокращения класса значений SQLSTATES , поэтому они имеют общий характер.

На основании правил приоритета обработчиков обработчик кода ошибки MySQL , обработчик SQLSTATE и обработчик SQLEXCEPTION имеют приоритеты один, два и три соответственно.

Предположим, что в хранимой процедуре insert_article_tags_3 мы объявляем три обработчика:

Теперь мы пробуем добавить в таблицу article_tags дубликат ключа через вызов хранимой процедуры:

Как видите, вызывается обработчик кода ошибки MySQL :

Использование проименованных условий ошибки

Начинаем с объявления обработчика ошибки:

Что означает код 1051 ? Представьте, что у вас есть большая хранимая процедура, по всему коду которой разбросаны некорректные значения. Настоящий кошмар для разработчиков обслуживания.

К счастью, MySQL предоставляет нам оператор DECLARE CONDITION , который объявляет проименованное условие ошибки, связанное с условием.

Синтаксис оператора DECLARE CONDITION выглядит следующим образом:

condition_value может представлять собой код ошибки MySQL , например 1015 , или значение SQLSTATE . condition_value представляется с помощью condition_name .

После объявления вы можете обращаться к condition_name вместо condition_value .

Таким образом, мы можем переписать код, приведенный выше, следующим образом:

Этот код, очевидно, более удобен для чтения, нежели предыдущий. Отметим, что объявление условия должно размещаться перед объявлением обработчика или объявлением курсора.

Сергей Бензенко автор-переводчик статьи « MySQL Error Handling in Stored Procedures »

Источник

mysqli::$error

(PHP 5, PHP 7, PHP

mysqli::$error — mysqli_error — Returns a string description of the last error

Description

Returns the last error message for the most recent MySQLi function call that can succeed or fail.

Parameters

Procedural style only: A mysqli object returned by mysqli_connect() or mysqli_init()

Return Values

A string that describes the error. An empty string if no error occurred.

Examples

Example #1 $mysqli->error example

= new mysqli ( «localhost» , «my_user» , «my_password» , «world» );

/* check connection */
if ( $mysqli -> connect_errno ) <
printf ( «Connect failed: %sn» , $mysqli -> connect_error );
exit();
>

if (! $mysqli -> query ( «SET a=1» )) <
printf ( «Error message: %sn» , $mysqli -> error );
>

/* close connection */
$mysqli -> close ();
?>

= mysqli_connect ( «localhost» , «my_user» , «my_password» , «world» );

/* check connection */
if ( mysqli_connect_errno ()) <
printf ( «Connect failed: %sn» , mysqli_connect_error ());
exit();
>

if (! mysqli_query ( $link , «SET a=1» )) <
printf ( «Error message: %sn» , mysqli_error ( $link ));
>

/* close connection */
mysqli_close ( $link );
?>

The above examples will output:

User Contributed Notes 7 notes

The mysqli_sql_exception class is not available to PHP 5.05

I used this code to catch errors
= «SELECT XXname FROM customer_table » ;
$res = $mysqli -> query ( $query );

if (! $res ) <
printf ( «Errormessage: %sn» , $mysqli -> error );
>

?>
The problem with this is that valid values for $res are: a mysqli_result object , true or false
This doesn’t tell us that there has been an error with the sql used.
If you pass an update statement, false is a valid result if the update fails.

So, a better way is:
= «SELECT XXname FROM customer_table » ;
$res = $mysqli -> query ( $query );

if (! $mysqli -> error ) <
printf ( «Errormessage: %sn» , $mysqli -> error );
>

This would output something like:
Unexpected PHP error [mysqli::query() [function.query]: (42S22/1054): Unknown column ‘XXname’ in ‘field list’] severity [E_WARNING] in [G:database.php] line [249]

Very frustrating as I wanted to also catch the sql error and print out the stack trace.

A better way is:

( MYSQLI_REPORT_OFF ); //Turn off irritating default messages

$mysqli = new mysqli ( «localhost» , «my_user» , «my_password» , «world» );

$query = «SELECT XXname FROM customer_table » ;
$res = $mysqli -> query ( $query );

if ( $mysqli -> error ) <
try <
throw new Exception ( «MySQL error $mysqli -> error
Query:
$query » , $msqli -> errno );
> catch( Exception $e ) <
echo «Error No: » . $e -> getCode (). » — » . $e -> getMessage () . «
» ;
echo nl2br ( $e -> getTraceAsString ());
>
>

//Do stuff with the result
?>
Prints out something like:
Error No: 1054
Unknown column ‘XXname’ in ‘field list’
Query:
SELECT XXname FROM customer_table

#0 G:\database.php(251): database->dbError(‘Unknown column . ‘, 1054, ‘getQuery()’, ‘SELECT XXname F. ‘)
#1 G:dataWorkSites1framework5testsdbtest.php(29): database->getString(‘SELECT XXname F. ‘)
#2 c:PHPincludessimpletestrunner.php(58): testOfDB->testGetVal()
#3 c:PHPincludessimpletestrunner.php(96): SimpleInvoker->invoke(‘testGetVal’)
#4 c:PHPincludessimpletestrunner.php(125): SimpleInvokerDecorator->invoke(‘testGetVal’)
#5 c:PHPincludessimpletestrunner.php(183): SimpleErrorTrappingInvoker->invoke(‘testGetVal’)
#6 c:PHPincludessimpletestsimple_test.php(90): SimpleRunner->run()
#7 c:PHPincludessimpletestsimple_test.php(498): SimpleTestCase->run(Object(HtmlReporter))
#8 c:PHPincludessimpletestsimple_test.php(500): GroupTest->run(Object(HtmlReporter))
#9 G:all_tests.php(16): GroupTest->run(Object(HtmlReporter))

This will actually print out the error, a stack trace and the offending sql statement. Much more helpful when the sql statement is generated somewhere else in the code.

The decription «mysqli_error — Returns a string description of the LAST error» is not exactly that what you get from mysqli_error. You get the error description from the last mysqli-function, not from the last mysql-error.

If you have the following situation

if (!$mysqli->query(«SET a=1»)) <
$mysqli->query(«ROLLBACK;»)
printf(«Errormessage: %sn», $mysqli->error);
>

you don’t get an error-message, if the ROLLBACK-Query didn’t failed, too. In order to get the right error-message you have to write:

if (!$mysqli->query(«SET a=1»)) <
printf(«Errormessage: %sn», $mysqli->error);
$mysqli->query(«ROLLBACK;»)
>

I had to set mysqli_report(MYSQLI_REPORT_ALL) at the begin of my script to be able to catch mysqli errors within the catch block of my php code.

Initially, I used the below code to throw and subsequent catch mysqli exceptions

try <
$mysqli = new mysqli ( ‘localhost’ , ‘root’ , ‘pwd’ , ‘db’ );
if ( $mysqli -> connect_errno )
throw new Exception ( $mysqli -> connect_error );

> catch ( Exception $e ) <
echo $e -> getMessage ();
>

I realized the exception was being thrown before the actual throw statement and hence the catch block was not being called .

My current code looks like
mysqli_report ( MYSQLI_REPORT_ALL ) ;
try <
$mysqli = new mysqli ( ‘localhost’ , ‘root’ , ‘pwd’ , ‘db’ );
/* I don’t need to throw the exception, it’s being thrown automatically */

> catch ( Exception $e ) <
echo $e -> getMessage ();
>

This works fine and I ‘m able to trap all mysqli errors

// The idea is the add formated errors information for developers to easier bugs detection.

$myfile = fopen ( «database_log.log» , «r» );
$db = new mysqli ( «localhost» , «root» , «root» , «data» );
if(! $db -> query ( «SELECT» )) <
$timestamp = new DateTime ();
$data_err = » <
»title»: » Select statement error »,
»date_time»: » . $timestamp -> getTimestamp (). «,
»error»:» » . $db -> error . » »
> » ; // Do more information
fwrite ( $myfile , $data_err ); // writing data
>
// In separate file do file read and format it for good visual.

$db -> close ();
fclose ( $myfile );
?>

Please note that the string returned may contain data initially provided by the user, possibly making your code vulnerable to XSS.

So even if you escape everything in your SQL query using mysqli_real_escape_string(), make sure that if you plan to display the string returned by mysqli_error() you run that string through htmlspecialchars().

As far as I can tell the two escape functions don’t escape the same characters, which is why you need both (the first for SQL and the second for HTML/JS).

Hi, you can also use the new mysqli_sql_exception to catch sql errors.
Example:
//set up $mysqli_instance here..
$Select = «SELECT xyz FROM mytable » ;
try <
$res = $mysqli_instance -> query ( $Select );
>catch ( mysqli_sql_exception $e ) <
print «Error Code
» . $e -> getCode ();
print «Error Message
» . $e -> getMessage ();
print «Strack Trace
» . nl2br ( $e -> getTraceAsString ());
>

Источник

MySQL Error Handling

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

In this post, I am sharing the full demonstration on how to manage error/exception handling in the Stored Procedure of MySQL.

Whenever an exception is occurring in a stored procedure, it is very important to handle it by showing proper error messages.

If you do not handle the exception, there would be a chance to fail application with the certain exception in a stored procedure.

If you get an error in stored procedure, instead of an exit, you should continue without any error message. That means you can show any default or custom error code or message to the application/user.

MySQL provides Handler to handle the exception in the stored procedure.

Below is a full demonstration of a handler with examples:

How to declare handler in store procedure:

Three type of Handler_Action:

Type of Condition Value:

mysql_error_code
sqlstate_value
SQLWarning
SQLException
NotFound

How to write handler in stored procedure?

The Above are four different handler examples. Now, I am going to insert a duplicate value into EmpID column.

In the above SP, I defined a CONTINUE handler with my custom exception message.

Now, call the above SP two times with same EmpID.

The first time, it will execute successfully, but the second time it will throw a custom error message.

As we defined CONTINUE handler, so it will just show an error message and CONTINUE to next part of the SELECT statement.

Above are the two different calls with same EmpID value. The first call executes without any error message and the second call execute with an error message.

The resule of Second Call:

As we defined CONTINUE, so you can find two results in above image. One is our custom error message and second is the result of the defined SELECT statement.
The execution didn’t stop by error, and it continued for another part.

Now, check the EXIT handler:

Please modify your handler and replace CONTINUE by EXIT:

Call with the same parameter:

The Result is an only error message, and you cannot find two results as we defined EXIT to exit the code when an error occurred.

The best practice is to create a output parameter and store 1 if any error occurred.

Application code has to check this output parameter is NULL or 1.

Below is a stored procedure for this:

Now call the above SP and select output parameter:

Now Results are:

Above is a simple demonstration of Error Handling in MySQL. You can also use SQLSTATE which shows default error messages of MySQL.

Please visit other related articles.

hi Avnesh i am struggling with DEFINER in stored procedure can you suggest some better idea
like DEFINER is = root @ % then i am unable to execute this stored procedure from my local mysql db.

Remove it. Its default added in your stored procedure.

Hi Anvesh,
I am beginner to MYSQL. Could you please throw more light in details on this exception Handling Part? If you have any detailed version of this Please reply with the URL of that article as well.
Many Thanks in Advance.
Regards
Shubhada

Sure, I will update this article and will share few other links.

I am using error handling in my stored proc and it works fine. the results show me only 1 field, the error.

What i am trying to do is call the proc into a reporting software (pentaho). when i call the proc in pentaho it only shows me the error field.

is there i way i can have all the fields i want show up blank and the error message if there is an error?

You can replace that 1 by NULL.

It is a well written article and you have managed to explain a fairly complex feature in simple terms! thanks. it was useful to me.

HI, Below query shows me “No data: 1329 No data – zero rows fetched, selected, or processed”. Cant fix it can u help me.
BEGIN
DECLARE from_date DATETIME;
DECLARE to_date DATETIME;
DECLARE hostname VARCHAR(50);
DECLARE servicename VARCHAR(50);
DECLARE running_no INT;
DECLARE loop_no INT;
DECLARE value1 FLOAT;
DECLARE value2 FLOAT;
DECLARE value3 FLOAT;
DECLARE state INT;
DECLARE logdate DATETIME;
DECLARE prev_status INT;
DECLARE c1len INT;
DECLARE c2len INT;
DECLARE c2flag INT;
DECLARE IsError INT;
DECLARE c1 CURSOR FOR SELECT host_name as host,service_name as service FROM cmp_warehouse.tbl_health_events_csv GROUP BY host_name,service_name ORDER BY host_name, service_name;

SET running_no = 1;
OPEN c1;
set c1len = (SELECT FOUND_ROWS());
l_c1:LOOP

Источник

Объявление обработчика
Примеры обработки ошибок MySQL
Пример обработчика MySQL в хранимых процедурах
- Приоритет обработчиков MySQL
- Использование проименованных условий ошибки

Чтобы объявить обработчик мы используем оператор DECLARE HANDLER:

DECLARE action HANDLER FOR condition_value statement;

Если значение условия совпадает со значением condition_value, MySQL выполнит оператор statement и продолжит или завершит текущий блок кода, исходя из значения action.

action может принимать следующие значения:

CONTINUE: исполнение блокированного кода (BEGIN … END) продолжается;
EXIT: выполнение блокированного кода, в котором был объявлен обработчик, завершается.

condition_value задает конкретное условие или класс условия, которые активируют обработчик.

condition_value может принимать одно из следующих значений:

код ошибки MySQL;
стандартное значение SQLSTATE. Или это может быть условие SQLWARNING, NOTFOUND или SQLEXCEPTION, которое является сокращением для класса значений SQLSTATE. Условие NOTFOUND используется для курсора или оператора SELECT INTO variable_list;
название условия, связанного либо с кодом ошибки MySQL, либо со значением SQLSTATE.

Давайте рассмотрим несколько примеров объявления обработчиков.

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET has_error = 1;

Если вы объявляете его внутри блока BEGIN END хранимой процедуры, он немедленно завершает хранимую процедуру:

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SELECT 'An error has occurred, operation rollbacked and the stored procedure was terminated';
END;

Если строк для вывода больше нет, для вариантов cursor или оператора SELECT INTO, значение переменной no_row_found устанавливается равным 1 и продолжается исполнение:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_row_found = 1;

При возникновении ошибки дублирования ключа, выдается ошибка MySQL 1062. Следующий обработчик выдает сообщение об ошибке и продолжает выполнение:

DECLARE CONTINUE HANDLER FOR 1062
SELECT 'Error, duplicate key occurred';

Во-первых, для демонстрации мы создаем новую таблицу с именем article_tags:

CREATE TABLE article_tags(
    article_id INT,
    tag_id     INT,
    PRIMARY KEY(article_id,tag_id)
);

Для простоты, мы не будем создавать таблицы articles и tags, а также внешние ключи в таблице article_tags.

Во-вторых, мы создаем хранимую процедуру, которая вставляет пару идентификаторов статьи и тега в таблицу article_tags:

DELIMITER $$
 
CREATE PROCEDURE insert_article_tags(IN article_id INT, IN tag_id INT)
BEGIN
 
    DECLARE CONTINUE HANDLER FOR 1062
    SELECT CONCAT('duplicate keys (',article_id,',',tag_id,') found') AS msg;
 
    -- insert a new record into article_tags
    INSERT INTO article_tags(article_id,tag_id)
    VALUES(article_id,tag_id);
 
    -- return tag count for the article
    SELECT COUNT(*) FROM article_tags;
END

В-третьих, для статьи 1 мы добавляем идентификаторы тега 1, 2 и 3, с помощью вызова хранимой процедуры insert_article_tags:

CALL insert_article_tags(1,1);
CALL insert_article_tags(1,2);
CALL insert_article_tags(1,3);

Четвертое. Давайте попробуем вставить дубликат ключа, чтобы увидеть, действительно ли вызывается обработчик:

CALL insert_article_tags(1,3);

Мы получили сообщение об ошибке. Однако, поскольку мы объявили тип обработчика CONTINUE, хранимая процедура продолжает исполняться.

В результате, мы все равно получили список тегов для статьи:

Если мы в объявлении обработчика изменим команду CONTINUE на EXIT, мы получим только сообщение об ошибке:

DELIMITER $$
 
CREATE PROCEDURE insert_article_tags_2(IN article_id INT, IN tag_id INT)
BEGIN
 
    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    SELECT 'SQLException invoked';
 
    DECLARE EXIT HANDLER FOR 1062 
        SELECT 'MySQL error code 1062 invoked';
 
    DECLARE EXIT HANDLER FOR SQLSTATE '23000'
    SELECT 'SQLSTATE 23000 invoked';
 
    -- insert a new record into article_tags
    INSERT INTO article_tags(article_id,tag_id)
       VALUES(article_id,tag_id);
 
    -- return tag count for the article
    SELECT COUNT(*) FROM article_tags;
END

Теперь, мы можем попробовать добавить дубликат ключа, чтобы увидеть результат:

CALL insert_article_tags_2(1,3);

Ошибка всегда обозначается одним из кодов ошибки MySQL, так что MySQL в этом плане имеет возможность четко их идентифицировать.

Обозначения SQLSTATE для многих кодов ошибок MySQL менее специфичны. SQLEXCPETION или SQLWARNING представляют собой сокращения класса значений SQLSTATES, поэтому они имеют общий характер.

На основании правил приоритета обработчиков обработчик кода ошибки MySQL, обработчик SQLSTATE и обработчик SQLEXCEPTION имеют приоритеты один, два и три соответственно.

Предположим, что в хранимой процедуре insert_article_tags_3 мы объявляем три обработчика:

DELIMITER $$
 
CREATE PROCEDURE insert_article_tags_3(IN article_id INT, IN tag_id INT)
BEGIN
 
    DECLARE EXIT HANDLER FOR 1062 SELECT 'Duplicate keys error encountered';
    DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT 'SQLException encountered';
    DECLARE EXIT HANDLER FOR SQLSTATE '23000' SELECT 'SQLSTATE 23000';
 
    -- insert a new record into article_tags
    INSERT INTO article_tags(article_id,tag_id)
    VALUES(article_id,tag_id);
 
    -- return tag count for the article
    SELECT COUNT(*) FROM article_tags;
END

Теперь мы пробуем добавить в таблицу article_tags дубликат ключа через вызов хранимой процедуры:

CALL insert_article_tags_3(1,3);

Как видите, вызывается обработчик кода ошибки MySQL:

Начинаем с объявления обработчика ошибки:

DECLARE EXIT HANDLER FOR 1051 SELECT 'Please create table abc first';
SELECT * FROM abc;

Что означает код 1051? Представьте, что у вас есть большая хранимая процедура, по всему коду которой разбросаны некорректные значения. Настоящий кошмар для разработчиков обслуживания.

К счастью, MySQL предоставляет нам оператор DECLARE CONDITION, который объявляет проименованное условие ошибки, связанное с условием.

Синтаксис оператора DECLARE CONDITION выглядит следующим образом:

DECLARE condition_name CONDITION FOR condition_value;

condition_value может представлять собой код ошибки MySQL, например 1015, или значение SQLSTATE. condition_value представляется с помощью condition_name.

После объявления вы можете обращаться к condition_name вместо condition_value.

Таким образом, мы можем переписать код, приведенный выше, следующим образом:

DECLARE table_not_found CONDITION for 1051;
DECLARE EXIT HANDLER FOR  table_not_found SELECT 'Please create table abc first';
SELECT * FROM abc;

Источник

In this tutorial, we will learn how we can handle the errors effectively in the MySQL stored procedures. We will see multiple aspects and possibilities while creating the error conditions, handlers and handling those errors. So, let’s get started!

Introduction to MySQL Error Handling

It is very important to handle the errors in MySQL stored programs such as procedures, functions, triggers and events. If you do not handle the errors in a proper way, the end user will see some system-generated error message that is not understandable to everyone. Also, it is a good practice which helps programmers for debugging the programs as well.

In MySQL, we can easily create handlers that can handle the warnings, exceptions and errors.

In simple terms, the handler is a statement that handles the error when a specific condition meets.

Now let’s see the syntax to define a handler.

Declare an Error Handler

We can declare an error handler using the DECLARE … HANDLER statement.

DECLARE handler_action HANDLER
    FOR condition_value [, condition_value] ...
    statement

handler_action: {
    CONTINUE
  | EXIT
  | UNDO
}

condition_value: {
    mysql_error_code
  | SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION
}
Code language: SQL (Structured Query Language) (sql)

Here, you can specify one of the three handler actions which having meaning as of the following-

CONTINUE- The present program is still being run.
EXIT – The BEGIN… END compound statement in which the handler is declared ends execution. Even if the condition occurs in an inner block, this is still true.
UNDO – MySQL does not support it.

A specific condition or a group of circumstances that activate the handler are specified by the condition value. One of the following values may be used with the condition value:

mysql_error_code – A MySQL error code represented by an integer literal, such as 1051 for “unknown table”:
SQLSTATE sqlstate_value – a literal 5-character string specifying an SQLSTATE value, such as ’42S01′ to denote ‘unknown table’:
condition_name – previously defined condition name using DECLARE… CONDITION. A condition name may be connected to an SQLSTATE value or a MySQL error number.
NOT FOUND –
SQLEXCEPTION – abbreviation for the group of SQLSTATE values that start with the number “02”. It is used to control what occurs when a cursor reaches the end of a data collection, making it significant in the context of cursors.

We have covered most of the theory parts about error handling. Let’s now take some examples to demonstrate how error handling is actually done.

Here, we will create a table and write a stored procedure to insert data into it. If the record is already present, we will issue an error message.

Let’s create a table for demonstration.

CREATE TABLE emps(
id INT PRIMARY KEY,
dept VARCHAR(10),
city VARCHAR(20)
);
Code language: SQL (Structured Query Language) (sql)

Creating a stored procedure to insert data

Now, we will create a stored procedure to insert the data into the table.

Inside the stored procedure, we will create an exit error handler for the error code 1062 which is for duplicate entries in the table. If the handler is invoked, we will show the message that a duplicate entry has been made and the exit handler will terminate the stored procedure execution.

DROP PROCEDURE IF EXISTS insertEmps;
DELIMITER //
CREATE PROCEDURE insertEmps(IN empId INT, IN dept VARCHAR(10), IN city VARCHAR(20))
BEGIN
DECLARE EXIT HANDLER FOR 1062
BEGIN
SELECT CONCAT("duplicate entry for the emp id ",empId) AS errorMessage;
END;
INSERT INTO emps VALUES(empId,dept,city);
SELECT * FROM emps WHERE id = empId;
END 
//
DELIMITER ;
Code language: SQL (Structured Query Language) (sql)

Here, we take three parameters which are employee id, department id and city.

Writing an exit handler for error code 1062

Then we write an exit handler for the error code 1062. When it gets invoked, the message will be shown and the procedure terminates.

If the employee id is unique then the insert statement will get executed and the data that is inserted will be displayed as it is.

Note that, if the error handler is invoked, the SELECT * FROM statement at the end will not execute because as soon as the handler is activated, the message will be displayed and it terminates the procedure.

Let’s call the stored procedure now.

CALL insertEmps(101,"IT","Mumbai");
Code language: SQL (Structured Query Language) (sql)

Call Stored Procedure

As you can see, the data that we have inserted into the table is shown in the output.

Inserting duplicate data

Let’s insert the same data that we have inserted just now. It should show the message that we defined in the handler.

CALL insertEmps(101,"IT","Mumbai");
Code language: SQL (Structured Query Language) (sql)

Duplicate Entry

As you can see here, we got the error message.

Switching the exiting handler

Now, let’s change the error handler from EXIT to CONTINUE and see the change.

DROP PROCEDURE IF EXISTS insertEmps;
DELIMITER //
CREATE PROCEDURE insertEmps(IN empId INT, IN dept VARCHAR(10), IN city VARCHAR(20))
BEGIN
DECLARE CONTINUE HANDLER FOR 1062
BEGIN
SELECT CONCAT("duplicate entry for the emp id ",empId) AS errorMessage;
END;
INSERT INTO emps VALUES(empId,dept,city);
SELECT * FROM emps WHERE id = empId;
END 
//
DELIMITER ;
Code language: SQL (Structured Query Language) (sql)

CALL insertEmps(101,"IT","Mumbai");
Code language: SQL (Structured Query Language) (sql)

Duplicate Entry With Continue Handler

As you can see, we first got the error message and then the record which is having the given employee id is displayed.

MySQL Error Handler Precedence

There might be possibilities that you have multiple handlers for the same error.

If you have several handlers for the same error, MySQL will call the one that can handle it the most specifically first based on the guidelines below:

Because a MySQL error code is the most specific, it always corresponds to an error.
An SQLSTATE is less precise since it can map to several MySQL error codes.
It is the most general sense, an SQLEXCPETION or SQLWARNING is the abbreviation for a group of SQLSTATES data.

For example, let’s take the previously created stored procedure example.

We will slightly modify that procedure and add two more handlers for the same error.

So, the new stored procedure will look like this-

DROP PROCEDURE IF EXISTS insertEmps;
DELIMITER //
CREATE PROCEDURE insertEmps(IN empId INT, IN dept VARCHAR(10), IN city VARCHAR(20))
BEGIN
DECLARE EXIT HANDLER FOR 1062 SELECT CONCAT("duplicate entry for the emp id ",empId) AS errorMessage;
DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT "SQLEXCEPTION occured" AS errorMessage;
DECLARE EXIT HANDLER FOR SQLSTATE "23000" SELECT "SQLSTATE 23000" AS errorMessage;
INSERT INTO emps VALUES(empId,dept,city);
SELECT * FROM emps WHERE id = empId;
END 
//
DELIMITER ;
Code language: SQL (Structured Query Language) (sql)

Now let’s try inserting the duplicate data.

CALL insertEmps(101,"IT","Mumbai");
Code language: SQL (Structured Query Language) (sql)

Error Handler Precedence Example

As you can see, even if we have three handlers for the same error, the one which is specified with the error code is encountered.

Summary

In this tutorial, we have learned how to handle errors in MySQL stored procedures. This topic is very vast but I have tried to make it as simple as possible without adding unnecessary things which you can learn later yourself. I hope you find this tutorial helpful. If you do, don’t forget to share it with your friends.

References

MySQL official documentation on the declare-handler statement.
Guide on the SIGNAL and the RESIGNAL Statement.
SQL Docs

Источник

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

In this post, I am sharing the full demonstration on how to manage error/exception handling in the Stored Procedure of MySQL.

Whenever an exception is occurring in a stored procedure, it is very important to handle it by showing proper error messages.

If you do not handle the exception, there would be a chance to fail application with the certain exception in a stored procedure.

If you get an error in stored procedure, instead of an exit, you should continue without any error message. That means you can show any default or custom error code or message to the application/user.

MySQL provides Handler to handle the exception in the stored procedure.

Below is a full demonstration of a handler with examples:

/*Create Employee database for demo */

CREATE DATABASE Employee;

/*Create sample EmployeeDetails table.*/

CREATE TABLE Employee.tbl_EmployeeDetails

(

EmpID INTEGER

,EmpName VARCHAR(50)

,EmailAddress VARCHAR(50)

,CONSTRAINT pk_tbl_EmployeeDetails_EmpID PRIMARY KEY (EmpID)

)ENGINE = InnoDB;

How to declare handler in store procedure:

Syntax of Handler:

DECLARE handler_action HANDLER FOR condition_value … statement

Three type of Handler_Action:

CONTINUE
EXIT
UNDO

Type of Condition Value:

mysql_error_code
sqlstate_value
SQLWarning
SQLException
NotFound

How to write handler in stored procedure?

E.g.

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SELECT ‘Error occured’;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET IsError=1;

DECLARE EXIT HANDLER FOR SQLEXCEPTION SET IsError=1;

DECLARE EXIT HANDLER FOR SQLSTATE ‘23000’ SET IsError = 1;

The Above are four different handler examples. Now, I am going to insert a duplicate value into EmpID column.

DELIMITER //

CREATE PROCEDURE Employee.usp_InsertEmployeeDetails

(

InputEmpID INTEGER

,InputEmpName VARCHAR(50)

,InputEmailAddress VARCHAR(50)

)

/****************************************************************

Authors Name : Anvesh Patel

Created Date : 2015-05-20

Description : This is demo stored procedure to

insert record into table with proper

error handling.Basically for www.dbrnd.com readers.

*****************************************************************/

BEGIN

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SELECT ‘Error occured’;

INSERT INTO Employee.tbl_EmployeeDetails

(

EmpID

,EmpName

,EmailAddress

)

VALUES

(

InputEmpID

,InputEmpName

,InputEmailAddress

);

SELECT *FROM Employee.tbl_EmployeeDetails;

END

// DELIMITER ;

In the above SP, I defined a CONTINUE handler with my custom exception message.

Now, call the above SP two times with same EmpID.

The first time, it will execute successfully, but the second time it will throw a custom error message.

As we defined CONTINUE handler, so it will just show an error message and CONTINUE to next part of the SELECT statement.

Let’s See,

CALL Employee.usp_InsertEmployeeDetails (1,’Anvesh’,’anvesh@gmail.com’);

CALL Employee.usp_InsertEmployeeDetails (1,’Roy’,’Roy@gmail.com’);

Above are the two different calls with same EmpID value. The first call executes without any error message and the second call execute with an error message.

The resule of Second Call:

Now, check the EXIT handler:

Please modify your handler and replace CONTINUE by EXIT:

DELIMITER //

CREATE PROCEDURE Employee.usp_InsertEmployeeDetails

(

InputEmpID INTEGER

,InputEmpName VARCHAR(50)

,InputEmailAddress VARCHAR(50)

)

/*****************************************************************

Authors Name : Anvesh Patel

Created Date : 2015-05-20

Description : This is demo stored procedure to

insert record into table with proper

error handling.Basically for www.dbrnd.com readers.

******************************************************************/

BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT ‘Error occured’;

INSERT INTO Employee.tbl_EmployeeDetails

(

EmpID

,EmpName

,EmailAddress

)

VALUES

(

InputEmpID

,InputEmpName

,InputEmailAddress

);

SELECT *FROM Employee.tbl_EmployeeDetails;

END

// DELIMITER ;

Call with the same parameter:

CALL Employee.usp_InsertEmployeeDetails (1,’Roy’,’Roy@gmail.com’);

The Result is an only error message, and you cannot find two results as we defined EXIT to exit the code when an error occurred.

Exit1

The best practice is to create a output parameter and store 1 if any error occurred.

Application code has to check this output parameter is NULL or 1.

1 = Error.

NULL = No Error.

Below is a stored procedure for this:

DELIMITER //

CREATE PROCEDURE Employee.usp_InsertEmployeeDetails

(

InputEmpID INTEGER

,InputEmpName VARCHAR(50)

,InputEmailAddress VARCHAR(50)

,out IsError INTEGER

)

/***********************************************************

Authors Name : Anvesh Patel

Created Date : 2015-05-20

Description : This is demo stored procedure to insert

record into table with proper error handling.

Basically for www.dbrnd.com readers.

************************************************************/

BEGIN

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET IsError=1;

INSERT INTO Employee.tbl_EmployeeDetails

(

EmpID

,EmpName

,EmailAddress

)

VALUES

(

InputEmpID

,InputEmpName

,InputEmailAddress

);

SELECT *FROM Employee.tbl_EmployeeDetails;

END

// DELIMITER ;

Now call the above SP and select output parameter:

CALL Employee.usp_InsertEmployeeDetails (1,’Roy’,’Roy@gmail.com’,@IsError);

SELECT @IsError;

Now Results are:

Above is a simple demonstration of Error Handling in MySQL. You can also use SQLSTATE which shows default error messages of MySQL.

May 21, 2015

Источник

Declaring a handler

MySQL error handling examples

MySQL handler example in stored procedures

MySQL handler precedence

Using a named error condition

Обработка ошибок MySQL в хранимых процедурах

Объявление обработчика

Примеры обработки ошибок MySQL

Пример обработчика MySQL в хранимых процедурах

Приоритет обработчиков MySQL

Использование проименованных условий ошибки

mysqli::$error

Description

Parameters

Return Values

Examples

See Also

User Contributed Notes 7 notes

MySQL Error Handling

Please visit other related articles.

Introduction to MySQL Error Handling

Declare an Error Handler

Creating a stored procedure to insert data

Writing an exit handler for error code 1062

Inserting duplicate data

Switching the exiting handler

MySQL Error Handler Precedence

Summary

References