SQL – GET DIAGNOSTICS

adminLinux, MariaDB, Mysql, RedHat / CentOS

測試環境為 CentOS 8 x86_64 (虛擬機)

執行 SQL 指令時,錯誤會直接顯示出來.

MariaDB [(none)]> INSERT INTO test VALUE(100);
ERROR 1046 (3D000): No database selected

想要保留這錯誤訊息可以透過 GET DIAGNOSTICS 來處理,語法如下.

GET [CURRENT] DIAGNOSTICS
{
    statement_property
    [, statement_property] ... 
  | CONDITION condition_number
    condition_property
    [, condition_property] ...
}

statement_property:
    variable = statement_property_name

condition_property:
    variable  = condition_property_name

statement_property_name:
    NUMBER
  | ROW_COUNT

condition_property_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

GET DIAGNOSTICS

參考範例 – https://mariadb.com/kb/en/get-diagnostics/

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.17-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

故意產生一個錯誤.

MariaDB [(none)]> INSERT INTO test VALUE(100);
ERROR 1046 (3D000): No database selected

第一步就是透過指令 GET DIAGNOSTICS ,參數 statement property 中的 NUMBER 來檢視這一次產生的錯誤有幾筆(上面範例只有一筆).

MariaDB [(none)]> GET DIAGNOSTICS @num_conditions = NUMBER;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SELECT @num_conditions;
+-----------------+
| @num_conditions |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.000 sec)

這一次產生的錯誤只有一筆,指定參數 CONDITION 為 1 ( 錯誤的第一筆,如果有第二筆就改成 CONDITION 2 ),並把參數 condition property 中的 RETURNED_SQLSTATE, MYSQL_ERRNO, MESSAGE_TEXT 資料儲存起來.

MariaDB [(none)]> GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SELECT @sqlstate, @errno, @text;
+-----------+--------+----------------------+
| @sqlstate | @errno | @text                |
+-----------+--------+----------------------+
| 3D000     |   1046 | No database selected |
+-----------+--------+----------------------+
1 row in set (0.000 sec)

PROCEDURE + GET DIAGNOSTICS

通常使用 PROCEDURE 會加入 GET DIAGNOSTICS 來確保資料處理沒有發生問題,來看下面的參考範例 – https://dev.mysql.com/doc/refman/5.7/en/get-diagnostics.html

先建立測試用的資料表.

MariaDB [(none)]> USE testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [testdb]> CREATE TABLE t1 (int_col INT, PRIMARY KEY (int_col));
Query OK, 0 rows affected (0.013 sec)

建立 PROCEDURE .

DROP PROCEDURE IF EXISTS do_insert;
 
DELIMITER //

CREATE PROCEDURE do_insert(value INT)
BEGIN
  DECLARE code CHAR(5) DEFAULT '00000';
  DECLARE msg TEXT;
  DECLARE nrows INT;
  DECLARE result TEXT;

  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
      GET DIAGNOSTICS CONDITION 1 code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT;
    END;

  INSERT INTO t1 (int_col) VALUES(value);
  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;
  SELECT result;
END;
//
 
DELIMITER ;

說明:

  • DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    這是關於 Store Procedure 的例外處理 ( DECLARE Error Handler ) – https://benjr.tw/103022 .概念很簡單就是在 Store Procedure 裡面發生錯誤時需要怎麼來處裡.
    這邊定義當發生 SQLEXCEPTION (所有 SQLSTATE 不屬於 00 , 01 , 02 開頭的錯誤, MariaDB Error Codes 請參考 – https://mariadb.com/kb/en/mariadb-error-codes/ ) 時,就開始執行下面的 BEGIN 到 END 的 SQL 指令. 

    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

    開始 HANDLER 要執行的 SQL 指令.

        BEGIN

    透過 GET DIAGNOSTICS 把 CONDITION 1 的 RETURNED_SQLSTATE, MESSAGE_TEXT 訊息記錄到 code 與 msg 變數.

          GET DIAGNOSTICS CONDITION 1 code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT;

    結束 HANDLER 要執行的 SQL 指令.

        END;
  • INSERT INTO t1 (int_col) VALUES(value);
    INSERT 一筆資料到 t1 資料表.

      INSERT INTO t1 (int_col) VALUES(value);
  • IF ELSE END IF;
    前面已經透過 GET DIAGNOSTICS 把 RETURNED_SQLSTATE, MESSAGE_TEXT 訊息記錄到 code 與 msg 變數.這邊就先檢視 code 的值是否為 00000 (代表成功執行)並顯示其成功訊息,非則把其相對應錯誤訊息顯示出來.

      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;
  SELECT result;

執行結果,一次輸入正確值 (數字 1),另外一次故意輸入 NULL.

MariaDB [testdb]> CALL do_insert(1);
+---------------------------------+
| result                          |
+---------------------------------+
| insert succeeded, row count = 1 |
+---------------------------------+
1 row in set (0.003 sec)

Query OK, 1 row affected (0.003 sec)

MariaDB [testdb]> CALL do_insert(NULL);
+-------------------------------------------------------------------------+
| result                                                                  |
+-------------------------------------------------------------------------+
| insert failed, error = 23000, message = Column 'int_col' cannot be null |
+-------------------------------------------------------------------------+
1 row in set (0.001 sec)

Query OK, 0 rows affected (0.001 sec)

PROCEDURE + Save DIAGNOSTICS

通常我會希望把錯誤記錄起來到另外一個 Table .這時候可以使用 GET DIAGNOSTICS ,用法如下主要抓取目前的 RETURNED_SQLSTATE, MYSQL_ERRNO 與 MESSAGE_TEXT 資訊.

GET [CURRENT] DIAGNOSTICS
{
    statement_property
    [, statement_property] ... 
  | CONDITION condition_number
    condition_property
    [, condition_property] ...
}

先建立要儲存的資料表.

MariaDB [testdb]> CREATE TABLE SQL_Error_Record (K1 INT(11) NOT NULL auto_increment, SQL_STATUS VARCHAR(50) , ERROR_NUM VARCHAR(50) , CONTENT VARCHAR(500), T1 TIMESTAMP , PRIMARY KEY (K1));
Query OK, 0 rows affected (0.011 sec)

MariaDB [testdb]> DESCRIBE SQL_Error_Record;
+------------+--------------+------+-----+---------------------+-------------------------------+
| Field      | Type         | Null | Key | Default             | Extra                         |
+------------+--------------+------+-----+---------------------+-------------------------------+
| K1         | int(11)      | NO   | PRI | NULL                | auto_increment                |
| SQL_STATUS | varchar(50)  | YES  |     | NULL                |                               |
| ERROR_NUM  | varchar(50)  | YES  |     | NULL                |                               |
| CONTENT    | varchar(500) | YES  |     | NULL                |                               |
| T1         | timestamp    | NO   |     | current_timestamp() | on update current_timestamp() |
+------------+--------------+------+-----+---------------------+-------------------------------+
5 rows in set (0.005 sec)

建立 Procedure.

DROP PROCEDURE IF EXISTS handlerdemo;

DELIMITER //

CREATE PROCEDURE handlerdemo ( )
     BEGIN
       DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' 
       BEGIN  
         GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,@errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
         INSERT INTO testdb.SQL_Error_Record (SQL_STATUS , ERROR_NUM , CONTENT) VALUE(@sqlstate, @errno, @text);
       END;
       SET @x = 1;
       INSERT INTO test VALUES (1);
       SET @x = 2;
       INSERT INTO test VALUES (1);
       SET @x = 3;
     END;
     //

DELIMITER ;

Procedure 說明,主要差別就在 DECLARE HANDLER 這一段:

  • DECLARE CONTINUE HANDLER FOR SQLSTATE ‘23000’
    當發生錯誤 23000 (ER_DUP_KEY : Can’t write; duplicate key in table ‘%s’) , 做以下 BEGIN END; 裡面的敘述.
  • GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,@errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
    使用 GET DIAGNOSTICS ,抓取目前的 RETURNED_SQLSTATE, MYSQL_ERRNO 與 MESSAGE_TEXT 資訊,並儲存到這三個變數 @sqlstate ,@errno , @text.

    GET [CURRENT] DIAGNOSTICS
{
    statement_property
    [, statement_property] ... 
  | CONDITION condition_number
    condition_property
    [, condition_property] ...
}

statement_property:
    variable = statement_property_name

condition_property:
    variable  = condition_property_name

statement_property_name:
    NUMBER
  | ROW_COUNT

condition_property_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

先清除 test 資料表裡面的資料.

MariaDB [testdb]> TRUNCATE test;
Query OK, 0 rows affected (0.012 sec)

測試一下,錯誤的確被記錄到 SQL_Error_Record 資料表.

MariaDB [testdb]> CALL handlerdemo( );
Query OK, 2 rows affected (0.005 sec)

MariaDB [testdb]> SELECT * FROM SQL_Error_Record;
+----+------------+-----------+---------------------------------------+---------------------+
| K1 | SQL_STATUS | ERROR_NUM | CONTENT                               | T1                  |
+----+------------+-----------+---------------------------------------+---------------------+
|  1 | 23000      | 1062      | Duplicate entry '1' for key 'PRIMARY' | 2020-06-03 15:31:50 |
+----+------------+-----------+---------------------------------------+---------------------+
1 row in set (0.001 sec)
沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步了解 Akismet 如何處理網站訪客的留言資料。