13.6.7.3 GET DIAGNOSTICS Statement — MySQL 5.7

13.6.7.3 Оператор GET DIAGNOSTICS

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_name

condition_information_item:
    target = condition_information_item_name

statement_information_item_name: {
    NUMBER
  | ROW_COUNT
}

condition_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_NAME
}

condition_number, target:
    (see following discussion)

SQL-операторы генерируют диагностическую информацию, которая заполняет область диагностики. Оператор GET DIAGNOSTICS позволяет приложениям просматривать эту информацию. (Также можно использовать SHOW WARNINGS или SHOW ERRORS для просмотра условий или ошибок.)

Для выполнения оператора GET DIAGNOSTICS не требуются особые привилегии.

Ключевое слово CURRENT означает получение информации из текущей области диагностики. Ключевое слово STACKED означает получение информации из второй области диагностики, которая доступна только если текущий контекст — обработчик условий. Если ни одно из ключевых слов не указано, по умолчанию используется текущая область диагностики.

Оператор GET DIAGNOSTICS обычно используется в обработчике внутри хранимой программы. Это расширение MySQL, которое позволяет использовать GET [CURRENT] DIAGNOSTICS вне контекста обработчика для проверки выполнения любого SQL-оператора. Например, если вы вызываете программу-клиент mysql, вы можете ввести эти операторы в командной строке:

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' |
+-------+------------------------------------+

Это расширение применяется только к текущей области диагностики. Оно не применяется ко второй области диагностики, так как использование GET STACKED DIAGNOSTICS разрешено только в контексте обработчика условий. Если это не так, возникает ошибка GET STACKED DIAGNOSTICS when handler not active.

Описание области диагностики см. в разделе 13.6.7.7, «Область диагностики MySQL». Вкратце, она содержит два вида информации:

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

Для оператора, генерирующего три условия, область диагностики содержит информацию об операторе и условиях следующим образом:

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 может получить либо информацию об операторе, либо информацию об условии, но не обе в одном операторе:

Для получения информации об операторе необходимо извлечь необходимые элементы оператора в целевые переменные. В этом случае оператор GET DIAGNOSTICS присваивает количество доступных условий и количество затронутых строк пользовательским переменным @p1 и @p2:
```
GET DIAGNOSTICS @p1 = NUMBER, @p2 = ROW_COUNT;
```
Для получения информации об условии необходимо указать номер условия и извлечь необходимые элементы условия в целевые переменные. В этом случае оператор GET DIAGNOSTICS присваивает значение SQLSTATE и сообщение об ошибке пользовательским переменным @p3 и @p4:
```
GET DIAGNOSTICS CONDITION 1
  @p3 = RETURNED_SQLSTATE, @p4 = MESSAGE_TEXT;
```

Список извлечения содержит одно или несколько target = item_name присваиваний, разделенных запятыми. Каждое присваивание называет целевую переменную и либо statement_information_item_name, либо condition_information_item_name обозначение в зависимости от того, извлекает ли оператор информацию об операторе или об условии.

Допустимые target обозначения для хранения информации об элементах могут быть параметрами хранимых процедур или функций, локальными переменными хранимых программ, объявленными с помощью DECLARE, или пользовательскими переменными.

Допустимые condition_number обозначения могут быть параметрами хранимых процедур или функций, локальными переменными хранимых программ, объявленными с помощью DECLARE, пользовательскими переменными, системными переменными или литералами. Литерал символьного типа может включать в себя _charset префикс. Возникает предупреждение, если номер условия не находится в диапазоне от 1 до количества областей условий, содержащих информацию. В этом случае предупреждение добавляется в область диагностики без её очистки.

При возникновении условия MySQL не заполняет все элементы условия, распознаваемые оператором GET DIAGNOSTICS. Например:

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

В стандартном SQL, если есть несколько условий, первое условие относится к значению SQLSTATE, возвращаемому предыдущим SQL-оператором. В MySQL это не гарантируется. Чтобы получить основную ошибку, нельзя сделать так:

GET DIAGNOSTICS CONDITION 1 @errno = MYSQL_ERRNO;

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

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

Сведения о допустимых элементах информации об операторе и условии, и о том, какие из них заполняются при возникновении условия, см. в Элементы информации области диагностики.

Вот пример, который использует GET DIAGNOSTICS и обработчик исключений в контексте хранимой процедуры для оценки результата операции вставки. Если вставка прошла успешно, процедура использует GET DIAGNOSTICS для получения количества затронутых строк. Это демонстрирует, что вы можете использовать GET DIAGNOSTICS несколько раз для извлечения информации об операторе, пока текущая область диагностики не очищена.

CREATE PROCEDURE do_insert(value INT)
BEGIN
  -- Declare variables to hold diagnostics area information
  DECLARE code CHAR(5) DEFAULT '00000';
  DECLARE msg TEXT;
  DECLARE nrows 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 nrows = ROW_COUNT;
    SET result = CONCAT('insert succeeded, row count = ',nrows);
  ELSE
    SET result = CONCAT('insert failed, error = ',code,', message = ',msg);
  END IF;
  -- Say what happened
  SELECT result;
END;

Предположим, что t1.int_col — это столбец целого типа, объявленный как NOT NULL. Процедура генерирует эти результаты при вызове для вставки значений, не являющихся NULL, и NULL значений, соответственно:

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 |
+-------------------------------------------------------------------------+

При активации обработчика условия происходит push в стек области диагностики:

Первая (текущая) область диагностики становится второй (стековой) областью диагностики, и создаётся новая текущая область диагностики как её копия.
GET [CURRENT] DIAGNOSTICS и GET STACKED DIAGNOSTICS могут быть использованы внутри обработчика для доступа к содержимому текущей и стековой областей диагностики.
Изначально обе области диагностики возвращают один и тот же результат, поэтому можно получить информацию из текущей области диагностики об условии, активировавшем обработчик, пока вы не выполните операторы внутри обработчика, которые изменят его текущую область диагностики.
Однако операторы, выполняющиеся внутри обработчика, могут изменить текущую область диагностики, очистив и установив её содержимое в соответствии с обычными правилами (см. Как область диагностики очищается и заполняется).
Более надёжный способ получения информации об условии, активировавшем обработчик, заключается в использовании стековой области диагностики, которую нельзя изменить операторами, выполняющимися внутри обработчика, за исключением RESIGNAL. Сведения о том, когда текущая область диагностики устанавливается и очищается, см. в разделе 13.6.7.7, «Область диагностики MySQL».

Следующий пример показывает, как использовать GET STACKED DIAGNOSTICS внутри обработчика для получения информации об обработанном исключении даже после того, как текущая область диагностики была изменена операторами обработчика.

Внутри хранимой процедуры p() мы пытаемся вставить два значения в таблицу, которая содержит столбец TEXT NOT NULL. Первое значение — это строка, не являющаяся NULL, а второе — NULL. Столбец запрещает NULL значения, поэтому первая вставка проходит успешно, а вторая вызывает исключение. Процедура включает в себя обработчик исключений, который преобразует попытки вставки NULL во вставки пустой строки:

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;

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

+---------------------------------+-------+----------------------------+
| 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 |
+---------------------------------+-------+----------------------------+

Операторы, выполняемые после операторов GET DIAGNOSTICS, могут сбросить текущую область диагностики. Например, обработчик преобразует вставку NULL во вставку пустой строки и отображает результат. Новая вставка проходит успешно и очищает текущую область диагностики, но стековая область диагностики остаётся неизменной и по-прежнему содержит информацию об условии, активировавшем обработчик:

+----------------------------------------------+
| op                                           |
+----------------------------------------------+
| mapped insert succeeded, current DA is empty |
+----------------------------------------------+

+--------------------------------+-------+----------------------------+
| op                             | errno | msg                        |
+--------------------------------+-------+----------------------------+
| stacked DA after mapped insert |  1048 | Column 'c1' cannot be null |
+--------------------------------+-------+----------------------------+

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

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

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

В предыдущем примере первые две GET DIAGNOSTICS операторы в обработчике условия, извлекающие информацию из текущей и стековой областей диагностики, возвращают одинаковые значения. Это не так, если операторы, которые сбрасывают текущую область диагностики, выполняются ранее в обработчике. Предположим, что p() переписан так, чтобы DECLARE операторы располагались внутри определения обработчика, а не перед ним:

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;
...

В этом случае результат зависит от версии:

До MySQL 5.7.2, DECLARE не изменяет текущую область диагностики, поэтому первые два GET DIAGNOSTICS оператора возвращают тот же результат, как и в исходной версии p().

В MySQL 5.7.2 была выполнена работа для обеспечения того, чтобы все недиагностические операторы заполняли область диагностики, согласно стандарту SQL. DECLARE является одним из них, поэтому в 5.7.2 и более поздних версиях DECLARE операторы, выполняемые в начале обработчика, очищают текущую область диагностики, и GET DIAGNOSTICS операторы дают разные результаты:
```
+---------------------------------+-------+------+
| op                              | errno | msg  |
+---------------------------------+-------+------+
| current DA before mapped insert |  NULL | NULL |
+---------------------------------+-------+------+

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

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