Error stack buffer for MySQL5.7

Error stack buffer for MySQL5.7
What is an error buffer stack? For example, execute 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

Where is the error message indicated by the code 1292 stored? It is saved in the Error Buffer stack and called diagnostics area in MySQL. This concept has been updated only in MySQL5.7.
Before MySQL5.5, to obtain data in this region, you can only obtain data through the c api, which cannot be retrieved from the SQL layer. MySQL5.5 first introduced this concept.
After MySQL5.6 is released, you can not only search this region, but also re-encapsulate it to obtain the desired data. However, this area can only save the error code once and can be easily reset.
After MySQL5.7 is released, it is easier to search this region and put the data in a STACK. The Reset conditions are looser. The following is an example.

The structure of the example table is as follows,
Create table 't_ datetime '(
'Id' int (11) not null,
'Log _ time' timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
'End _ time' datetime not null,
Primary key ('id ')
) ENGINE = InnoDB default charset = utf8;

The log table used to record error data.
Create table tb_log (errorno int, errortext TEXT, error_timestamp DATETIME );

In the MySQL5.6 environment, I want to write a complicated piece of 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), IN f_end_time VARCHAR (255) begin declare done1 tinyint default 0; -- save the Boolean value for whether an exception occurs. DECLARE I TINYINT DEFAULT 1; DECLARE v_errcount INT DEFAULT 0; -- get the number of error data entries once DECLARE v_errno INT DEFAULT 0; -- get the Error Code DECLARE v_msg TEXT; -- Get error details declare continue handler for sqlexception -- Define an exception processing block begin set done1 = 1; -- if an exception occurs, SET it 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 -- Record the error data to the tb_log table. SET I = 1; WHILE I <= v_errcount DO 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;

After MySQL5.7 is released, you can now 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), IN f_end_time VARCHAR (255) begin declare I TINYINT DEFAULT 1; DECLARE v_errcount INT DEFAULT 0; -- get the number of error data entries at a time DECLARE v_errno INT DEFAULT 0; -- get Error Code DECLARE v_msg TEXT; -- get error details declare continue handler for sqlexception -- Define an exception processing block BEGIN 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_msg = 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;

Run the following command:
Mysql> call sp_do_insert (2, '4', '5 ');
Query OK, 1 row affected (0.01 sec)




To retrieve the data of 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)

To sum up, if you first use the diagnostics area, it is best to write code in the stored procedure to encapsulate the SQL statement.