Error stack buffer for MySQL5.7

What is the error buffer stack? A very simple example, such as executing the following statement:
mysql> INSERT into T_datetime VALUES (2, ' 4 ', ' 5 ');
ERROR 1292 (22007): Incorrect datetime value: ' 4 ' for column ' Log_time ' at row 1


Above 1292 where is the error message indicated by this code stored? There is an error buffer stack, called diagnostics area in MySQL. With regard to this concept, it has been MySQL5.7 to get a definite update.
Before MySQL5.5, to get the data of this area, it can only be obtained through the API of C, it is not retrieved from the SQL level. MySQL5.5 first introduced this concept.
After the release of MySQL5.6, not only can the area be retrieved, but it can also be re-encapsulated to get the data we want. But this area is still only able to save the error code once, it is easy to be reset.
After the MySQL5.7 is released, it is easier to retrieve the area and put the data into a stack where the reset condition is more relaxed. Here's an example to illustrate this.


The sample table structure is as follows
CREATE TABLE ' T_datetime ' (
' id ' int (one) is not NULL,
' Log_time ' timestamp not NULL the DEFAULT current_timestamp on UPDATE current_timestamp,
' End_time ' datetime not NULL,
PRIMARY KEY (' id ')
) Engine=innodb DEFAULT Charset=utf8;




Log table used to log error data.
CREATE TABLE tb_log (errorno int,errortext text,error_timestamp DATETIME);






In the MySQL5.6 environment, I would like to write a complicated code to get the error message.

DELIMITER $ $USE ' new_feature ' $ $DROP PROCEDURE IF EXISTS ' Sp_do_insert ' $ $CREATE definer= ' root ' @ ' localhost ' PROCEDURE ' sp _do_insert ' (in f_id int,in f_log_time varchar (255), F_end_time varchar (255)) BEGIN DECLARE done1 TINYINT DEFAULT 0;  --A Boolean value that saves whether an exception occurred.  DECLARE i TINYINT DEFAULT 1;  DECLARE V_errcount INT DEFAULT 0; --Gets the number of error data bars DECLARE V_errno INT DEFAULT 0; --Get error code DECLARE v_msg TEXT; --Get Error details DECLARE CONTINUE HANDLER for SQLEXCEPTION--Define an exception handling block BEGIN SET done1 = 1;    --An exception occurred, set to 1.    Get diagnostics V_errcount = number;    SET v_msg = ';        While I <= v_errcount do GET diagnostics CONDITION I v_errno = mysql_errno, v_msg = Message_text;        SET @stmt = CONCAT (' Select ', V_errno, ', ' ', ' v_msg, ' ', ' ', ' Now () ', ' into @errno ', I, ', @msg ', I, ', ' @log_timestamp ', I, '; ');        PREPARE S1 from @stmt;      EXECUTE S1;    SET i = i + 1;    END while;  DROP PREPARE S1;    END;  INSERT into T_datetime (id,log_time,end_time) VALUES (f_id,f_log_time,f_end_time);  IF done1 = 1 Then-logs the error data to the table Tb_log.      SET i = 1;        While I <= v_errcount does SET @stmt = CONCAT (' INSERT into Tb_log ');        SET @stmt = CONCAT (@stmt, ' Select @errno ', I, ', @msg ', I, ', @log_timestamp ');        PREPARE S1 from @stmt;        EXECUTE S1;      SET i = i + 1;      END while;    DROP PREPARE S1; END IF; end$ $DELIMITER;

Once the MySQL5.7 is released, it's time to streamline my code.

DELIMITER $ $USE ' new_feature ' $ $DROP PROCEDURE IF EXISTS ' Sp_do_insert ' $ $CREATE definer= ' root ' @ ' localhost ' PROCEDURE ' sp _do_insert ' (in f_id int,in f_log_time varchar (255), F_end_time varchar (255)) BEGIN  DECLARE i TINYINT DEFAULT 1;  DECLARE V_errcount INT DEFAULT 0; --Gets the number of error data bars  DECLARE V_errno INT DEFAULT 0;  --Get error code  DECLARE v_msg TEXT;--Get error details  DECLARE CONTINUE HANDLER for SQLEXCEPTION  --Define an exception handling block  begin< C8/>get Stacked Diagnostics v_errcount = number;    While I <= v_errcount    do      GET Stacked Diagnostics CONDITION I--Save the error data in the variable        v_errno = Mysql_errno, v_m sg = message_text;        INSERT into Tb_log VALUES (V_errno,v_msg,now ());      SET i = i + 1;    END while;  END;    INSERT into T_datetime (id,log_time,end_time) VALUES (f_id,f_log_time,f_end_time);  end$ $DELIMITER;

Now to do the following:
Mysql> Call Sp_do_insert (2, ' 4 ', ' 5 ');
Query OK, 1 row affected (0.01 sec)




To retrieve the data for the table Tb_log.

Mysql> SELECT * from tb_log\g*************************** 1. Row ***************************        errorno:1265      errortext:data truncated for column ' Log_time ' at row 1error_ TIMESTAMP:2015-11-17 11:53:10*************************** 2. Row ***************************        errorno:1265      errortext:data truncated for column ' End_time ' at row 1error_ TIMESTAMP:2015-11-17 11:53:10*************************** 3. Row ***************************        errorno:1062      errortext:duplicate entry ' 2 ' for key ' PRIMARY ' Error_timestamp: 2015-11-17 11:53:103 rows in Set (0.00 sec)

In conclusion, if diagnostics area is used first, it is best to write code in the stored procedure to encapsulate SQL.

Error stack buffer for MySQL5.7