http://dev.mysql.com/doc/refman/5.7/en/get-diagnostics.html

GET [CURRENT | STACKED] DIAGNOSTICS{    statement_information_item    [, statement_information_item] ...  | CONDITION condition_number    condition_information_item    [, condition_information_item] ...}statement_information_item:    target = statement_information_item_namecondition_information_item:    target = condition_information_item_namestatement_information_item_name:    NUMBER  | ROW_COUNTcondition_information_item_name:    CLASS_ORIGIN  | SUBCLASS_ORIGIN  | RETURNED_SQLSTATE  | MESSAGE_TEXT  | MYSQL_ERRNO  | CONSTRAINT_CATALOG  | CONSTRAINT_SCHEMA  | CONSTRAINT_NAME  | CATALOG_NAME  | SCHEMA_NAME  | TABLE_NAME  | COLUMN_NAME  | CURSOR_NAMEcondition_number, target:    (see following discussion)

SQL statements produce diagnostic information that populates the diagnostics area. The GET DIAGNOSTICS statement enables applications to inspect this information. (You can also use SHOW WARNINGS or SHOW ERRORS to see conditions or errors.)

No special privileges are required to execute GET DIAGNOSTICS.

The keyword CURRENT means to retrieve information from the current diagnostics area. The keyword STACKED means to retrieve information from the second diagnostics area, which is available only if the current context is a condition handler. If neither keyword is given, the default is to use the current diagnostics area.

The GET DIAGNOSTICS statement is typically used in a handler within a stored program. It is a MySQL extension that GET [CURRENT] DIAGNOSTICS is permitted outside handler context to check the execution of any SQL statement. For example, if you invoke the mysql client program, you can enter these statements at the prompt:

mysql> DROP TABLE test.no_such_table;ERROR 1051 (42S02): Unknown table ‘test.no_such_table‘mysql> GET DIAGNOSTICS CONDITION 1    ->   @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;mysql> SELECT @p1, @p2;+-------+------------------------------------+| @p1   | @p2                                |+-------+------------------------------------+| 42S02 | Unknown table ‘test.no_such_table‘ |+-------+------------------------------------+

This extension applies only to the current diagnostics area. It does not apply to the second diagnostics area because GET STACKED DIAGNOSTICS is permitted only if the current context is a condition handler. If that is not the case, a GET STACKED DIAGNOSTICS when handler not active error occurs.

For a description of the diagnostics area, see Section 14.6.7.7, “The MySQL Diagnostics Area”. Briefly, it contains two kinds of information:

For a statement that produces three conditions, the diagnostics area contains statement and condition information like this:

Statement information:  row count  ... other statement information items ...Condition area list:  Condition area 1:    error code for condition 1    error message for condition 1    ... other condition information items ...  Condition area 2:    error code for condition 2:    error message for condition 2    ... other condition information items ...  Condition area 3:    error code for condition 3    error message for condition 3    ... other condition information items ...

GET DIAGNOSTICS can obtain either statement or condition information, but not both in the same statement:

The retrieval list specifies one or more target = item_name assignments, separated by commas. Each assignment names a target variable and either a statement_information_item_name or condition_information_item_namedesignator, depending on whether the statement retrieves statement or condition information.

Valid target designators for storing item information can be stored procedure or function parameters, stored program local variables declared with DECLARE, or user-defined variables.

Valid condition_number designators can be stored procedure or function parameters, stored program local variables declared with DECLARE, user-defined variables, system variables, or literals. A character literal may include a _charsetintroducer. A warning occurs if the condition number is not in the range from 1 to the number of condition areas that have information. In this case, the warning is added to the diagnostics area without clearing it.

When a condition occurs, MySQL does not populate all condition items recognized by GET DIAGNOSTICS. For example:

mysql> GET DIAGNOSTICS CONDITION 1    ->   @p5 = SCHEMA_NAME, @p6 = TABLE_NAME;mysql> SELECT @p5, @p6;+------+------+| @p5  | @p6  |+------+------+|      |      |+------+------+

In standard SQL, if there are multiple conditions, the first condition relates to the SQLSTATE value returned for the previous SQL statement. In MySQL, this is not guaranteed. To get the main error, you cannot do this:

GET DIAGNOSTICS CONDITION 1 @errno = MYSQL_ERRNO;

Instead, retrieve the condition count first, then use it to specify which condition number to inspect:

GET DIAGNOSTICS @cno = NUMBER;GET DIAGNOSTICS CONDITION @cno @errno = MYSQL_ERRNO;

For information about permissible statement and condition information items, and which ones are populated when a condition occurs, see Section 14.6.7.7.2, “Diagnostics Area Information Items”.

Here is an example that uses GET DIAGNOSTICS and an exception handler in stored procedure context to assess the outcome of an insert operation. If the insert was successful, the procedure uses GET DIAGNOSTICS to get the rows-affected count. This shows that you can use GET DIAGNOSTICS multiple times to retrieve information about a statement as long as the current diagnostics area has not been cleared.

CREATE PROCEDURE do_insert(value INT)BEGIN  -- Declare variables to hold diagnostics area information  DECLARE code CHAR(5) DEFAULT ‘00000‘;  DECLARE msg TEXT;  DECLARE rows INT;  DECLARE result TEXT;  -- Declare exception handler for failed insert  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION    BEGIN      GET DIAGNOSTICS CONDITION 1        code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT;    END;  -- Perform the insert  INSERT INTO t1 (int_col) VALUES(value);  -- Check whether the insert was successful  IF code = ‘00000‘ THEN    GET DIAGNOSTICS rows = ROW_COUNT;    SET result = CONCAT(‘insert succeeded, row count = ‘,rows);  ELSE    SET result = CONCAT(‘insert failed, error = ‘,code,‘, message = ‘,msg);  END IF;  -- Say what happened  SELECT result;END;

Suppose that t1.int_col is an integer column that is declared as NOT NULL. The procedure produces these results when invoked to insert non-NULL and NULL values, respectively:

mysql> CALL do_insert(1);+---------------------------------+| result                          |+---------------------------------+| insert succeeded, row count = 1 |+---------------------------------+mysql> CALL do_insert(NULL);+-------------------------------------------------------------------------+| result                                                                  |+-------------------------------------------------------------------------+| insert failed, error = 23000, message = Column ‘int_col‘ cannot be null |+-------------------------------------------------------------------------+

When a condition handler activates, a push to the diagnostics area stack occurs:

The next example shows how GET STACKED DIAGNOSTICS can be used within a handler to obtain information about the handled exception, even after the current diagnostics area has been modified by handler statements.

Within a stored procedure p(), we attempt to insert two values into a table that contains a TEXT NOT NULL column. The first value is a non-NULL string and the second is NULL. The column prohibits NULL values, so the first insert succeeds but the second causes an exception. The procedure includes an exception handler that maps attempts to insert NULL into inserts of the empty string:

DROP TABLE IF EXISTS t1;CREATE TABLE t1 (c1 TEXT NOT NULL);DROP PROCEDURE IF EXISTS p;delimiter //CREATE PROCEDURE p ()BEGIN  -- Declare variables to hold diagnostics area information  DECLARE errcount INT;  DECLARE errno INT;  DECLARE msg TEXT;  DECLARE EXIT HANDLER FOR SQLEXCEPTION  BEGIN    -- Here the current DA is nonempty because no prior statements    -- executing within the handler have cleared it    GET CURRENT DIAGNOSTICS CONDITION 1      errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;    SELECT ‘current DA before mapped insert‘ AS op, errno, msg;    GET STACKED DIAGNOSTICS CONDITION 1      errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;    SELECT ‘stacked DA before mapped insert‘ AS op, errno, msg;    -- Map attempted NULL insert to empty string insert    INSERT INTO t1 (c1) VALUES(‘‘);    -- Here the current DA should be empty (if the INSERT succeeded),    -- so check whether there are conditions before attempting to    -- obtain condition information    GET CURRENT DIAGNOSTICS errcount = NUMBER;    IF errcount = 0    THEN      SELECT ‘mapped insert succeeded, current DA is empty‘ AS op;    ELSE      GET CURRENT DIAGNOSTICS CONDITION 1        errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;      SELECT ‘current DA after mapped insert‘ AS op, errno, msg;    END IF ;    GET STACKED DIAGNOSTICS CONDITION 1      errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;    SELECT ‘stacked DA after mapped insert‘ AS op, errno, msg;  END;  INSERT INTO t1 (c1) VALUES(‘string 1‘);  INSERT INTO t1 (c1) VALUES(NULL);END;//delimiter ;CALL p();SELECT * FROM t1;

When the handler activates, a copy of the current diagnostics area is pushed to the diagnostics area stack. The handler first displays the contents of the current and stacked diagnostics areas, which are both the same initially:

+---------------------------------+-------+----------------------------+| op                              | errno | msg                        |+---------------------------------+-------+----------------------------+| current DA before mapped insert |  1048 | Column ‘c1‘ cannot be null |+---------------------------------+-------+----------------------------++---------------------------------+-------+----------------------------+| op                              | errno | msg                        |+---------------------------------+-------+----------------------------+| stacked DA before mapped insert |  1048 | Column ‘c1‘ cannot be null |+---------------------------------+-------+----------------------------+

Statements executing after the GET DIAGNOSTICS statements may reset the current diagnostics area. statements may reset the current diagnostics area. For example, the handler maps the NULL insert to an empty-string insert and displays the result. The new insert succeeds and clears the current diagnostics area, but the stacked diagnostics area remains unchanged and still contains information about the condition that activated the handler:

+----------------------------------------------+| op                                           |+----------------------------------------------+| mapped insert succeeded, current DA is empty |+----------------------------------------------++--------------------------------+-------+----------------------------+| op                             | errno | msg                        |+--------------------------------+-------+----------------------------+| stacked DA after mapped insert |  1048 | Column ‘c1‘ cannot be null |+--------------------------------+-------+----------------------------+

When the condition handler ends, its current diagnostics area is popped from the stack and the stacked diagnostics area becomes the current diagnostics area in the stored procedure.

After the procedure returns, the table contains two rows. The empty row results from the attempt to insert NULL that was mapped to an empty-string insert:

+----------+| c1       |+----------+| string 1 ||          |+----------+

In the preceding example, the first two GET DIAGNOSTICS statements within the condition handler that retrieve information from the current and stacked diagnostics areas return the same values. This will not be the case if statements that reset the current diagnostics area execute earlier within the handler. Suppose that p() is rewritten to place the DECLARE statements within the handler definition rather than preceding it:

CREATE PROCEDURE p ()BEGIN  DECLARE EXIT HANDLER FOR SQLEXCEPTION  BEGIN    -- Declare variables to hold diagnostics area information    DECLARE errcount INT;    DECLARE errno INT;    DECLARE msg TEXT;    GET CURRENT DIAGNOSTICS CONDITION 1      errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;    SELECT ‘current DA before mapped insert‘ AS op, errno, msg;    GET STACKED DIAGNOSTICS CONDITION 1      errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;    SELECT ‘stacked DA before mapped insert‘ AS op, errno, msg;...

In this case, the result is version dependent:

To avoid this issue within a condition handler when seeking to obtain information about the condition that activated the handler, be sure to access the stacked diagnostics area, not the current diagnostics area.

GET DIAGNOSTICS Syntax